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.

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;
/