Temporary tables use temporary segments. Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created
Here is another feature I found interesting that was introduced in Oracle 8i. Temporary tables, yep temporary tables. You may say, whats the big deal, I was creating temporary tables from before, whats new now. These temporary tables have a global definition. When a user inserts rows in a session, those rows are only visible to that session and the rows are only available until the transaction or the session is ended based on how the table has been defined. Confused....Read On... Temporary TablesOracle 8i introduces a new concept called global temporary tables. This version only provides global temporary tables and local tables are expected in the future. These are like normal tables but the segment is created only when the data is inserted and hence temporary in nature. create global temporary table dbatest ( c1 number, c2 number);The above statement creates a table whose definition is visible to all the sessions but the data is visible to only the session that has created it. Temporary tables can be used by developers to store session / transaction specific data that can be discarded as soon as the session/transaction ends. When a truncate is issued on this table, only data related to that particular session is truncated.The above code creates a table named dbatest. When data is inserted into this table the data persists either at the session or transaction level based on how "On Commit" parameter is specified. On commit can be specified as "delete rows" or "preserve rows". Delete Rows seems to be the default.Create global temporary table dbatest ( c1 number, c2 number ) on commit delete rows;In the above table, as soon as the user ends the transaction by issuing a commit statement, the data in the temporary table is deleted ( truncated ).Create global temporary table dbatest ( c1 number, c2 number ) on commit preserve rows;The above statement causes the creation of a table that will keep its rows even after the transaction is committed. If the user inserts rows into the above table and then commits the data, the data that was inserted will exist until the session is ended.RestrictionsTemporary tables cannot contain nested tables or varray types or they cannot be partitioned, index-organized or clustered. They cannot be used in parallel DML or parallel queries and distributed transactions are not supported on these tables.
tables and their indexes do not automatically allocate a segment when they are
created
create global temporary table dbatest
( c1 number, c2 number);The above statement creates a table whose definition is visible to all the sessions but the data is visible to only the session that has created it. Temporary tables can be used by developers to store session / transaction specific data that can be discarded as soon as the session/transaction ends. When a truncate is issued on this table, only data related to that particular session is truncated.The above code creates a table named dbatest. When data is inserted into this table the data persists either at the session or transaction level based on how "On Commit" parameter is specified. On commit can be specified as "delete rows" or "preserve rows". Delete Rows seems to be the default.Create global temporary table dbatest
(
c1 number,
c2 number
) on commit delete rows;In the above table, as soon as the user ends the transaction by issuing a commit statement, the data in the temporary table is deleted ( truncated ).Create global temporary table dbatest
(
c1 number,
c2 number
) on commit preserve rows;The above statement causes the creation of a table that will keep its rows even after the transaction is committed. If the user inserts rows into the above table and then commits the data, the data that was inserted will exist until the session is ended.RestrictionsTemporary tables cannot contain nested tables or varray types or they cannot be partitioned, index-organized or clustered. They cannot be used in parallel DML or parallel queries and distributed transactions are not supported on these tables.