1

I was trying to do full database import. I created tablespaces manually from sql developer DBA option. Then did import but am facing this errror.

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** full=yes directory=exp_table dumpfile=full_exp1.dmp logfile=full.log
    Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
    Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
    Processing object type DATABASE_EXPORT/TABLESPACE
    ORA-39083: Object type TABLESPACE:"INDEX" failed to create with error:
    ORA-02236: invalid file name

    Failing sql is:
    CREATE TABLESPACE "INDEX" DATAFILE SIZE 178257920 AUTOEXTEND ON NEXT 178257920 MAXSIZE 30720M,SIZE 178257920 AUTOEXTEND ON NEXT 178257920 MAXSIZE 30720M,SIZE 253755392 AUTOEXTEND ON NEXT 253755392 MAXSIZE 30720M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
    ORA-39083: Object type TABLESPACE:"INDEX" failed to create with error:
    ORA-02236: invalid file name

Please help me to solve this. How to do a import with all tablespace, users and tables from one instance to another(fresh full import)

0

The CREATE TABLESPACE ... command in your data pump file is not correctly formatted. According to the Oracle documentation it should be similar to:

CREATE 
TABLESPACE 
    <name>
DATAFILE 
    <Name of the file>, SIZE <size>
AUTOEXTEND 
    <ON NEXT <growth>, MAXSIZE <maxsize>
    | 
    OFF>
....

Reference: CREATE TABLESPACE (Oracle Help Center)

Your statement has multiple AUTOEXTEND ... statements, but no DATAFILE definitions. Either they were removed by yourself, omitted from the error message or are missing in the dump file.

Work Around

You might be able to work around this issue by pre-creating the tablespace "Index" and then adding the parameter REUSE_DATAFILES=NO to your command prompt or par file.

If the default (n) is used and the data files specified in CREATE TABLESPACE statements already exist, then an error message from the failing CREATE TABLESPACE statement is issued, but the import job continues.

Reference: REUSE_DATAFILES (Oracle Help Center); emphasis mine

  • Thanks for the correction @hot2use ..i recreated the tablespaces with the same way i followed early. This time i copied all data files from server to pc oradata folder. Then added each data files which associated with particular tablespaces. Still import fails. Am i doing the right way to do full import? anything am missing?? full export using expdp -->created all tablespaces-->imdp full import(still trying) – Mathew Jul 28 '18 at 10:06
  • Finally got the solution.!!! Its because of default location for data file. We can create tablespaces automatically from sql script which generated from the dumb backup. A usefull link [link]chandu208.blogspot.com/2012/07/… – Mathew Aug 5 '18 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.