nerdexam
Oracle

1Z0-052 · Question #22

You executed this command to create a temporary table: SQL> CREATE GLOBAL TEMPORARY TABLE report_work_area ( startdate DATE, enddate DATE, class CHAR(20) ) ON COMMIT PRESERVE ROWS; Which statement…

The correct answer is A. The rows stay in the table only until session termination. A global temporary table created with ON COMMIT PRESERVE ROWS retains rows after each commit but automatically deletes all rows when the session that inserted them terminates.

Managing Schema Objects

Question

You executed this command to create a temporary table:

SQL> CREATE GLOBAL TEMPORARY TABLE report_work_area ( startdate DATE, enddate DATE, class CHAR(20) ) ON COMMIT PRESERVE ROWS; Which statement is true about the rows inserted into the REPORT_WORK_AREA table during a transaction?

Options

  • AThe rows stay in the table only until session termination
  • BThe rows stay in the table only until the next transaction starts on the table
  • CThe rows are visible to all current sessions after the transaction in committed
  • DThe rows stay available for subsequent sessions after the transaction is committed

How the community answered

(28 responses)
  • A
    89% (25)
  • C
    7% (2)
  • D
    4% (1)

Why each option

A global temporary table created with ON COMMIT PRESERVE ROWS retains rows after each commit but automatically deletes all rows when the session that inserted them terminates.

AThe rows stay in the table only until session terminationCorrect

The ON COMMIT PRESERVE ROWS clause causes Oracle to retain rows across transaction boundaries within the same session, but the rows are automatically purged when the session ends - making them available only to the inserting session for its entire duration, not beyond.

BThe rows stay in the table only until the next transaction starts on the table

Rows are not deleted when the next transaction starts; they persist across multiple transactions within the same session because ON COMMIT PRESERVE ROWS was specified rather than ON COMMIT DELETE ROWS.

CThe rows are visible to all current sessions after the transaction in committed

Global temporary table data is session-private by design - rows inserted by one session are never visible to any other concurrent session, regardless of whether they have been committed.

DThe rows stay available for subsequent sessions after the transaction is committed

Rows do not survive session termination; once the inserting session ends, all of its rows are automatically removed and are unavailable to any subsequent sessions.

Concept tested: Oracle global temporary table ON COMMIT PRESERVE ROWS behavior

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html

Topics

#global temporary tables#ON COMMIT PRESERVE ROWS#session scope#DDL

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-052 Practice