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

转载请注明出处:http://blog.csdn.net/guoyjoe/article/details/11991583



正确答案:ACE

 

建临表有两种类型:

分别创建如下:

 (1)当提交时,将自动清除表中所有数据。

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,没有记录了。

gyj@OCM> commit;

Commit complete.

gyj@OCM> select * from temp_t1;

no rows selected

(2)只有当会话退出后,临时表中的行才会被清除。

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,结果如下:

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:不正确,只能导出临时表结构而不能导出临时表中的数据。

[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
. . 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 所指定的临时表空间中

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表空间中的。下面来证明

gyj@OCM> SELECT UserName, Default_Tablespace def_ts, Temporary_Tablespace temp_ts  FROM User_Users;

USERNAME                       DEF_TS                         TEMP_TS
------------------------------ ------------------------------ ------------------------------
GYJ                            GYJ                            TEMP
gyj@OCM> alter tablespace temp tempfile  offline;
alter tablespace temp tempfile  offline
*
ERROR at line 1:
ORA-12905: default temporary tablespace cannot be brought OFFLINE

gyj@OCM> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/ocm/temp02.dbf' size 10M;

Tablespace created.

gyj@OCM> alter database  default temporary tablespace temp1;

Database altered.

gyj@OCM>  alter tablespace temp tempfile  offline;

Tablespace altered.

gyj@OCM> 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:dbathink@hotmail.com

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 




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值