Oracle 基础篇 --- 表删除

###2.2 删除表

删除一个表将会删除：

• 数据
• 表结构
• 数据库触发器
• 相应的索引
• 关联的对象权限

DROP TABLE 语句的可选子句：

• CASCADE CONSTRAINTS：相关的引用完整性约束条件
• PURGE：无法闪回

DROP TABLE 的一些考虑事项：

• 如果不使用PURGE子句，则表定义、关联索引和触发器会被放置在回收站中。表数据仍存在，但如不使用修改后的表名称就无法访问（将表放在RECYCLEBIN 时表名称发生了更改）。如果使用Enterprise Manager 删除了表，则不需要使用PURGE 子句。

• 使用FLASHBACK TABLE 命令可从回收站恢复方案对象。PURGE RECYCLEBIN 命令可清空回收站。

• 需要使用CASCADE CONSTRAINTS 选项才能删除所有相关的引用完整性约束条件。

注： 如果不使用PURGE选项，那么表及表索引所占用的空间仍会计入用户的表空间可用限额中。也就是说，这些空间仍被视为已占用。但是，如果用户已经达到了其限额，则系统会自动从回收站对象中回收空间。

######################删除表######################
SQL> create table emp2 as select * from emp1;

Table created.

SQL> desc emp2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP1_ID                                            NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBE                                        VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)
 SCORE                                              NUMBER(3)

SQL> select constraint_name, constraint_type from user_constraints where table_name = 'EMP2';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C0044626                   C
SYS_C0044627                   C
SYS_C0044628                   C
SYS_C0044629                   C

SQL> drop table emp2;

Table dropped.

###查看闪回区
SQL> desc USER_RECYCLEBIN;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 ORIGINAL_NAME                                      VARCHAR2(32)
 OPERATION                                          VARCHAR2(9)
 TYPE                                               VARCHAR2(25)
 TS_NAME                                            VARCHAR2(30)
 CREATETIME                                         VARCHAR2(19)
 DROPTIME                                           VARCHAR2(19)
 DROPSCN                                            NUMBER
 PARTITION_NAME                                     VARCHAR2(32)
 CAN_UNDROP                                         VARCHAR2(3)
 CAN_PURGE                                          VARCHAR2(3)
 RELATED                                   NOT NULL NUMBER
 BASE_OBJECT                               NOT NULL NUMBER
 PURGE_OBJECT                              NOT NULL NUMBER
 SPACE                                              NUMBER

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from USER_RECYCLEBIN;

OBJECT_NAME                    ORIGINAL_NAME                    TYPE
------------------------------ -------------------------------- -------------------------
BIN$Gccf4DgLkOfgUOAKyMMgAQ==$0 EMP2                             TABLE
BIN$GSvDnQHLJFDgUOAKyMNIWg==$0 TEST01                           TABLE
BIN$GSusjACgDczgUOAKyMNG9Q==$0 TEST03                           TABLE
BIN$GSm4+ZP7c1fgUOAKyMNDdQ==$0 TEST_01                          TABLE
BIN$FyE8rj5stLDgUOAKyMMgiQ==$0 EMP1                             TABLE
BIN$FyE8rj5ntLDgUOAKyMMgiQ==$0 EMPL                             TABLE
BIN$FyE8rj5itLDgUOAKyMMgiQ==$0 EMP1                             TABLE
BIN$FxuyiLIm053gUOAKyMMSgw==$0 EMP1                             TABLE
BIN$/ktf0vegBxjgQOAKyMNg6A==$0 LOCATIONS_DEMO                   TABLE
BIN$/ktf0vefBxjgQOAKyMNg6A==$0 LOC_ID_PK1                       INDEX
BIN$/C1n/wm/Y0jgQOAKyMMSaQ==$0 MY_TALBE                         TABLE

###恢复删掉的表
SQL> flashback table emp2 to before drop;

Flashback complete.

SQL> select constraint_name, constraint_type from user_constraints where table_name = 'EMP2';

CONSTRAINT_NAME                C
------------------------------ -
BIN$Gccf4DgHkOfgUOAKyMMgAQ==$0 C
BIN$Gccf4DgIkOfgUOAKyMMgAQ==$0 C
BIN$Gccf4DgJkOfgUOAKyMMgAQ==$0 C
BIN$Gccf4DgKkOfgUOAKyMMgAQ==$0 C

SQL> select count(*) from emp2;

  COUNT(*)
----------
         1

####################删除表带有purge####################
SQL> drop table emp2 purge;

Table dropped.

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from USER_RECYCLEBIN where ORIGINAL_NAME = 'EMP2';

no rows selected

####################删除表带有cascade constraints####################
create table dep3
(dep1_id number(2) constraint dep1_id_pk primary key,
 dep1_name varchar(10));

insert into dep3 values(1, 'dev');
insert into dep3 values(2, 'qa');

commit; 

create table emp3
(emp3_id number(3) constraint emp3_id_pk primary key,
 emp3_name varchar(10),
 dep_id number(2) constraint sele references dep3(dep1_id)
);

insert into emp3 values(001, 'dev1', 1 );
insert into emp3 values(002, 'qa1', 2);

commit;

SQL> select * from emp3;

   EMP3_ID EMP3_NAME      DEP_ID
---------- ---------- ----------
         1 dev1                1
         2 qa1                 2

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints where table_name = 'EMP3';

CONSTRAINT_NAME                C
------------------------------ -
EMP3_ID_PK                     P
DEP_ID_FK                      R
		 

SQL> drop table dep3;
drop table dep3
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> alter table emp3
  2  disable novalidate constraint dep_id_fk;

Table altered.

SQL> drop table dep3;
drop table dep3
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

#需要使用CASCADE CONSTRAINTS 选项才能删除所有相关的引用完整性约束条件。
SQL> drop table dep3 cascade constraints;

Table dropped.

SQL> select * from emp3;

   EMP3_ID EMP3_NAME      DEP_ID
---------- ---------- ----------
         1 dev1                1
         2 qa1                 2

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints where table_name = 'EMP3';

CONSTRAINT_NAME                C
------------------------------ -
EMP3_ID_PK                     P


#添加外键约束
SQL> alter table emp3
  2  add constraint emp3_id_fk foreign key (dep_id) references dep3(dep1_id);

Table altered.

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints where table_name = 'EMP3';

CONSTRAINT_NAME                C
------------------------------ -
EMP3_ID_PK                     P
EMP3_ID_FK                     R

截断表

• 截断表时会删除数据并释放已用空间。
• 会截断相应的索引。
• 通过将高水位标记(HWM)设置到表开头，将表标记为空，从而使表行不可用。
• 由于TRUNCATE TABLE 是DDL 命令，因此不会生成还原数据，而且会隐式提交命令。
• 不会截断外键引用的表。
• 使用此命令时不会触发删除触发器。

注： 要删除所有表行，这种方式通常比发出DELETE 语句的速度要快很多倍，原因如下：

• Oracle DB 重置表的HWM，而不是象DELETE 一样处理每行。
• 不生成还原数据。

####Truncate表
SQL> truncate table dep3 ;
truncate table dep3
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

#根据表来查看引用外键的表
SELECT uc.constraint_name,
       uc.constraint_type,
       uc.table_name,
       uc.r_constraint_name
  FROM user_constraints uc, user_cons_columns ucc
 WHERE UC.R_CONSTRAINT_NAME = UCC.CONSTRAINT_NAME AND UCC.TABLE_NAME = 'DEP3';

CONSTRAINT_NAME      C TABLE_NAME      R_CONSTRAINT_NAME
-------------------- - --------------- --------------------
EMP3_ID_FK           R EMP3            DEP1_ID_PK

#禁用掉外键
SQL> alter table emp3
  2  disable novalidate constraint emp3_id_fk;

Table altered.

SQL> truncate table emp3;

Table truncated.