Oracle in World: June 2008

Monday, June 30, 2008

Datetime and Interval Datatypes Description in Oracle

DateTime DataTypes
--------------------------------------
1)DATE Datatype
----------------------------------
•To store date and time in a table you can use DATE datatype in oracle.
•To insert DATE datatype in a table you have to use either date value as a literal or convert by TO_DATE funcation.
An example,

SQL> create table a_t (a date);
Table created.
As a literal,
SQL> insert into a_t values ( DATE '11-02-07');
1 row created.
Using TO_DATE function,
SQL> insert into a_t values (to_date('10-02-07','DD-MM-yy'));
1 row created.

SQL> select * from a_t;
A
---------
07-FEB-11
10-FEB-07

2)TIMESTAMP Datatype
---------------------------------------
•It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values.
The fields are discussed in http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes-in.html
•It is an extension of DATE datatype.
•To convert character data to timestamp values use TO_TIMESTAMP function.
3)TIMESTAMP WITH TIME ZONE Datatype
------------------------------------------------
•TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone offset in its value.
•This datatype is really useful for collecting and evaluating date information across geographic regions.

4)TIMESTAMP WITH LOCAL TIME ZONE Datatype
----------------------------------------------------------
•TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that includes a time zone offset in its value.
•This datatype differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone.

Interval DataTypes
-----------------------------------------
1)INTERVAL YEAR TO MONTH Datatype
---------------------------------------------
•This datatype stores a period of time using the YEAR and MONTH datetime fields.

•When we want to store the difference between two datetime values in terms of year and months then we can use this datatype.

2)INTERVAL DAY TO SECOND Datatype
---------------------------------------------------
•This datatype stores a period of time in terms of days, hours, minutes, and seconds.

•This datatype is useful for representing the actual difference between two datetime values.

Examples:
------------------------
SQL>CREATE TABLE with_date_interval (date_dt DATE, timest_dt TIMESTAMP, timest_wtz TIMESTAMP WITH TIME ZONE, timest_wltz TIMESTAMP WITH LOCAL TIME ZONE,
int_1 INTERVAL YEAR TO MONTH, int_2 INTERVAL DAY TO SECOND);
Table created.

SQL> desc with_date_interval;
Name Null? Type
----------------------------------------- -------- ----------------------------
DATE_DT DATE
TIMEST_DT TIMESTAMP(6)
TIMEST_WTZ TIMESTAMP(6) WITH TIME ZONE
TIMEST_WLTZ TIMESTAMP(6) WITH LOCAL TIME
ZONE
INT_1 INTERVAL YEAR(2) TO MONTH
INT_2 INTERVAL DAY(2) TO SECOND(6)

SQL>insert into with_date_interval values(DATE '11-01-08', SYSTIMESTAMP, SYSTIMESTAMP, SYSDATE,INTERVAL '10-2' YEAR(3) TO MONTH, INTERVAL '7 8:10:10.100' DAY TO SECOND(3)) ;
1 row created.

SQL> select * from with_date_interval;

DATE_DT TIMEST_DT TIMEST_WTZ TIMEST_WLTZ INT_1 INT_2
-------------------- --------------------
08-JAN-11 01-JUL-08 02.19.20.238715 AM 01-JUL-08 02.19.20.238715 AM -04:00 01-JUL-08 02.19.20.000000 AM +10-02 +07 08:10:10.100000

Related Documents:
------------------------
Datetime and Interval Datatypes Fields and Values in Oracle

ROWID and UROWID Datatype in Oracle

ROWID Datatype
-------------------------
•Each row stored in a table has an address. You can see a row address by querying ROWID pseudo column. Like,
SQL> select rowid from with_lob;
ROWID
------------------
AAANp9AALAAADDPAAA

ROWIDs can be restricted Rowids which forms the format as block.row.file and can be Extended Rowids which forms the format as the data in the restricted rowid plus a data object number. The data object number can be found by querying from USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS like
SQL> select DATA_OBJECT_ID from dba_objects;

UROWID Datatype
------------------------
The rows of some tables have addresses that are not physical or permanent or were not generated by Oracle Database. Like, the row addresses of index-organized tables are stored in index leaves, which can move.

Oracle uses universal rowids (urowids) to store the addresses of index-organized and foreign tables. Index-organized tables have logical urowids and foreign tables have foreign urowids.

Large Object (LOB) Datatypes with Example.

•The LOB datatypes are used to store large and unstructured data such as text, image, video, and spatial data.

•Oracle can store large objects in both internally and externally.

•Oracle built-in LOB datatypes BLOB, CLOB, and NCLOB store data internally and built-in BFILE datatype store data externally.

•The size of BLOB, CLOB, and NCLOB data can be up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage). If LOBs are stored in 8K block sized tablespace and if you have used the default value of the CHUNK parameter of LOB storage when creating a LOB column then maximum size of LOB can be =(4GB-1)*8KB

•BFILE data can be up to power(2,31)-1 bytes.

BFILE Datatype
-----------------------
•Suppose a LOB file is exist in OS file system that is outside of oracle database. Then to access of that file you will use BFILE datatype.

•A BFILE column or attribute stores a BFILE locator, which serves as a pointer to the LOB file on the OS file system. The locator maintains the directory name and the filename.

•The BFILE datatype enables read-only support of large binary files. The column defined as BFILE can't be modified or can't be replicated.

An example is here about how we can access LOB data externally by BFILE datatype.
How to Insert Blob data(image, video) into oracle and determine LOB size

BLOB Datatype
--------------------------
•To store binary file internally into a database we can use BLOB datatype.
•The column defined as BLOB datatype have fully transactional support that is they can modified, committed, rolled back and can be replicated.

An example is here about how we can store LOB data in to oracle database using BLOB.
How to Insert Blob data(image, video) into oracle and determine LOB size

CLOB Datatype
----------------------------
•To store a large character of strings we can use CLOB datatype.
•The column defined as CLOB datatype have fully transactional support that is they can modified, committed, rolled back and can be replicated.

NCLOB Datatype
-----------------------------------
•The NCLOB datatype stores Unicode data. Both fixed-width and variable-width character sets are supported, and both use the national character set.

•The column defined as NCLOB datatype have fully transactional support that is they can modified, committed, rolled back and can be replicated.

Example of CLOB and NCLOB
-----------------------------------
SQL> CREATE TABLE WITH_LOB(clob_dt CLOB, nclob_dt NCLOB);
Table created.

SQL> insert into with_lob values('This is Clob','This is Nclob');
1 row created.

SQL> select * from with_lob;
CLOB_DT
--------------------------------------------------------------------------------
NCLOB_DT
--------------------------------------------------------------------------------
This is Clob
This is Nclob

Related Documents:
--------------------------------
How to Insert Blob data(image, video) into oracle and determine LOB size

Advantages of LOB against LONG- Feature of LOB datatype

How to move LOB data to another tablespace
How to Insert Blob data(image, video) into oracle BLOB size
Char, Varchar2, Long etc Datatype Limits in Oracle

Datetime and Interval Datatypes Fields and Values in Oracle

•In oracle, the datetime datatypes are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. The interval datatypes are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.

•Both Datetime and Interval datatypes in oracle are made up of fields. These fields determines the value of these datatypes.

•Database and sesion time zone can be verified by querying the built-in SQL functions DBTIMEZONE and SESSIONTIMEZONE.

•If the time zones have not been set manually, Oracle Database uses the OS time zone by default. If the OS time zone is not a valid Oracle time zone, then Oracle uses UTC as the default value.

•From now on datetime datatypes will be referred as datatimes and interval datatypes will be referred as intervals.

Datetime Fields and Values
---------------------------------------
YEAR: The valid value of YEAR field are from -4712 to 9999 (excluding year 0) in case of datetimes and in case of intervals any integer values are valid.

MONTH:The valid value of MONTH field are from 01 to 12 in case of datetimes and from 0 to 11 in case of intervals.

DAY:The valid value of DAY field are from 0 to 31 in case of datetimes and in case of intervals any integer values are valid.

HOUR:Valid value ranges from 00 to 23.

MINUTE:Valid value ranges from 00 to 59.

SECOND:Valid value ranges from 00 to 59.9(n). It is not applicable for DATE datatype.

TIMEZONE_HOUR:Only applicable for datetimes (except DATE and TIMESTAMP) and valid value ranges from -12 to 14.

TIMEZONE_MINUTE:Only applicable for datetimes (except DATE and TIMESTAMP) and valid value ranges from 0 to 9.

TIMEZONE_REGION:Only applicable for datetimes except DATE and TIMESTAMP) and to know the valid values query from V$TIMEZONE_NAME,
SQL> select distinct TZNAME from V$TIMEZONE_NAMES;

TIMEZONE_ABBR:Only applicable for datetimes except DATE and TIMESTAMP) and to know the valid values query from V$TIMEZONE_NAME,
SQL> select distinct TZABBREV from V$TIMEZONE_NAMES;
DateTime DataTypes
--------------------------------------
1)DATE Datatype
2)TIMESTAMP Datatype
3)TIMESTAMP WITH TIME ZONE Datatype
4)TIMESTAMP WITH LOCAL TIME ZONE Datatype

Interval DataTypes
-----------------------------------------
1)INTERVAL YEAR TO MONTH Datatype
2)INTERVAL DAY TO SECOND Datatype

LONG Datatype and its restriction in Oracle

•Before going into detail oracle strongly recommend not to use LONG datatype in oracle. LONG datatype is remained for backward compatibility. If you have LONG datatype in your database then convert it to LOB data type using TO_LOB function which is discussed on How to Convert LOB .

•LONG datatype store variable-length character strings containing up to 2 gigabytes -1, or power(2,31)-1 bytes.

The use of LONG datatype is subject to the following restriction.

•A table can contain only one LONG column.

•You cannot create an object type with a LONG attribute.

•LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).

•Index can't be created on LONG columns.

•In regular expressions LONG datatype can't be specified.

•Stored function can't return a LONG value.

•You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.

•LONG and LONG RAW columns can't be replicated.

•All LONG columns, updated tables, and locked tables must be located on the same database within an SQL statement.

•LONG column can't appear in GROUP BY, ORDER BY clause, UNIQUE / DISTINCT operator or CONNECT BY clause in SELECT statements.

•LONG columns cannot appear in these parts of SQL statements
ALTER TABLE ... MOVE statement.
SELECT lists in subqueries in INSERT statements
SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
SQL built-in functions, expressions, or conditions

Example:
----------------------
Create table with_long (long_dt LONG);
Table created.

SQL> insert into with_long values('This is a long datatype');
1 row created.

SQL> select * from with_long;
LONG_DT
--------------------------------------------------------------------------------
This is a long datatype

Sunday, June 29, 2008

Numeric Datatype in Oracle with Examples

1)NUMBER Datatype
-----------------------------------------
•The NUMBER datatype can store numeric values ranges from 1.0 xpower(130,-10) to (but not including) 1.0 x power(10,126).

•NUMBER value requires from 1 to 22 bytes.

•To store a fixed-point number use following form NUMBER (p,s) where p is the precision which specifies the total number of significant decimal digits and it can be 39 or 40. s is the scale which specifies the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127.

•The precision p is counted as total number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit.

•If actual data is 123.67 and you declare as NUMBER(10,1) then it is stored as 123.7

•To store an integer use NUMBER(p) where scale is considered as 0.

•If you use only NUMBER without any precision and scaling value then oracle uses the maximum range and precision of NUMBER.

2)BINARY_FLOAT
----------------------------
•BINARY_FLOAT differ from NUMBER datatype in the way the values are stored internally by oracle database.

•BINARY_FLOAT is a 32-bit, single-precision floating-point number datatype.

•BINARY_FLOAT value requires 5 bytes, including a length byte.

•Maximum positive value for BINARY_FLOAT datatype is 3.40282E+38F and Minimum is 1.17549E-38F.

3)BINARY_DOUBLE
----------------------------------
•BINARY_DOUBLE is a 64-bit, double-precision floating-point number datatype.

•Each BINARY_DOUBLE value requires 9 bytes, including a length byte.

•Maximum positive value for BINARY_DOUBLE datatype is 1.79769313486231E+308 and Minimum is 2.22507485850720E-308.

SQL>CREATE TABLE WITH_NUMBER(number_dt NUMBER, num_dt_1 NUMBER(3), num_dt_2 NUMBER(6,7), num_dt_4 NUMBER(3,-2),num_dt_5 NUMBER(4,5), bd_dt BINARY_DOUBLE, bf_dt BINARY_FLOAT);
Table created.

SQL> insert into WITH_NUMBER values(12.7,12.7,12.7,12.7,12.7,12.7,12.7);
insert into WITH_NUMBER values(12.7,12.7,12.7,12.7,12.7,12.7,12.7)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

As end column specification is NUMBER(6,7) and we tried to insert 12.7 so error comes.

SQL> desc WITH_NUMBER
Name Null? Type
----------------------------------------- -------- ----------------------------
NUMBER_DT NUMBER
NUM_DT_1 NUMBER(3)
NUM_DT_2 NUMBER(6,7)
NUM_DT_4 NUMBER(3,-2)
NUM_DT_5 NUMBER(4,5)
BD_DT BINARY_DOUBLE
BF_DT BINARY_FLOAT

SQL> insert into WITH_NUMBER values(12.7,12.7,12.7e-8,121.7,1211e-9,12.7,12.7);
1 row created.


SQL> select * from with_number;
NUMBER_DT   NUM_DT_1   NUM_DT_2   NUM_DT_4   NUM_DT_5      BD_DT      BF_DT
---------- ---------- ---------- ---------- ---------- ---------- ----------
     12.7         13   .0000001        100          0  1.27E+001  1.27E+001

Related Documents
Types of SQL function in Oracle
Numeric EXP FLOOR LN LOG MOD NANVL POWER functions
List of Analytic functions in oracle
List of Aggregate functions in Oracle
Numeric ABS ACOS ASIN ATAN ATAN2 BITAND CEIL COS COSH functions
List of Single row functions in Oracle

Character Datatypes with example in Oracle

1)CHAR Datatype
---------------------------------------
•The CHAR datatype in oracle specifies fixed length character string. That is if you specify datatype as COL1 CHAR(10) then regardless of value entered in column COL1 the length of the value will be 10 bytes.

•In fact if you insert a value that is shorter than the column length, then Oracle blank pads (add spaces after the text) the value to column length. If you try to insert a value that is larger than the column length, then Oracle returns an error.

•The default length for a CHAR datatype column is 1 byte and the maximum allowed is 2000 bytes.

•The column length for CHAR datatype can be specified both in bytes and characters. By default if you just CHAR(10) then 10 bytes of column size is specified. If you want to specify the size of CHAR datatype in characters then declare as CHAR(10 CHAR). Then the size of the CHAR datatypes column varies between 1 to 4 bytes based on the database character sets.

•The BYTE and CHAR qualifiers override the semantics specified by the NLS_LENGTH_SEMANTICS parameter, which has a default of byte semantics.

2)NCHAR Datatype
--------------------------------------------
•When a column is defined with NCHAR datatype then column length is defined with characters.

•It is a Unicode-only datatype.

•The maximum column size allowed is 2000 bytes.

•If you insert a value that is shorter than the column length, then Oracle blank pads (add spaces after the text) the value to column length.

•CHAR value can't be inserted into an NCHAR column,and also NCHAR value can't be inserted into a CHAR column.

3)NVARCHAR2 Datatype
-----------------------------------------------
•The NVARCHAR2 datatype is a Unicode-only datatype.

•When you create a table with an NVARCHAR2 column, you specify the maximum number of characters it can hold.

•The maximum column size allowed is 4000 bytes.

4)VARCHAR2 Datatype
----------------------------------------------------
•When you create a column with VARCHAR2 datatype, you specify the maximum number of bytes or characters of data that it can hold.

•This minimum length of VARCHAR2 datatype must be at least 1 byte, although the actual string stored is permitted to be a zero-length string ('').

•The maximum length of VARCHAR2 data is 4000 bytes.

5)VARCHAR Datatype
------------------------
•Oracle recommends not to use VARCHAR datatype. Though currently there is no difference between VARCHAR and VARCHAR2 datatype. The VARCHAR datatype is currently synonymous with VARCHAR2.

•Oracle schedule VARCHAR datatype to use separate datatype.

To know the differenece between CHAR, VARCHAR2 and VARCHAR please visit What is the difference between VARCHAR, VARCHAR2 and CHAR data types

Example:
-----------------
In the following example I used all character datatypes to create a table.

SQL> SHOW PARAMETER nls_length_semantics

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string BYTE

SQL> CREATE TABLE WITH_ALL_CHAR(char_dt CHAR, char_dt_in_char CHAR(5 CHAR), nchar_dt NCHAR(4), varchar_dt VARCHAR2(10), nvarchar2_dt NVARCHAR2(10));
Table created.

SQL> desc WITH_ALL_CHAR
Name Null? Type
----------------------------------------- -------- ----------------------------
CHAR_DT CHAR(1)
CHAR_DT_IN_CHAR CHAR(5 CHAR)
NCHAR_DT NCHAR(4)
VARCHAR_DT VARCHAR2(10)
NVARCHAR2_DT NVARCHAR2(10)

SQL> select length(CHAR_DT) CHAR_DT, length(CHAR_DT_IN_CHAR) CHAR_DT_IN_CHAR , length(NCHAR_DT) NCHAR_DT ,length(VARCHAR_DT) VARCHAR_DT, length(NVARCHAR2_DT) NVARCHAR2_DT from WITH_ALL_CHAR;

CHAR_DT CHAR_DT_IN_CHAR NCHAR_DT VARCHAR_DT NVARCHAR2_DT
---------- --------------- ---------- ---------- ------------
1 5 4 4 7

Oracle Built in Datatypes

Each column in a table/ index or each argument in a function/ procedure is associated with a datatype what represents how data will be.

Oracle has built-in datatypes.

The datatype code of a column or object attribute is returned by the DUMP function. To know more visit How can one dump or examine the exact content of a database column?

We can categories the list of oracle built in datatypes as following.

A)Character datatypes.
B)Numeric datatypes.
C)Long and Raw datatypes.
D)Date time datatypes.
E)Large Object datatypes.
F)RowID datatypes.

A)Character Datatypes.
----------------------------------------------
CHAR Datatype
NCHAR Datatype
NVARCHAR2 Datatype
VARCHAR2 Datatype
To know about these datatypes and example of these please visit
Character Datatypes with example in Oracle

B)Numeric datatypes.
---------------------------------------------
NUMBER Datatype
BINARY_FLOAT
BINARY_DOUBLE
To know about these datatypes and example of these please visit
Numeric Datatype in Oracle with Examples
C)Long and Raw datatypes.
-----------------------------------------------------------
LONG Datatype
RAW Datatype
LONG RAW Datatype

To know about these datatypes and example of these please visit
LONG Datatype in Oracle
D)Date time datatypes.
------------------------------------------------
DATE Datatype
TIMESTAMP Datatype
TIMESTAMP WITH TIME ZONE Datatype
TIMESTAMP WITH LOCAL TIME ZONE Datatype
INTERVAL YEAR TO MONTH Datatype
INTERVAL DAY TO SECOND Datatype
To know about these datatypes and example of these please visit
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes-in.html
E)Large Object datatypes.
------------------------------------------------
BFILE Datatype
BLOB Datatype
CLOB Datatype
NCLOB Datatype

To know about these datatypes and example of these please visit
Large Object (LOB) Datatypes with Example.
F)RowID datatypes.
------------------------------------------------
ROWID Datatype
UROWID Datatype

Saturday, June 28, 2008

Database Objects in Oracle

If we look for database objects in oracle then there comes two types of objects, one is schema objects that are associated with a particular schema or in other word they are owned by a database user. And the other is nonschema Objects that are not reside in a particular schema.

To know the object type in your database you can query,
SQL>select distinct object_type from dba_objects order by 1;

Schema Objects Lists
------------------------------------
APPLY
CAPTURE
CLUSTER
CONSTRAINT
CONSUMER GROUP
CONTEXT
DATABASE LINK
DIRECTORY
EVALUATION CONTEXT
FUNCTION
INDEX
INDEX PARTITION
INDEXTYPE
JAVA CLASS
JAVA DATA
JAVA RESOURCE
JOB
JOB CLASS
LIBRARY
LOB
LOB PARTITION
MATERIALIZED VIEW
OPERATOR
PACKAGE
PACKAGE BODY
PROCEDURE
PROGRAM
QUEUE
RESOURCE PLAN
RULE
RULE SET
SCHEDULE
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
TYPE BODY
UNDEFINED
VIEW
WINDOW
WINDOW GROUP
XML SCHEMA

Non-Schema Object Lists
--------------------------------------
Parameter file (PFILE) and server parameter files (SPFILEs)
Profile
Role
Rollback segment
Tablespace
User

History of SQL

What is SQL?

Thursday, June 26, 2008

Privileges and it's Category in Oracle

In your database if you are a dba then you possibly can do everything in your database like create a new user, create objects in another schema and etc. If you are a normal user then you can't create user or create objects in another schema. All these tasks are maintained by granting privilege to a user. We can define privilege in following ways,

"A privilege is a right to execute a particular type of SQL statement or to access another user's object."

Privilege can be divided into six major category.

1)System Privileges
2)Schema Object Privileges
3)Table Privileges
4)View Privileges
5)Procedure Privileges
6)Type Privileges

Wednesday, June 25, 2008

ORA-00903: Oracle Database Reserved Words

I will start this post with some experiment.

1)Create a Table named ACCESS but failed- ACCESS is Reserved Keyword.
SQL> CREATE TABLE ACCESS ( A NUMBER);
CREATE TABLE ACCESS ( A NUMBER)
*
ERROR at line 1:
ORA-00903: invalid table name

2)Select from a Table failed with ORA-00903:- Table spelling is not correct.
SQL> select * from as;
select * from as
*
ERROR at line 1:
ORA-00903: invalid table name

3)Create Table with named 1table failed with ORA-00903:- Invalid name specification.
SQL> create table 1table ( a number);
create table 1table ( a number)
*
ERROR at line 1:
ORA-00903: invalid table name

In above three cases we got the error ORA-00903. The summary of the expriment suggest that while table creation if I get ORA-00903 error then we have to look at,

•The specified table or cluster name is valid. The valid table name must be less than or equal to 30 characters.

•The table or cluster name must begin with a letter and may contain only alphanumeric characters and the special characters $, _, and #.

•The table name cannot be a reserved word.

Hopefully we already understood first two scenario. The third one need an special attention like which keywords are reserved in oracle? The reserved keyword can be found by querying V$RESERVED_WORDS data dictionary view.

We can see the always reserved keywords by following query,
select keyword from v$reserved_words where reserved='Y';

Let's see the table.
SQL> desc v$reserved_words;
Name Null? Type
----------------------------------------- -------- ----------------------------
KEYWORD VARCHAR2(30)
LENGTH NUMBER
RESERVED VARCHAR2(1)
RES_TYPE VARCHAR2(1)
RES_ATTR VARCHAR2(1)
RES_SEMI VARCHAR2(1)
DUPLICATE VARCHAR2(1)
•A value of column reserved Y means that the keyword cannot be used as an identifier. A value of N means that it is not reserved and hence can be used as an identifier.

•A value of column RES_TYPE Y means that the keyword cannot be used as a type name. A value of N means that it can be used as a type name.

•A value of column RES_ATTR Y means that the keyword cannot be used as an attribute name. A value of N means that it is not reserved as an attribute name.

•A value of column RES_SEMI Y means that the keyword is not allowed as an identifier in certain situations, such as in DML. A value of N means that it is not reserved.
In order words Y means the keyword is always reserved or N means it is reserved only for particular uses.

In order to know a complete list of oracle always reserved keywords you can query,
SQL> select keyword from v$reserved_words where reserved='Y' order by keyword;

KEYWORD
------------------------------
!
&
(
)
*
+
,
-
.
/
:
<=>
@
ALL
ALTER
AND
ANY
AS
ASC
BETWEEN
BY
CHAR
CHECK
CLUSTER
COMPRESS
CONNECT
CREATE
DATE
DECIMAL
DEFAULT
DELETE
DESC
DISTINCT
DROP
ELSE
EXCLUSIVE
EXISTS
FLOAT
FOR
FROM
GRANT
GROUP
HAVING
IDENTIFIED
IN
INDEX
INSERT
INTEGER
INTERSECT
INTO
IS
LIKE
LOCK
LONG
MINUS
MODE
NOCOMPRESS
NOT
NOWAIT
NULL
NUMBER
OF
ON
OPTION
OR
ORDER
PCTFREE
PRIOR
PUBLIC
RAW
RENAME
RESOURCE
REVOKE
SELECT
SET
SHARE
SIZE
SMALLINT
START
SYNONYM
TABLE
THEN
TO
TRIGGER
UNION
UNIQUE
UPDATE
VALUES
VARCHAR
VARCHAR2
VIEW
WHERE
WITH
[
]
^
|

99 rows selected.

See here ACCESS keyword does not appear beacuse ACCESS can't be used as an identifier.

SQL> select keyword,reserved from v$reserved_words where keyword='ACCESS';

KEYWORD R
------------------------------ -
ACCESS N

Related Documents:
Troubleshooting ORA-00942: ORA-04043:

List of Available Hints in Oracle

A)Optimization Goals and Approaches Category
--------------------------------------------------------------
ALL_ROWS
FIRST_ROWS
RULE

B)Access Path Hints Category
----------------------------------------------------
CLUSTER
FULL
HASH
INDEX
NO_INDEX
INDEX_ASC
INDEX_DESC
INDEX_COMBINE
INDEX_JOIN
INDEX_FFS
INDEX_SS
INDEX_SS_ASC
INDEX_SS_DESC
NO_INDEX_FFS
NO_INDEX_SS

C)Join Order Hints Category
---------------------------------------------
ORDERED
LEADING

D)Join Operation Hints Category
---------------------------------------------
USE_HASH
NO_USE_HASH
USE_MERGE
NO_USE_MERGE
USE_NL Hint
USE_NL_WITH_INDEX
NO_USE_NL

E)Parallel Execution Hints Category
-------------------------------------------------------------------
PARALLEL
NO_PARALLEL
PARALLEL_INDEX
NO_PARALLEL_INDEX
PQ_DISTRIBUTE

F)Query Transformation Hints Category
-------------------------------------------------------------
FACT
NO_FACT
MERGE
NO_MERGE
NO_EXPAND
USE_CONCAT
REWRITE
NO_REWRITE
UNNEST
NO_UNNEST
STAR_TRANSFORMATION
NO_STAR_TRANSFORMATION
NO_QUERY_TRANSFORMATION

Other Hints Category
-----------------------------------------------------
APPEND
NOAPPEND
CACHE
NOCACHE
CURSOR_SHARING_EXACT
DRIVING_SITE
DYNAMIC_SAMPLING
PUSH_PRED
NO_PUSH_PRED
PUSH_SUBQ
NO_PUSH_SUBQ
PX_JOIN_FILTER
NO_PX_JOIN_FILTER
NO_XML_QUERY_REWRITE
QB_NAME
MODEL_MIN_ANALYSIS

Hints in Oracle

Automatic Load Generation tool in Database

Today I work with automatic and free load generation tool in my database. It is quite essential to experiment on test database performance by generating load on it before going to production database. There are many tools that may load workload. But I today work with tool named Swingbench and it is free to use.

If you need to download any load generation tool you can download swingbench from link,

http://www.dominicgiles.com/downloads.html

The main problem of this product it is free and has no support.

There is one user's manual of this software which can be downloaded from
http://www.dominicgiles.com/swingbench/swingbench22.pdf

You must have JVM installed on the client platform to run swingbench. It also requires an Oracle client. This can either be in the form of a full blown Oracle database install or the Oracle instant client downloadable from the OTN (Oracle Technology Network).

http://www.oracle.com/technology/software/tech/oci/instantclient/index.html

In order to use the product no need to install. Just download the zip file and save it in your computer. Then unzip it where you want to resides the program.

Before using this tool you have to at first modify swingbench.env file and change your JAVAHOME, SWINGHOME location according to your wish. On Windows the file is $SWINGHOME/swingbenchenv.bat. Note that java must be installed before using this product.

For unix based user use swingbench which is under $SWINGHOME/bin directory and for windows based user use $SWINGHOME/winbin directory.

The swingbench by default has a configuration file. Swingbench reads its configuration properties from a file called swingconfig.xml located in the $SWINGHOME/bin directory. In the file you can set general variable like host, user name, password etc.

It can be worked with the default swingbench environment ships with the source code for 4 benchmarks.
• CallingCircle
• OrderEntry (PL/SQL)
• OrderEntry (jdbc)
• PL/SQL stubs

The callingcircle schema can be loaded with ccwizrd and corresponding settings is in ccwizzard.xml
The order entry schema can be loaded with oewizard and corresponding settings in in oewizard.xml

After loading data you can run swingbench.

Related Documents:
------------------------------
How to develop own transactions load with Swingbench

Tuesday, June 24, 2008

ORA-12154: TNS:could not resolve the connect identifier specified

Problem Description:
--------------------------------------------
Whenever you try to connect your database it returns error ORA-12154.
SQL> conn prod/prod@jupi
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Cause of The Problem:
------------------------------------
The cause of the problem is depends on the which naming method you are using. Suppose if you use tnsnames.ora for naming method then there might be problem in it. I look for oerr command on my unix machine and got,

-bash-3.00$ oerr ora 12154
12154, 00000, "TNS:could not resolve the connect identifier specified"
// *Cause: A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a
// naming method repository, or the repository could not be
// located or reached.
// *Action:
// - If you are using local naming (TNSNAMES.ORA file):
// - Make sure that "TNSNAMES" is listed as one of the values of the
// NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA)
// - Verify that a TNSNAMES.ORA file exists and is in the proper
// directory and is accessible.
// - Check that the net service name used as the connect identifier
// exists in the TNSNAMES.ORA file.
// - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
// file. Look for unmatched parentheses or stray characters. Errors
// in a TNSNAMES.ORA file may make it unusable.
// - If you are using directory naming:
// - Verify that "LDAP" is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// - Verify that the LDAP directory server is up and that it is
// accessible.
// - Verify that the net service name or database name used as the
// connect identifier is configured in the directory.
// - Verify that the default context being used is correct by
// specifying a fully qualified net service name or a full LDAP DN
// as the connect identifier
// - If you are using easy connect naming:
// - Verify that "EZCONNECT" is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// - Make sure the host, port and service name specified
// are correct.
// - Try enclosing the connect identifier in quote marks.
//
// See the Oracle Net Services Administrators Guide or the Oracle
// operating system specific guide for more information on naming.

Solution of The Problem:
-----------------------------------------------
Actually above solution already depicted what to do if you get the problem. My suggest after getting ORA-12154 immediately go as the steps mentioned below.

Step 1: Look for tnsnames.ora and sqlnet.ora.
-----------------------------------------------------------------------
Look for your tnsnames.ora and sqlnet.ora file that you are using. On unix the default location is $ORACLE_HOME/network/admin. You better avoid to use tnsnames.ora. By using easy naming service you can easily avoid this error. Easy naming service is discussed on See the solution part of this post

Step 2: Check both file
---------------------------------------------------------------------------
•After locating both file open it with any viewer like on windows with notepad and on unix with less or more or cat.

•Check within the files whether any illegal character or any unnecessary space exists. If have then remove that.

•Check whether the exact entry exist on the tnsnames.ora that you used in connection identifer. Like if you use conn a/a@jupi then within tnsnames.ora search for only jupi alias.

•You can check your whether there is error or not in the tnsnames alias inside tnsnames.ora by using tnsping. In order to check alias jupi we issue,

bash-3.00$ tnsping jupi

TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 12-OCT-2008 03:25:58

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = ((ADDRESS = (PROTOCOL = TCP)(HOST = neptune)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ARJU)))
TNS-12533: TNS:illegal ADDRESS parameters
So there is illegal ADDRESS parameter which we see an extra ( before ADDRESS parameter.

If our tnsalias was good, then it would result below with how many miliseconds.
bash-3.00$ tnsping jupi

TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 12-OCT-2008 03:28:26

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = neptune)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ARJU)))
OK (10 msec)

•Note that with name alias checking tnsnames also tell us which location parameter files it used. Here the location is /oracle/app/oracle/product/10.2.0/db_1/network/admin. So /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora location is used in order to resolve name. However if your tnsnames.ora is in other location then you have to set TNS_ADMIN variable.

•If you see tnsnames.ora is most likely accurate, echo the TNS_ADMIN environment variable.

% echo $TNS_ADMIN

•If nothing is returned, try to set the TNS_ADMIN environment variable to explicitly point to the location of the TNSNAMES.ORA file.

In C Shell, the syntax is:
% setenv TNS_ADMIN full_path_of_tnsnames.ora_file

In K Shell or bash, the syntax is:
% export TNS_ADMIN=full_path_of_tnsnames.ora_file

In windows the syntax is:
set TNS_ADMIN=full_path_of_tnsnames.ora_file

•Now try and see whether error remains. If still you get error then in the SQLNET.ORA file, add the parameter AUTOMATIC_IPC = OFF. If AUTOMATIC_IPC is already set to ON, then change the value to OFF. And try to connect. If still you get error then check the permission of tnsnames.ora and sqlnet.ora. For workaround you can set chmod 777 of these files and try to connect. At this point it is expected you have solved your problem.

Related Documents
ORA-12560: TNS:protocol adapter error on windows machine while starting oracle
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Startup fails with oracle error ORA-00119, ORA-00132

ORA-12541: TNS:no listener

For newbie users they found ORA-12541 and search here and there to solve the problem. But it is one word about the error which it says no listener and think next what to do. Never mix this error with another error like ORA-12514: which are different and mutually exclusive.

Error Description
---------------------------------------------
-bash-3.00$ sqlplus arju/a@neptune/saturn

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 24 06:39:15 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-12541: TNS:no listener

Cause of the Problem:
--------------------------------------
Listener service is not running in the database to which you are requested to connect. Here we see that is neptune database. If you use tnsnames.ora then look at host parameter value in it.

Solution of the Problem:
----------------------------------
Solution of the problem is straightforward which is start the listener. It is done by lsnrctl start in the server machine to which you connect.
Step1:
---------
First check status by
$lsnrctl status

If you have default named listener which is LISTENER then lsnrctl status is fine. However if you don't have default listener then from listerner.ora see the available listener. And use that name after status. If your listener name is TEST_LISTERNER then use,
$lsnrctl status TEST_LISTERNER
LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 06:55:08

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Solaris Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=neptune)(PORT=1522)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Solaris Error: 146: Connection refused

If you get TNS-00511 then go to step 2 otherwise if you get listener status then go to step 3.

Step 2:
---------------
Start the listener.
If you have default listener then use
-bash-3.00$ lsnrctl start

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 06:53:50

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /oracle/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
.
.

If you have other than default then use that name like,
$ lsnrctl start NON_DEFUALT_LISTENER
Step 3:
--------------
It is natural after starting listener service it takes minute to register the service of the listener. You can dynamically service of the listner by issuing following command in SQL*Plus,
SQL> alter system register;
System altered.

Step 4:
------------------
Try to connect to database using connection identifier. If you still get error ORA-12541 then check the addition settings of the connection identifier. If you use tnsnames.ora then also check settings listener post. This all can be seen after issuing lsnrctl status command

Like in this example my listener port is 1522 and if I use 1521 in my connection identifier error will come.
-bash-3.00$ lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 07:02:18

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 24-JUN-2008 07:01:52
Uptime 0 days 0 hr. 0 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=neptune)(PORT=1522)))
.
.

-bash-3.00$ sqlplus arju/a@neptune:1521/dbase

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 24 07:06:00 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-12541: TNS:no listener

As listener is running on port 1522 so connect to port 1521 will fail.
-bash-3.00$ sqlplus arju/a@neptune:1522/dbase

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 24 07:06:15 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

In this post I will try to show how efficiently we can avoid error ORA-12514. My suggestion is whenever you get this error forget about tnsnames.ora and other stuff related to it. Start fixing problem step by step.

Problem Description:
Whenever you try to connect to database the following error comes.
-bash-3.00$ sqlplus arju/a:1522/dba

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jun 24 06:35:02 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Cause of The problem:
The services named issued with the connection identifier has not been registered yet.

Solution of The Problem:
After getting above error forget any tnsnames.ora file. Issue lsnrctl status command on the server to which you try to connect like,
-bash-3.00$ lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 07:17:56

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 24-JUN-2008 07:01:52
Uptime 0 days 0 hr. 16 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=neptune)(PORT=1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dbase" has 1 instance(s).
Instance dbase, status READY, has 1 handler(s) for this service...
Service "dbaseXDB" has 1 instance(s).
Instance "dbase", status READY, has 1 handler(s) for this service...
Service "dbase_XPT" has 1 instance(s).
Instance "dbase", status READY, has 1 handler(s) for this service...
The command completed successfully

Now closely look at the bolded items above , it is host, port, service and optionally instance.
Now use it in the connection descriptor as follows.

sqlplus user_id/password@host:port/service

Like here,
$sqlplus arju/a@neptune:1522/dbase
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 24 07:21:19 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

This method of connecting to database is called Easy Connect Naming Method.

Related Documents:
ORA-12541: TNS:no listener