Private temporary tables are stored in memory. Private temporary tables exist only in the
session in which they were created and disapper when the session ends.
Creating a private temporary table
Note the prefix of the table name:
create private temporary table ora$ptt_renes_temp_table (
num number,
txt varchar2(20)
);
When created,
DML and
select statements can be performed on the table as being used from ordinary heap tables:
insert into ora$ptt_renes_temp_table values (1, 'x');
insert into ora$ptt_renes_temp_table values (2, 'y');
update ora$ptt_renes_temp_table set num = 3 where txt = 'y';
delete from ora$ptt_renes_temp_table where num = 1;
select * from ora$ptt_renes_temp_table;
However, when the
transaction ends (and the table was not created with
on commit preserve definition), the table automatically gets deleted:
commit;
select * from ora$ptt_renes_temp_table;
--
-- ORA-00942: table or view does not exist
Data Dictionary
Because prviate temporary tables are constructs in memory, they don't show up in the
data dictionary view
user_objects (or
dba_objects for that matter).
ON COMMIT PRESERVE DEFINITION
The on commit preserve defnition clause allows a temporary private table to be outlive a transaction:
create private temporary table ora$ptt_xyz (
num number,
txt varchar2(20)
)
on commit preserve definition;
insert into ora$ptt_xyz values (1, 'one');
commit;
select * from ora$ptt_xyz;
Creation of a private temporary table does not commit the current transaction
create table tq84_trx (id number);
insert into tq84_trx values(1);
commit;
insert into tq84_trx values(2);
--
-- Following statement does not commit (or rollback)
-- the current transaction
--
create private temporary table ora$ptt_trx (
num number,
txt varchar2(20)
)
on commit preserve definition;
--
-- Removes uncommitted number 2 from tq84_trx
--
rollback;
--
-- Selects one record with num = 1
--
select * from tq84_trx;