Results 1 to 11 of 11
  1. #1
    Join Date
    May 2003
    Posts
    12

    Unanswered: Export - Import question

    Greetings,

    I am about to make export - Import in 8.0.4 database which runs under Windows NT server 4.0. I do this to defragment the database. I have a plan how to make it, though I would like you to confirm I am making it right (I am not used to databases running under windows).

    First:
    I open the command prompt at C:\
    and write following line:

    Exp system/<password> file=c:\expdat.dmp full=y COMPRESS=Y owner=<user to be exported>

    Then Import:
    imp80 userid= <user to be imported> file=C:\expdat.dmp full=y commit=Y ignore=y

    So, if this goes as it should, it will defrag the database, for all the information is under that one user I am about to export. If there is any flaws in this, please tell me. All comments are welcome
    ------------------------------------
    Long days and pleasant nights
    - Roland the gunslinger
    ------------------------------------

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I have noticed no major difference run exp/imp on Windows or UNIX

  3. #3
    Join Date
    May 2003
    Posts
    12
    However, when I make import, it should overwrite the data in the database. Correct? (if it does not, then there obviously will be no defragmentation happening).
    ------------------------------------
    Long days and pleasant nights
    - Roland the gunslinger
    ------------------------------------

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

    Re: Export - Import question

    I recommend you to do this to set ORACLE_SID enviroment variable or to add the service name to the command line

    Exp system/<password>@service_name file=c:\expdat.dmp full=y COMPRESS=Y owner=<user to be exported>

    the same for this:

    imp80 userid= <user to be imported> file=C:\expdat.dmp full=y commit=Y ignore=y

    before import the schema you have to drop the user and recreate it again or perform activities to avoid errors during the load between new obejcts and old objects.
    Joel Pérez

  5. #5
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151
    Originally posted by Karibu
    However, when I make import, it should overwrite the data in the database. Correct? (if it does not, then there obviously will be no defragmentation happening).
    That is not so simple as you are thinking
    Joel Pérez

  6. #6
    Join Date
    May 2003
    Posts
    12

    Re: Export - Import question

    Originally posted by joelperez
    before import the schema you have to drop the user and recreate it again or perform activities to avoid errors during the load between new obejcts and old objects.
    Okay, I was thinking this might be the case. So I do like this:

    First:
    Exp system/<password>@service_name file=c:\expdat.dmp full=y COMPRESS=Y owner=<user to be exported>

    Second:
    drop user <user exported> cascade

    Third:
    imp80 userid= <user to be imported> file=C:\expdat.dmp full=y commit=Y ignore=y

    So example would look like this:

    First:
    Exp system/manager@mydatabase file=c:\expdat.dmp full=y COMPRESS=Y owner=scott

    Second:
    drop user scott cascade; (this I will do with a tool I use to manage databases)

    Third:
    imp80 userid= scott file=C:\expdat.dmp full=y commit=Y ignore=y

    Does that look right?
    Last edited by Karibu; 01-15-04 at 09:25.
    ------------------------------------
    Long days and pleasant nights
    - Roland the gunslinger
    ------------------------------------

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

    Re: Export - Import question

    Yes, those are the steps that you have to carry out.
    Joel Pérez

  8. #8
    Join Date
    May 2003
    Posts
    12

    Thumbs up

    Thank you very much for your help.
    ------------------------------------
    Long days and pleasant nights
    - Roland the gunslinger
    ------------------------------------

  9. #9
    Join Date
    Jan 2004
    Posts
    84
    FULL=Y and OWNER=user option can not be given in one exp command.
    Moreover if you are using compress=y make sure the size of the extent of tablespace in which import will be done is big enough to accommodate all the original extents now compressed in a single one.
    Pls give log=logfile option as well in your exp and imp statements to be able to have errors also logged in a log file which you may refer to later.

  10. #10
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Dropping each object in the user is faster than dropping the user itself.

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

    if you just want to defrag, wouldn't it be simpler to simply move the tables and indexes back into the same tablespace. You may have to run it a few times to achive a full defrag?

    Alternatively, move then into a different tablespace and then back again?

    You'll may need to rebuild indexes afterwards.

    Hth
    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.

Posting Permissions

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