Oracle starts and disables constraints and deletes records that violate constraints

First, disable constraints

ALTER TABLE table_name DISABLE NOVALIDATE constraint constraint_name

Second, bulk import data

Be sure to check the violation of the constraint before opening the constraint record

1. Execute the script Utlexpt1.sql from Oracle to create the exceptions table. The script is in the Oracle_home/rdbms/admin directory

Sql> @oracle_home \rdbms\admin\utlexpt1.sql

2. Execute an ALTER statement with the exception option to add a record that violates the constraint to the exceptions table to save

ALTER TABLE table_name ENABLE VALIDATE constraint constraint_name exceptions into sys.exceptions;

3. Use subqueries in the exceptions table to lock invalid rows of data

Select COLUMN1,COLUMN2,COLUMN3 from table_name where ROWID in (

Select row_id from Sys.exceptions

) for update

4, according to the query results to modify the violation of the constraint record line (can be directly updated via ROWID)

5. Re-execute the statement with ALTER OPEN constraint

ALTER TABLE table_name ENABLE VALIDATE constraint constraint_name

6, whether the state of the query constraint is enable, validate

Select constraint_name,constraint_type,status,validated from dba_constraints where owner= ' owner ' and Table_name= ' TABLE_NAME ';

Four, confirm exceptions table useless after delete or TRUNCATE TABLE

drop table sys.exceptions;

How do I restore the recyclebin in the Recycle Bin?

Sql> Flashback table Cube_scope to before drop

The table name can be the dba_recyclebin.object_name of the Recycle Bin system or it can be dba_recyclebin.original_name

But at this point, I have a backup of the DDL statement rebuilt a new table, this time again with this command to restore the obvious error, this time what to do, can only be reverted to an alias, the specific Operation command is

Sql> Flashback table Cube_scope before drop rename to Cube_scope_old

Now that you have recovered the data from the table before the deletion, you can only insert Cube_scope from the data in the Cube_scope_old

sql> INSERT INTO Cube_scope select * from Cube_scope_old t

　

Oracle starts and disables constraints and deletes records that violate constraints