Soru – Cevap: Oracle Database’ i Kaç Data File’ı kullanabilir ?

Potansiyel olarak 65532 adet data file’ ı oluşturabiliriz. Ama gerçekçi olmak gerekirse, bu rakamlara ulaşmak günümüz için imkansız gibi gözüküyor. Tabi bir de şöyle bir durum var, zamanında ip adresleri içinde gayet yeterli denmiş. Şimdi durumun ne olduğunu biliyoruz.

SQL*PLUS ile instance durumunu kontrol etmek

” select status from V$instance ” komutu ile sql plus arabiriminden oracle instanceınızın durumunu kontrol edebilirsiniz. 

ERROR at line 1:

ORA-01034: ORACLE not available 

mesajı alırsanız bu instanceın down olduğunu gösterir.

Environment variable ORACLE_SID not defined. Please define it. Hatası

Bu hata aslında kendini açıklamaktadır. Kullandığınız ortamda oracle sidi tanımlı değildir. Örnek vermek gerekirse, dbconsole hizmetini başlatmak istediğinizde command line a ” emctl start dbconsole ” yazarsanız bu hatayı alırsınız. Engellemenin yolu ise, hata mesajında da açıklandığı gibi  oracle sidi ni tanımlamak. Bu işlemi ” set ORACLE_SID=orcl ” komutu ile yapabiliriz. Unutmayın, orcl yerine sizin sidiniz gelmelidir.

Soru – Cevap: Mevcut Oturumumun NLS (National Language Support ) Ayarlarını Nasıl Görüntülerim ?

 select * from nls_session_parameters; sorgusunu çalıştırarak dil ve lokasyon ile ilgili tüm ayarları görüntüleyebilirsiniz.

Tüm Built-in Oracle Fonksiyonları Görüntülemek

Oracle, built-in olarak bir çok fonksiyon içerir. Bu fonksiyonların listesini aşağıdaki sorgu ile görebilirsiniz.

select distinct      object_name
from                         all_arguments
where                      package_name = ‘STANDARD’;

Fonksiyonlardan bazıları:

 

ABS(n)        Absolute value of number
ACOS(n)       arc cosine of n
ADD_MONTHS(date,num_months)
              Returns date + num_months
ASCII(char)   Converts char into a decimal ascii code
ASIN(n)       arc sine of n.
ATAN(n)       arc tangent of n.
ATAN2(n.m)    arc tangent of n and m.
AVG([DISTINCT]n)
              Averge value of 'n' ignoring NULLs

BETWEEN value AND value
              Where 'x' between 25 AND 100
BFILENAME('directory','filename')
              Get the BFILE locator associated with a physical LOB binary file.

CASE          Group the data into sub-sets.
CEIL(n)       Round n up to next whole number.
CHARTOROWID(char)
              Converts a Char into a rowid value.
CHR(n)        Character with value n
CONCAT(s1,s2) Concatenate string1 and string2
CONVERT(char_to_convert, new_char_set, old_char_set)
              Convert character sets
COS(n)        Cosine of number
COSH(n)       Hyperbolic Cosine of number
COUNT(*)      Count the no of rows returned
COUNT([DISTINCT] expr)
              Count the no of rows returned by expr

DECODE        IF x THEN return y ELSE return z
DENSE_RANK    Calculate the rank of a value in a group
DEREF(e)      Return the object reference of argument e.
DUMP(expr,fmt[,start,length])
              Convert to dec/hex/oct and display char set

EMPTY_BLOB    Return an empty LOB locator (use to empty a column or variable)
EMPTY_CLOB    Return an empty LOB locator (use to empty a column or variable)
EXISTS        Return TRUE if a subquery returns at least one row
EXP(n)        Exponential (e to 'n'th power)

FLOOR(n)      Round n down to the next whole number.

GREATEST(expression, expression...)
              Returns the largest in a list of expressions.
GROUPING      Grouping for superaggregate rows=NULL
              (see GROUP BY ROLLUP/CUBE)

HEXTORAW(char) Convert char containing hex digits to a raw value.

IN (list of comma separated values)
              Effectively a shorthand for ['x' = y OR 'x' = z...] i.e.
              Where 'x' IN ('sales','marketing','recruitment')
INITCAP(char) String with Initial Capitals
INSTR(str, chars[,s[,n]])
              Find the 'n'th occurence of 'chars' in 'str'
              Starting at position 's'
              n and s default to 1
INSTRB (str, chars[,s[,n]])
              The same as INSTR, except that 's' and the return value are expressed in bytes,
              use for double-byte char sets
IS [NOT] NULL Check for NULL (empty) values
              Where 'x' IS NULL;

LAST_DAY(date)Returns the last day of month in Date
LEAST(expression, expression...)
              Returns the smallest in a list of expressions
LENGTH(char)  Returns the number of characters in char
LENGTHB(char) Returns the number of bytes in char (use for double-byte char sets)
LIKE wildcard/value
              Wildcards are [% = any chars] [ _ = any one char]
              Where 'x' LIKE 'smith%' [will find 'Smithson']
              Where 'x' LIKE 'smith_' [will find 'Smithy']
LN(n)         Natural Log of n, where n>0
LOG(b,n)      log of n, base b
LOWER(char)   Returns character string in lowercase
LPAD(char, n[,PadChar])
              Left Pad char with n spaces [or PadChars]
LTRIM(char[,set])
              Left Trim char - remove leading spaces [or char set]

MAKE_REF(table,key)
              Create a REF to a row of an OBJECT view/table
MAX([DISTINCT] expr)
              Maximum value returned by expr
MIN([DISTINCT] expr)
              Minimum value returned by expr
MOD(x,y)      Remainder of x divided by y
MONTHS_BETWEEN(end_date, start_date)
              Number of months between the 2 dates (integer)

NEW_TIME(date, zone1, zone2)
              Convert between GMT and US time zones (but not CET)
NEXT_DAY(date,day_of_week)
              '12-OCT-01','Monday' will return the next Mon after 12 Oct
NLS_CHARSET_DECL_LEN (bytecount,charset)
              Returns the declaration width (no of chars) of an NCHAR column
NLS_CHARSET_ID(varchars)
              Returns the char set ID given a charset name
NLS_CHARSET_NAME(charset_id)
              Returns the char set name given a charset id
NLS_INITCAP(char[,'NLS_SORT = sort_sequence'])
              Returns char in Initial Caps, using an NLS sort_sequence
              either the session default or specified directly
NLS_LOWER(char[,'NLS_SORT = sort_sequence'])
              Returns char in lower case, using an NLS sort_sequence
              either the session default or specified directly
NLSSORT(char[,'NLS_SORT = sort_sequence'])
              Return the string of bytes used to sort char, using an NLS sort_sequence
              either the session default or specified directly
NLS_UPPER(char[,'NLS_SORT = sort_sequence'])
              Returns char in UPPER case, using an NLS sort_sequence
              either the session default or specified directly
NVL(expression, value_if_null)
              If expression is null, returns value_if_null; if expression is not null, returns expression.
              The arguments can have any datatype (Oracle will perform implicit conversion where needed).
PERCENT_RANK  Calculate the percent rank of a value in a group.
POWER(m,n)    m raised to the nth power

RANK          Calculate the rank of a value in a group
RAWTOHEX(raw) Convert raw to a character value containing its hex equivalent
REF(table_alias)
              Returns a REF value for an object instance (bound to the variable or row.)
              The table alias (correlation variable) is associated with
              one row of an object table or an object view in an SQL statement.
REFTOHEX(ref) Convert ref (object type) to a char value containing its hex equivalent.
REPLACE(char, search_str[, replace_str])
              ANSI alternative to decode() Replace every occurrence of search_str
              with replace_str, replace_str defaults to null.
ROUND(n,d)    n rounded to d decimal places (d defaults to 0)
ROUND(date,fmt)
              date rounded to fmt
ROWIDTOCHAR(rowid)
              Convert a rowid value to VARCHAR2
ROW_NUMBER    Assign a unique number to each row of results.
RPAD(char, n[,PadChar])
              Right Pad char with n spaces [or PadChars]
RTRIM(char[,set])
              Right Trim char - remove trailing spaces [or char set]

SIGN(n)       positive = 1, zero = 0, negative = -1
SIN(n)        Sine of n in Radians
SINH(n)       Hyperbolic Sine of n in Radians
SOUNDEX(char) Returns a char value representing the sound of the words
SQRT(n)       Square Root (returns NULL for negative no's)
STDDEV([DISTINCT] n)
              Standard deviation of n
SUBSTR(char, s[,l])
              A substring of char, starting at character s, length l
SUBSTRB(char, s[,l])
              A substring of char, starting at character s, length l
              The same as SUBSTR, except that 's', 'l' and the return value are expressed in bytes,
              use for double-byte char sets
SUM([DISTINCT] n)
              Sum of values of n, ignoring NULLs
SYS_CONTEXT('namespace','attribute_name')
              Examine the package associated with the context namespace
              Possible attributes are: NLS_TERRITORY, NLS_CURRENCY, NLS_CALENDAR
              NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, SESSION_USER, CURRENT_USER
              CURRENT SCHEMAID,SESSION_USERID, CURRENT_USERID, CURRENT_SCHEMA
              note: CURRENT_USER may be different from SESSION_USER within a stored procedure
              (e.g an invoker-rights procedure).
SYS_CONTEXT ('USERENV','IP_ADDRESS')
SYS_GUID()    Returns a globally unique identifier (16 byte RAW value)
SYSDATE       The current system date & time

TAN(n)        Tangent of n in Radians
TANH(n)       Hyperbolic tangent of n in Radians
TO_CHAR       Convert to character String
TO_DATE       Convert to date value
TO_LOB(long)  Convert LONG values to CLOB or NCLOB values
              or convert LONG RAW values to BLOB values
              Use only as part of an "INSERT INTO ... SELECT..." subquery.
TO_MULTI_BYTE(char)
              Convert single-byte char to multi-byte char.
TO_NUMBER     Convert to numeric format
TO_SINGLE_BYTE(char)
              Convert multi-byte char to single-byte char.
TRANSLATE('char','search_str','replace_str')
              Replace every occurrence of search_str with replace_str
              unlike REPLACE() if replace_str is NULL the function returns NULL
TRANSLATE (text USING charset)
              Convert text into a specific character set
              Use this instead of CONVERT() if either the input or output datatype
              is NCHAR or NVARCHAR2.
TRIM(LEADING|TRAILING|BOTH trim_char FROM trim_source)
              Returns trim_source as a VARCHAR2 with leading/trailing items removed
              trim_char defaults to a space ' ' but may be numeric or char 'A'
TRUNC(i,d)    i truncated to d decimal places (d defaults to 0)
TRUNC(date,fmt)
              date truncated to nearest fmt

UID           User id - unique number
UPPER(char)   Returns Chars in uppercase
USER          Returns the current Username
USERENV('option')
              Can return any of the options: ENTRYID, SESSIONID,
              TERMINAL, LANGUAGE, ISDBA, LANG, INSTANCE, CLIENT_INFO

VALUE(correlation_variable)
              Return the object instance for a row of an object table
              as associated with the correlation_variable (table alias)
VARIANCE([DISTINCT] n)
              Variance of n, ignoring NULLs
VSIZE(expr)   Value Size - returns the number of bytes used by each row of expr.

iSQL*Plus Sistem Değişkenleri

iSQL*Plus çalıştığımız ortamı kontrol etmek için bir takım sistem değişkenleri kullanır. Bu değişkenlerin bir çoğunun değerini bizde oturumuz boyunca değiştirebiliriz. Örnek olarak  “ SET VERIFY ON  ” diyerek substitution değişkeninin değerlerini önce ve sonra şeklindegösterebiliriz. Bu ve benzeri bir çok sistem değişkenini ve durumlarını “ SHOW ALL ” komutu ile göstermek mümkün.

Oracle Enterprise Manager Console HTTP Port ve iSQL*Plus HTTP Port Numaraları

Gelen bir soru üzerine sürümlere göre farklılık gösteren bu port numaralarını her sürüm için yazmak yerine, nereden görebileceğinizi yazmaya karar verdim.  $ORACLE_HOME/install/portlist.ini pathinde bulunan portlist.ini dosyasını text editörü ile açarsanız, ilgili port numaralarını görebilirsiniz.