Temporary tables

Temporary tables data can be for a temporary period of a transaction or a session.
Temporary data for a transaction example :-

create global temporary table emp_temp
(empno number,
 ename varchar2(30),
 sal number,
 deptno number) on commit delete rows;

  

Insert records to test data for a transaction:-

insert into emp_temp values ( 1, 'krishna', 1000, 10);
insert into emp_temp values ( 2, 'Raghu', 1000, 10);
insert into emp_temp values ( 3, 'Sachin', 1000, 10);
insert into emp_temp values ( 4, 'Amit', 1000, 10);

Verifying table data before deleting with a commit command :-

select * from emp_temp;

Issuing commit to delete the records of the temporary table:-

commit;

Verifying table data after deleting with a commit command :-

select * from emp_temp;

no rows selected

Temporary data for a session example :-

Drop table emp_temp;

create global temporary table emp_temp
(empno number,
 ename varchar2(30),
 sal number,
 deptno number) on commit preserve rows;

  

Insert records to test data for a transaction:-

insert into emp_temp values ( 1, 'krishna', 1000, 10);
insert into emp_temp values ( 2, 'Raghu', 1000, 10);
insert into emp_temp values ( 3, 'Sachin', 1000, 10);
insert into emp_temp values ( 4, 'Amit', 1000, 10);

Verifying table data that should delete after ending a session :-

select * from emp_temp;

Issuing disc command  to end your session and delete the records of the temporary table:-

disc;

Connect back to check if the records are available:-

select * from emp_temp;

no rows selected