Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: drop and re-add schema
fitzjarrell_at_cox.net wrote:
> On Nov 12, 8:44 pm, Geoff Muldoon <geoff.muld..._at_trap.gmail.com>
> wrote:
>> In article <1194920706.612932.209..._at_22g2000hsm.googlegroups.com>, ddog >> says... >> >>> What are the commands to drop a schema you are logged in to (or is >>> this even possible?) and recreate it? All of the tables need to be >>> dropped and recreated. Rather than drop 100+ tables one at a time, I >>> was hoping there would be a reasonable way to accomplish this. >> Assuming that you can only operate within the schema (don't have DBA >> rights where you could EXPort the schema and IMPort it with the >> appropriate "with no data" flags), then I guess all you really want to do >> is just to TRUNCATE the tables (completely empty the data in them all) >> rather than drop and then recreate them ... >> >> ... create a PL/SQL procedure which has a cursor returning all the table >> names (select TABLE_NAME from USER_TABLES) and loop through this cursor >> using EXECUTE IMMEDIATE dynamic SQL to truncate them. >> >> GM
begin
for i in (select table_name from user_tables) loop
execute immediate 'truncate table '||i.table_name;
end loop;
end;
/
-- Regards, Frank van Bortel Top-posting is one way to shut me up...Received on Tue Nov 13 2007 - 15:10:27 CST
![]() |
![]() |