How to delete all tables in oracle

The way Oracle deletes all tables is

1. Disable all foreign key constraints

Execute the following statement under pl/sql developer:

SELECT 'ALTER TABLE ' || table_name || ' disable CONSTRAINT ' || constraint_name || ';' FROM user_constraints
 where CONSTRAINT_TYPE = 'R';

Copy the query result and execute it under pl/sql developer.
If you don't have pl/sql developer, you can operate it in sqlplus. The method is as follows:
1. Open sqlplus and connect with the corresponding user.
2. Set the pagesize larger, such as set pagesize 20000
3. Use spool to import the corresponding results to the file, such as

SQL> spool /home/oracle/constraint.sql
SQL> SELECT 'ALTER TABLE ' || table_name || ' disable CONSTRAINT ' || constraint_name || ';' FROM user_constraints 
where CONSTRAINT_TYPE = 'R';
SQL> spool off

4. The corresponding statement has been generated script, but there are redundant statements at the beginning and end of the script file. Open it with a text editor and delete the useless statements
5. Log in to sqlplus with the corresponding user again and execute the following command

SQL> @/home/oracle/constraint.sql

2. Use delete or truncate to delete the contents of all tables

SELECT 'DELETE FROM '|| table_name || ';' FROM USER_TABLES
ORDER BY TABLE_NAME;

or

SELECT 'TRUNCATE TABLE '|| table_name || ';' FROM USER_TABLES
ORDER BY TABLE_NAME;

Use a similar method to the first step . One thing to note is that if the data in the table is associated with a trigger, you can only use the truncate statement. However, the truncate statement cannot be rolled back, so you should pay attention when

3. Disable the Open the foreign key

SELECT 'ALTER TABLE ' || table_name || ' enable CONSTRAINT ' || constraint_name || ';' FROM user_constraints 
where CONSTRAINT_TYPE = 'R';

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to delete all tables in oracle. For more information, please follow other related articles on the PHP Chinese website!