[每日一题] 11gOCP 1z0-052 :2013-09-24 temporary tables
[每日一题] 11gOCP 1z0-052 :2013-09-24 temporary tables
正确答案：ACE
建临表有两种类型：
分别创建如下：
(1)当提交时，将自动清除表中所有数据。
[html]
gyj@OCM> create global temporary table temp_t1(id int,name varchar2(10)) on commit delete rows;
gyj@OCM> insert into temp_t1 values(1,'AAAAA');
1 row created.
gyj@OCM> select * from temp_t1;
ID NAME
---------- ----------
1 AAAAA
提交后再查temp_t1，没有记录了。
[html]
gyj@OCM> commit;
Commit complete.
gyj@OCM> select * from temp_t1;
no rows selected
(2)只有当会话退出后，临时表中的行才会被清除。
[html]
gyj@OCM> create global temporary table temp_t2(id int,name varchar2(10)) on commit preserve rows;
Table created.
gyj@OCM> insert into temp_t2 values(1,'AAAAA');
1 row created.
gyj@OCM> commit;
Commit complete.
gyj@OCM> select * from temp_t2;
ID NAME
---------- ----------
1 AAAAA
开另一会话，查temp_t2，结果如下：
[html]
gyj@OCM> select sid from v$mystat where rownum=1;
SID
----------
16
gyj@OCM> select * from temp_t2;
no rows selected
说明临时表是私有的，每个会话只能查到当前会话下自己DML的数据，每个会话互不干涉，因此临时表不需要锁。
我们来解析答案：
答案A,正确，在临时表上可以创建索引和视图，实验如下：
（1）创建索引
gyj@OCM> create index indx_temp_t2 on temp_t1(id);
Index created.
（2）创建视图
gyj@OCM> create view v_temp_t2 as select * from temp_t2;
View created.
答案B：不正确，只能导出临时表结构而不能导出临时表中的数据。
[html]
[oracle@mydb ~]$ 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
. .<span style="color:#ff0000;"> exporting table TEMP_T1
. . exporting table TEMP_T2</span>
. 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 所指定的临时表空间中。
[html]
gyj@OCM> Select Table_Name, Tablespace_Name From User_Tables Where Table_Name Like 'TEMP%';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_T1
TEMP_T2
可见这两张临时表并未存放在用户的表空间中。
用户 GYJ的临时表空间是 TEMP , 用户创建的临时表是存放在TEMP表空间中的。下面来证明
[html]
gyj@OCM> SELECT UserName, Default_Tablespace def_ts, Temporary_Tablespace temp_ts FROM User_Users;
USERNAME DEF_TS TEMP_TS
------------------------------ ------------------------------ ------------------------------
GYJ GYJ &nbs
