simple export/import question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: simple export/import question

  1. #1
    Join Date
    Nov 2000
    Posts
    172
    hi everyone,

    I am working on moving tables from one tablespace to another on an nt machine. I would like to use the import/export option. How do I start this utility on NT?. what kinds of things must I keep in mind when doing this.

    1. Export table mode, select tables I want, compress = N.
    exp xxx/xxx tables=(xxx,yyy,zzz) rows=Y file=export.dmp
    2. Alter user default tablespace to new tablespace, grant quota on that tb space. (IS this needed)
    3. Import those table rows =Y, commit = Y
    imp xxx/xxx tables=9xxx,yyy,zzz) rows=Y file=import.dmp

    Any more steps, Does the syntax look correct.

    thanks as always,

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    Revoke any quota on the old tablespace for the user.

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    To move a table from one tablespace to another tablespace , just use ALTER statement. You do not need to exp/imp.

  4. #4
    Join Date
    Mar 2001
    Posts
    5
    Just a quick question.... Why not use 'create table as select ...' to copy your tables to the new tablespsace and avoid having to write the data to disk?

    Eg.
    create table newtable tablespace newdata as select * from oldtable;
    drop table oldtable;
    rename newtable to oldtable;

    Syntax above may be a bit dodgy... it came from the top of my head!

    Adam.

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    You have to specify it as following

    exp sys/passwd@sid FILE=exp.dmp LOG= exp.log TABLES=(schema.table1, schema.table2,...) CONSISTENT=Y DIRECT=Y COMPRESS=N

    Alter the user table space, grant the permissions

    imp sys/passwd@sid FILE=exp.dmp LOG=imp.log TABLES=(schema.table1, schema.table2, schema.table3,...) COMMIT=Y

    Btw, are you going to export and import a perticular user? if so then you have to use the OWNER=user_name on your export and then FROMUSER=user_name TOUSER=user_name in your import clause.

    Good luck,
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Nov 2000
    Posts
    172
    thanks for great advice,

    Why not use 'create table as select' well I am very new to this and I have read that you don't get everything, so I would have to rebuild Indexes, triggers, constraints? More than I know how to do right now.

    'just use ALTER statement'. I have read several things about corrupt indexes after using this command. I also tried it and it wouldn't work, keep saying can't perform two operations at once?

    So I figured export/import would be the best for a novice like me, right now.

    Again, does anyone know how to use/start the export utility on NT, I am mostly experienced with UNIX.

    Thanks,

  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    From the dos prompt issue the command exp or imp. The actual executable is in %ORACLE_HOME%\bin

    Good luck,
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  8. #8
    Join Date
    Mar 2001
    Posts
    16

    Here you go !!!

    Hello kburrows
    just a fellow like you
    ok! now about your thing
    just go to command pmt.type this
    C:\>exp80 (return key)
    it will ask you for everytihng it needs.
    try make your own file.
    For import than.
    c:\>imp80 (return key)
    and it will give you every thing.
    keep in mind if a guy with dba privs made the expfile only someone with same or higher privs can read from it.

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