TABLE CONSTRAINTS STATUS

 set lines 400
col constraint_name for a20;
col constraint_type for a20;
col search_condition for a20;
select CONSTRAINT_NAME,CONSTRAINT_TYPE ,SEARCH_CONDITION,validated from USER_CONSTRAINTS where table_name in ('table_name');

select CONSTRAINT_NAME,CONSTRAINT_TYPE ,SEARCH_CONDITION,validated from USER_CONSTRAINTS where owner='schema';

col owner for a20;
col constraint_name for a20;
col constraint_type for a5;
col table_name for a20;
col status for a20;
col validated for a5;
col invalid for a5;
col index_owner for a20;
col index_name for a20;
select owner,constraint_name,constraint_type,table_name,status,validated,invalid,index_owner,index_name from all_constraints where owner='&username' and table_name='&table';

spool 1_truncateActiveUserTables_02262023.out

set echo on

show user

select * from global_name;

-- disable the constraints so the table can be truncated

ALTER TABLE SCHEMA.TABLE

  DISABLE CONSTRAINT CONSTRAINT_NAME;

drop table SCHEMA.TABLE_OLD_BACKUP;

create table SCHEMA.TABLE_NEW_BACKUP as select * from SCHEMA.TABLE;

select count(*) from SCHEMA.TABLE_NEW_BACKUP;

select count(*) from SCHEMA.TABLE;

truncate table SCHEMA.TABLE;

-- enable the constraints

ALTER TABLE SCHEMA.TABLE

  ENABLE CONSTRAINT CONSTRAINT_NAME;

-- disable the constraints

 ALTER TABLE SCHEMA.TABLE

  DISABLE CONSTRAINT CONSTRAINT_NAME;

drop table SCHEMA.TABLE_OLD_BACKUP;

create table SCHEMA.TABLE_NEW_BACKUP as select * from SCHEMA.TABLE;

select count(*) from SCHEMA.NEW_BACKUP;

select count(*) from SCHEMA.TABLE;

truncate table SCHEMA.TABLE;

drop table SCHEMA.TABLE_OLD_BACKUP;

create table SCHEMA.TABLE_NEW_BACKUP as select * from SCHEMA.TABLE;

select count(*) from SCHEMA.TABLE_NEW_BACKUP;

select count(*) from SCHEMA.TABLE;

truncate table SCHEMA.TABLE;

-- enable the constraints

 ALTER TABLE SCHEMA.TABLE

  ENABLE CONSTRAINT CONSTRAINT_NAME;

drop table SCHEMA.TABLE_OLD_BACKUP;

create table SCHEMA.TABLE_NEW_BACKUP as select * from SCHEMA.TABLE;

select count(*) from SCHEMA.TABLE_NEW_BACKUP;

select count(*) from SCHEMA.TABLE;

truncate table SCHEMA.TABLE;

-- confirm counts are all 0

select count(*) from SCHEMA.TABLE;

select count(*) from SCHEMA.TABLE;

select count(*) from SCHEMA.TABLE;

select count(*) from SCHEMA.TABLE;

select count(*) from SCHEMA.TABLE;

spool off

********************* To check status of constraints on tables **********************

set lines 400

col constraint_name for a20;

col constraint_type for a20;

col search_condition for a20;

select CONSTRAINT_NAME,CONSTRAINT_TYPE ,SEARCH_CONDITION,validated from USER_CONSTRAINTS where table_name in ('TABLE','TABLE');