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

Posted on 2006-05-16
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

    !) 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


    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


    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
    LVL 1

    Expert Comment

    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

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    Via a live example, show how to take different types of Oracle backups using RMAN.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now