Creating temporary tables

Temporary tables are created using the global temporary clause

Create global temporary table Hr.employees_temp
As select * from hr.employees;

Use of Temporary Tables

• Tables retain data only for the duration of a transacton or session.
• DML locks are not acquired on the data
• Can create indexes,views and triggers on temporary tables
  

You can import and export the definition but you can able to import and export the data.

Demonstration

Create global temporary table tt1
(lname varchar2(25), fname varchar2(25),empno number) on commit delete rows;

desc tt1;

select count(*) from tt1;

insert into tt1 values (‘thaker’,’nikunj’,1);

select * from tt1;

commit;

select * from tt1;

create global temporary table tt2
(lname varchar2(25), fname varchar2(25),empno number) on commit preserve rows;

data reside in the table will remain in the table after commit but logs are remove of this tables on structure of the table will resides.

Select table_name,temporary from user_tables Where table_name like ‘TT%’;

Drop table tt1;

Drop table tt2;

This will gives you error because it contains row because of the table we had created with on commit preserve rows;

Because of preserve rows row will reside in the table till session is open.

So we reconnet with that user and try to

select count(*) from tt2;

there is no data now we can remove this tables.