Very good and detailed review
onsh76, October 10, 2003 - 1:36 pm UTC
Tom,
It would be also great if Oracle include your suggestion in the Oracle documentation.
ora-02304 while creating type ordsource
A reader, December 15, 2004 - 4:35 pm UTC
Hi Tom,
We are offering support to a database at the customer site where they are creating a new database instance. When they run the script intermedia.sql during database creation, they are getting errors in certain type creations. Pretty much all of them are getting error ora-02304. Pls see below for a part of the log file with the error at the end. While reading a previous response in this article, you have mentioned that there can be only one OID in the database. So is that why we are getting the error at the create type ordsource statement? Is there any way that we can get around this error? Pls let us know and any help will be appreciated.
SQL> CREATE TYPE ORDSource
2 TIMESTAMP '1998-06-25:12:08:00'
3 OID '2F1B4210A0E33FFEE03400400B4066F3'
4 authid current_user
5 AS OBJECT
6 (
7 -------------
8 -- Attributes
9 -------------
10
11 -- storage for data within oracle. Every operation
12 -- for the data stored in this field will be under
13 -- the control of transaction within which the
14 -- methods are called. This means that all the changes can
15 -- either be commited or rolled back as desired by the
16 -- user
17 localData BLOB,
18
19 --
20 -- srcType, srcLocation and srcName are limited to 4K
21 -- due to system limitation on the size of varchar2 field
22 --
23 srcType VARCHAR2(4000),
24 srcLocation VARCHAR2(4000),
25 srcName VARCHAR2(4000),
26
27 -- updateTime maintains the time when the users updated either
28 -- source or the object containing the source last. This attribute
29 -- is not updated when import is called, but importFrom updates
30 -- this method since the source information is changed.
31 --
32 -- For recoding updates when the top level objevt changes, users
33 -- must call set method at appropriate time.
34 updateTime DATE,
35
36 -- 1 or NULL means data is in LOB
37 -- 0 means that the data is in external sources
38 local NUMBER
39 );
40 /
CREATE TYPE ORDSource
*
ERROR at line 1:
ORA-02304: invalid object identifier literal
Thanks a lot for your time.
December 15, 2004 - 6:33 pm UTC
where does the script "intermedia.sql" come from?
oid problem
A reader, December 16, 2004 - 9:47 am UTC
we got the intermedia.sql script from the standard oracle 9i database creation scripts.
December 16, 2004 - 10:05 am UTC
I've never seen it, nor is it on my distributions. Please contact support.
[tkyte@xtkyte-pc tkyte]$ oerr ora 2304
02304, 00000, "invalid object identifier literal"
// *Cause: An attempt was made to enter an object identifier literal for
// CREATE TYPE that is either:
// - not a string of 32 hexadecimal characters
// - an object identifier that already identifies an existing
// object
// - an object identifier different from the original object
// identifier already assigned to the type
// *Action: Do not specify the object identifier clause or specify a 32
// hexadecimal-character object identifier literal that is unique
// or identical to the originally assigned object identifier. Then
// retry the operation.
[tkyte@xtkyte-pc tkyte]$
it could be that you already ran the install for Oracle Text (it isn't called intermedia in 9i either -- hence the real confusion)
I would guess this is a rogue script -- not part of the install and the object id is already taken.
In 9ir2, the file:
$ORACLE_HOME/ord/im/admin/ordsrcsp.sql
has this object defined (with the same object id already)
but please -- contact support to sort through this.
Dave, December 16, 2004 - 10:14 am UTC
when you use dbca to install it gets made for you when you asl for the scripts to be saved
its a wrapper script for all the intermedia scripts
[oracle@LNCSTRTLDB01 scripts]$ more interMedia.sql
connect SYS/change_on_install as SYSDBA
set echo on
spool /u01/app/oracle/product/ora92/assistants/dbca/logs/interMedia.log
@/u01/app/oracle/product/ora92/ord/im/admin/iminst.sql;
spool off
exit;
December 16, 2004 - 10:25 am UTC
there is no intermedia.sql in the distribution!
please take the last suggestion above to heart.
Dave
A reader, December 16, 2004 - 10:31 am UTC
I know - its made by dbca on the fly.
Fire up dbca to create a database (including this option) at the end choose create scripts instead of create db and there it will be in all its glory :-)
December 16, 2004 - 10:36 am UTC
Ok, if that be the case then they ran it twice.
another options?
Alexey, June 24, 2005 - 7:27 am UTC
Using TYPE database object we have problems when import the copy of an application schema to the same DB:
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "TP_UNVRS_TBL" TIMESTAMP '2005-05-11:13:45:17' OID 'F580CEF560B"
"34C588A8319067C4C970F' "
" "
" AS TABLE OF tp_unvrs_obj"
""
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
As you mentioned above we should create schema TYPES and install all the types we using into that schema.
But this is quite difficult for us to set it up on the 200+ customers' sites.
Do we have another option to get rid of that OID-error? May be a parameter of exp/imp or something else?
June 24, 2005 - 7:34 am UTC
there are no magic parameters that I am aware of, no.
TOID_NOVALIDATE ?
Rajesh, June 24, 2005 - 9:31 am UTC
To Alexey from Russia,
You can make use of toid_novalidate parameter in imp. PLEASE read documentation to check if/how you can make use of the same.
That is *the* magic parameter that i am aware of to get rid of these errors when using used_defined_datatypes.
June 24, 2005 - 12:49 pm UTC
not sure that it'll work in this case. I'm assuming they are doing a fromuser/touser sort of thing.
Import/Export - Why?
VIKAS SANGAR, July 19, 2005 - 7:31 am UTC
Dear Mr. Kyte
I was trying to use the Export/Import utility using Oracle 9iR2.I wanted to import all the objects owned by Scott to user VS. I went through the following steps....
At the command line:-
I typed Exp
I got connected......
user_id: scott@SomeDB
Password: abc
*(Tablespace=System)
Keeping all the other options to default other than,
Grants (=NO), when i tried to export the User (U) Scott.
i was able to export the same successfully with no wanrnings or errors.
later on on the same prompt, i typed ...
IMP
I got connected...
User_id: vs@SomeDB
password:xyz
*VS was granted the role DBA (Tablespace=TBS1).
It asked me for the file to be imported which was the default file name EXPDAT.Dmp
import full export file = yes
import process started...........
The whole process ended up successfully with no Warnings/errors.
exit.
Then i got connected to SQLPLUS as user vs@someDB
sql> select * from Tab;
i get...
TABLENAME TABTYPE CLUSTERID
t1 TABLE
t2 TABLE
t3 TABLE
*All above table were made by myself in VS's schema before the Export/Import of User SCOTT.
All i want to know here is that, Why i am not able to See/Select the obejects owned by Scott (Emp, Bonus, Salgrade etc...) though VS schema when i ahve successfully imported the user SCOTT ?
What went wrong over here? Did i missed out something?
What i should do to to correct the above procedure, to view the default tables (imported after importing Scott) that are present in Scott's schema using the VS schema?
Pls help....
Take care...
VIKAS.
July 19, 2005 - 7:52 am UTC
I don't see you trying to access scotts objects here at all.
a DBA has "SELECT ANY TABLE" granted via the DBA role, you can select them. If you want to "see them" in the dictionary, you must use the right views -- ALL_* views
ALL_TABLES
Import/Export - Why?
Vikas, September 26, 2005 - 6:56 am UTC
Thanx for this peice of information.
Take care, regards...
Error in exporting data
KurmaKumar.m, November 02, 2005 - 2:15 am UTC
Hi Tom,
I got Fallowing error when I am taking an user backup it is oracle 10.1.0.3.0 version, I exported user level backups in past but not from this tablespace when I am exporting from this tablespace I got the fallowing error how to resolve it?????.
[oracle@localhost oradata]$ exp cms/cms
file='/usr/oradata/cms_backup.dmp' owner=cms rows=n
Export: Release 10.1.0.3.0 - Production on Mon Oct 31 14:02:05 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release
10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character
set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user CMS
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user CMS
About to export CMS's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 4021 encountered
ORA-04021: timeout occurred while waiting to lock object
ORA-06512: at "SYS.DBMS_METADATA", line 1511
ORA-06512: at "SYS.DBMS_METADATA", line 1548
ORA-06512: at "SYS.DBMS_METADATA", line 1864
ORA-06512: at "SYS.DBMS_METADATA", line 3707
ORA-06512: at "SYS.DBMS_METADATA", line 3689
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
[oracle@localhost oradata]$
Thank you,
WR/KurmaKumar.m
November 02, 2005 - 5:05 am UTC
well, first - exp is not a backup tool if you ask me, it is a logical data copy tool, useful to move/copy data from database 1 to database 2. You would never really use it for proper backups.
is dbms_metadata valid and usable in your database would be the first thing I would ask.
catz, May 02, 2006 - 8:25 am UTC
2304 errors - really need a work around
Michael Friedman, May 11, 2006 - 11:41 pm UTC
Tom,
As you point out, you should have one instance per server.
We pretty much follow that rule.
That means we often have 2 or 3 versions of the same system on our development DBs... and yes, we create them by using export / import since our installation scripts are not yet stable.
And, yes, those 2304 errors are killing us to the point where we're ready to reconsider using object types.
Honestly, we need a way to tell Oracle "Ignore the OID and create a new type with a new OID".
May 12, 2006 - 9:08 am UTC
... And, yes, those 2304 errors are killing us to the point where we're ready to
reconsider using object types. ...
(have you read Expert One on One Oracle, the object relational chapter... :)
there is no "ignore the oid"
an dba
an dba, July 26, 2006 - 4:51 pm UTC
I ran into an import issue. An oracle object table failed with IMP-00017 with "ALTER TABLE ABC ADD (SCOPE FOR (REF_DEF) IS OID 'xxxxxxx' USER_DEFINED (ID), followed by IMP-00003 and ORA-00942 errors. The table is created but data not imported. For your information, REF_DEF is defined as REF OF DEF which is an type. I have researched all over metalink and google, seems no solution. Can you advice?
Alexander, January 10, 2008 - 3:54 pm UTC
Hi Tom,
I'm having an intermittant issue I hope you can toss your 2 cents at.
O/S: AIX 5.3
Oracle: 10.2.0.2
Running a schema level export via cron, lately we get
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: constraints on tables will not be exported
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user NIKU
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user NIKU
About to export NIK's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 1116 encountered
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/corp_01/temp01/temp01.dbf'
ORA-27092: size of file exceeds file size limit of the process
Additional information: 262143
Additional information: 1280001
EXP-00000: Export terminated unsuccessfully
ulimit is
$ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 4194304
memory(kbytes) unlimited
coredump(blocks) 2097151
nofiles(descriptors) 2000
Temp is
SQL> select file_name, bytes/1024/1024 "SIZE (MEG)", maxbytes/1024/1024 "MAXSIZE (MEG)", user_bytes/1024/1024 "USED (MEG)"
2 from dba_temp_files;
FILE_NAME SIZE (MEG) MAXSIZE (MEG) USED (MEG)
---------------------------------------- ---------- ------------- ----------
/corp_01/temp01/temp01.dbf 10000 11000 9999
I don't really have any idea what this is about. The export only uses a tiny bit of temp when it completes successfully.
January 10, 2008 - 5:31 pm UTC
ahh, what is the ulimit of the CRON job tho.
could be entirely different. have your cron job log the ulimit information to /tmp or something and review that
if the export needs some temp - sometimes it might get it entirely in the PGA and sometimes not (hence needing to open and write to the temp file). With pga_aggregate_target, the amount of PGA workarea you get can change from run to run depending on what else is going on in the server at that moment.
Not able to import an AQ table with user defined type
Kulkarni, April 20, 2009 - 3:55 am UTC
Hi Tom,
I am facing a problem in importing a large table. This is a AQ table using user defined type. This is a big table in production database and not partitioned. As we are facing space issue and also performance issue I need to backup old data and delete it from the table. As part of this activity I exported old data from this table and tried to import it into our QA database. But I am getting the following error.
Export file created by EXPORT:V08.01.06 via conventional path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
import server uses US7ASCII character set (possible charset conversion)
export client uses UTF8 character set (possible charset conversion)
export server uses US7ASCII NCHAR character set (possible ncharset conversion)
. importing ENT's objects into ENT
. importing ENT's objects into ENT
IMP-00017: following statement failed with ORACLE error 4021:
"CREATE TYPE "INBOX_ARCHIVE_MSG_TYPE" TIMESTAMP '2007-08-08:03:27:51' OID '8"
"00A17F3968041EBE034001083FB95C3' "
" "
" "
" as OBJECT"
" (seq_id NUMBER(38),"
" msg_type CHAR(2),"
" timestamp DATE,"
" msg_text VARCHAR2(2048),"
" is_proc_text char(1))"
IMP-00003: ORACLE error 4021 encountered
ORA-04021: timeout occurred while waiting to lock object ENT.INBOX_ARCHIVE_MSG_TYPE
IMP-00063: Warning: Skipping table "ENT"."FC_INBOX_ARCHIVE" because object type "ENT"."INBOX_ARCHIVE_MSG_TYPE" cannot be created or has different identifier
Import terminated successfully with warnings.
To avoid this error I created a new database and created this user type under different schema as suggested by you in this discussion. I granted all on this type to table owner and tried to import. But again getting the same error. However database versions of source and target are different ( source-8.1.6 and target-10.2.02)
Is there any way do complete this activity?
Oracle Export Problem due to Redaction Policy
INDRANIL DAS, April 07, 2015 - 4:12 am UTC
Hi Tom,
We are getting below issues at the time of export....when the REDACTION POLICY were not there, the export were absolutely fine, but after we apply the Redaction Policy for some tables, from the very next day, we are getting below types of error in the export log .....Could you please advise.
-------------------------------------------------------------------------------------------------------------------
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" PARFILE=/data/backup/exp.par
........
Processing object type DATABASE_EXPORT/AUDIT
--------
ORA-31693: Table data object "SRC_REM7_12022015"."T164" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01426: numeric overflow
ORA-31693: Table data object "SRC_REM7_11022015"."T164" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01426: numeric overflow
. . exported "SRC_CLARIFY_11022015"."TABLE_NOTES_LOG" 4.872 GB 15618845 rows
Thanks in Advance,
INDRANIL