Printable View
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
This is a very simplistic approach and you will probably need to disable any referential constraints.Code:SET HEADING OFF FEEDBACK OFF PAGES 0
SPOOL delete_table.sql
SELECT 'DROP TABLE '||table_name||';'
FROM user_tables;
SPOOL OFF
@delete_table
HTH
iF you wana drop all objects in a schema, then Drop schema & Recreate it. Any Probs?
Quote:
Originally posted by sonuji_in
I want to delete all the tables in my schema
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.
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?
Quote:
Originally posted by abhaysk
iF you wana drop all objects in a schema, then Drop schema & Recreate it. Any Probs?
well many times I cant be bothered to recreate users and grant all the privileges, roles, profiles
User creation/grant/assign_profile script wont do?
so whats the difference between writing a script to drop all objects and writing a script to create users then do all the grants, roles and profiles?
A script to create a user is specific each time, a script to drop objects can be used over and over again
Abhay, hasn't the original poster explicitely specified he doesn't have DBA privileges in his very first message? So how would he drop schema?
It has to be done object by object, no other way around, not with the description of the problem he provided. Ther's no magic "DROP ALL MY OBJECTS" command as he was hoping. Of course, using SQL to generate scripts to drop all objects is trivial and will speed up the dropping extensively. Jim has provided him the sample for the tables - the remaining stuff for all other type of objects should be easy.
Specifc? how...pass pram (user_name) and make ur script genric one.Quote:
Originally posted by davey23uk
A script to create a user is specific each time, a script to drop objects can be used over and over again
But yes I didnt notice, that He said he didnt have DBA Privilage as Jurij pointed me...:rolleyes:
Abhay.
A few changes to Pando's script make it generic and without the DBA dependency:
But it does not detract from the fact that the original post requested how to remove all tables and not all objects. Unless the poster is clear in thier requirements than confusion like this is bound to occur.Code:set termout on
set head off feedback off verify off termout off
spool /tmp/drop_all.sql
Select
Rtrim('drop '||object_type||' '
||object_name||decode(object_type,'TABLE',' cascade constraints')
|| ';' )
from user_objects
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
Regards