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.

In article <1194920706.612932.209790 (AT) 22g2000hsm (DOT) googlegroups.com>, ddog
says...
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

On Nov 12, 8:44 pm, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com>
wrote:
A bit of overkill, really, as a simple SQL*Plus script and a spool
operation would do the same thing without context switching:

set head off pagesize 0 linesize 200 trimspool on verify off feedback
off termout off

select 'truncate table '||table_name||';'
from user_tables

spool trunc_usr_tbls.sql
/
spool off

set feedback on termout on head on pagesize 60

@trunc_usr_tbls



David Fitzjarrell

fitzjarrell (AT) cox (DOT) net wrote:

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...

"Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> wrote

The truncate will fail if you have referential constraints. You have to
disable those constraints first.

--
Terry Dykstra

On 13 nov, 23:09, "Terry Dykstra" <tddyks... (AT) forestoil (DOT) ca> wrote:
Eehhh - yes.

And one should add error trapping, too:

begin
for i in (select table_name from user_tables) loop
begin
execute immediate 'truncate table '||i.table_name;
exception
when others then
null;
end;
end loop;
end;
/

Maybe David is having a point after all (although he's running into
the same issue)

On Nov 14, 6:03 am, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com>
wrote:
I did not take into account any constraints and, yes, the truncates
will fail on parent tables when the child tables have data.
Presumably a second run of the script would truncate the parents after
the children have been successfully truncated.

My issue was with the original respondent blindly recommending PL/SQL
to do such a job when, in many cases, a simple SQL*Plus script would
suffice and probably be a bit more performant.


David Fitzjarrell