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.
