web analytics

Oracle: disable all constraints referencing the table

BEGIN FOR cur IN (SELECT fk.owner, fk.constraint_name , fk.table_name     FROM all_constraints fk, all_constraints pk     WHERE fk.CONSTRAINT_TYPE = ‘R’ AND     pk.owner = ‘&which_owner’ AND     fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME            AND pk.TABLE_NAME = ‘&which_table’         ) LOOP         EXECUTE IMMEDIATE ‘ALTER TABLE ‘||cur.owner||’.’||cur.table_name||
                ‘ MODIFY CONSTRAINT ‘||cur.constraint_name||’ DISABLE’;

Oracle: info about the corrupted block

SELECT * FROM dba_extents WHERE  file_id = &file_id AND    &block_id BETWEEN block_id AND block_id + blocks – 1;

Oracle: using recycling bin

Information about the objects in recycle bin:

SELECT object_name,  original_name, TYPE,  can_undrop , can_purge ,  droptime FROM recyclebin


show recyclebin


flashback TABLE mytable TO before DROP;

Clear recycle bin

purge recyclebin;