Error in impdp, ORA-39083: TABLE object type

Backup, Recover, Import, Export, Datapump, etc
Post Reply
dejambo
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 68
Joined: Wed, 27 Jun 2012 8:58 am

Good Morning.

Today I tried to rise a dump using the IMPDP and alleged the following error:

ORA-39083: Table object type: "Schema". "Table" Failed to be created with the error:
ORA-00922: Option not found or invalid

This occurred only with one of the Dump tables. What would be the reason for this error?

orbigade.
dejambo
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 68
Joined: Wed, 27 Jun 2012 8:58 am

Would anyone have an idea of ??why this mistake? Would it be problem with export?
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Was it just this mistake that gave?
Put us a little more log and let us know the version of your bank.
: Roll:
dejambo
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 68
Joined: Wed, 27 Jun 2012 8:58 am

Good morning Dr_Gori.

Bank version: Release 11.2.0.1.0.

More about log:

Select all

Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE 
ORA-39083: Tipo de objeto TABLE:"USER"."TABELA" falhou ao ser criado com o erro: 
ORA-00922: opção não encontrada ou inválida 
O código sql com falha é: 
CREATE TABLE "USER"."TABELA" ("NRO_INT_PI" NUMBER(10,0) NOT NULL ENABLE, "NRO_INT_COND_USUARIO" NUMBER(8,0) NOT NULL ENABLE, "NRO_INT_PESSOA_PROP_TERRA" NUMBER(8,0), "NRO_INT_DISTRITO" NUMBER(8,0), "NRO_INT_BACIA" NUMBER(8,0) NOT NULL ENABLE, "NRO_INT_CORPO_HIDRICO" NUMBER(8,0), "NRO_INT_TIPO_INTERVENCAO" NU 
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA 
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/COMMENT 
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/INDEX 
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT 
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 
O job "SYSTEM"."SYS_IMPORT_TABLE_01" foi concluído com 1 erro(s) em 10:51:58
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother, Good morning.

Just a question of mine this export, did you use any parfile? If a positive case, would you like to go to us?
This table that is giving error, is it in the same as the others?

Valeu.

embrace,

Trevisolli
dejambo
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 68
Joined: Wed, 27 Jun 2012 8:58 am

Hello Trevisoli.

In fact, it was not me who did the Export, I received this dump and I have to rise in my local environment. It follows an excerpt from the export log. The Owner is the same as.

Select all

Export: Release 11.2.0.4.0 - Production on Mon Jul 13 16:07:33 2015 
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
;;; Legacy Mode Active due to the following parameters: 
;;; Legacy Mode Parameter: "consistent=TRUE" Location: Command Line, Replaced with: "flashback_time=TO_TIMESTAMP('2015-07-13 16:07:33', 'YYYY-MM-DD HH24:MI:SS')" 
;;; Legacy Mode has set reuse_dumpfiles=true parameter. 
Starting "NOME"."SYS_EXPORT_SCHEMA_02":  NOME/******** dumpfile=nome:dump.dp logfile=nome:log.log schemas=schema exclude=grant flashback_time=TO_TIMESTAMP('2015-07-13 16:07:33', 'YYYY-MM-DD HH24:MI:SS') reuse_dumpfiles=true  
Estimate in progress using BLOCKS method... 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA 
Total estimation using BLOCKS method: 7.118 GB 
Processing object type SCHEMA_EXPORT/USER 
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother,

Another tip, it would be you to "extract" the DDL that it is trying to import (through the sqlfile parameter) and, Try to run "in hand", to see where the error is with this table:

Select all

 
$ impdp directory=expdir dumpfile=myexp.dmp sqlfile=ddl.sql 
Source: http://www.dba-oracle.com/t_data_pump_s ... ameter.htm ]

tries to do this and see what contains in this SQL generated.

Anything, send us.
embrace,

Trevisolli

dejambo
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 68
Joined: Wed, 27 Jun 2012 8:58 am

Good afternoon Trevisolli.

I generated the .sql file as you have suggested and noticed that in the table there is the following creation command:

Select all

LOB ("CAMPO") STORE AS BASICFILE "BASICFILE"( 
        TABLESPACE "P_128M" ENABLE STORAGE IN ROW CHUNK 16384 
       NOCACHE LOGGING  
       STORAGE(INITIAL 131072 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645 
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT       CELL_FLASH_CACHE DEFAULT)) ;
I copied and pasted the table creation script and gave error right in that part and

Select all

 LOB ("CAMPO") STORE AS BASICFILE "BASICFILE"
the error is as follows:

Select all

ORA-22850:duplicar a opção de armazenamento LOB especificada
] I did a test replacing the name "BasicFile" by another or by the same name in a minusculo and it worked. The problem may be there, but I can not say what happened for sure.
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother,

It is good that he found the prolema.
This part of the Lob segment, has an explanation of Oracle herself here: http://docs.oracle.com/cd/B28359_01/app ... tables.htm

can now try to realize your import.

Any questions, send us.

large embrace,

Trevisolli
dejambo
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 68
Joined: Wed, 27 Jun 2012 8:58 am

Thank you very much Trevisolli for help.

I will try to describe here what was the solution of the problem in question:

1) The following command was executed to generate a script with the DDL in the Export:



2) With the .sql file generated copied and pasted the creation script of the table that gave the error and tried to create it in my Bank. I realized that it was a mistake in the creation of the LOB stored, so I renamed with another name and the table was created.

3) With the table created in my bank, I have again performed the IDPP only from the problem table with the TABLE_EXISTS_ACTION = Append parameter. With this Oracle checks if the table already exists, if there is the inserts in the table that is empty created in the previous step.

Select all

impdp user@sid directory=meu_dir dumpfile=exp.dp tables=tabela_com_problema table_exists_action=APPEND
So my table was populated.

Thanks to all for help.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest