Hi freiends
I have a schema with about 100 tables. Is there a single command to drop all tables in the schema rather than having to call DROP TABLE 100 times?
Printable View
Hi freiends
I have a schema with about 100 tables. Is there a single command to drop all tables in the schema rather than having to call DROP TABLE 100 times?
No such thing there.. but if possible you can drop the user and recreate it.
spool drop.sql
select 'drop table '||table_name||';' from tabs;
spool off
@drop
write a script to create the sql to do it for you like this..
Code:set echo off termout on pages 0 feedback off
accept site prompt 'Enter db name: '
spool run_drop_table1.&site..sql
select 'drop table '||owner||'.'||table_name||' cascade constraint;'
from dba_tables where owner in ('insert_owner_here') order by table_name desc;
spool off
set echo on termout on pages 24 feedback on
spool truncate.lst
@run_drop_table1.&site..sql
Davey and OracleDoc,
You will be still calling "DROP TABLE" 100 times. See the original post.
yeah i know, but it is kinda of one statement.
Just an alternative because if you drop the user, lose all your rights in the database, thats all
Sanjay, why do you always have to be so meticulous :p Once again you are correct.
I was just going under the assumption that the orginal poster understood what you said was correct, and that the only way you could do it quickly was through the script.
SQL> Drop user Yo_Moma cascade;
This WILL drop all the schema table with one command.
So what, I colored outside the lines, kill me!
MH
The other option is to do a dropt tablespace xxx including contents. Assuming that the only only objects owned by the schema are in the table space that you want dropped.
Therefore if all of the objects are in 1 tablespace and only one tablespace, with no other data. This will meet your requirements.
I would either drop the user or do dynamic sql to drop the tables.
:D
Is that a contest?
How would you hang an entire production environment with a single command and the hands tied? :D