A private temporary table belongs to the database session that creates it: both its definition and its rows are private to that session. Choose transaction lifetime when a calculation needs a disposable table for one transaction. Choose session lifetime when intermediate work must continue across commits in the same database session.
Oracle DBA Lesson 60 Part 2 — Give the Temporary Definition a Lifetime
Choose the definition's lifetime
| Table type and clause | At COMMIT in its session | At database session termination |
|---|---|---|
Global temporary table, DELETE ROWS | Its session's rows clear; reusable definition remains | Session data clears; definition remains |
Global temporary table, PRESERVE ROWS | Its session's rows remain; reusable definition remains | Session data clears; definition remains |
Private temporary table, DROP DEFINITION | Definition and data are removed | Any remaining private object is removed |
Private temporary table, PRESERVE DEFINITION | Definition and committed rows remain in the same session | Definition and data are removed |
DROP DEFINITION is the private temporary table default. Write the clause explicitly so the required lifetime is clear. A full transaction rollback also ends a transaction-scoped private table's lifetime. With PRESERVE DEFINITION, a rollback undoes uncommitted DML; it does not undo previously committed rows. Private tables are not durable recovery or cross-session staging objects.
Two sessions connected with the same application account can create private temporary tables with the same name and independent definitions. A query in one session cannot use the other session's private definition. Ordinary session visibility is the scope here. Administrators have a separate diagnostic view, DBA_PRIVATE_TEMP_TABLES.
See creating private temporary tables for transaction and session lifetime, same-account sessions, and temporary storage.
Required name prefix
Every private temporary table name must start with the configured private_temp_table_prefix. The default value is ORA$PTT_.
SHOW PARAMETER private_temp_table_prefixUnquoted names become uppercase in metadata. Thus ora$ptt_work is inspected as ORA$PTT_WORK. Use the prefix configured in that database in every CREATE, DML statement, and metadata filter. The current session's private definitions are:
SELECT table_name, duration
FROM user_private_temp_tables
WHERE table_name IN ('ORA$PTT_WORK', 'ORA$PTT_SESSION');A private table in another session does not establish that the name is in use in this session.
See PRIVATE_TEMP_TABLE_PREFIX for the default and the required naming prefix, and SQL*Plus SHOW for parameter inspection.
Transaction lifetime
In session A, create a transaction-scoped private table and insert a row:
CREATE PRIVATE TEMPORARY TABLE ora$ptt_work (id NUMBER)
ON COMMIT DROP DEFINITION;
INSERT INTO ora$ptt_work VALUES (101);
SELECT id FROM ora$ptt_work;Before commit, A sees ID 101. The definition and the inserted row belong to A's current transaction. Creating the DROP DEFINITION form does not implicitly commit an existing transaction, so this form can be created within ongoing transactional work. Do not extrapolate that exception to every CREATE TABLE statement.
Committing immediately after CREATE or INSERT ends a transaction-scoped private table before the intended observation. SQL*Plus EXIT normally commits pending work unless instructed otherwise.
Inspect A's definition before committing:
SELECT table_name, duration
FROM user_private_temp_tables
WHERE table_name = 'ORA$PTT_WORK';| TABLE_NAME | DURATION |
|---|---|
ORA$PTT_WORK | TRANSACTION |
USER_PRIVATE_TEMP_TABLES describes private tables of the current session. Its DURATION values are TRANSACTION or SESSION. These differ from the SYS$TRANSACTION and SYS$SESSION values used for global temporary tables in USER_TABLES.
See USER_PRIVATE_TEMP_TABLES and DBA_PRIVATE_TEMP_TABLES for current-session scope and DURATION values.
Visibility before commit
While A's transaction is still open, session B queries the name without creating its own table:
SELECT id FROM ora$ptt_work;The query fails because B has no private definition with that name. This is a missing table in B, rather than a successful query returning zero rows. A still returns ID 101. Both sessions use the same account, but that does not make A's private definition usable in B.
B can create its own ORA$PTT_WORK with DROP DEFINITION and insert ID 201. A still sees 101, and B sees 201. Committing B ends B's private object.
Commit ends the transaction-scoped definition
In session A:
COMMIT;
SELECT table_name, duration
FROM user_private_temp_tables
WHERE table_name = 'ORA$PTT_WORK';
SELECT id FROM ora$ptt_work;| Check after A commits | Expected result |
|---|---|
| Matching metadata query | No matching entry |
Query of ORA$PTT_WORK | Fails because the table definition has ended |
The table itself is gone. COUNT(*) against it cannot report zero: COUNT(*) also needs a resolvable table. Recreate the private table when a later transaction needs a new disposable definition. The private table is not moved into another session or persisted by the commit.
Session lifetime across commits
A second private table in A keeps its definition across commits:
CREATE PRIVATE TEMPORARY TABLE ora$ptt_session (id NUMBER)
ON COMMIT PRESERVE DEFINITION;
INSERT INTO ora$ptt_session VALUES (101);
COMMIT;
SELECT id FROM ora$ptt_session;
SELECT table_name, duration
FROM user_private_temp_tables
WHERE table_name = 'ORA$PTT_SESSION';After commit, A still sees ID 101. The metadata for that session is:
| TABLE_NAME | DURATION |
|---|---|
ORA$PTT_SESSION | SESSION |
Both the definition and the committed row remain in A after commit. B cannot use A's private definition. Ending A's database session removes its private table and its data. Returning a logical connection to a pool may keep the underlying session alive. Check the pool's real session and reset behavior.
The DROP DEFINITION object is gone after its transaction ends. Remove the session-scoped private table in the creating session:
DROP TABLE ora$ptt_session;
SELECT table_name, duration
FROM user_private_temp_tables
WHERE table_name IN ('ORA$PTT_WORK', 'ORA$PTT_SESSION');After the transaction-scoped definition has ended and this drop runs, neither name has a matching entry. DROP TABLE of a private temporary table does not implicitly commit other transactional work and does not put the private table in the recycle bin. It still removes that private table and its contents. A new session cannot inspect or clean up the previous session's private tables.
See DROP TABLE for private-table removal and the implicit-commit exception.
Creation changes transaction behavior
| CREATE PRIVATE TEMPORARY TABLE clause | Implicit commit when creating it |
|---|---|
ON COMMIT DROP DEFINITION | No |
ON COMMIT PRESERVE DEFINITION | Yes; existing transactional work in that session is committed |
This difference matters inside application transactions. Choose the creation point together with the required lifetime.
Fit, restrictions and storage
Private temporary tables suit temporary calculation and report structures created for one session and discarded with a transaction or session. A global temporary table suits a reusable schema definition with session-private rows. Results that must be shared between sessions or survive loss of the creating session need an appropriate persistent design.
Private temporary tables cannot have indexes and cannot be referenced by permanent objects such as views or triggers. Other restrictions include no column defaults, materialized views or zone maps, and no access through database links.
Rows use temporary tablespace storage, by default the creating user's default temporary tablespace. Physical temp files on disk back that storage. Temporary segments are allocated on first insert. Session ownership is not a claim that all private-table data resides in a particular SGA or PGA area. Plan temporary space for the workload even though the object has a short lifetime.
See CREATE TABLE for private temporary tables, restrictions, and the different ON COMMIT rules. See temporary table concepts for global and private differences and temporary segment allocation, and managing data files and temp files for physical temporary storage.
Recap
A private temporary table keeps its definition and its rows in the session that creates it. ON COMMIT DROP DEFINITION is the default: commit, or a full rollback of that transaction, removes the definition and the data. After that commit, a query such as COUNT(*) fails because no definition remains. ON COMMIT PRESERVE DEFINITION keeps the definition and committed rows until the database session ends. A later rollback undoes uncommitted DML and leaves rows that already committed. Two sessions on the same account can each create a private table of the same name. The other session's query fails as a missing table. It does not return an empty result, and it does not see the first session's rows.
Names must begin with private_temp_table_prefix. The default is ORA$PTT_, and an unquoted name is stored in uppercase. USER_PRIVATE_TEMP_TABLES.DURATION is TRANSACTION or SESSION. Those values differ from SYS$TRANSACTION and SYS$SESSION on a global temporary table in USER_TABLES. Creating the DROP DEFINITION form does not implicitly commit. Creating the PRESERVE DEFINITION form does. Private temporary tables cannot have indexes, cannot be referenced by permanent objects, and store rows in the temporary tablespace. An indexed result that another session must read after the creating session ends needs a persistent design. DROP TABLE removes a session-scoped private table without an implicit commit and without the recycle bin. Ending the database session removes it as well. A new session cannot see the previous session's private tables, and returning a pooled connection can leave that session alive.
No comments:
Post a Comment