There are two types of temporary tables in Oracle. They are Global temporary tables and Private temporary tables.

Private Temp Tables:

Private temp tables exist from oracle 18C onwards. They exists in the memory and do not have physical objects in the database. They exists during the session and after a re-login or in a new session it will not exists. Following syntax can be used to create a private temporary table.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_customer ( customer_id NUMBER, name VARCHAR2(20) )

As you can see we have to use ora$ptt identifies when creating a private temp table. If we want to remove the table after the commit we have to use the  “ON COMMIT PRESERVE DEFINITION” definition and if we want to remove it after the commit we can use “ON COMMIT DROP DEFINITION” clause.

Advantages:

The advantages of having these private temp tables is we dont have the hedache of removing tables after a technical investigation. And also we can use these tables to insert important data/information we need during an investigation. Also, we can develop test scripts using them. We can use these tables as if we use traces during an investigation. These tables exists in the memory so there is no overhead to the database as well.

Disadvantages:

We cannot define primary keys/indexes against private temp tables. Another disadvantage is since they are memory based we cannot create views or materialzed views using them and also we have to prefix ora$ptt each time we create them