###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.
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
