Tuesday, September 27, 2016

Oracle Temporary Tables


About Temporary Tables :

Temporary tables are database tables that hold session specific data
Each session can only see and modify its own data
Types :– Transaction Specific – Session Specific





Transaction Specific
– Data exists for the duration of the Transaction
– Declared by syntax “On Commit Delete Rows”
Session Specific
– Data exists for the duration of the Session
– Declared by syntax “On Commit Preserve Rows”

Key Points :

• TRUNCATE statement issued against a temporary table, will truncate only the
session specific data. There is no effect on the data of other sessions.
• Data in temporary tables is automatically deleted at the end of the database
session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the
scope of the index is that same as the database session.
• Views can be created against temporary tables and combinations of temporary and
permanent tables.
• Tempoarary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data
rows are processed
• Foreign keys cannot be specified
• Cannot contain columns of nested table or varray
• Cannot be partitioned, index- organized or clustered

Example :

create global temporary table emp_temp
(empno number primary key,
deptno number,
ename varchar2(10))
on commit delete rows;

insert into emp_temp
select empno,deptno,ename from emp;
Commit;


Notice all the rows are lost after commit.

Featured Post

Will the data from Oracle Database be lost after formatting the system in which it is installed?

So Someone asked me a question,  Will the data from Oracle Database be lost after formatting the system in which it is installed? Since the ...

Popular Posts