[每日一題] 11gOCP 1z0-052 :2013-09-24 temporary tables.........................................C11

轉載請注明出處：http://blog.csdn.net/guoyjoe/article/details/11991583

正确答案：ACE

建臨表有兩種類型：

分别建立如下：

 (1)當送出時，将自動清除表中所有資料。

[email protected]>  create global temporary table temp_t1(id int,name varchar2(10)) on commit delete rows;
[email protected]> insert into temp_t1 values(1,'AAAAA');

1 row created.
[email protected]> select * from temp_t1;

        ID NAME
---------- ----------
         1 AAAAA
           

送出後再查temp_t1，沒有記錄了。

[email protected]> commit;

Commit complete.

[email protected]> select * from temp_t1;

no rows selected
           

(2)隻有當會話退出後，臨時表中的行才會被清除。

[email protected]> create global temporary table  temp_t2(id int,name varchar2(10)) on commit preserve rows;

Table created.

[email protected]> insert into temp_t2 values(1,'AAAAA');

1 row created.

[email protected]> commit;

Commit complete.

[email protected]> select * from temp_t2;

        ID NAME
---------- ----------
         1 AAAAA
           

開另一會話，查temp_t2，結果如下：

[email protected]> select sid from v$mystat where rownum=1;

       SID
----------
        16

[email protected]> select * from temp_t2;

no rows selected
           

說明臨時表是私有的，每個會話隻能查到目前會話下自己DML的資料，每個會話互不幹涉，是以臨時表不需要鎖。

我們來解析答案：

答案A,正确，在臨時表上可以建立索引和視圖，實驗如下：

（1）建立索引

[email protected]> create index indx_temp_t2 on temp_t1(id);

Index created.

（2）建立視圖

[email protected]> create view v_temp_t2 as select * from temp_t2;

View created.

答案B：不正确，隻能導出臨時表結構而不能導出臨時表中的資料。

[[email protected] ~]$ exp gyj/gyj file=/home/oracle/gyj.dmp;

Export: Release 11.2.0.3.0 - Production on Tue Sep 24 21:04:44 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user GYJ 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user GYJ 
About to export GYJ's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export GYJ's tables via Conventional Path ...
. . exporting table                           DEPT          2 rows exported
. . exporting table                            EMP          3 rows exported
. . exporting table                      NEW_ORDER          1 rows exported
. . exporting table                             T1          2 rows exported
. . exporting table                           T100          2 rows exported
. . exporting table                        TEMP_T1
. . exporting table                        TEMP_T2
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
           

如果不信你可以把gyj.dmp資料用工具imp導入另一個使用者,然後查一下temp_t1,temp_t2臨時表中有沒有資料，這個我就不做了，留給大家思考、實踐。臨時表是私有的資料隻是臨時存放，送出或着退出會話都會釋放臨時表中的資料。

 答案C，正确。臨時表并非存放在使用者的表空間中，而是存放在 Schema 所指定的臨時表空間中。

[email protected]> Select Table_Name, Tablespace_Name From User_Tables Where Table_Name Like 'TEMP%';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_T1
TEMP_T2
           

可見這兩張臨時表并未存放在使用者的表空間中。

使用者 GYJ的臨時表空間是 TEMP , 使用者建立的臨時表是存放在TEMP表空間中的。下面來證明

[email protected]> SELECT UserName, Default_Tablespace def_ts, Temporary_Tablespace temp_ts  FROM User_Users;

USERNAME                       DEF_TS                         TEMP_TS
------------------------------ ------------------------------ ------------------------------
GYJ                            GYJ                            TEMP
[email protected]> alter tablespace temp tempfile  offline;
alter tablespace temp tempfile  offline
*
ERROR at line 1:
ORA-12905: default temporary tablespace cannot be brought OFFLINE

[email protected]> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/ocm/temp02.dbf' size 10M;

Tablespace created.

[email protected]> alter database  default temporary tablespace temp1;

Database altered.

[email protected]>  alter tablespace temp tempfile  offline;

Tablespace altered.

[email protected]> INSERT INTO TEMP_T2 VALUES(1,'AAAAA');
INSERT INTO TEMP_T2 VALUES(1,'AAAAA')
            *
ERROR at line 1:
ORA-00376: file 201 cannot be read at this time
ORA-01110: data file 201: '/u01/app/oracle/oradata/ocm/temp01.dbf'
           

答案：D不正确。臨時表是私有，不同的會話互相之間是不能看到對方操作的資料。以上有證明我就不再舉例了。

答案：E正确。臨時表是私有的，每個會話隻能查到目前會話下自己DML的資料，每個會話互不幹涉，是以臨時表不需要鎖。這個大家可以去實驗驗證。

QQ：252803295

技術交流QQ群：

DSI&Core Search  Ⅰ 群：127149411（2000人技術群:未滿）

DSI&Core Search  Ⅱ 群：177089463（1000人技術群:未滿）

DSI&Core Search  Ⅲ 群：284596437（500人技術群:未滿）

DSI&Core Search  Ⅳ 群：192136702（500人技術群:未滿）

DSI&Core Search  Ⅴ 群：285030382（500人閑聊群:未滿）

MAIL：[email protected]

BLOG： http://blog.csdn.net/guoyjoe

WEIBO：http://weibo.com/guoyJoe0218

ITPUB： http://www.itpub.net/space-uid-28460966.html

OCM：   http://education.oracle.com/education/otn/YGuo.HTM