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 :-)
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
thanks very good
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.
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.
You are god!, Thanks
Thanks , But what i would write in place of table_name if i want to drop more than one table
The SELECT statement will generate DROP statements for each table. table_name is variable, the value is retrieved from the SELECT query.
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
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"
Error at Line: 1 Column: 25
please advise what it could be