truncate FK에러 및 모든 테이블 카운트 SQL

1.truncate table때 FK제약 에러가 발생하면.. ORA-02266: unique/primary keys in table referenced by enabled foreign keys

set pagesize 500;
select 'alter table '||a.owner||'.'||a.table_name||' disable constraint'||a.constraint_name||';' from all_constraints a, all_constraints b where a.constraint_type = 'R' and a.r_constraint_name = b.constraint_name and a.r_owner  = b.owner and b.table_name = 'テーブル名';
http://snisaac.blogspot.jp/2012/08/ora-02266-uniqueprimary-keys-in-table.html

2.모든 테이블에 저장된 레코드를 카운트

select table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) count from user_tables where table_name not like 'BIN$%' and (iot_type !=  'IOT_OVERFLOW' or iot_type is null) order by table_name;
]]>

Related Posts