Oracle - Datatypes, ROWID, ROW NUMBER, TIMESTAMP

New datatypes introduced in Oracle 9i:
1. TIMESTAMP
2. TIMESTAMP WITH TIME ZONE
3. TIMESTAMP WITH LOCAL TIME ZONE
4. INTERVAL YEAR TO MONTH
5. INTERVAL DAY TO SECOND

Oracle has 3 types tables:
--------------------------
Data Dictionary Tables
User Defined Tables
System Tables


Data Dictionary :
1. centralized repository of Metadata.
2. Always availble in the System TableSpace.
3. Its owned by "SYS" user.
dba_tables;
user_tables;
all_tables;
DICT; // to view datadictionary tables

Diff betwenn ROWID and ROW NUMBER:
ROWNUMBER will change but row id will not change,
example: Suppose when u sort a set of records in desc order then row number changes, now top most record row number is 1.
ROWID is a 80 bit hexa decimal number. But rowid will not change. its constant.

select rowid, name,salary from ex_details;

TIMESTAMP:
its used to accomidate fraction seconds including date and time. 12-sep-2009 12:12:12 000000

TIMEZONE:
12-sep-2009 12:12:12 000000 +5:30

INTERVAL YEAR TO MONTH:
create table t20(d1 INTERVAL YEAR(3) TO MONTH);
insert into t20 values(INTERVAL '100' YEAR(3));
SQL> select * from t20;

D1
-------------------------------------------------------
+100-00
+100-03

SQL> select to_char(sysdate+d1,'DD-MON-YYYY') from t20;

TO_CHAR(SYS
-----------
26-SEP-2108
26-DEC-2108