-
Delete all table in the schema with a single command
Hi All,
I want to delete all the tables in my schema in a single command / function. I don't have DBA rights for this schema and I have only SQL Plus. I am working on :
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
PL/SQL Release 9.0.1.0.0 - Production
CORE 9.0.1.0.0 Production
TNS for Solaris: Version 9.0.1.0.0 - Production
NLSRTL Version 9.0.1.0.0 - Production
Thanks in adv,
-
spool drop.sql
select 'drop table ' || table_name || ';' from tabs;
spool off
@drop
-
Run the following
Code:
SET HEADING OFF FEEDBACK OFF PAGES 0
SPOOL delete_table.sql
SELECT 'DROP TABLE '||table_name||';'
FROM user_tables;
SPOOL OFF
@delete_table
This is a very simplistic approach and you will probably need to disable any referential constraints.
HTH
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
iF you wana drop all objects in a schema, then Drop schema & Recreate it. Any Probs?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Re: Delete all table in the schema with a single command
Originally posted by sonuji_in
I want to delete all the tables in my schema
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
Jim :
I saw that he indeed wrote Tables, but what I guessed, seeing the way he posted, was he wants to flush all of the objects(Tables,Procs,Packages and so)...
Let him comment on it.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Want to delete all objects in a schema
Thanks for the co-operation and instant reply....
This procedure worked fine and I was able to delete all the tables :
spool drop.sql
select 'drop table ' || table_name || ';' from tabs;
spool off
@drop
but, I want to delete all tbe objects in the schema.
Thanks,,
-
Code:
set termout on
set head off feedback off verify off termout off
spool /tmp/drop_all.sql
Select
Rtrim('drop '||object_type||' '||owner||'.'
||object_name||decode(object_type,'TABLE',' cascade constraints')
|| ';' )
from dba_objects
where owner = upper('&owner')
order by object_name
/
spool off
set head on feedback on verify on
spool /tmp/drop_all.log
set echo on
@/tmp/drop_all.sql
spool off
set echo off
set pagesize 14 termout on
-
I dunno why you guys wana do circus....isnt this enough?
Originally posted by abhaysk
iF you wana drop all objects in a schema, then Drop schema & Recreate it. Any Probs?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
well many times I cant be bothered to recreate users and grant all the privileges, roles, profiles
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
|