Export / Import question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Export / Import question

  1. #1
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66

    Export / Import question

    Hi all,

    I want to export a table 'ACCOUNTS on a daily basis, with export dump file name ACCOUNTS_ddmmyyyy.dmp. I may need to re-import this table at a later date so that I can compare balances between any two days.
    At the moment, when I do the import I need to import the table into a different schema as I dont want to overwrite the live ACCOUNTS table.
    (The import tries to 'CREATE TABLE ACCOUNTS')
    I would like to import the table into the same schema with table name
    ACCOUNTS_ddmmyyyy.
    Is there any option that I can specify at the export stage that can, in effect, rename the table to the same name as the dump file?

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you cannot

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Try something like:

    a) in PL/SQL as dynamic SQL:
    CREATE TABLE ACCOUNTS_ddmmyyyy AS SELECT * FROM ACCOUNTS;
    (you will need some code to generate the tablename)

    b) the export job should select this table - again you'll need to generate the names to use in the parameter file (how? - will depend on OS).

    c) DROP TABLE ACCOUNTS_ddmmyyyy; when you are done.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    we are talking about import not export

  5. #5
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66
    Thanks for the swift replies.
    DaPi, I had used a method similar to yours to get around the problem.
    ie import ACCOUNTS to different schema, create ACCOUNTS_ddmmyy in my schema as select from different schema.
    ACCOUNTS is very large table so I was just trying to see I could find a quicker way of doing things.


    Thanks again.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    to a different schema obviously you can do it but under same schema impossible, even you change your create table statement in the dump file are you gonna change all INSERT statements in the dump risking of corrupting the dump file? guess not

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    bgill,

    "great minds . . " Looks like your decison has to be when to spend the extra time (you need the extra disk space no matter what).

    Taking extra time after the import minimizes the total time lost (since you do this rarely) BUT it's when the **** hits the fan that you need to act fast, so there might be an argument to make the export job "slow" to enable you to do an import at top speed.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  8. #8
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66
    Pando, DaPi,
    Thanks for the help. Now I need to decide if its better to save time on export or import. Probably just toss a coin!

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Another approach would be ...

    connect accounts_owner/password

    create table ACCOUNTS_28112002 as select * from accounts where rownum < 1;

    grant insert ,select on ACCOUNTS_28112002 to accounts_importer;

    connect accounts_importer

    create view accounts as select * from accounts_owner.ACCOUNTS_28112002;

    ... then import ACCOUNTS into the accounts_importer schema with IGNORE=Y.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Good one slimdave!
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

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