OCP-1Z0-052-V8.02-146题

146. Identify three situations in which you would NOT be able to drop a tablespace. (Choose three.)

A.when the tablespace is online

B.when the tablespace has segments with data in it

C.when the tablespace is a default permanent tablespace for the database

D.when the tablespace contains a table that is currently being used by transactions

E.when the tablespace contains undo data that is needed to rollback an uncommitted transaction

Answer: CDE

答案解析:

A,B答案:

1、创建表空间

sys@TEST0924> create tablespace lxtbs datafile '/u01/app/oracle/oradata/test0924/lxtb01.dbf' size 10m;

Tablespace created.
2、创建表并指定表空间
sys@TEST0924> create table lxtb tablespace lxtbs as select * from scott.emp;
Table created.
sys@TEST0924> select tablespace_name,status,contents from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
SYSTEM ONLINE PERMANENT
SYSAUX ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
EXAMPLE ONLINE PERMANENT
LXTBS ONLINE PERMANENT
7 rows selected.
sys@TEST0924> select table_name,tablespace_name from dba_tables where table_name='LXTB';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
LXTB LXTBS
3、此时插入数据,不提交,然后drop表空间,报错。A,B错
sys@TEST0924> drop tablespace lxtbs;
drop tablespace lxtbs
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
sys@TEST0924> drop tablespace lxtbs INCLUDING CONTENTS ;
Tablespace dropped.

C答案:

sys@TEST0924> select TABLESPACE_NAME,CONTENTS,LOGGING from dba_tablespaces
2 ;
TABLESPACE_NAME CONTENTS LOGGING
------------------------------ --------- ---------
SYSTEM PERMANENT LOGGING
SYSAUX PERMANENT LOGGING
UNDOTBS1 UNDO LOGGING
TEMP TEMPORARY NOLOGGING
USERS PERMANENT LOGGING
EXAMPLE PERMANENT NOLOGGING
6 rows selected.
sys@TEST0924> drop tablespace USERS;
drop tablespace USERS
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
D,E答案

You cannot drop a tablespace that contains any active segments.

For example, if a table in the tablespace iscurrently being used or the tablespace contains undo data needed to roll backuncommitted transactions, you cannot drop the tablespace. The tablespace can be online or offline, but it is best to take the tablespace offline before dropping it.

官方参考:http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#i1007008

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值