-
Quick way to drop all tables in a schema!
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.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
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
Oracle it's not just a database it's a lifestyle!
--------------
BTW....You need to get a girlfriend who's last name isn't .jpg
-
Davey and OracleDoc,
You will be still calling "DROP TABLE" 100 times. See the original post.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
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 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.
Oracle it's not just a database it's a lifestyle!
--------------
BTW....You need to get a girlfriend who's last name isn't .jpg
-
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
I remember when this place was cool.
-
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.
-
Is that a contest?
How would you hang an entire production environment with a single command and the hands tied?
An ounce of action is worth a ton of theory.
—Friedrich Engels
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|