import and export schema
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: import and export schema

  1. #1
    Join Date
    Feb 2002
    Posts
    4

    Thumbs up

    I need to use import and export feature to transfer a schema from one tablespace to another. I am using Sql*Plus, and I need the correct process, plese include the actual sql request
    Thx in advance,
    Daniel

    I am using Oracle 8i standart edition



    [Edited by 4daniel on 04-09-2002 at 04:25 AM]

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hello

    What version of oracle are you using ?

    regards
    Hrishy

  3. #3
    Join Date
    Feb 2002
    Posts
    4
    Originally posted by hrishy
    Hello

    What version of oracle are you using ?

    regards
    Hrishy
    I am using Oracle 8i standart edition
    Daniel

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    why don't you use the data manager/ thru OEM.??
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    Feb 2002
    Posts
    4

    Re

    Originally posted by Tarry
    why don't you use the data manager/ thru OEM.??
    I do not have it installed on the computer
    Daniel

  6. #6
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    As you are working with 8i, why don't you use the alter table move for changing the tablespace for this schema?

    1. Create a new tablespace and give quote to the owner
    2. Connect as the owner of tables
    3. spool move_tables
    select 'alter table '||table_name||' move tablespace new_tablespace;'
    from user_tables;

    spool off
    4. Execute with this user the script move_tables
    5. Change the default tablespace for this user to the new TB.
    6. Recreate the indexes

    I think I don't forget anything

    Cheers

    Angel

  7. #7
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    what u can do is do the export.
    exp username/password@service_name file=
    owner=schema name

    Then open the export file.Search for word tablespace,replace the name of the tablespace with the new tablespace created and then do the import.

    before doing the import create the new tablespace and make this the default tablespace for the user.

    cheese
    anandkl
    anandkl

  8. #8
    Join Date
    Oct 2000
    Location
    Halifax, Nova Scotia
    Posts
    197
    From my experience opening and editing a export dump in a text editor will corrupt the dump file.

    You can try this

    1. Export the schema
    2. Change the user's default tablespace to the new tablespace
    3. Remove any quota the user had on the old tablespace
    4. Import the schema

    If the user has no quota on the tablespace specified in the export file then the objects are created in the user's default tablespace.

    Don't be afraid to try something new. Amateurs built the Ark, professionals built the Titanic

  9. #9
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    I repeat, the easiest way is move the tables from one tablespace to another, and then recreate the indexes in the tablespace you want.

    Regards

    Angel

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