OCP-1Z0-053-V12.02-278题

278.You issued the following command:

CREATE GLOBAL TEMPORARY TABLE admin_work_area (startdate DATE, enddate DATE, class

CHAR(20)) ON COMMIT DELETE ROWS TABLESPACE tbs_t1;

An index is then created on the ADMIN_WORK_AREA temporary table.

Which two statements are true regarding the TBS_T1 tablespace in the above command? (Choose two.)

A. It stores only the temporary table but not its indexes.

B. It stores both the temporary table as well as its indexes.

C. It must be a nondefault temporary tablespace for the database.

D. It can be a default or nondefault temporary tablespace for the database.

E. It must be the default temporary tablespace of the user who issues the command.

Answer: BD

答案解析：

参考：http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11633

 

Creating a Temporary Table

Temporary tables are useful in applications where a result set is to be buffered (temporarily persisted), perhaps because it is constructed by running multiple DML operations. For example, consider the following:

A Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.

During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped.

The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table.

Use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT clause indicates if the data in the table is transaction-specific(the default) or session-specific, the implications of which are as follows:

ON COMMIT Setting	Implications
DELETE ROWS	This creates a temporary table that is transaction specific. A session becomes bound to the temporary table with a transactions first insert into the table. The binding goes away at the end of the transaction. The database truncates the table (delete all rows) after each commit.
PRESERVE ROWS	This creates a temporary table that is session specific. A session gets bound to the temporary table with the first insert into the table in the session. This binding goes away at the end of the session or by issuing aTRUNCATE of the table in the session. The database truncates the table when you terminate the session.

This statement creates a temporary table that is transaction specific:

CREATE GLOBAL TEMPORARY TABLE admin_work_area

        (startdate DATE,

         enddate DATE,

         class CHAR(20))

      ON COMMIT DELETE ROWS;

Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.

By default, rows in a temporary table are stored in the default temporary tablespace of the user who creates it. However, you can assign a temporary table to another tablespace upon creation of the temporary table by using the TABLESPACE clause of CREATE GLOBAL TEMPORARY TABLE. You can use this feature to conserve space used by temporary tables. For example, if you must perform many small temporary table operations and the default temporary tablespace is configured for sort operations and thus uses a large extent size, these small operations will consume lots of unnecessary disk space. In this case it is better to allocate a second temporary tablespace with a smaller extent size.

The following two statements create a temporary tablespace with a 64 KB extent size, and then a new temporary table in that tablespace.

CREATE TEMPORARY TABLESPACE tbs_t1

    TEMPFILE 'tbs_t1.f' SIZE 50m REUSE AUTOEXTEND ON

    MAXSIZE UNLIMITED

    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

 

CREATE GLOBAL TEMPORARY TABLE admin_work_area

        (startdate DATE,

         enddate DATE,

         class CHAR(20))

      ON COMMIT DELETE ROWS

      TABLESPACE tbs_t1;