Drop all user objects (prior to an oracle import)

# ——————————————————————————————-
Principle:

• Step 1: the generate_drop_all.sql script is used to generate drop statements for all objects that belongs to the current user. Theses statements are generated into drop_all_objects.sql
• Step 2: the drop_all_objects.sql script is used to drop everything prior to import
• Step 3: import…

Details:

# Step 1 :Move to the /tmp directory
# Copy the generate_drop_all.sql file here
# Generate the drop script
exit | sqlplus target_user/target_password @generate_drop_all.sql

# -> a new file drop_all.sql is generated

# Step2 : Run the drop script
exit | sqlplus target_user/target_password @drop_all_objects.sql

# Step3 : Import the dump
imp userid=target_user/target_password file= the_dump_file.dump log=the_log_file.log fromuser=origin_user_name touser=target_user_name

Content of the generate_drop_all.sql file

set feedback off
set trimspool on
set echo off
set verify off
set linesize 200
set pagesize 0
spool drop_all_objects.sql

select ‘drop ‘ || object_type || ‘ ‘ || object_name || ‘ cascade constraints;’
from user_objects
where object_type in (‘TABLE’);

select ‘drop ‘ || object_type || ‘ ‘ || object_name || ‘;’
from user_objects
where object_type not in (‘TABLE’, ‘INDEX’, ‘PACKAGE BODY’, ‘LOB’);

select ‘commit;’
spool off