Query to delete all Objects from specific user in Oracle database 11gR2
Posted by Mir Sayeed Hassan on February 4th, 2019
Query to delete all Objects from specific user in Oracle database 11gR2
Database Status
sys@TESTDB>select instance_name,status,version from V$instance; INSTANCE_NAME STATUS VERSION ---------------- ------------ ----------------- testdb OPEN 11.2.0.4.0
Connect to specific user “MIR”
sys@TESTDB>connect mir Enter password: Connected.
Check the object exist in user “MIR”
mir@TESTDB>select table_name from user_tables; TABLE_NAME ------------------------------ T1_TEST T2_TEST T3_TEST -- --
mir@TESTDB>select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';') from user_objects; 'DROP'||OBJECT_TYPE||''||OBJECT_NAME||DECODE(OBJECT_TYPE,'TABLE','CASCADECONSTRA -------------------------------------------------------------------------------- drop TABLE T1_TEST CASCADE CONSTRAINTS; drop TABLE T2_TEST CASCADE CONSTRAINTS; drop TABLE T3_TEST CASCADE CONSTRAINTS; 3 rows selected.
To drop the above tables
mir@TESTDB>drop drop TABLE T1_TEST CASCADE CONSTRAINTS; drop TABLE T1_TEST CASCADE CONSTRAINTS; drop TABLE T2_TEST CASCADE CONSTRAINTS; Table dropped. Table dropped. Table dropped.
=========Hence tested & verified in our test env=========