Export From User Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Export From User Question

  1. #1
    Join Date
    Feb 2001
    Posts
    34

    Export From User Question

    I have 2 Oracle databases running on 2 different
    Solaris 8 machines. Database 1 is a Oracle 8.1.5 database and Database 2 is an Oracle 8.1.7. I want to import the tables from database 1 to database 2 via user_name. This user_name is the same for both databases. The ultimate goal is to get all the tables owned by said user into 1 tablespace on database 2. Easy enough. Here's the rub. Both databases have the same tablespaces names (Help). Both database have the same table names (11 total). Both databases have the same user name (User1). However, the "exporting" databases have all the tables in 1 tablespace (Help) while the matching tables on database are in 2 different tablespaces (Help and Data). User1 owns the tables in both Help and Data in the recieving database.
    How can I make sure that when I import database 2 tables into database 1 the tables in Data are not overwritten. I want all the imported tables to go into the Help tablespace.
    Damn, I hope this is easier to understand then what I've written.
    Ken Hammer

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I want to import the tables from database 1 to database 2 ... How can I make sure that when I import database 2 tables into database 1 the tables in Data are not overwritten. I want all the imported tables to go into the Help tablespace.
    Which way are you importing?

    You don't want to "overwrite" the tables in Data, but you do want all the tables to import into Help?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ===
    How can I make sure that when I import database 2 tables into database 1 the tables in Data are not overwritten. I want all the imported tables to go into the Help tablespace.
    Damn, I hope this is easier to understand then what I've written.
    =====

    From your post I understand, you want to duplicate the tables in 2 different tablespaces in db-2. It is not possible. One schema will have only table irrespective of tablespace.

    Tamil

  4. #4
    Join Date
    Jan 2002
    Posts
    113
    Your question is about as confusing as possible! From what I can tell you want to import tables that already exist and don't what to overwrite them in database 2. Do you want to append the data to the current table in database 2? The import utility will not overwrite your existing tables, it will append them if you use IGNORE=Y.
    Arm yourself with knowledge

  5. #5
    Join Date
    Feb 2001
    Posts
    34
    To clarify.

    What I have: (All tables owned by the "Ken" User)

    Production Staging <- Data Base
    Help Data Help < - Tablespace
    Tbl A Tbl D Tbl A
    Tbl B Tbl E Tbl B <- Tables
    Tbl C Tbl F Tbl C
    Tbl D
    Tbl E
    Tbl F
    etc

    I can not have the tables that are in the Data table space above over written, by the tables with the same names from the Staging data base.
    The tables that are in the Production data base in the Help table space can be dropped or over written. So I need to keep the tables owned by the "ken" user in the Data table space above to remain untocuched after the import. Basically, the Help table space on production needs to look like the help table space on Staging (Table wise). So I would end up with fully qualified table names like this:
    help.ken.table_A AND data.ken.table_A. Same table name, same owner, but different table spaces and contents..

    HTH.
    Ken Hammer

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    As a previous member pointed out, you cannot have two tables with the same name in the same schema, regardless of what tablespace they are in. You would have to rename the existing ones, or the new ones, or import the new ones to a different schema.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width