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

Posted on 2006-05-16
Medium Priority
Last Modified: 2010-05-18
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.
Question by:sirishageeth

Expert Comment

ID: 16695512
!) 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
LVL 29

Expert Comment

ID: 16698583

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

LVL 29

Accepted Solution

MikeOM_DBA earned 500 total points
ID: 16698635

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

Expert Comment

ID: 16715631
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.
LVL 22

Expert Comment

ID: 19743735
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month14 days, 23 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question