Oracle Data Types

The following is a list of datatypes available in Oracle.

Character Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
char(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length strings. Space padded.
nchar(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length NLS string Space padded.
nvarchar2(size)	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Where size is the number of characters to store. Variable-length NLS string.
varchar2(size)	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Where size is the number of characters to store. Variable-length string.
long	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length strings. (backward compatible)
raw	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Variable-length binary strings
long raw	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length binary strings. (backward compatible)

Numeric Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
number(p,s)	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Where p is the precision and s is the scale. For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
numeric(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
float
dec(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
decimal(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
integer
int
smallint
real
double precision

Date/Time Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
date	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
timestamp (fractional seconds precision)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds. For example: timestamp(6)
timestamp (fractional seconds precision) with time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone displacement value. For example: timestamp(5) with time zone
timestamp (fractional seconds precision) with local time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone. For example: timestamp(4) with local time zone
interval year (year precision) to month	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	Time period stored in years and months. For example: interval year(4) to month
interval day (day precision) to second (fractional seconds precision)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	Time period stored in days, hours, minutes, and seconds. For example: interval day(2) to second(6)

Large Object (LOB) Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
bfile	Maximum file size of 4GB.	Maximum file size of 232-1 bytes.	Maximum file size of 264-1 bytes.	File locators that point to a binary file on the server file system (outside the database).
blob	Store up to 4GB of binary data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Stores unstructured binary large objects.
clob	Store up to 4GB of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Stores single-byte and multi-byte character data.
nclob	Store up to 4GB of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Stores unicode data.

Rowid Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
rowid	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	Fixed-length binary data. Every record in the database has a physical address or rowid.
urowid(size)				Universal rowid. Where size is optional.

Normalization

What is Normalization?

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

The main normal forms are summarized below.

Normal form	Defined by	Brief definition
First normal form(1NF)	Two versions: E.F. Codd (1970), C.J. Date (2003)	Table faithfully represents a relation and has no repeating groups
Second normal form (2NF)	E.F. Codd (1971)	No non-prime attribute in the table is functionally dependenton a part (proper subset) of a candidate key
Third normal form(3NF)	E.F. Codd (1971); see also Carlo Zaniolo's equivalent but differently-expressed definition (1982)	Every non-prime attribute is non-transitively dependent on every key of the table
Boyce-Codd normal form(BCNF)	Raymond F. Boyce and E.F. Codd (1974)	Every non-trivial functional dependency in the table is a dependency on a superkey
Fourth normal form (4NF)	Ronald Fagin (1977)	Every non-trivial multivalued dependency in the table is a dependency on a superkey
Fifth normal form(5NF)	Ronald Fagin (1979)	Every non-trivial join dependency in the table is implied by thesuperkeys of the table
Domain/key normal form(DKNF)	Ronald Fagin (1981)	Every constraint on the table is a logical consequence of the table's domain constraints and key constraints
Sixth normal form(6NF)	Chris Date, Hugh Darwen, and Nikos Lorentzos (2002)	Table features no non-trivial join dependencies at all (with reference to generalized join operator)

Popular Misconception

There is a popular misconception that NOT IN and NOT EXISTS are two ways to filter out rows present in one table and not in another table, with both methods being usable interchangeably. Popular wisdom is that the difference between the two is only in terms of performance (being on the basis of whether the larger table is the "outer" or the "inner" in the query) but that the results are the same.

However, we must remember that such an operation being an Anti-Join fails when NULLs are involved. In Oracle a NULL cannot be compared to any other value, not even another NULL. Therefore, a NOT IN operation would fail if the result set being probed returns a NULL. In such a case, the results of a NOT IN query is 0 rows while a NOT EXISTS query would still show the rows present in the one table but not in the other table.

Here is a simple demonstration :-

-- create the two test tables
SQL> create table results_table as select owner,object_name,object_type from dba_objects where owner in ('HEMANT','DBSNMP','OUTLN');

Table created.

SQL> create table query_table as select owner,object_name,object_type from dba_objects where owner in ('DBSNMP','OUTLN');

Table created.

-- A NOT IN query
SQL> SELECT r.owner, r.object_name 
                   FROM results_table r
                WHERE r.owner NOT IN (SELECT q.owner FROM query_table q)
                ORDER by 1,2;

OWNER OBJECT_NAME  
------------------------------ ------------------------------  
HEMANT DUPDB  
HEMANT MY_T_A  
HEMANT RESULTS_TABLE  
HEMANT SOURCE_TABLE  
HEMANT TEST_APPEND  
HEMANT TRACE_USER_SESSIONS  
HEMANT TRACE_USER_SESSIONS_BEGIN  
HEMANT TRACE_USER_SESSIONS_END  

8 rows selected.

-- A NOT EXISTS query

SQL> SELECT r.owner, r.object_name 
                FROM results_table r
               WHERE NOT EXISTS (SELECT '1' 
                                                           FROM query_table q
                                                         WHERE r.owner=q.owner)
               ORDER by 1,2;

OWNER OBJECT_NAME  
------------------------------ ------------------------------  
HEMANT DUPDB  
HEMANT MY_T_A  
HEMANT RESULTS_TABLE  
HEMANT SOURCE_TABLE  
HEMANT TEST_APPEND  
HEMANT TRACE_USER_SESSIONS  
HEMANT TRACE_USER_SESSIONS_BEGIN  
HEMANT TRACE_USER_SESSIONS_END  

8 rows selected.

SQL> REM So far, NOT IN and NOT EXISTS have presented the same results
SQL> REM What happens if there is a row with a NULL value ?
SQL> INSERT INTO query_table VALUES (NULL,'ABCDEFGH','TABLE');

1 row created.

SQL> -- Retry the NOT IN query
SQL> SELECT r.owner, r.object_name 
                   FROM results_table r
                 WHERE r.owner NOT IN (SELECT q.owner FROM query_table q)
                  ORDER by 1,2;

no rows selected

SQL> -- retry the NOT EXISTS query
SQL> SELECT r.owner, r.object_name 
                 FROM results_table r
               WHERE NOT EXISTS (SELECT '1' 
                                                           FROM query_table q
                                                        WHERE r.owner=q.owner)
             ORDER by 1,2;
OWNER OBJECT_NAME  
------------------------------ ------------------------------  
HEMANT DUPDB  
HEMANT MY_T_A  
HEMANT RESULTS_TABLE  
HEMANT SOURCE_TABLE  
HEMANT TEST_APPEND  
HEMANT TRACE_USER_SESSIONS  
HEMANT TRACE_USER_SESSIONS_BEGIN  
HEMANT TRACE_USER_SESSIONS_END  

8 rows selected.

SQL> REM Why ? Because of the presence of a NULL in the query_table !
SQL> REM So a "NOT IN" anti-join fails because a NULL returned cannot be compared !
SQL> REM One "workaround" is to filter out rows which contain NULLs
SQL> REM but think carefully before you do so. Are you sure you want to exclude them ?
SQL>
SQL> REM In the ideal world, such columns should be defined as NOT NULL columns !
SQL> REM That would be the right schema design !
SQL> -- test the suggested workaround
SQL> SELECT r.owner, r.object_name 
                  FROM results_table r
                WHERE r.owner NOT IN (SELECT q.owner 
                                                                 FROM query_table q
                                                              WHERE OWNER IS NOT NULL)
                ORDER by 1,2;

OWNER OBJECT_NAME  

------------------------------ ------------------------------  
HEMANT DUPDB  
HEMANT MY_T_A  
HEMANT RESULTS_TABLE  
HEMANT SOURCE_TABLE  
HEMANT TEST_APPEND  
HEMANT TRACE_USER_SESSIONS  
HEMANT TRACE_USER_SESSIONS_BEGIN  
HEMANT TRACE_USER_SESSIONS_END  

8 rows selected.


You can see that if there is a single row with a NULL value on the query predicate (the OWNER column in QUERY_TABLE), the NOT IN query actually fails.

Tom Kyte has explained this in the section on Anti-Joins in his "Effective Oracle By Design" book. And its posted by Hemant K Chitale in his http://hemantoracledba.blogspot.com./


I conclude that NOT IN will behave like <>ALL and NOT EXISTS will behave like <>ANY.

Memory Map of Oracle Forms

In oracle forms after we deployed our forms into a service when a user opens a form until the user close the form we cant be able to compile it (Convert to fmx). To achieve this we can use this in the configuration.

FORMS_MMAP=FALSE

Export and Import Data Pump Utility

This Export (EXPDP) and Import (IMPDP) utilities are introduced in Oracle 10g for full or partial data export and import. 

It has many new features compared with the old version of Export (EXP) and Import (IMP) utilities.

Additional features :- 

1. We can compress the datas when export. so that the dump file size will be reduced.

2. We can Split the export as jobs. And start/stop/restart it when ever needed.

3. We can export specific database object of specific database users and specific tablespaces alone.

4. When Import we can remap the user to another user. i.e when we export a user objects and want to import it as another user object it will be useful.

5. Same way we want to remap the objects from one tablespace to another tablespace when importing. 

6. We can import the database when exporting the database. Which looks like direct export and import of database/users/objects from one system to another system in the network.

Here Code given will be used to achieve 4 and 5.

EXPDP 'SYS/ORACLE@NEW AS SYSDBA' SCHEMAS=SCOTT DUMPFILE=STS.DMP

IMPDP 'SYS/ORACLE22@ORCL22 AS SYSDBA' DUMPFILE=STS.DMP REMAP_SCHEMA=SCOTT:RMVER REMAP_TABLESPACE=USERS:RMVER

This code will be used to achieve 6

create database link dbl2  connect to  rmver721 identified by rmver721 using '(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = DB_SERVER)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = orcl)
   )
)';

IMPDP 'SYS/ORACLE22@ORCL22 AS SYSDBA' SCHEMAS=SCOTT REMAP_SCHEMA=SCOTT:RMVER REMAP_TABLESPACE=USERS:RMVER NETWORK_LINK=dbl2

Have Updated and Use Features

Thanks 

Have a nice day.