This content has been marked as final. Show 4 replies
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 220.127.116.11 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
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.
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';
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';
select distinct tablespace_name from dba_segments where owner='YourSchemaOwner';
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.
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:
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?