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?