Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Dec 2003
    Posts
    9

    Unanswered: move data from a tablespace to another

    Hello!!
    I want to move all the data from one tablespace to another.
    I made a export of the tablespace and create a new tablespace.
    How do i move the data from the export to the new tablespace ??
    Thanks...

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: move data from a tablespace to another

    import the exported data dump file.

    Originally posted by thorsten2202
    Hello!!
    I want to move all the data from one tablespace to another.
    I made a export of the tablespace and create a new tablespace.
    How do i move the data from the export to the new tablespace ??
    Thanks...
    Oracle can do wonders !

  3. #3
    Join Date
    Dec 2003
    Posts
    9

    Re: move data from a tablespace to another

    Originally posted by cmasharma
    import the exported data dump file.
    I tried to make an import, but i dont know the correct syntax for an import...
    i tried the command imp xxx/xxx@xx file=expdat.dmp fromuser=xxx touser=yyy
    But it doesent work...

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: move data from a tablespace to another

    what is the error you receive?

    Originally posted by thorsten2202
    I tried to make an import, but i dont know the correct syntax for an import...
    i tried the command imp xxx/xxx@xx file=expdat.dmp fromuser=xxx touser=yyy
    But it doesent work...
    Oracle can do wonders !

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    ... and the error message is ??????

    until you give us something to work with [ in the words of that great life commentator Anacedent] Your On Your Own (YOYO)

  6. #6
    Join Date
    Dec 2003
    Posts
    9
    Originally posted by Todd Barkus
    ... and the error message is ??????

    until you give us something to work with [ in the words of that great life commentator Anacedent] Your On Your Own (YOYO)
    sorry...
    here ist the error...

    Export-Datei wurde von EXPORT:V09.02.00 ³ber konventionellen Pfad erstellt
    Importvorgang mit Zeichensatz WE8MSWIN1252 und Zeichensatz AL16UTF16 NCHAR durch
    gef³hrt
    Import-Server verwendet Zeichensatz WE8ISO8859P1 (m÷gliche Zeichensatzkonvertier
    ung)
    . Import SA002's Objekte in SA991;
    "ALTER SESSION SET CURRENT_SCHEMA= "SA991;""
    IMP-00003: Oracle-Fehler 1435 gefunden
    ORA-01435: Benutzer ist nicht vorhanden
    IMP-00000: Der Import-Vorgang endete mit Fehlern

  7. #7
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    With import export, u cannot move the data within the same schema.
    use the following command

    alter table t1 move tablespace <<new tablesapce>>;

  8. #8
    Join Date
    Dec 2003
    Posts
    9
    Originally posted by shelva
    With import export, u cannot move the data within the same schema.
    use the following command

    alter table t1 move tablespace <<new tablesapce>>;
    where must i use this command? must i login in sql as user, manager...?
    and what is table t1?
    Can you please tell me a little bit more....
    thanks

  9. #9
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Hmmm,
    That was just the syntax.

    u have the list of tables that was present in your original tablespace say TS1.

    Now you have a new tablespace Say TS2.
    for each table in old tablespace TS1 u have to issue that command.

    use the following script for it


    spool c:\move.sql -- change the path accordingly

    select 'ALTER TABLE ' || segment_name || ' MOVE TABLESAPCE ' || tablespace_name || ';' FROM user_segments WHERE TABLESPACE_NAME = 'TS1'
    SPOOL OFF

    the above script will generate the commands in a file called move.sql

    then run teh move.sql

  10. #10
    Join Date
    Dec 2003
    Posts
    9
    Originally posted by shelva
    Hmmm,
    That was just the syntax.

    u have the list of tables that was present in your original tablespace say TS1.

    Now you have a new tablespace Say TS2.
    for each table in old tablespace TS1 u have to issue that command.

    use the following script for it


    spool c:\move.sql -- change the path accordingly

    select 'ALTER TABLE ' || segment_name || ' MOVE TABLESAPCE ' || tablespace_name || ';' FROM user_segments WHERE TABLESPACE_NAME = 'TS1'
    SPOOL OFF

    When i tried to run this command, i got the message, that the FROM is at the wrong place...


    the above script will generate the commands in a file called move.sql

    then run teh move.sql

  11. #11
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    yah thgere was a syntax error .. now u try...

    select 'ALTER TABLE '|| segment_name || ' MOVE TABLESAPCE ' || tablespace_name || ';' FROM user_segments;



    This is becoming spoon feeding i think..
    you should try and correct these things.
    U cannot expect 100% working scripts...

  12. #12
    Join Date
    Dec 2003
    Posts
    9
    Originally posted by shelva
    yah thgere was a syntax error .. now u try...

    select 'ALTER TABLE '|| segment_name || ' MOVE TABLESAPCE ' || tablespace_name || ';' FROM user_segments;



    This is becoming spoon feeding i think..
    you should try and correct these things.
    U cannot expect 100% working scripts...
    OK, that works, but when i tri to run the move.sql i got the error, that 'alter table move' cannot run with other process...

  13. #13
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    OK OK!!
    Replace the word <<NEW TABLESPACE_NAME>> with
    the correct tablespace name...with in single quotes

    use the following script.
    before running move.sql , open that file and delete the first 2 lines which are the QUERY lines.


    SET HEAD OFF
    SET PAUSE OFF
    SET FEEDBACK OFF
    SET PAGES 0
    SPOOL c:\move.sql
    select 'ALTER TABLE '|| segment_name || ' MOVE TABLESPACE ' || <<NEW TABLESPACE NAME>> || ';' FROM user_segments;

    spool off
    SET HEAD ON
    SET PAUSE ON
    SET FEEDBACK ON
    SET PAGES 0

  14. #14
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    dont forget to rebuild your indexes as they will become invalid when their table moves. Use a similar sql to select from user_indexes where status!='VALID'

    Alan

  15. #15
    Join Date
    Dec 2003
    Posts
    9
    Originally posted by shelva
    OK OK!!
    Replace the word <<NEW TABLESPACE_NAME>> with
    the correct tablespace name...with in single quotes

    use the following script.
    before running move.sql , open that file and delete the first 2 lines which are the QUERY lines.


    SET HEAD OFF
    SET PAUSE OFF
    SET FEEDBACK OFF
    SET PAGES 0
    SPOOL c:\move.sql
    select 'ALTER TABLE '|| segment_name || ' MOVE TABLESPACE ' || <<NEW TABLESPACE NAME>> || ';' FROM user_segments;

    spool off
    SET HEAD ON
    SET PAUSE ON
    SET FEEDBACK ON
    SET PAGES 0
    Sorry, but i got a new error: When i replace 'new_tablespace' with the name of my new tablespace, i got following error:

    incorrect tablecolumn

Posting Permissions

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