One possible solution would be to login as SYS and drop the schema itself, but I need to configure a single login for any database access, i.e., the user whose database objects will be deleted.
I did not find my specific solution online, but I owe thanks to alternative approaches in some other posts.
First, make sure that you connect to Oracle as the correct user, i.e., the one whose tables will be dropped.
BEGIN
FOR i IN (SELECT table_name FROM user_tables)
LOOP
EXECUTE IMMEDIATE('DROP TABLE ' || user || '.' || i.table_name || ' CASCADE CONSTRAINTS');
END LOOP;
END;
Similar commands can be used to drop all triggers, sequences, etc. For example,
BEGIN
FOR i IN (SELECT trigger_name FROM user_triggers)
LOOP
EXECUTE IMMEDIATE('DROP TRIGGER ' || user || '.' || i.trigger_name);
END LOOP;
END;
and
FOR i IN (SELECT sequence_name FROM user_sequences)
LOOP
EXECUTE IMMEDIATE('DROP SEQUENCE ' || user || '.' || i.sequence_name);
END LOOP;
END;
NB: if you drop all tables before triggers, your triggers will be renamed with special characters and the command might not work; make sure to drop triggers before tables.
29 comments:
Where was this post when I needed it a year ago? :)
Thanks for the comment, and I totally hear you. It seems like such an obvious thing to know, and it should come up right away in any web search, but it took me more than a minute to find the answer, so I figured it was worth posting. Hopefully this will save someone some time, even if it is too late to help you....
-----Tim-----
I should mention that this is what I did...
declare
cursor dropObjectsCusor is
select 'drop '|| object_type || ' ' || object_name as sqlDropStmt
from user_objects
where object_type <> 'TABLE' and object_type <> 'INDEX'
order by object_type;
cursor dropTablesCusor is
select 'truncate table ' || object_name as sqlTruncTbl,
'drop table ' || object_name || ' cascade constraints' as sqlDropTbl
from user_objects
where object_type = 'TABLE'
order by object_type;
begin
for ob in dropObjectsCusor
loop
execute immediate ob.sqlDropStmt;
end loop;
for ob in dropTablesCusor
loop
execute immediate ob.sqlTruncTbl;
execute immediate ob.sqlDropTbl;
end loop;
Great contribution. I may end up using some form of your script in the future. Thanks!
-----Tim------
Awesome script! Just what I needed! Thanks, you saved me a great deal of time...
Hi Emperor361,
Thanks for the feedback! Let me know if you come up with any improvements.
---Tim---
Thank you, it works great. I was lucky to find this post
Svetlana
Hi Svetlana,
Thanks for the feedback. If you run into any issues or have ideas for improvements, let me know!
---Tim---
Thank you ! It worked great ! I will visit this blog often.
Hi Ranganathan,
I appreciate the feedback and am happy that it worked for you!
---Tim---
Thanks.. It saved a lot of my time.
You could try this logging in as sysdba (just one log in will do the job)
For all tables
==============
SELECT 'DROP TABLE ' || owner || '.' || table_name || ' CASCADE CONSTRAINTS;'
FROM all_tables
/
For all triggers
================
SELECT 'DROP TRIGGER ' || owner || '.' || trigger_name || ' ;'
FROM all_triggers
/
For all sequences
=================
SELECT 'DROP SEQUENCE ' || sequence_owner || '.' || sequence_name || ' ;'
FROM all_sequences
/
Custom for specific owners/users (but make sure you log in as SYSDBA) Also substitute the sql above if tables, triggers or sequences and include/exclude users/owners as needed
====================================================================
Example for sequence
SELECT 'DROP SEQUENCE ' || sequence_owner || '.' || sequence_name || ';'
FROM all_sequences
where sequence_owner IN (
'SCOTT',
'SH',
'SSS',
'TEST1',
.
.
.
.
.
'WHD'
)
order by sequence_owner
Really useful, thanks!
Thanks for the feedback, Suzanne! Glad you found the post useful.
ya solution is quite brilliant that we can delete all the tables from the database at a time...... but how we can delete only 3tables (dept,emp,salgrade) from the database when there are 15 different tables from the same database.
thanks in advance
regards,
thanmathpradeep@gmail.com
Hi Pradeep,
If I understand your question correctly, I think I would modify the inner query for finding all the schema tables to "SELECT table_name FROM user_tables WHERE table_name IN ('dept','emp','salgrade')".
Let me know if that is what you were looking for.
Thanks,
---Tim---
hello Tim Myer::
my question is that ........... in my database i have 5tables i.e.,(emp,dept,salgrade,bonus,sales).
In that 5tables am looking to delete only three table i.e,(emp,dept,bonus) at a time and the remaining tables should be in the database itself.
This is my small requirement.thanks in advance
regards,
thanmathpradeep@gmail.com
Hi Pradeep,
It appears that I did understand your question correctly. Did you attempt what I suggested?
---Tim---
HELLO Tim Myer ,
I HAD TRIED UR COMMAND IT WAS EXCELLENT AND U HAD CREATED THE ABOVE MENTIONED COMMAND TO DROP ALL THE TABLES FROM THE DATABASE AT A TIME.
BUT AM ASKING IN A DIFFERENT WAY,LET ME EXPLAIN WITH ONE EXAMPLE ,,
LET AS ASSUME THE FOLLOWING THINGS...
(1) ASSUME THAT THE NAME OF THE DATABASE IS "PRADEEP"
(2) ASSUME THAT THERE ARE 8 TABLES IN THIS DATABASE.
(3) HERE I WOULD LIKE TO DROP ONLY FOUR TABLES AT A TIME AND I WOULD LIKE TO KEEP THE REMAINING TABLES.
SELECT *FROM TAB;
TABLE_NAME TABLE_TYPE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
BONUS TABLE
INTEREST TABLE
STUDENT_INFO TABLE
BANK_INFO TABLE
ECONOMY TABLE
HERE FROM THE ABOVE TABLES , I WOULD LIKE TO DROP FOUR TABLES I.E.,DEPT,EMP,SALGRADE,BONUS.
WE CAN DROP THOSE TABLES INDIVIDUALLY AS,
SQL>DROP TABLE DEPT;
TABLE DROPPED.
SQL>DROP TABLE EMP;
TABLE DROPPED.
SQL>DROP TABLE SALGRADE;
TABLE DROPPED.
SQL>DROP TABLE BONUS;
TABLE DROPPED.
BUT I WON'T LIKE TO DO THESE OPERATIONS SEPARATELY. I NEEDED A SINGLE,SIMPLE PL/SQL COMMAND TO DROP THESE FOUR TABLES FROM AN ORACLE DATABASE WITH NAMING EACH TABLE AND WITHOUT DROPPING THE ENTIRE SCHEMA.
HOPE YOU UNDERSTAND MY WORDS AND EXPECTATIONS .........
THANKS AGAIN
REGARDS,
THANMATHPRADEEP@GMAIL.COM
Hi Pradeep,
Yes, I understand what you are asking. I will ask you again, did you try what I suggested above?
BEGIN
FOR i IN (SELECT table_name FROM user_tables
WHERE table_name IN ('DEPT','EMP','SALGRADE','BONUS'))
LOOP
EXECUTE IMMEDIATE('DROP TABLE ' || user || '.' || i.table_name || ' CASCADE CONSTRAINTS');
END LOOP;
END;
You may need to uppercase the table name.
---Tim---
thank you sir, my doubt was clarified.
regards,
thanmathpradeep@gmail.com
hello sir,
I have another requirement ...........
let us consider the following table.
select *from pradeep;
ename sal deptno
----- ----- --------
pradeep 5000 10
prasad 2000 20
tulasi 3530 30
venkat 4400 40
sachin 1000 50
dravid 5000 60
sewag 10000 70
( sir,this is the related table)
my requirement is that
i want the get the out put as mentioned below,
ename sal deptno
----- ----- --------
prasad 2000 20
tulasi 3530 30
venkat 4400 40
my condition is that, the above mentioned data should be retrieved from the base table only when deptno is present in the sal
i.e., sal=(20)00 where deptno=20;
sal=35(30) where deptno=30;
sal=4(40)0 where deptno=40;
hope you understood my requirement..
thanks in advance....
regards,
thanmathpradeep@gmail.com
YA I GOT IS SUCCESSFULLY ,
I SOLVED IT BY D FOLLOWING QUERY
SELECT A.ENAME,B.SAL,A.COMM.B.DEPTNO FROM PRADEEP A,PRADEEP B WHERE A.DEPTNO=B.DEPTNO AND INSTR(A.SAL,B.DEPTNO)>0;
REGARDS,
THANMATHPRADEEP@GMAIL.COM
hi pradeep,
I have a backup 10g database server I have daily full export dmp file of my active database server. can you guide me how I can update my backup database server from full export file on daily basis. I want to import file on daily basis on my backup server to upto date my backup database server.
Thanks in advance
Muhammad Uzair (ORACLE APEX Developer)
Thanks a lot for sharing this nice information to save the time.
Thanks for sharing this helpful information to save my time.
A really helpful bit of code for someone new to Oracle.
Thanks
Works like a charm !!!
-- Why is this procedure not giving the output I intended-- delete all views
--It compiles fine and tests fine on pl/sql developer, but never deletes the views
-- I used your code and it worked fine
CREATE OR REPLACE PROCEDURE delete_views IS
v_qtext VARCHAR2(100);
rec_views VARCHAR2(40);
old_views VARCHAR2(40);
CURSOR rec_views IS(
SELECT ut.view_name view_name
FROM user_views ut
ORDER BY view_name);
BEGIN
OPEN rec_views;
FETCH rec_views
INTO old_views;
IF rec_views%NOTFOUND
THEN
v_qtext := 'DROP VIEW ' || old_views;
EXECUTE IMMEDIATE v_qtext;
END IF;
CLOSE old_views;
END delete_views;
Post a Comment