error 6550 during export
aspen, February 08, 2002 - 10:22 am UTC
Hi tom,
When i do an export (full or user) i get this message after it starts exporting for sometime:
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 18:
PLS-00201: identifier 'SYS.OUTLN_PKG' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1118
ORA-06512: at "SYS.DBMS_SQL", line 316
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 84
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 135
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
February 08, 2002 - 4:25 pm UTC
Sounds like you upgraded but did not follow the steps in the migration guide (hence the dictionary didn't get upgraded).
Did you upgrade this database and if so, did you read and follow the steps in the migration guide?
^d or EOF might do the same
Mohammed Ahmed, February 10, 2002 - 9:32 am UTC
if ^d or EOF is in the data being exported might give the same problem???
February 10, 2002 - 1:34 pm UTC
no.
dcd
Alan, February 11, 2002 - 8:21 am UTC
Is this error database version specific, or will dead connection detection do this everytime?
February 11, 2002 - 8:50 am UTC
In the problem report above, it has:
... which is fixed in version 2.3.4 of SQL*Net
...
Sorry for being dense!!
Alan, February 11, 2002 - 9:10 am UTC
I'll try to read more thoroughly next time. thanks.
Exp with error - EXP-00008 & ORA-01455
Sivababu, March 19, 2002 - 4:21 am UTC
Hello TOM,
I got you answer regarding add log file. It is very nice.
While I exporting the user from the linux(oracle 8i) i got the following error.
EXP-00008 Oracler-Fehler 1455 gefunden
ORA-01455 überlauf Von integer-datentyp bei umwantlung der spalte.
it is installed in german.germany_WE8IOS...
Thanks and expecting your reply.
regards,
siva
March 19, 2002 - 8:51 am UTC
try adding "statistics=none" to the EXP command line and see what happens.
Export Errors
atul, July 02, 2003 - 12:04 am UTC
Hi tom,
I'm getting following errors while doing export...
Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported
About to export specified tables via Conventional Path ...
Current user changed to VESOWNER
. . exporting table VES_TRADELANES 376 rows exported
. . exporting table VES_TRADELANE_DATAS
EXP-00056: ORACLE error 1013 encountered
ORA-01013: user requested cancel of current operation
. . exporting table VES_DATA_VERSIONS 1 rows exported
. . exporting table VES_STRETCH_VOYAGES
EXP-00056: ORACLE error 1013 encountered
ORA-01013: user requested cancel of current operation
Export terminated successfully with warnings.
Could you kindly suggest me a cause for that..
Or its because of DCD?
Thanks.
atul
July 02, 2003 - 8:07 am UTC
are you using a 7.3 client?
did you try simply disabling dcd (simple sqlnet.ora edit) to see if that was in fact the cause?
ORA-21779: duration not active
Sami, December 14, 2003 - 12:23 pm UTC
Dear Tom,
Could you please help me to resolve this issue? I am trying to take full export logical backup for my database and getting "ORA-21779: duration not active" error.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 21779 encountered
ORA-21779: duration not active
ORA-06512: at "SYS.DBMS_AW", line 106
ORA-06512: at "SYS.DBMS_AW", line 222
ORA-06512: at "SYS.DBMS_AW_EXP", line 264
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_AW_EXP.schema_info_exp
. exporting cluster definitions
EXP-00056: ORACLE error 31600 encountered
ORA-31600: invalid input value EMIT_SCHEMA for parameter NAME in function SET_TRANSFORM_PARAM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3926
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4050
ORA-06512: at "SYS.DBMS_METADATA", line 836
ORA-06512: at line 1
EXP-00056: ORACLE error 31600 encountered
ORA-31600: invalid input value EMIT_SCHEMA for parameter NAME in function SET_TRANSFORM_PARAM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3926
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4050
ORA-06512: at "SYS.DBMS_METADATA", line 836
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
D:\oracle\oradata>
Thanks
Sami
December 14, 2003 - 12:36 pm UTC
contact support for this one, never saw it before and don't see any record of it happening in the problem database.
export problem
Branka, May 19, 2004 - 2:23 pm UTC
I have similar problem, only I tried to make export of user data.
Username: teedev
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Enter array fetch buffer size: 4096 > 1000000
Export file: expdat.dmp > t1
(2)U(sers), or (3)T(ables): (2)U >
Export grants (yes/no): yes > no
Export table data (yes/no): yes >
Compress extents (yes/no): yes > no
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEEDEV
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEEDEV
About to export TEEDEV's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 31600 encountered
ORA-31600: invalid input value EMIT_SCHEMA for parameter NAME in function SET_TRAN
SFORM_PARAM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3926
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4050
ORA-06512: at "SYS.DBMS_METADATA", line 836
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
May 19, 2004 - 2:46 pm UTC
well, since I first answsered this, the other guy must have done what I posted (used support....) and they developed this note:
Article-ID: <Note:232120.1>
Circulation: PUBLISHED (EXTERNAL)
Folder: server.Utilities.ExportImport
Topic: Known Problems
Title: ORA-31600: invalid input value EMIT_SCHEMA for parameter
NAME in function SET_TRANSFORM_PARAM
Document-Type: PROBLEM
Impact: LOW
Skill-Level: NOVICE
Server-Version: 09.02 to 09.02
Updated-Date: 31-MAR-2003 11:30:28
Attachments: NONE
Content-Type: TEXT/PLAIN
Keywords: 31600; EXP-00000; EXP-00056; EXP-56; ORA-06512; ORA-31600; ORA-6512;
Products: 5/RDBMS;
Platforms: GENERIC;
Symptom(s)
~~~~~~~~~~
You are performing a database export against your 9.2 database. The database
export abnormally ends with the following errors:
EXP-00056: ORACLE error 31600 encountered
ORA-31600: invalid input value EMIT_SCHEMA for parameter NAME in function
SET_TRANSFORM_PARAM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3926
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4050
ORA-06512: at "SYS.DBMS_METADATA", line 836
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
Change(s)
~~~~~~~~~~
One of the known causes for the error above is that a patchset was recently
applied to the database but one or more of the patchset steps were not
performed.
Cause
~~~~~~~
View the readme notes for the patchset recently installed. You will see a step
that should have been performed that directs you to run the script named
catpatch.sql. Very likely this step was never performed.
Fix
~~~~
As the sysdba user run the catpatch.sql script found in the directory named
$ORACLE_HOME/rdbms/admin. Before you submit this script, view it's contents.
You may see that this script places the database in restricted mode.
export problem
Branka, May 19, 2004 - 3:00 pm UTC
Thank you.
Export ora-31600 error
Gerardo, May 31, 2004 - 7:19 pm UTC
Thanks Tom! One of my oracle server was missing the catpatch.sql run and I was getting ora-31600: invalid input value emit_schema.... while trying to export
Thanks!
Exp error EXP-00056/ORA-31600
Jenny Chang, October 13, 2004 - 5:34 pm UTC
I got exp errors:
EXP-00056: ORACLE error 31600 encountered
ORA-31600: invalid input value EMIT_SCHEMA for parameter NAME in function SET_TRANSFORM_PARAM
I follow the recommendation here to run catpatch.sql and it worked. Very helpful.
ora 600 while taking export
A reader, January 06, 2005 - 11:13 pm UTC
i faced this error while taking export
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 600 encountered
ORA-00600: internal error code, arguments: [xsoptloc2], [4], [4], [0], [], [], [], []
ORA-06512: at "SYS.DBMS_AW", line 347
ORA-06512: at "SYS.DBMS_AW", line 470
ORA-06512: at "SYS.DBMS_AW_EXP", line 269
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_AW_EXP.schema_info_exp
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table AQ$_INTERNET_AGENTS 0 rows exported
. . exporting table AQ$_INTERNET_AGENT_PRIVS 0 rows exported
. . exporting table DEF$_AQCALL 0 rows exported
. . exporting table DEF$_AQERROR 0 rows exported
. . exporting table LOGSTDBY$PARAMETERS 0 rows exported
.19 rows exported
. . exporting table CUSTOMERS 50000 rows exported
. . exporting table FWEEK_PSCAT_SALES_MV 149325 rows exported
. . exporting table MVIEW$_EXCEPTIONS 0 rows exported
. . exporting table MV_CAPABILITIES_TABLE 0 rows exported
. . exporting table PLAN_TABLE 0 rows exported
. . exporting table PRODUCTS 10000 rows exported
. . exporting table PROMOTIONS 501 rows exported
. . exporting table REWRITE_TABLE 0 rows exported
. . exporting table SALES
. . exporting partition SALES_1995 0 rows exported
. . exporting partition SALES_1996 0 rows exported
. . exporting partition SALES_H1_1997 0 rows exported
. . exporting partition SALES_H2_1997 0 rows exported
. . exporting partition SALES_Q1_1998 71805 rows exported
. . exporting partition SALES_Q2_1998 72705 rows exported
. . exporting partition SALES_Q3_1998 63390 rows exported
. . exporting partition SALES_Q4_1998 83415 rows exported
. . exporting partition SALES_Q1_1999 86217 rows exported
. . exporting partition SALES_Q2_1999 87298 rows exported
. . exporting partition SALES_Q3_1999 76139 rows exported
. . exporting partition SALES_Q4_1999 100110 rows exported
. . exporting partition SALES_Q1_2000 104544 rows exported
. . exporting partition SALES_Q2_2000 104652 rows exported
. . exporting partition SALES_Q3_2000 91260 rows exported
. . exporting partition SALES_Q4_2000 74736 rows exported
. . exporting table SALES_TRANSACTIONS_EXT
. . exporting table TIMES 1461 rows exported
. about to export QS_ADM's tables via Conventional Path ...
. about to export QS's tables via Conventional Path ...
. . exporting table AQ$_AQ$_MEM_MC_H 0 rows exported
. . exporting table AQ$_AQ$_MEM_MC_I 0 rows exported
. . exporting table AQ$_AQ$_MEM_MC_NR 0 rows exported
. . exporting table AQ$_AQ$_MEM_MC_S 1 rows exported
. . exporting table AQ$_AQ$_MEM_MC_T 0 rows exported
. . exporting table AQ$_MEM_MC 0 rows exported
. . exporting table AQ$_QS_ORDERS_PR_MQTAB_H 0 rows exported
. . exporting table AQ$_QS_ORDERS_PR_MQTAB_I 0 rows exported
. . exporting table AQ$_QS_ORDERS_PR_MQTAB_NR 0 rows exported
. . exporting table AQ$_QS_ORDERS_PR_MQTAB_S 1 rows exported
. . exporting table AQ$_QS_ORDERS_PR_MQTAB_T 0 rows exported
. . exporting table QS_ORDERS_PR_MQTAB 0 rows exported
. . exporting table QS_ORDERS_SQTAB 0 rows exported
. about to export QS_WS's tables via Conventional Path ...
. . exporting table AQ$_QS_WS_ORDERS_MQTAB_H 0 rows exported
. . exporting table AQ$_QS_WS_ORDERS_MQTAB_I 0 rows exported
. . exporting table AQ$_QS_WS_ORDERS_MQTAB_NR 0 rows exported
. . exporting table AQ$_QS_WS_ORDERS_MQTAB_S 5 rows exported
. . exporting table AQ$_QS_WS_ORDERS_MQTAB_T 0 rows exported
. . exporting table AQ$_QS_WS_ORDERS_PR_MQTAB_H 0 rows exported
. . exporting table AQ$_QS_WS_ORDERS_PR_MQTAB_I 0 rows exported
. . exporting table AQ$_QS_WS_ORDERS_PR_MQTAB_NR 0 rows exported
. . exporting table AQ$_QS_WS_ORDERS_PR_MQTAB_S 1 rows exported
. . exporting table AQ$_QS_WS_ORDERS_PR_MQTAB_T 0 rows exported
. . exporting table QS_WS_ORDERS_MQTAB 0 rows exported
. . exporting table QS_WS_ORDERS_PR_MQTAB 0 rows exported
. about to export QS_ES's tables via Conventional Path ...
. . exporting table AQ$_QS_ES_ORDERS_MQTAB_H 0 rows exported
. . exporting table AQ$_QS_ES_ORDERS_MQTAB_I 0 rows exported
. . exporting table AQ$_QS_ES_ORDERS_MQTAB_NR 0 rows exported
. . exporting table AQ$_QS_ES_ORDERS_MQTAB_S 1 rows exported
. . exporting table AQ$_QS_ES_ORDERS_MQTAB_T 0 rows exported
. . exporting table AQ$_QS_ES_ORDERS_PR_MQTAB_H 0 rows exported
. . exporting table AQ$_QS_ES_ORDERS_PR_MQTAB_I 0 rows exported
. . exporting table AQ$_QS_ES_ORDERS_PR_MQTAB_NR 0 rows exported
. . exporting table AQ$_QS_ES_ORDERS_PR_MQTAB_S 1 rows exported
. . exporting table AQ$_QS_ES_ORDERS_PR_MQTAB_T 0 rows exported
. . exporting table QS_ES_ORDERS_MQTAB 0 rows exported
. . exporting table QS_ES_ORDERS_PR_MQTAB 0 rows exported
. about to export QS_OS's tables via Conventional Path ...
. . exporting table AQ$_QS_OS_ORDERS_MQTAB_H 0 rows exported
. . exporting table AQ$_QS_OS_ORDERS_MQTAB_I 0 rows exported
. . exporting table AQ$_QS_OS_ORDERS_MQTAB_NR 0 rows exported
. . exporting table AQ$_QS_OS_ORDERS_MQTAB_S 1 rows exported
. . exporting table AQ$_QS_OS_ORDERS_MQTAB_T 0 rows exported
. . exporting table AQ$_QS_OS_ORDERS_PR_MQTAB_H 0 rows exported
. . exporting table AQ$_QS_OS_ORDERS_PR_MQTAB_I 0 rows exported
. . exporting table AQ$_QS_OS_ORDERS_PR_MQTAB_NR 0 rows exported
. . exporting table AQ$_QS_OS_ORDERS_PR_MQTAB_S 1 rows exported
. . exporting table AQ$_QS_OS_ORDERS_PR_MQTAB_T 0 rows exported
. . exporting table QS_OS_ORDERS_MQTAB 0 rows exported
. . exporting table QS_OS_ORDERS_PR_MQTAB 0 rows exported
. about to export QS_CBADM's tables via Conventional Path ...
. . exporting table AQ$_QS_CBADM_ORDERS_MQTAB_H 0 rows exported
. . exporting table AQ$_QS_CBADM_ORDERS_MQTAB_I 0 rows exported
. . exporting table AQ$_QS_CBADM_ORDERS_MQTAB_NR 0 rows exported
. . exporting table AQ$_QS_CBADM_ORDERS_MQTAB_S 3 rows exported
. . exporting table AQ$_QS_CBADM_ORDERS_MQTAB_T 0 rows exported
. . exporting table QS_CBADM_ORDERS_MQTAB 0 rows exported
. . exporting table QS_CBADM_ORDERS_SQTAB 0 rows exported
. about to export QS_CB's tables via Conventional Path ...
. about to export QS_CS's tables via Conventional Path ...
. . exporting table AQ$_QS_CS_ORDER_STATUS_QT_H 0 rows exported
. . exporting table AQ$_QS_CS_ORDER_STATUS_QT_I 0 rows exported
. . exporting table AQ$_QS_CS_ORDER_STATUS_QT_NR 0 rows exported
. . exporting table AQ$_QS_CS_ORDER_STATUS_QT_S 1 rows exported
. . exporting table AQ$_QS_CS_ORDER_STATUS_QT_T 0 rows exported
. . exporting table ORDER_STATUS_TABLE 0 rows exported
. . exporting table QS_CS_ORDER_STATUS_QT 0 rows exported
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. about to export RMAN's tables via Conventional Path ...
. . exporting table AL 0 rows exported
. . exporting table BCB 0 rows exported
. . exporting table BCF 0 rows exported
. . exporting table BDF 0 rows exported
. . exporting table BP 0 rows exported
. . exporting table BRL 0 rows exported
. . exporting table BS 0 rows exported
. . exporting table BSF 0 rows exported
. . exporting table CCB 0 rows exported
. . exporting table CCF 0 rows exported
. . exporting table CDF 0 rows exported
. . exporting table CKP 0 rows exported
. . exporting table CONF 0 rows exported
. . exporting table CONFIG 1 rows exported
. . exporting table DB 0 rows exported
. . exporting table DBINC 0 rows exported
. . exporting table DF 0 rows exported
. . exporting table DFATT 0 rows exported
. . exporting table OFFR 0 rows exported
. . exporting table ORL 0 rows exported
. . exporting table RCVER 1 rows exported
. . exporting table RLH 0 rows exported
. . exporting table RR 0 rows exported
. . exporting table RT 0 rows exported
. . exporting table SCR 0 rows exported
. . exporting table SCRL 0 rows exported
. . exporting table TS 0 rows exported
. . exporting table TSATT 0 rows exported
. . exporting table XCF 0 rows exported
. . exporting table XDF 0 rows exported
. about to export PIHIST's tables via Conventional Path ...
. . exporting table ERRORMESSAGESHIST 90 rows exported
. . exporting table EVENTAUDITH 52519 rows exported
. . exporting table FILEMASTERHIST 1305 rows exported
. . exporting table GMEBCIDICMSGSHIST 17796 rows exported
. . exporting table GMNONRECOVERABLEIMSGTHIST 264 rows exported
. . exporting table GMNONRECOVERABLEOMSGTHIST 13 rows exported
. . exporting table GMRECOVERABLEIMSGTHIST 6339 rows exported
. . exporting table GMRECOVERABLEOMSGTHIST 559 rows exported
. . exporting table GMSIGNEDMSGSHIST 20340 rows exported
. . exporting table HOSTMESSAGESHIST 0 rows exported
. . exporting table MESSAGEMASTERHISTORY 7212 rows exported
. . exporting table MNSBDETAILSHIST 0 rows exported
. . exporting table MSGAUTHAUDITHSTRY 1342 rows exported
. . exporting table RSAAUTHSTATUSDHIST 0 rows exported
. . exporting table RSAAUTHSTATUSTHIST 0 rows exported
. . exporting table TRANSACTIONAUTHINFOHIST 156 rows exported
. . exporting table TXNSCREENDHIST 0 rows exported
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.
January 07, 2005 - 8:58 am UTC
contact support.
DBMS_REPCAT_MIG_INTERNAL
friend, May 18, 2005 - 8:01 pm UTC
IMP-00017: following statement failed with ORACLE error 23373:
"BEGIN SYS.DBMS_REPCAT_MIG.POST_IMPORT; END;"
IMP-00003: ORACLE error 23373 encountered
ORA-23373: object group "PUBLIC"."FRACAS" does not exist
ORA-06512: at "SYS.DBMS_REPCAT_MIG_INTERNAL", line 883
ORA-06512: at "SYS.DBMS_REPCAT_MIG", line 124
ORA-06512: at line 1
Hi Tom,
I ran one job to import 8.1.7.4 data into 9.2.0.5 which took 3 days and on 3rd day I got above message.
I know its mistake as didnt run catrep.sql but please suggest what should I do to correct above except rerunning import.
Thanks
May 18, 2005 - 8:31 pm UTC
I'll have to refer you to support for this, not sure what it was trying to create at that point.
(simply upgrading the database would have been much much faster)
problem
friend, May 19, 2005 - 5:27 pm UTC
Hi Tom,
I had AL24UTFFSS 8.17.4 database with replication.
What I did ..just shutdown it took cold backup and then startup --> exort full --> created new 9.2.0.5 database --> ran catrep.sql --> imported full.
DO thinks its the right approach to do.:(
My manager asked me what about replication ? can i say its also upgraded.
May 20, 2005 - 7:04 am UTC
well, that is not the way I would have upgraded, but -- it works.
(you should just "upgrade" databases)
Export Error: unable to read data - table definition has changed
Laxman Kondal, June 02, 2005 - 8:03 am UTC
Hi Tom
'ORA-01466 unable to read data - table definition has changed'
I am getting this error and unable to figure out how to fix it. Document refers to snapshot and creation time stamp. In our case there is no snapshot and creation time is same as servertime and far behind time of export.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table EI_ELEMENTS
EXP-00056: ORACLE error 1466 encountered
ORA-01466: unable to read data - table definition has changed
. . exporting table EI_MAPPINGS
EXP-00056: ORACLE error 1466 encountered
ORA-01466: unable to read data - table definition has changed
It happens when we run script again to drop and create all tables for testing export. If I do export direct then it works fine and when through application then this error comes up in export log
There is difference of few minutes in Oracle instance and OS but time of export is much later then creation time.
I am not sure where Oracle gets time, its OS or its own creation. If two different sources then how to synchronize them.
Thanks and regards.
June 02, 2005 - 4:44 pm UTC
how about a timeline of what you have done. and the export command you are using.
sounds like you are flashing back -- but the tables are "too new" and you get that until the scn smon table catches up.
Export Error: unable to read data - table definition has changed
Laxman Kondal, June 03, 2005 - 9:29 am UTC
Hi Tom
Thanks for reply.
In a procedure all tables to be exported are first truncated and then loaded with data to be exported.
Once tabels are loaded and commited then export is called from the same procedure.
This is the string passed to run_command (which calles Java UTIL calss to do EXP)
=========
v_str := p_userlogin||'/'||p_pwd||'@'||v_glb_name;
--
--make exp string
v_exp_cmd := v_bin||'bin/exp '
||v_str
||' TABLES=(OPS.ei_%) CONSISTENT=Y '
||' COMPRESS=N STATISTICS=NONE '
||' FILE='||v_dir||'exp_low.dmp '
||' LOG='||v_dir||'exp_low.log';
--get last id pk
SELECT id+1, exp_till_date INTO v_id, v_last_exp_date
FROM Ops_Ei_Logs
WHERE id = (SELECT MAX(id) FROM Ops_Ei_Logs);
--get expdate now
v_exp_till_date := SYSDATE;
--
--get Ei_Logs ready before exp
INSERT INTO Ei_Logs(id, exp_from_date, exp_till_date, exp_date, exp_by, version)
VALUES (v_id, v_last_exp_date,v_exp_till_date,SYSDATE,p_userlogin,v_lo_version);
--
COMMIT;
--
--do export
v_return := P_Exp_Imp.run_cmd(v_exp_cmd);
--
--check success
IF v_return = 0 THEN
===============
the value v_return gets is 0.
It works fine if I call this proc from sql*plus and error comes when called from application.
Time fetched from Oracle and OS is like this
ops@ORA9I> select to_char(sysdate, 'dd-Mon-yyyy hh:mi:ss') from dual;
TO_CHAR(SYSDATE,'DD-
--------------------
03-Jun-2005 08:49:14
1 row selected.
ops@ORA9I>
and at the same time from another session from OS is
[oracle@apps oracle]$ date; hwclock
Fri Jun 3 08:49:14 EDT 2005
Fri Jun 3 07:56:15 2005 0.557388 seconds
[oracle@apps oracle]$
It seems Oracle time and OS time is same but hwclock is behind about 51 minutes.
Question is when Oracle takes time from OS or hardware.
In one case when I call this proc from sql*plus to test it works and when through application then error.
That shows that there are different source for fetching time stamp.
Is there any opotion that hwclock not to used?
Thanks and regards.
June 03, 2005 - 10:06 am UTC
the truncate is the DDL
the consistent=y is the flashback.
the clock isn't really part of the equation here I don't think. how long between the truncate and the export?
oracle just uses OS calls to get the time, it'll be what date prints out.
Export Error: unable to read data - table definition has changed
Laxman Kondal, June 03, 2005 - 10:21 am UTC
Hi Tom
Thanks for reply and now you points me to new direction. If Oracle takes time from OS then DDL or Flashback should not be the source of this error all should have same time frame.
I am not sure why should this error come if all times are taken from one source.
Thanks and regards.
June 03, 2005 - 10:57 am UTC
flashing back very soon near to ddl doesn't work, around the point in time of the ddl operation -- you cannot get the clear read on the object.
how long between
a) truncate
b) export
and what is your release.
Export Error: unable to read data - table definition has changed
Laxman Kondal, June 03, 2005 - 11:27 am UTC
Hi Tom
We are using 9iR2 and truncating 30 tables then loading these table, some time not all 30 tables are loaded, commited and then call is made to do export them from another porcedure within same pkg.
What you said "until the scn smon table catches up" might be the case.
Thanks and regards
June 03, 2005 - 12:41 pm UTC
do you need the consistent=y in your case? is anything modifying them?
Laxman Kondal, June 03, 2005 - 1:13 pm UTC
Hi Tom
Practically I dont find any good reason to use consistent=y because of export structure.
But the fact remains why its getting different timestamp and throwing error.
Thanks for your help to understand Oracle concept.
Thanks and regards.
June 03, 2005 - 1:32 pm UTC
it is not getting a "different timestamp"
Export Error: unable to read data - table definition has changed
Laxman Kondal, June 06, 2005 - 7:19 am UTC
Hi Tom
If source of timesatamp is same, what causes the 'table definition has changed' error to be generated. With my limitted knowledge on this, there is some thing else is the cause of this error, please help me to understand what's that?
Thanks and regards.
June 06, 2005 - 7:49 am UTC
You know what -- I just looked something up, and with export -- it could be a clock thing.
see support Note 167495.1
sorry -- I saw the error message and the only way I knew to get it was to try to flashback on a object over DDL changes (which you have performed) too quickly after doing the DDL.
Export Error: unable to read data - table definition has changed
Laxman Kondal, June 06, 2005 - 7:52 am UTC
Hi Tom
Thanks and I will look into support note.
Thanks and regards.
Export Error: unable to read data - table definition has changed
Laxman Kondal, June 06, 2005 - 12:51 pm UTC
Hi Tom
Support note referes to creation time in future. If I call export just after truncating and loading tables how will the ceation time be in future. And same export procedure called from sqlplus works fine and from application it gives an error.
Object was created long back and did number of exports for testing from sqlplus without error.
Thanks and regards.
==================================================
Comment ~~~~~~~ The CONSISTENT=YES parameter is used
to ensure that the data seen by Export is consistent
to a single point in time and doesn't change during
the execution of the export command.
fix:
If the object creation time-stamp is still greater
than the system time, then export the object's data
without using the CONSISTENT=YES parameter, drop the
object, recreate the object so it has a new creation time-stamp, import the object's data, and resume work.
==================================================
June 06, 2005 - 12:55 pm UTC
you mentioned out of sync clocks, that is what caught my eye on that note.
It could even be a TIMEZONE issue. The dedicated server you are running might have a different TZ than the environment the export is running in. Consider:
[tkyte@xtkyte-pc tkyte]$ echo $TZ
[tkyte@xtkyte-pc tkyte]$ plus
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Jun 6 12:53:04 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
ops$tkyte@ORA9IR2> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) from dual;
TO_CHAR(SYSDATE,'DD-
--------------------
06-jun-2005 12:53:15
ops$tkyte@ORA9IR2> !
[tkyte@xtkyte-pc tkyte]$ export TZ=PST
[tkyte@xtkyte-pc tkyte]$ plus
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Jun 6 16:53:23 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
ops$tkyte@ORA9IR2> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) from dual;
TO_CHAR(SYSDATE,'DD-
--------------------
06-jun-2005 16:53:33
One thing to check would be that the TZ of the export session is consistent with the rest of the sessions.
Export Error: unable to read data - table definition has changed
Laxman Kondal, June 06, 2005 - 1:16 pm UTC
Hi Tom
Out of syn was hwclock which is behind OS. Our application and Oracle instance are both in same TZ.
[oracle@apps oracle]$ date; hwclock
Mon Jun 6 13:03:18 EDT 2005
Mon Jun 6 12:10:27 2005 0.697219 seconds
[oracle@apps oracle]$
SQL> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) from dual;
TO_CHAR(SYSDATE,'DD-
--------------------
06-jun-2005 13:03:19
SQL>
Seems something else may be the cause of this error.
Thanks and regards
Export Error: unable to read data - table definition has changed
Laxman Kondal, June 10, 2005 - 11:10 am UTC
Hi Tom
Support note 167495.1 says
=================================
Reason ~~~~~~ There are objects inside the database
that have a object creation time-stamp that is greater
than the current system time. Probable this happened
by once setting a wrong system date/time. To find out
the affected objects run the following command:
SQL> select to_char(created,'dd-mm-yyyy hh24:mi:ss')
"CREATION TIME", object_name, object_type, object_id
from dba_objects where created > sysdate;
CREATION TIME OBJECT_NAME OBJECT_TYPE OBJECT_ID
------------------- --------------- -------------
----------
22-05-2006 10:55:35 PK_T1C1 INDEX 25490
22-05-2006 10:56:10 PK_T2C1 INDEX 25491
22-05-2006 10:56:10 PK_T3C1 INDEX 25492
22-05-2006 10:56:10 PK_T4C1 INDEX 25493
22-05-2006 10:56:56 PK_T5 INDEX 25494
22-05-2006 10:49:55 TAB1 TABLE 25485
22-05-2006 10:51:13 TAB2 TABLE 25486
22-05-2006 10:51:36 TAB3 TABLE 25487
22-05-2006 10:51:57 TAB4 TABLE 25488
22-05-2001 10:53:45 TAB5 TABLE 25489
Comment ~~~~~~~ The CONSISTENT=YES parameter is used
to ensure that the data seen by Export is consistent
to a single point in time and doesn't change during
the execution of the export command.
fix:
If the object creation time-stamp is still greater
than the system time, then export the object's data
without using the CONSISTENT=YES parameter, drop the
object, recreate the object so it has a new creation time-stamp, import the object's data, and resume work.
========================================
Here is what I did
scott@ORA9I> select sal from emp1;
SAL
----------
11 rows selected.
scott@ORA9I> update emp1 set sal = 9999;
11 rows updated.
scott@ORA9I> select sal from emp1;
SAL
----------
9999
9999
9999
9999
9999
9999
9999
9999
9999
9999
9999
11 rows selected.
scott@ORA9I> select sal from emp1 as of timestamp(systimestamp-interval'1'day);
SAL
----------
11 rows selected.
scott@ORA9I> truncate table emp1;
Table truncated.
scott@ORA9I> select sal from emp1 as of timestamp(systimestamp-interval'1'day);
select sal from emp1 as of timestamp(systimestamp-interval'1'day)
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
scott@ORA9I> insert into emp1 select * from emp;
14 rows created.
scott@ORA9I> select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ -------- --------- ------- --------- ------ ----- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
scott@ORA9I> select * from emp1 as of timestamp(systimestamp-interval'1'day);
select * from emp1 as of timestamp(systimestamp-interval'1'day)
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
scott@ORA9I> select to_char(created,'dd-mm-yyyy hh24:mi:ss')
2 "CREATION TIME", object_name, object_type, object_id
3 from dba_objects where created > sysdate;
no rows selected
scott@ORA9I> commit;
Commit complete.
scott@ORA9I> select * from emp1 as of timestamp(systimestamp-interval'1'day);
select * from emp1 as of timestamp(systimestamp-interval'1'day)
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
scott@ORA9I>
This query in not fetching any table to be dropped and recreated.
If truncate or consistent causing this error then I can work aroud by deleting rather truncating which will keep pushing HWM and TS up and up till rebuild, but in the case above I used truncate and 'as of timestamp' but not expoted it.
What and where my concept of Oracle is going wrong?
Thanks and regards
June 10, 2005 - 11:19 am UTC
you cannot flashback OVER ddl -- that was my original point (and in 9i, using a timestamp is only accurate within about 5 minutes)
Export Error: unable to read data - table definition has changed
Laxman KOndal, June 10, 2005 - 12:20 pm UTC
Hi Tom
What I understand is if I do not use consistent then truncate will not give error. Its the flashback - constient, spits this error.
Thanks and regards.
June 10, 2005 - 3:43 pm UTC
It is either flashing back OR the creation times.
Export Error: unable to read data - table definition has changed
Laxman Kondal, June 13, 2005 - 8:45 am UTC
Hi Tom
This query do not show creation time in future
scott@ORA9I> select to_char(created,'dd-mm-yyyy hh24:mi:ss')
2 "CREATION TIME", object_name, object_type, object_id
3 from dba_objects where created > sysdate;
no rows selected
And now export parameter consistent=y.
This takes the snapshot at the beginning of export and again at the end. Compares the difference and adds it to the export.
If this is correct then its comparing it with new state of table with old one which was already taken before export. And after first snapshot no DDL/DML on these tables. How flashback does come here?
Thanks and regards.
June 13, 2005 - 11:42 am UTC
it is not taking snapshots at beginning and end, it is using the equivalent of "flashback query" to query the data as of the same point in time.
You mentioned "I've got clocks that are very far apart". I mentioned "hey, based on this note, maybe that is a problem after all"
why don't you fix your clocks? they should be the same don't you think? It would be an easy way to see if it has something to do with it.
user a export user b
AJ Allen, August 31, 2005 - 1:07 pm UTC
I have a database with account a_mgr, which owns all of the objects for system A. Similarly, in this database are accounts b_mgr, c_mgr, and so forth. I have a need for user of system A, a_usr, to export a_mgr and only a_mgr ( exp userid=a_usr/foo owner=a_mgr ).
I may not grant a broad-privileged role such as exp_full_database or dba to a_usr because he will then have access to b_mgr, c_mgr, etc. -- as well as other possibly broad privileges (depending on role granted).
Are there a set of privileges I can grant to a_usr that would allow him to export a_mgr, and only a_mgr? I have tried to construct a role using the privileges of exp_full_database as a model, but no luck. The 'ANY' privieges are too broad and I do not understand what the SYS owned procs do, so I am kind of stuck.
If there is a way, I would appreciate any help you can give.
Thanks,
August 31, 2005 - 2:15 pm UTC
sorry, export only lets DBA type accounts export other users tables.
EXP-00000: Export terminated unsuccessfully
Jagadeesha, September 22, 2005 - 5:04 am UTC
EXP-00056: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 4 with name "R03" too small
EXP-00008: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 4 with name "R03" too small
EXP-00000: Export terminated unsuccessfully
os - SunOS
Database - Oracle8.1.7.4
Export - FULL=Y COMPRESS=N CONSISTENT=Y FEEDBACK=10000
September 22, 2005 - 1:53 pm UTC
search this site for 1555 -- it quite simply means in this case, your dba hasn't made your rollback segments large enough to do the work you need to do.
export terminating for snapshot tables
Jagadish, October 12, 2005 - 7:59 pm UTC
We running oracle 8.1.7.4 on sun 5.8, In this DB we have some replicated snopshot(child)table. During export with full=y consistent=y (n) direct=y (n) compress=n, export hangs( it don't move further) at one table & at this stage after skipping this table (ctrl + c) it resumes and hangs at another table. Again after using ctrl+c, it starts exporting next tables & export terminated successfully with warnings. Earlier we never faced this problem. These 2 tables r snapshots (along with other tables) & having 20000 rows. Status=invalid, Object_type=undefined for 14 objects including these 2 tables, export is giving problem for these 2 tables only & not for other tables, why? Today I executed utlrp.sql to recompile invalid objects, now one more problem started. i.e when we use ctrl+c for those tables, export process aborts and terminated unsuccessfully.
Plz guide me...
October 13, 2005 - 10:27 am UTC
please contact support for this one.
(or use feedback=100 or something to get exp to print out a '.' as it moves along to see if it is really hung or just plodding along.
or query the v$ views to see what it is doing what it is waiting on)
Great help re Export 9.2.0.6 and catpatch.sql
Olli Heinamaki, April 10, 2006 - 8:16 am UTC
Tried to export full database and got the error.
Your reminder to run catpatch.sql helped me here - I actually forgot that I'd patched the binaries used with this database.
A reader, July 28, 2006 - 10:51 am UTC
it had me as an invalid friend id when it was my id and i didn't cancel my account
July 28, 2006 - 8:11 pm UTC
eh? not a clue in the world what you are talking about.
exp ora-1455 for external table
Steve, November 12, 2009 - 2:30 pm UTC
Hi Tom, There is no solution for such error in 9i?!
November 15, 2009 - 2:20 pm UTC
is there no example for me to work with?
my car won't start, when you tell me why it won't, I'll tell you why you are hitting your issue.
My car is a year 2004 car.
We are even as far as useful informations... now, solve away.
give a way to reproduce what you are seeing, else no one can help you.
jijeesh, December 08, 2010 - 6:15 am UTC
Hi tom,
Iam working in a unix platform which connects to database, sometimes iam getting the intimation of the error ORA-01013 of cancelling a query, i know that it may be also a time out..
could you please let me know, where i could find the detail error stored, and by which query i got that ORA-01013, becasue there are many jobs are running in the server.
December 08, 2010 - 10:36 am UTC
more information - if you don't know what query is getting the ora-1013 - how do you know you are getting it??? give us the surrounding information - what are you doing/how do you see this 1013?