export tablespace error on win2k/oracle 8.1.7
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: export tablespace error on win2k/oracle 8.1.7

  1. #1
    Join Date
    Jul 2003
    Posts
    136

    Cool archiving tablespace on Oracle 8.1.7

    I need to archive a tablespace on Oracle 8.1.7 on Win2k. Its like this - I have a user and 3 tablespaces attached to this user userName, userNameIndex, userNameReport. I no longer need this schema(user) and its objects, So whats the best way to fully remove this schema (3 tablespaces) from the database to make some space on the server and still be abe to recover it back into database if need be in future.

    Do you think
    Export/Import with Transport_Tablespace=y is all what I need. What are the dependencies/prerequisites I need to be careful about.
    -D

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    1) Export the schema in question. Zip and save the .dmp file
    2) Save the DDL used to create the tablespaces that you are going to drop.


    If you no longer need the user than drop that user with the cascade option. Then check those tablespaces for any other objects that may have been created in them accidently. If there are no objects in those tablespaces, drop them. Then manually delete the datafiles from the OS.

    Did I leave anything out?

    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Jul 2003
    Posts
    136
    Mr. Hanky,

    As spelled in my message the indexes of objects belonging to this user are written in 'userNameIndexes' and objects itelf to 'userName' tablespaces. So does that going to create problem in export/import.

    Also are you saying I need to create this user (with saved create user DDL) again if needed and then do the import. I know this tablespace must not exist for import to be sucessfull. So create tablespace DDL is needed in what scenario.
    -D

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131
    I may be mis-understanding you here, I'm not sure.

    If you drop the user it does not matter which tablespace his objects are in, they will be gone providing you use CASCADE.

    The reason for backing up the DDL for the tablespaces is in case you need to re-import that user you can re-create those tablespaces prior to the import. You also have the option of creating the user before the import (using different tablespaces) and running the import with ignore=y, this is a little more complicated.

    So, if I have this right I would...

    1) export the user schema, take a full backup if possible.
    2) backup the DDL for the tablespaces you are going to drop.
    3) Drop the user (cascade)
    4) drop the tablespaces AFTER double checking them for any objects.
    5) remove the datafiles from the OS

    Does this make sense?

    MH
    I remember when this place was cool.

  5. #5
    Join Date
    Jul 2003
    Posts
    136
    While trying to do the export I am getting errors:
    ==================================================
    (exp 'sys/syspassword@db as sysdba' TRANSPORT_TABLESPACE=y TABLESPACES=(ALSO, ALSO$I) ROWS=y FILE=c:\also_exp.dmp LOG=c:\also_exp.log)

    Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    JServer Release 8.1.7.0.0 - Production
    Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
    Note: table data (rows) will not be exported
    About to export transportable tablespace metadata...
    EXP-00008: ORACLE error 29341 encountered
    ORA-29341: The transportable set is not self-contained
    ORA-06512: at "SYS.DBMS_PLUGTS", line 1031
    ORA-06512: at line 1
    EXP-00000: Export terminated unsuccessfully

    I have executed
    dbms_tts.transport_set_check('ALSO,ALSO$I', TRUE);
    which does bring me an empty TRANSPORT_SET_VIOLATIONS view.
    telling me that set of tablespaces is self-contained

    Also why it say its now going to export ROWS data

    -D

  6. #6
    Join Date
    Jul 2003
    Posts
    136

    Exclamation export tablespace error on win2k/oracle 8.1.7

    While trying to do the export I am getting errors:
    ==================================================
    (exp 'sys/syspassword@db as sysdba' TRANSPORT_TABLESPACE=y TABLESPACES=(ALSO, ALSO$I) ROWS=y FILE=c:\also_exp.dmp LOG=c:\also_exp.log)

    Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    JServer Release 8.1.7.0.0 - Production
    Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
    Note: table data (rows) will not be exported
    About to export transportable tablespace metadata...
    EXP-00008: ORACLE error 29341 encountered
    ORA-29341: The transportable set is not self-contained
    ORA-06512: at "SYS.DBMS_PLUGTS", line 1031
    ORA-06512: at line 1
    EXP-00000: Export terminated unsuccessfully

    I have executed
    dbms_tts.transport_set_check('ALSO,ALSO$I', TRUE);
    which does bring me an empty TRANSPORT_SET_VIOLATIONS view.
    telling me that set of tablespaces is self-contained

    Also why it say its now going to export ROWS data

    -D

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    I'm confused now.
    Why are you using the transportable tablespace option?
    Are you creating a new database?

    MH
    I remember when this place was cool.

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Curtesy of Metalink

    Code:
    Solution Description
    --------------------  
    
    Check the REASON column in 'PLUGGABLE_SET_CHECK'.  
    
    For example    
    
     select reason from sys.pluggable_set_check 
     where ts1_name = 'TEST_TT' 
    
    SQL> /  
    
    REASON 
    -------------------------------------------------------------------------------- 
    Tables and associated indexes not fully contained in the pluggable set 
    Table and Index enforcing primary key/Unique constraint not in same tablespace   
    
    This shows the reason for which is cannot be exported.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Please do not spin duplicate Thread on the same issue with different title

    -Sam
    Last edited by sambavan; 07-23-2003 at 07:03 PM.
    Thanx
    Sam



    Life is a journey, not a destination!


  10. #10
    Join Date
    Jul 2003
    Posts
    136
    Thanks My Hanky for all the help.

    You wrote:
    1) export the user schema, take a full backup if possible.
    2) backup the DDL for the tablespaces you are going to drop.
    3) Drop the user (cascade)
    4) drop the tablespaces AFTER double checking them for any objects.
    5) remove the datafiles from the OS
    ==========================================

    I have done the first 3 steps

    Ok, then how do we make sure that the user being re-created uses the same tablespace what it used for all its objects before I removed it.
    Do I need to run the create user (saved earlier with default tablespace clause) before I run the import user script.

    The question is: Do I need to have the user exist before import or the user will be created automatically with the import user script and objects will be written to which tablespace. I want it to use recreated tablespace.

    Also do you suggest using ignore=y
    and can we do this saving user objects at exp/imp tablespace level.

    -D

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