2 Replies Latest reply on Sep 11, 2009 2:41 PM by Dean Gagne-Oracle

    Oracle 10g R2 - Transportable Tablespaces

    Sachin B
      Hi,

      I am trying to transport tablespaces from one DB Instance to another db instances on Solaris 64bit, It works fine for Tables/Index and constrains but unable to transport Functions and Sequences.

      expdp \"sys/poddb@poddb as sysdba\" transport_tablespaces=(ADB_USER_TBS, ADB_USER_IDX_TBS, ADB_AGENCY_TBS, ADB_AGENCY_IDX_TBS, ADB_ARCH_TBS, ADB_ARCH_IDX_TBS, ADB_CMPG_TBS, ADB_CMPG_IDX_TBS, ADB_CMPG_LOG_TBS, ADB_CMPG_LOG_IDX_TBS, ADB_SYS_TBS, ADB_SYS_IDX_TBS) DIRECTORY=datapump DUMPFILE=datapump:adb01.dmp LOGFILE=datapump:adb.log EXCLUDE=STATISTICS

      i tried to add include=sequence but it gives below error:

      Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
      With the Partitioning, OLAP and Data Mining options
      ORA-39001: invalid argument value
      ORA-39038: Object path "SEQUENCE" is not supported for TRANSPORTABLE jobs.

      how to add sequences , function and procedures in expdb or any other method i can use to export these objects?

      Regards
      Sachin
        • 1. Re: Oracle 10g R2 - Transportable Tablespaces
          Toni Lazarin
          Sachin B wrote:
          Hi,

          I am trying to transport tablespaces from one DB Instance to another db instances on Solaris 64bit, It works fine for Tables/Index and constrains but
          unable to transport Functions and Sequences.
          Yes, and this is expected behaviour when using transportable tablespaces.
          It's explained here
          how to add sequences , function and procedures in expdb or any other method i can use to export these objects?
          Use data pump
          1 person found this helpful
          • 2. Re: Oracle 10g R2 - Transportable Tablespaces
            Dean Gagne-Oracle
            Sachin,

            The only objects that are exported as part of transportable are objects associated with the tablespaces that you list. If you want to see the complete list, you can query the view:

            select unique full_path from SYS.datapump_paths where het_type = 'TRANSPORTABLE_EXPORT';

            This will give you what gets exported as part of a datapump transportable export job. If you need to export synonyms or packages, you can use datapump, but you will either need to use the schema mode or full mode. You can add the include parameter to only get what you want.

            Hope this helps.

            Dean