Drop All tables in Oracle using SQL Statement | Coding Cluster - using asp.net, c#, mvc 4, iphone, php, ios, javascript, in asp.net mvc 3 & more
 

Drop All tables in Oracle using SQL Statement

Friday

Dropping all user tables in Oracle:
                               Sometime for developer want drop all table, and this is the working example that I'm used.

Note:
  •  You need to log in to that user which you wanted to drop the tables
  •  Keep in mind, If run, it will not able to rollback

Here is the SQL Script:

BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
('TABLE',
'VIEW',
'PACKAGE',
'PROCEDURE',
'FUNCTION',
'SEQUENCE'
))
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE'
THEN
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"';
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'FAILED: DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"'
);
END;
END LOOP;
END;


I got this solution from http://stackoverflow.com/questions/1690404/how-to-drop-all-user-tables

0 comments:

Post a Comment

Share your thoughts here...

 
 
 

RECENT POSTS

Boost

 
Blogger Widgets