The main problem when you want to empty all the tables in a schema is the foreign keys. It's pretty simple to disable them, but meanwhile the users can write some data, data that can lead to errors, without the enforced constraints.
So, there it is a script, made to empty the tables, without disabling any foreign key. Pay attention to the posible foreign keys from another schema, this script is to be run under the sys user, and the tables from another schema, that reference your tables, from the schema wanted to be empty, will be emptied as well. The ow variable is containing the name of the schema owner.
Enjoy!
declare
ow varchar2(100);
rcount number;
begin
ow:='SCOTT';
rcount:=0;
for i in (select * from (
select a.owner powner,a.table_name tableswithoutpk, b.table_name ptable,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || a.owner || '.' || '"'||a.table_name||'"')),'/ROWSET/ROW/C')) pcount,
b.constraint_name pkname, b.status pkstatus,
c.table_name chtable, c.owner chowner, c.constraint_name fkname, c.status fkstatus,
decode(c.table_name, null, null, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || c.owner || '.' || '"'||c.table_name||'"')),'/ROWSET/ROW/C'))) chCount
from all_tables a
left outer join all_constraints b on b.owner=ow and b.constraint_type='P' and a.table_name=b.table_name
left outer join all_constraints c on c.R_constraint_name=b.constraint_name
where a.owner=ow order by ptable
)
where chcount is null
)
loop
null;
execute immediate 'delete from '||i.powner ||'.'||i.tableswithoutpk;
end loop;
while true LOOP
for i in (select * from (
select a.owner powner,a.table_name tableswithoutpk, b.table_name ptable,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || a.owner || '.' || '"'||a.table_name||'"')),'/ROWSET/ROW/C')) pcount,
b.constraint_name pkname, b.status pkstatus,
c.table_name chtable, c.owner chowner, c.constraint_name fkname, c.status fkstatus,
decode(c.table_name, null, null, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || c.owner || '.' || '"'||c.table_name||'"')),'/ROWSET/ROW/C'))) chCount
from all_tables a
left outer join all_constraints b on b.owner=ow and b.constraint_type='P' and a.table_name=b.table_name
left outer join all_constraints c on c.R_constraint_name=b.constraint_name
where a.owner=ow order by ptable
)
where chcount >0)
loop
execute immediate 'delete from '||i.chowner||'.'||i.chtable;
end loop;
for i in (select * from (
select a.owner powner, a.table_name tableswithoutpk,b.table_name ptable,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || a.owner || '.' || '"'||a.table_name||'"')),'/ROWSET/ROW/C')) pcount,
b.constraint_name pkname, b.status pkstatus,
c.table_name chtable, c.owner chowner, c.constraint_name fkname, c.status fkstatus,
decode(c.table_name, null, null, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || c.owner || '.' || '"'||c.table_name||'"')),'/ROWSET/ROW/C'))) chCount
from all_tables a
join all_constraints b on b.owner=ow and b.constraint_type='P' and a.table_name=b.table_name
join all_constraints c on c.R_constraint_name=b.constraint_name
where a.owner=ow order by ptable
)
where chcount =0 and pcount>0)
loop
execute immediate 'delete from '||i.powner||'.'||i.ptable;
end loop;
dbms_output.put_line('444');
select sum(pcount) into rcount from
(
select * from (
select a.owner powner, a.table_name tableswithoutpk, b.table_name ptable,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || a.owner || '.' || '"'||a.table_name||'"')),'/ROWSET/ROW/C')) pcount,
b.constraint_name pkname, b.status pkstatus,
c.table_name chtable, c.owner chowner, c.constraint_name fkname, c.status fkstatus,
decode(c.table_name, null, null, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT count(*) c FROM ' || c.owner || '.' || '"'||c.table_name||'"')),'/ROWSET/ROW/C'))) chCount
from all_tables a
/*left outer */join all_constraints b on b.owner=ow and b.constraint_type='P' and a.table_name=b.table_name
/*left outer*/ join all_constraints c on c.R_constraint_name=b.constraint_name
where a.owner=ow order by ptable
)
)
;
EXIT WHEN rcount=0;
END LOOP;
end;
... and don't forget to commit!!
If you want to make some exercises, using the SCOTT schema, you want to save somewhere the content of the SCOTT tables. Use these lines bellow:
begin
for i in (select * from all_tables where owner='SCOTT') loop
execute immediate 'create table '||i.table_name||' as select * from SCOTT.'||i.table_name;
end loop;
end;
/
... creates the backup tables, under the sys schema
begin
for i in (select * from all_tables where owner='SCOTT') loop
execute immediate 'insert into scott.'||i.table_name||' select * from '||i.table_name;
end loop;
end;
/
... reloads the SCOTT tables, for a future run...
Misto !!!
RăspundețiȘtergere