Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    14

    Unanswered: SQL import/exports

    Hi there!

    Is there any safe way to export data from one Oracle database, and import it on another Oracle database, using only SQL selects/inserts? Thanks in advance,

    Arménio Pinto

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    I'm confused by the terminology.
    What do you mean by SAFE?
    By export, do you mean export via exp utility?

  3. #3
    Join Date
    Jan 2004
    Posts
    14
    Originally posted by SkyWriter
    I'm confused by the terminology.
    What do you mean by SAFE?
    By export, do you mean export via exp utility?
    Sorry for my poor explanation. The problem is that I patched Oracle 9.2.0.1.0 with patchset 9.2.0.4 and that ruined exp/imp utility operation. I've been talking with Oracle for the last 3 days, throught Metalink, and they can't solve my problem properly.

    Anyway, I'm able to "sqlplus" to all the users and "select" all the tables... So, I thought it could be possible to "export" the data with simple SQL statements. Am I right? I pretend something like:

    1. Create all the users and tables in the new database.
    2. Export data from the old database to files called userXXX.sql, where XXX are the various users.
    3. Run each script in the respective user, in the new database.

    Thanks!

    Arménio Pinto

  4. #4
    Join Date
    Jan 2004
    Posts
    370
    I'm still not sure how you intend to 'export' the data to xxx.sql files.

    You could set up a database link between the databases and copy the data if you don't need to export too much data.
    Last edited by SkyWriter; 01-14-04 at 20:30.

  5. #5
    Join Date
    Jan 2004
    Posts
    14
    Originally posted by SkyWriter
    I'm still not sure how you intend to 'export' the data to xxx.sql files.

    You could set up a database link between the databases and copy the data if you don't need to export too much data.
    That's not possible, because there's no connectivity between the two databases. But think about this: a "select" returns a table-like output; if you can manipulate this output and write it to a file, you could do the following:

    1. select * from mytable;
    2. tweak "select" output into an "insert";
    3. write the result to a file;

    Arménio Pinto

  6. #6
    Join Date
    Jan 2004
    Posts
    370
    Ah, OK.
    I was just trying to figure out what you were aiming for.
    An alternative way is to dump out the tables to to a flat file and use sql*loader to load them into the database. You could try using sql*plus (with set colsep ',' or whatever) to do this.


    It may also be worthwhile posting your export errors in separate post on this forum. Somebody may be able to help you out with those.

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I'm struggling to believe that a problem with core functionality such as exp/imp could not be solved by Oracle support after three days.

    I'd like to know exactly what the problem is, and Oracle's response as to how or why it cannot be fixed.

    My instincts tell me there is either something you don't understand or something you expect too much of.

    I'm not trying to say I could fix it, just that I'm having difficulty believing it. We all start up SqlPlus/SvrMgrl every day expecting it to work.

    Please correct me if I'm wrong, and my sincere apologies if I am - I'm just a little sceptical. I also need a solid description of such a core problem.

    Regards
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  8. #8
    Join Date
    Jan 2004
    Posts
    14
    If found the procedure I need in this Metalink document. If anyone is interested:

    http://metalink.oracle.com/metalink/...&p_id=123852.1

    Thanks everybody,
    Arménio Pinto

  9. #9
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: SQL import/exports

    I do not know really why you want to duplicate a database so. I guess that you are looking for that option because until the moment you do not know another one.

    You have several ways to duplicate a database:

    1.- RMAN ( Difficult to do )
    2.- Exp/Imp utilities ( Easy but you have to care some details )
    3.- Recreating Controlfile ( It faster and better if the database is not so large )

    Your database is very large ? What is the size of the database ?
    Joel Pérez

Posting Permissions

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