4 Replies Latest reply: Apr 5, 2013 2:18 PM by 926398 RSS

    transform=segment_attributes:n not working as documented... : (

    926398
      I used expdp to create a no rows export (content=metadata_only) of a 10.2.0.4 database. I am now trying to import the structures into a newly create 11.2.0.3 database using impdp.
      Here is the command used to run the import.
      impdp system/password DIRECTORY=EXPORTDB_DIR dumpfile=onedev_NOROWS_03292013.dmp logfile=onedev_norows_import.log transform=segment_attributes:n job_name=full_norow_onedev

      From my understanding of the documentation concerning the transform=segment_attributes:n parameter that The import would not try and create tablespaces and would also strip out segment attributes during import...
      This from the datapump documentation.
      Specify the value of SEGMENT_ATTRIBUTES as n. This results in the exclusion of segment attributes (both storage and tablespace) from the table.

      Here is from the log of the import and the first thing it is doing is trying to create tablespaces? What am I doing wrong?
      Master table "SYSTEM"."FULL_NOROW_ONEDEV" successfully loaded/unloaded
      Starting "SYSTEM"."FULL_NOROW_ONEDEV": system/******** DIRECTORY=EXPORTDB_DIR dumpfile=onedev_NOROWS_03292013.dmp logfile=onedev_norows_import.log transform=segment_attributes:
      n job_name=full_norow_onedev
      Processing object type DATABASE_EXPORT/TABLESPACE
      ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
      ORA-31684: Object type TABLESPACE:"TEMP" already exists
      ORA-31684: Object type TABLESPACE:"USERS" already exists
      ORA-39083: Object type TABLESPACE failed to create with error:
      ORA-01119: error in creating database file '/data1/ONEDEV/undotbs02.dbf'
      ORA-27040: file create error, unable to create file
      IBM AIX RISC System/6000 Error: 2: No such file or directory
      Additional information: 1
      Failing sql is:
        • 1. Re: transform=segment_attributes:n not working as documented... : (
          rp0428
          >
          I used expdp to create a no rows export (content=metadata_only) of a 10.2.0.4 database. I am now trying to import the structures into a newly create 11.2.0.3 database using impdp.
          Here is the command used to run the import.
          impdp system/password DIRECTORY=EXPORTDB_DIR dumpfile=onedev_NOROWS_03292013.dmp logfile=onedev_norows_import.log transform=segment_attributes:n job_name=full_norow_onedev

          From my understanding of the documentation concerning the transform=segment_attributes:n parameter that The import would not try and create tablespaces and would also strip out segment attributes during import...
          This from the datapump documentation.
          Specify the value of SEGMENT_ATTRIBUTES as n. This results in the exclusion of segment attributes (both storage and tablespace) from the table.
          >
          That quote is correct: 'results in the exclusion of segment attributes . . . from the table'.

          See the last three words? 'from the table'.

          Your error occurs when the DDL to create the tablespaces is executed.

          If you do not want to create the tablespaces then specify the EXCLUDE parameter.

          See the doc
          http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm#i1007865

          You can also exclude the tables 'segment_attributes' as you show but the table has to use SOME tablespace. If you don't include the segment to use in the DDL then the table will get created in the users default tablespace.
          • 2. Re: transform=segment_attributes:n not working as documented... : (
            Mohamed ELAzab
            Hello,

            As described above.The statement is correct but the datapump will still try to create the tablespace but the data will be created on the default tablespace.

            Please execute the below statements and return the result:
            Select default_tablespace from dba_users where username='YourSchemanameOwner';
            and
            select OWNER,TABLE_NAME,TABLESPACE_NAME,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EX
            TENTS, MAX_EXTENTS,PCT_INCREASE,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,NUM_FREELIST_BLOCKS,SAMPLE_SIZE from dba_tables where owner='YourSchemaOwner';
            and
            select distinct tablespace_name from dba_segments where owner='YourSchemaOwner';
            BR
            Mohamed ElAzab
            Blog: http://mohamedelazab.blogspot.com/
            • 3. Re: transform=segment_attributes:n not working as documented... : (
              926398
              Thak you sir, you are correct. I was seeing a bunch of errors while it was creating the objects in the logs concerning tablespaces thats why I had originally thought it was not working as expected but when I looked closed it was just erroring on the original tablespace quotas because the tablespaces do not exists.
              Thanks.
              • 4. Re: transform=segment_attributes:n not working as documented... : (
                926398
                Hoping I could hit you up with a follow up question? So I used the transform=segment_attributes:n while importing the metadata and that seemed to work and the objects have been created...
                Now I am trying to import the data on top of the already created objects and I am running into alot of difficulty becuase we have alot of tables with xml,clob,blob data types and I am getting errors stating that ORA-39242: Unable to export/import TABLE_DATA:"ONE_SPRV"."DSPTCH_JOB_SIGNATURE" due to table attributes.
                So my question is, if I wanted to keep the storage parameters but just wanted to omit the tablespaces in the create statements then how would I use the transform?
                I read the doc and it says...
                "To omit only the STORAGE clause and retain the TABLESPACE clause, you can use the STORAGE transform, as follows:
                TRANSFORM=STORAGE:n:table

                But it does not say how I can do that exact opposite? I want only to remove the tablespace definitions form the create statements? How is this done?
                Thanks.