Managing Private Temporary Tables

Before You Begin

This 15-minute tutorial shows you how create and use private temporary tables and how to manage them in your session.

Background

Prior to Oracle Database 18c, only Global Temporary Tables existed. A global temporary table is a persistent database object, visible to all the sessions, until user drops it explicitly using DROP TABLE statement. Data is only visible to the session which inserts it. The main motivators for the feature are reporting applications that store temporary data in transient tables that are typically populated once, read few times and then are automatically released by RDBMS upon the end of a transaction or end of a session.

Private Temporary Tables (PTTs) are local to a specific session. In contrast with Global Temporary Tables, the definition and content are local to the session that creates the PTT only and are not visible to other sessions. Hence an application can create the PTT with the same name in different sessions. At the end of a transaction or end of a session, the PTT is automatically dropped.

What Do You Need?

Oracle Database 18c installed
A container database (CDB) with one pluggable database (PDB)
HR schema installed in PDB1 as an example of application tables, or any other table that you created. If you want to use the HR.EMPLOYEES table, use the hr.sql. Download the SQL script to the labs directory created on your server /home/oracle/labs. In the script, update the password of the user connected to the database.

Create PTTs

Log in to PDB_ORCL and start a session as HR. This is Session1.
```
sqlplus hr@PDB_ORCL
Enter password: password
```

Create a simple table with some data values.

CREATE TABLE test (x NUMBER, y VARCHAR2(10));
INSERT INTO test VALUES (1,'A');
COMMIT;

Create a PTT.

CREATE PRIVATE TEMPORARY TABLE mine (x NUMBER, y VARCHAR2(10));
CREATE PRIVATE TEMPORARY TABLE mine (x NUMBER, y VARCHAR2(10))
*
ERROR at line 1:
ORA-00903: invalid table name

All PTT must have a predefined prefix. Check the prefix defined by default. Open another terminal window and log in to another session as SYSTEM.

sqlplus system@PDB_ORCL
Enter password: password

SHOW PARAMETER PRIVATE_TEMP_TABLE_PREFIX 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
private_temp_table_prefix            string      ORA$PTT_

In the initial HR session, create a PTT with the appropriate prefix.
```
CREATE PRIVATE TEMPORARY TABLE ora$ptt_mine (x NUMBER, y VARCHAR2(10));
```

Insert rows in the PTT.

INSERT INTO ora$ptt_mine VALUES (1,'Work1');

Display data from the PTT.

SELECT * FROM ora$ptt_mine;

         X Y
---------- ----------
         1 Work1

Identify PTTs Between Sessions

In this section, you verify that only the session that created a PTT can see its definition and content.

In Session1, describe the

 ORA$PTT_mine

PTT.

DESC hr.ORA$PTT_mine 
Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  NUMBER
 Y                                                  VARCHAR2(10)

Find all information related to the PTT.
```
SELECT sid, serial#, table_name, tablespace_name, duration
FROM   user_private_temp_tables;

 SID SERIAL# TABLE_NAME   TABLESPACE_NAME DURATION    
---- ------- ------------ --------------- ----------- 
  43   28813 ORA$PTT_MINE TEMP            TRANSACTION 
```
Observe that the PTT is of TRANSACTION type. This is the default duration type. This means that the PTT is automatically dropped at the end of the transaction in which the PTT has been created.
Log in to PDB_ORCL in another terminal window and start a session as HR. This is Session2. Verify that the PTT created by Session1 is not visible to Session2.
```
sqlplus hr@PDB_ORCL
Enter password: password
```
```
DESC hr.ORA$PTT_mine
ERROR:
ORA-04043: object hr.ORA$PTT_mine does not exist
```

Use PTTs

In Session1, insert a row into TEST and rollback the transaction.
```
INSERT INTO test VALUES (2,'B');
ROLLBACK;
```
What happens to the PTT?
```
SELECT sid, serial#, table_name, tablespace_name, duration
FROM   user_private_temp_tables; 

no rows selected 
```
Observe that the TRANSACTION duration type PTT is automatically dropped with the ROLLBACK statement. A COMMIT would have also dropped the PTT.

Create a new PTT of SESSION duration type that will last until your session ends.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_mine2 
              (x NUMBER, y VARCHAR2(10)) ON COMMIT PRESERVE DEFINITION;

Find all information related to the PTT.

SELECT sid, serial#, table_name, tablespace_name, duration
FROM   user_private_temp_tables; 

 SID SERIAL# TABLE_NAME    TABLESPACE_NAME DURATION    
---- ------- ------------- --------------- ----------- 
  43   28813 ORA$PTT_MINE2 TEMP            SESSION

Insert rows in the PTT.

INSERT INTO ora$ptt_mine2 VALUES (2,'Work2');

Display data from the PTT.

SELECT * FROM ora$ptt_mine2;

         X Y
---------- ----------
         2 Work2

Insert a row into TEST and commit the transaction.
```
INSERT INTO test VALUES (3,'C');
COMMIT;
```

What happens to the PTT?

SELECT sid, serial#, table_name, tablespace_name, duration
FROM   user_private_temp_tables;

 SID SERIAL# TABLE_NAME    TABLESPACE_NAME DURATION    
---- ------- ------------- --------------- -----------
  43   28813 ORA$PTT_MINE2 TEMP            SESSION

Display data from the PTT.

SELECT * FROM ora$ptt_mine2;

         X Y
---------- ----------
         2 Work2

Quit Session1 and verify that the PTT is automatically dropped.
```
EXIT 
```

Verify that the PTT is automatically dropped.

sqlplus hr@PDB_ORCL
Enter password: password

SELECT * FROM user_private_temp_tables;

no rows selected

Use PTTs With Savepoints

Use the hr.sql to create the HR user and EMPLOYEES table.
```
@/home/oracle/labs/hr.sql
```
Create the EMP table with EMPLOYEES rows. The first command may return an error stating that the table does not exist, depending on the setup.
```
DROP TABLE emp PURGE;
CREATE TABLE emp AS SELECT * FROM employees;
```

Create a transaction duration PTT with the 107 rows of


                    EMP.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_emp AS SELECT * FROM emp;

Insert another set of rows.

INSERT INTO ora$ptt_emp SELECT * FROM emp;

107 rows created.

Create a first savepoint.
```
SAVEPOINT point1;
```

Count the number of rows in the PTT.

SELECT count(*) FROM ora$ptt_emp;

  COUNT(*)
----------
       214

Find all information related to the PTT.

SELECT sid, serial#, table_name, tablespace_name, duration, num_rows
FROM   user_private_temp_tables;

 SID SERIAL# TABLE_NAME    TABLESPACE_NAME DURATION      NUM_ROWS
---- ------- ------------- --------------- ----------- ----------
  39   56869 ORA$PTT_EMP   TEMP            TRANSACTION        107

The number of rows corresponds to the number of rows in the PTT at the PTT's creation.

Insert another bunch of rows.

INSERT INTO ora$ptt_emp SELECT * FROM emp;
107 rows created.

Count the number of rows in the PTT.

SELECT count(*) FROM ora$ptt_emp;

  COUNT(*)
----------
       321

Create the second savepoint.
```
SAVEPOINT point2;
```

Insert another set of rows.

INSERT INTO ora$ptt_emp SELECT * FROM emp;

107 rows created.

Count the number of rows in the PTT.

SELECT count(*) FROM ora$ptt_emp;

  COUNT(*)
----------
       428

You discover that you should keep only the first set of rows inserted in step 3. Rollback to point1. .
```
ROLLBACK TO point1;
```
What would have happened if you had used the ROLLBACK command? The PTT would have been dropped.

Count the number of rows in the PTT.

SELECT count(*) FROM ora$ptt_emp;

  COUNT(*)
----------
       214

You can insert the temporary rows into EMP.

INSERT INTO emp SELECT * FROM ORA$PTT_emp;

214 rows created.

Commit.
```
COMMIT;
```

Count the number of rows in EMP.

SELECT count(*) FROM emp;

  COUNT(*)
----------
       321

Does the PTT exist?

SELECT sid, serial#, table_name, tablespace_name, duration, num_rows
FROM  user_private_temp_tables;

no rows selected

Quit the session after dropping the HR schema.
```
DROP USER hr CASCADE;
```
```
EXIT
```