The Oracle database can also create temporary tables in addition to saving permanent tables. A temporary table is a nologging that is stored in a temporary table space and does not log.
1. Temporary tables can create temporary indexes, views, triggers
2. If you want to drop the session-level staging table, and it contains data, you must first truncate the data in it. Otherwise you will get an error.
3. Temporary tables do not have DML locks
4. Temporary tables cannot save data permanently
5. Primary foreign key relationships are not supported
There are two types of temporary tables
1. Session-level staging table
A session-level temporary table is the data in a staging table that exists only during the session lifecycle, and when the user exits the end of the session, Oracle automatically clears the data from the staging table.
Create Global Temporary Table Table_Name(id numbername varchar2(20)...)On Commit Preserve Rows;
2. Transaction-level staging table
A transaction-level temporary table is the data in a temporary table that exists only during the transaction life cycle, and when a transaction ends (commit or rollback), Oracle automatically clears the data in the staging table
Create Global Temporary Table Table_Name(id numbername varchar2(20)...)On Commit Delete Rows;
Note: Although temporary tables automatically purge data after exiting a session or after committing a transaction to roll back a transaction, the structure of the staging table and metadata are also stored in the user's data dictionary. If the staging table completes its mission, it is best to delete the temporary table, otherwise the database will have many table structures and metadata for the temporary table.
What is a temporary table for Oracle??