Overwrite User Tables/Views/etc. From Oracle Export File

I am having an Oracle import issue. Currently, I have an export from a production database. There is an existing test database that I want to overwrite with the production data (schema objects / data) for one user only.

As a side note, I do NOT have the sys password and dropping/recreating the user on the test instance is not an option.

After reviewing the Oracle documentation, it is not immediately apparent how to do this.

Surely, this is a common task. Please advise the best way to do this.

Thanks, any help is greatly appreciated.
All the best.
Who is Participating?
MikeOM_DBAConnect With a Mentor Commented:

Or maybe just using User_Objects will work:

set pages 0 trims on lin 120 feed off ver off
spo /tmp/drop_usr_obj.sql
Select 'Drop '||object_type||' '||object_name||';' From User_Objects;
spo off
!) I supouss ypou are working with a user that have the dba role or import role
in such case

you can drop the user

drop user USER cascade;

and then

create user USER identified by PASSWD default tablespace TABLESPACE temporary tablespace TEMP_TBS;

grant ROL to USER;
grant ROL2 to USER;

2) you don't want drop the user, then droip the user objects.

select 'drop '||object_type||' '||object_name||';' from all_objects;

then do import

If not, write a script to DROP all objects (for example):

set pages 0 trims on lin 120 feed off ver off
spo /tmp/drop_usr_obj.sql
-- Drop Views:
Select 'Drop View '||view_name||';' From User_Views;
-- Drop Sequences:
Select 'Drop Sequence '||sequence_name||';' From User_Sequences;
-- Drop tables:
Select 'Drop Table '||table_name||';' From User_Tables;
-- etc...
spo off

When importing, the tables cannot be populated.  You need to remove the data from the tables or as suggested drop the tables.

If you truncate the tables to remove the data then you can use the ignore=y option when you import so import does not try to create the tables.

What version of Oracle are you using?  If you're using 10g you will get more choice in what you can do using Data Pump rather than export/import.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup Zone:
Accept MikeOM_DBA(16698635)

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

Experts Exchange Cleanup Volunteer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.