Imagine you have a list of tables in your Oracle DB and you want to drop them all using a client like SQLDeveloper. That's easier said then done, as you have to drop each table individually.
Therefore, I wrote a SQL query that generates a "DROP-sql-script":
SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables;
- user_tables is a system table which contains all the tables of the user
- the SELECT clause will generate a DROP statement for every table
This will generate something like:
DROP TABLE "FOO" CASCADE CONSTRAINTS; DROP TABLE "BAR" CASCADE CONSTRAINTS; DROP TABLE "DUMMY" CASCADE CONSTRAINTS; ...
Now you can easily run that script :-)
Comments
Thanks, this helped a lot!
This was a lifesaver as DBA was not around and needed to do this urgently to get app back up and running, it worked perfectly
Thank you!
thanks very good
thanx
Thank you,
in my case i don't have all the table names in a table. do u have any script that removes table without mentioning them in another table.
Dear,
user_tables is system table, it's not a table which I created. Try to select from user_tables, you will have it as well.
Kind regards,
Jochen
You are god!, Thanks
Thanks , But what i would write in place of table_name if i want to drop more than one table
Hi irshad,
The SELECT statement will generate DROP statements for each table. table_name is variable, the value is retrieved from the SELECT query.
Jochen
I LOVE YOUR CODE IT HELPED ME A LOT <3
thanks helped a lot
i copied and pasted the script you provided and a list of the tables came up..what should i do next to delete them
Select all the text and copy/paste it in a new query window
I tried using the above command
SELECT 'DROP TABLE "' || table_name || '" CASCADE CONSTRAINTS;' FROM user_tables;
its giving below error...pls let me know how to del all tables
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
00604. 00000 - "error occurred at recursive SQL level %s"
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.
Error at Line: 1 Column: 70
Are you sure the 'quotes' are placed correct?
Very excellent, Thank you.
Thanks..... it's very helpfull to me...... Thank you so much
Thanks heaps
I had previously set up 3 databases in SQL Developer using scripts and a setup batch file provided along with the Murach PL/SQL book. Now I am taking a class and want to set up my own (empty) database. I followed instructions found on numerous sites to create a new connection and database, but the database created was auto-populated with a large number of unwanted tables (most with a $ in the name). I can drop/delete most of these tables individually, however that is slow-going and some can't be dropped (an error message appears). I tried running the provided script (edited for the auto-created table names), and it displays as if its working, but then leaves all of the tables intact.
Is there a way to force-drop all existing tables? Or better yet, is there a way to create a new database that has no auto-generated tables so the only ones in it will be the ones I create?
Thanks for any replies
it shows error for me
ORA-00904: "TABLE_NAME": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 1 Column: 25
please advise what it could be
helped, thx
Add new comment