• 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

  • 相关阅读:
    2018 蓝桥杯省赛 B 组模拟赛(一)-年龄
    在win10系统下安装和卸载Ubuntu系统(为了搞双系统)的各种办法
    2018 CCPC 中国大学生程序设计竞赛-网络选拔赛 1004(D题 )Find Integer(三角函数+费马大定理)
    HDU(杭州电子科技大学) 2614 Beat (BFS写法)
    SQL server用到的SQL语句备份下
    【SQL Server】SQL触发器经验详解
    SQL SERVER 语句大全
    sqlserver 触发器实例代码
    触发器deleted 表和 inserted 表详解
    SQL server触发器中 update insert delete 分别给写个例子被。
  • 原文地址:https://www.cnblogs.com/hzcya1995/p/13317151.html
Copyright © 2020-2023  润新知