Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2002
    Posts
    23

    Unanswered: Import data from another Oracle db

    Is there any tool?

    I understand sqlloader is just for importing data from a flat file(?).

    what I vision is I can use the tool to get data from oracle db A and put into oracle db B(with some chekcing, filtering operations of course). Pls note it's not just import a whole table. Data is from multiple tables in A and goes to multiple tables in B.

    Many thanks!

    -Lili
    DTS -> Oracle

  2. #2
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    I understand sqlloader is just for importing data from a flat file(?).
    Right. And you can choose how to compose this flat file before loading it. Use something like that:

    spool c:\tmp\data.dat

    SELECT column1 || '~' || column2 || '~'
    FROM table1
    WHERE <some condition>;

    spool off

    Then load the file in the database2. Of course, you can manipulate the data before goes to the flat file the way how you like it. Just write as much SQL - PL/SQL code as you need to get the data out from the first database.

    Hope that helps,

    clio_usa
    OCP -DBA

    visit our web site
    -------------------

  3. #3
    Join Date
    Apr 2002
    Posts
    23
    Thank u for the information. Now I have access to both database, so I'm thinking of getting data from one db directly to another db, instead of creating a flat file as the middle-man. Is that possible?

    Example:
    I created a view(location_view) in db A. If I can feed the data into db B in a temporary table, I can write procedure to update necessary tables/fields in db B.

    Not sure whether I explain my thoughts/needs clearly...

    Thanks,

    -Lili
    DTS -> Oracle

  4. #4
    Join Date
    May 2002
    Location
    Moscow, Russia
    Posts
    34
    Create public/private database links between two databases.
    Hope this helps.

  5. #5
    Join Date
    Apr 2002
    Posts
    23
    This is what I need! But how to? Sorry for my ignorance
    DTS -> Oracle

  6. #6
    Join Date
    May 2002
    Location
    Moscow, Russia
    Posts
    34
    Originally posted by lili3000
    This is what I need! But how to? Sorry for my ignorance
    1. you must have alias of DB A in tnsnames.ora of DB B and vice versa
    2. create db link by using the following command:
    CREATE [PUBLIC] DATABASE LINK <link name> USING 'your alias
    name';
    3. If you don't want to use the same names for both db_link and alias in
    tnsnames.ora set GLOBAL_NAMES init parameter to FALSE, otherwise
    you would need to use a little bit different syntax for CREATE
    command.
    4. Query your tables via database link like:
    SELECT * FROM <table name>@<link name>;

  7. #7
    Join Date
    May 2002
    Posts
    8
    Hi,

    Try to use exp / imp Oracle utilities. That's the fastest way to move data from one Oracle db to another Oracle db. You need to have a resource role for that.

    Thank you.

  8. #8
    Join Date
    Apr 2002
    Posts
    23
    import util can only import tables but not views from another db
    DTS -> Oracle

  9. #9
    Join Date
    May 2002
    Posts
    8
    no, you can specify no data (structures only) that can bring tables, views,stored proc, packages, functions, materialized vews, types ... whatever you need.

    In order to recreate views you need only DDL statements that you can extract from the source database and run on target.

    Thanks.

  10. #10
    Join Date
    Jun 2002
    Posts
    7

    imp/exp with parfiles

    The easiest and fastest way is to do an import and export using a parfile rather than invoking imp and answering teh questions interactively. Then you can specufy rows=n for just the table structure with no data, etc.

Posting Permissions

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