truncate清空一张表记录发现ORA-02266，进行模拟测试
SQL> truncate table p;
truncate table p
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
--错误原因
SQL> !oerr ora 02266
02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"
// *Cause: An attempt was made to truncate a table with unique or
// primary keys referenced by foreign keys enabled in another table.
// Other operations not allowed are dropping/truncating a partition of a
// partitioned table or an ALTER TABLE EXCHANGE PARTITION.
// *Action: Before performing the above operations the table, disable the
// foreign key constraints in other tables. You can see what
// constraints are referencing a table by issuing the following
// command:
// SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
--查询是否有主键或者唯一index
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
SET LONG 999999
SET PAGESIZE 1000
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);--不显示存储信息
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','P') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','P')
--------------------------------------------------------------------------------
CREATE TABLE "CHF"."P"
( "X" NUMBER(*,0),
PRIMARY KEY ("X")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS
--查询主外键关系
SQL> col 主键表名 for a20
SQL> col 主键列名 for a20
SQL> col 外键表名 for a20
SQL> col 外键列名 for a20
SQL> select b.table_name 主键表名,
b.column_name 主键列名,
a.table_name 外键表名,
a.column_name 外键列名
from (select a.constraint_name,
b.table_name,
b.column_name,
a.r_constraint_name
from dba_constraints a, dba_cons_columns b
WHERE a.constraint_type = 'R'
and a.constraint_name = b.constraint_name) a,
(select distinct a.r_constraint_name, b.table_name, b.column_name
from dba_constraints a, dba_cons_columns b
WHERE a.constraint_type = 'R'
and a.r_constraint_name = b.constraint_name) b
where a.r_constraint_name = b.r_constraint_name
and b.table_name='P';
主键表名 主键列名 外键表名 外键列名
-------------------- -------------------- -------------------- --------------------
P X C X
1 row selected.
--处理方法一
SQL> alter table p disable primary key cascade;
Table altered.
SQL> truncate table p;
Table truncated.
SQL> alter table p enable primary key;
Table altered.
--处理方法二
SQL> delete from t;
1 row deleted.
SQL> commit;
Commit complete.
