declare perform_execute boolean; procedure putline_or_execute(s varchar2, perform_execute boolean) is begin if perform_execute = true then begin execute immediate s; exception when others then dbms_output.put_line('FAILED: '||s); raise; end; else dbms_output.put_line(s||';'||chr(10)); end if; end putline_or_execute; begin perform_execute := true; for com in ( with cte(table_name,constraint_name,r_table_name,r_constraint_name,deferred) as ( select r.table_name,r.constraint_name,p.table_name,p.constraint_name,r.DEFERRED from user_constraints r inner join user_constraints p on p.constraint_name = r.r_constraint_name where r.constraint_type = 'R' and r.deferred = 'IMMEDIATE' and r.status = 'ENABLED' ), hcte(table_name,constraint_name,r_table_name,r_constraint_name,lvl) as ( select null,null,e.table_name,null,1 from user_tables e where e.iot_name is null and e.table_name not in (select r_table_name from cte) union all select pa.table_name,pa.constraint_name,pa.r_table_name,pa.r_constraint_name , ch.lvl+1 from cte pa inner join hcte ch on pa.table_name = ch.r_table_name ) SEARCH breadth FIRST BY table_name set dep select 'delete from '||r_table_name statem,max(lvl) mx from hcte group by r_table_name order by max(lvl),r_table_name ) loop putline_or_execute(com.statem, perform_execute); end loop; end; /
Making a schema empty
A thing to a future Oracle version wish list. Schema level privileges. I mean way to grant select on all tables on a schema. These grants should become grants to the tables created afterwards. SQL Server has such possibility since 2008. One can find drop all schema objects scripts and discussions. This way you preserve the grants given to your database user. And no need to issue drop user cascade. Sometimes there is only a need to make all schema tables empty. After dropping tables you loose the grants given to others. Disabling foreign key constraints, truncating tables and enabling constraints may be a way to do this. If your tables are small truncating may do more work than just deleting. Also deleting requires foreign keys to be considered. One way is to disable them, delete and enable constraints. But another way could be deleting the rows in the order foreign keys are defined. Here is an anonymous block deleting all rows from all tables in a schema. It starts deleting rows from tables that are not referenced from another tables. And level by level in the foreign key tree deletes all rows from tables. You probably do not want to run this in a schema with huge tables.
- ▼ April (6)
- ► 2011 (38)
- ► 2010 (37)
- I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.