ignore=y
X Wang, May 23, 2002 - 11:31 am UTC
I use ignore=y option when I import the data. It only ignore CREATE TABLE command, but failed in CREATE TYPE command.
IMP-00015: following statement failed because the object already exists:
"CREATE TYPE "MYTYPE_T" TIMESTAMP '2002-02-10:16:24:15' OID 'F4CDB776D6771"
"1D3AD090008C7E98428' "
How can I ignore create type command?
Thank you
May 23, 2002 - 3:31 pm UTC
it didn't fail, it just keeps on going.
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop user a cascade;
User dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> grant dba to a identified by a;
Grant succeeded.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> @connect a/a
ops$tkyte@ORA817DEV.US.ORACLE.COM> set termout off
a@ORA817DEV.US.ORACLE.COM> REM GET afiedt.buf NOLIST
a@ORA817DEV.US.ORACLE.COM> set termout on
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM> create table t ( x int );
Table created.
a@ORA817DEV.US.ORACLE.COM> create or replace type my_type as object ( x int );
2 /
Type created.
a@ORA817DEV.US.ORACLE.COM> create table t2 of my_type;
Table created.
a@ORA817DEV.US.ORACLE.COM> create table t3 ( x int );
Table created.
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM> !exp userid=a/a owner=a;
Export: Release 8.1.7.3.0 - Production on Thu May 23 15:28:48 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user A
. exporting object type definitions for user A
About to export A's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export A's tables via Conventional Path ...
. . exporting table T 0 rows exported
. . exporting table T2 0 rows exported
. . exporting table T3 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM> drop table t2;
Table dropped.
a@ORA817DEV.US.ORACLE.COM> drop table t3;
Table dropped.
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM> !imp userid=a/a full=y ignore=y
Import: Release 8.1.7.3.0 - Production on Thu May 23 15:28:57 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing A's objects into A
. . importing table "T" 0 rows imported
. . importing table "T2" 0 rows imported
. . importing table "T3" 0 rows imported
Import terminated successfully without warnings.
a@ORA817DEV.US.ORACLE.COM> set echo off
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX SYS_C007424 SYSTEM
TABLE T SYSTEM
T2
T3 SYSTEM
TYPE MY_TYPE
<b>versions and such are always appreciated</b>
X Wang, May 23, 2002 - 4:25 pm UTC
Version 8.1.7.3.0
Big help. I am wondering why Oracle shows IMP error messages if it skips error and keeps going.
May 24, 2002 - 9:14 am UTC
Just letting you know -- Just because the objects have the same name doesn't mean they are the same. It is just informing you that "something with the name I wanted to use already exists".
Importing just the data...
sm, October 21, 2002 - 5:53 pm UTC
UNIX, 8.1.7.4.0
Tom,
I want to import just the data from one database to the other..
Export is done as grants=n indexes=n rows=y constraints=n full=y triggers=n direct=y compress=n
Then Import is done as:
ignore=y
One of the schema has some snapshots...
Whenever I do data refresh this way...
some of the snapshot refresh jobs are added too...
So, instead of 10 original jobs..there are 20..and then I have to delete the duplicate jobs..
How I can I avoid this?
October 21, 2002 - 7:04 pm UTC
delete the 10 jobs before you import i guess? there are lots of nuances like that (you might check constraints on these tables -- if you have unnamed constraints, you might find you have a constraint PER import you've done).
The other would be
tables=(.......)
instead of FULL=.....
to copy the data, pulling just the tables you want.
Constraints per import?
sm, October 21, 2002 - 7:32 pm UTC
"there are lots of nuances like
that (you might check constraints on these tables -- if you have unnamed
constraints, you might find you have a constraint PER import you've done)."
I assumed you were talking about snapshot tables here..
I didn't understand this..
Export was done as constraints = 'N'
Constraints will be disabled before import..
Could you please explain the above in quotes.
Thanks...
October 21, 2002 - 7:39 pm UTC
I was just stating that with imp/exp -- there are lots of nuances like that to watch out for. It is not a general purpose "data mover" really -- it is just what it is.
Cannot tell you how many times I've seen "what, I've 1000 constraints on that table -- no wonder its slow". All cause of using exp/imp with un-named constraints
It was just a "for example"
Ouch!!!
sm, October 21, 2002 - 7:54 pm UTC
Might as well..just use insert statement (with append hint) then...I will have better control....
considering my database is 2 and a half gigs only...
A reader, October 22, 2002 - 12:43 am UTC
Hi Tom
Is this at sqlplus or command line you have given here
a@ORA817DEV.US.ORACLE.COM> !exp userid=a/a owner=a;
a@ORA817DEV.US.ORACLE.COM> !imp userid=a/a full=y ignore=y
If this is sqlplus can you tell me how to do it.
Samit
October 22, 2002 - 7:11 am UTC
That is sqlplus, that is sqlplus on unix
you probably are on windows -- use HOST <command>
A reader, October 22, 2002 - 5:51 am UTC
Hi Samit,
If you are new then this question is ok else its time for you to refresh things.
! or HOST is used to execute the O\S (Operating System) commands from SQL Prompt. '!' Used for Unix Flavours and HOST for NT.
Ex:
SQL\> !DIR /* On Unix */
or
SQL\> HOST dir /* On Win-NT */
Regards,
Sagi
A reader, July 25, 2003 - 10:20 am UTC
Dear Tom,
I know you are on vacation, but hope to get a chance to answer my question.
The issue is with the Import of almost a big database. There isn't any error in export log file though. But I faced the same error for the same table twice. Even the error force Shutdown to the instance and I have to restart the database again. Then if I countinue, it will work ok. I don't have any idea why is that? could you please shed any light as to why it generate this error?
Thanks in advance,
Arash
P.S. It's Oracle 8.1.7
imp system/manager@perd file=c:\perd.exp log=c:\perd.log fromuser=graindba touser=graindba commit=y grants=n recalculate_statistics=Y
. . importing table "S1_SETTLEMENT_CHARGES" 1181837 rows imported
. . importing table "S1_SETTLEMENT_CHARGE_TABLE" 6058405 rows imported
IMP-00017: following statement failed with ORACLE error 1114:
"CREATE UNIQUE INDEX "PK_SETTLEMENT_CHARGE_TABLE" ON "S1_SETTLEMENT_CHARGE_T"
"ABLE" ("VEHICLE_DISPOSITION_NBR" , "CONTRACT_NBR" , "PRICING_ORDER" , "SETT"
"LEMENT_NBR" , "CHARGE_GRADE_CODE" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STO"
"RAGE(INITIAL 40960 FREELISTS 1 FREELIST GROUPS 1) NOLOG"
"GING"
IMP-00003: ORACLE error 1114 encountered
ORA-01114: IO error writing block to file 4 (block # 524297)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 524303)
ORA-01114: IO error writing block to file 4 (block # 524297)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 524303)
IMP-00017: following statement failed with ORACLE error 1115:
"ALTER TABLE "S1_SETTLEMENT_CHARGE_TABLE" ADD CONSTRAINT "PK_SETTLEMENT_CHA"
"RGE_TABLE" PRIMARY KEY ("VEHICLE_DISPOSITION_NBR", "CONTRACT_NBR", "PRICING"
"_ORDER", "SETTLEMENT_NBR", "CHARGE_GRADE_CODE") USING INDEX PCTFREE 10 INIT"
"RANS 2 MAXTRANS 255 STORAGE(INITIAL 40960 FREELISTS 1 FREELIST GROUPS 1) "
" ENABLE "
IMP-00003: ORACLE error 1115 encountered
ORA-01115: IO error reading block from file 4 (block # 494963)
ORA-01110: data file 4: 'D:\DATA\DATABASES\PERDUE\DATA_FILES\GRAIN_1.DBF'
ORA-27091: skgfqio: unable to queue I/O
OSD-04026: Invalid parameter passed. (OS 494962)
IMP-00017: following statement failed with ORACLE error 3113:
"CREATE TABLE "S1_SETTLEMENT_CHARGE_TEMP" ("SETTLEMENT_NBR" NUMBER, "CONTRAC"
"T_NBR" NUMBER, "PRICING_ORDER" NUMBER, "VEHICLE_DISPOSITION_NBR" NUMBER, "C"
"HARGE_GRADE_CODE" VARCHAR2(10), "CHARGE_GRADE_PRICE" NUMBER, "CHARGE_GRADE_"
"VALUE" NUMBER(6, 3), "SORT_ORDER" NUMBER(5, 0), "PRORATED_FLAG" CHAR(1)) P"
"CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 368640"
" NEXT 65536 MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 1 FREELISTS 1 FREELIST "
"GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SMARTGRAIN""
IMP-00003: ORACLE error 3113 encountered
ORA-03113: end-of-file on communication channel
IMP-00000: Import terminated unsuccessfull
July 25, 2003 - 12:30 pm UTC
check the ulimit for the user that started the listener. It is possible that the user running the listener has a low ulimit, the create index is doing direct writes (dbwr isn't doing the writes) and the ulimit on the user is too low.
A reader, July 25, 2003 - 1:05 pm UTC
Hi Tom,
Thanks for your time. If I understood well, you mean limit in profile. I checked that and all of them are unlimited!
I start from scratch and this time without Recalculate_Statistics=Y, and so far so good. It has finished downloading the data and it's in Enabling Constraints phase.
Is it possible that Recalculate_Statistics=Y cause that error? If so could you please tell me why?
Thanks millions of tons for your time,
Arash
July 25, 2003 - 1:14 pm UTC
I meant ULIMIT at the os level.
stats would not do it, no
Ulimit
A reader, July 29, 2003 - 3:17 pm UTC
Dear Tom,
I still didn't understand what was the problem with import. You told me check ULIMIT ! I don't know what that is at OS level ! The OS is windows 2000 and unfortunately our network administrator is on vacation. Could you please explain it more?
Another thing is, as I told you before, It faild at point 'A', I restart the instance and continue the import as of point 'A'. This time it worked. Reanalyze it. But it is very very slow. Why is that?
Thanks again for your time,
Arash
July 29, 2003 - 3:29 pm UTC
opps -- sorry, i assumed "unix" for some reason. ulimit doesn't "exist" on windows.
I'll have to refer you to support for this one, sorry.
A reader, July 29, 2003 - 3:31 pm UTC
Thanks
A reader, July 29, 2003 - 4:44 pm UTC
Dear Tom,
I found the following in Metalink. Do you think that's the issue(Not Bigger Than 4GB)?
fact: Oracle Server - Enterprise Edition 8
fact: MS Windows NT
fact: MS Windows 2000
symptom: Rman Backup Fails
symptom: RMAN-06003: ORACLE error from target database: %s
symptom: ORA-01114: IO error writing block to file %s (block # %s)
symptom: ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
symptom: OSD-04026: Invalid parameter passed.
symptom: Select from table fails
symptom: ORA-01115: IO error reading block from file <name> block # &
lt;num>
symptom: Stack trace contains
ksedmp ksupop opiodr opitsk opiino opiodr opidrv sou2o opimai
change: resized database datafile > 4GB
cause: <Bug:1668488>
Cannot AUTOEXTEND to 4Gb boundary - full restore required
<Bug:1612154>
Instance Crashes with ORA-27069 When a Datafile Autoextends up to
4096MB
The datafile# mentioned in error ORA-01114 is larger than 4 Gb.
Check:
SQL> select file#, bytes, create_bytes from v$datafile;
fix:
The datafile has to be made smaller than 4Gb, by dropping the tablespace and
recreating the content of the tablespace in datafiles smaller than 4Gb.
Avoid datafiles with autoextend enabled as they might extend to 4Gb or beyond.
Example:
SQL> select tablespace_name
from dba_data_files
where file_id=<file_number> mentioned in ORA-01114 and ORA-027059&
#059;
SQL> select contents from dba_tablespaces where tablespace_name=
<name_of_the_tablespace>; => temporary
As the datafile belongs to a temporary tablespace you can drop and
recreate it with datafiles smaller than 4Gb. Do not use AUTOEXTEND
unless in combination with MAXSIZE.
In other cases it might be wise to secure the data of the tablespace first
in order to be able to recreate
July 29, 2003 - 8:04 pm UTC
not if it works the second time around
Bug#1668488
A reader, July 31, 2003 - 11:52 am UTC
Hi Tom,
Just F.Y.I, I contact Oracle Support. The following was their reply:
"
if reports more than 4Gb you might be hitting Bug#1668488.
please apply ythe patshset 8174
"
I tried bith ways. I mean:
1- Added another datafile to the tablespace
2- Installed Oracle patch 8.1.7.4
both of them fixed the problem.
Regards,
Arash
import tables
A reader, February 24, 2004 - 5:51 pm UTC
Tom,
trnn1 schema is in DEV and PROD. Say there is one table trnn1.TB1 in PROD I want to replace with trnn1.TB1 from DEV. But the table definitions of these tables are not exactly same. Can I export trnn1.TB1 from DEV and sucessfully import it to trnn1 on PROD without droping trnn1.TB1 in PROD?
Thanks
Mike
February 25, 2004 - 7:41 am UTC
how exactly do they differ. then maybe I can say yes or no.
ora-01114 error
A reader, July 16, 2004 - 9:49 am UTC
Hi,
I am facing the same error ora-01114. these are the contents of my alert log file
ORA-01114: IO error writing block to file 28 (block # 2096564)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 2096690)
ORA-01114: IO error writing block to file 28 (block # 2096564)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 2096690)
now as mentioned in the article, i did
select tablespace_name from dba_data_files
where file_id=28;
the file id 28 corresponds to the temporary tablespace and the datafile size of temp tablespace is 4096 mb. what shud i do now? just drop the temporary tablespace and recreate it with smaller datafiles? shud i take any other precautionary measures? pls help.
thanks.
July 16, 2004 - 11:32 am UTC
please contact support for an issue like this. they'll need version information, platform, etc -- this is clearly a support issue.
but yes, if this is just temp, just drop it and recreate -- fastest path to a solution, but it won't get to the real answer (to prevent it from happening yet again)
thanks
A reader, July 16, 2004 - 2:16 pm UTC
thanks a lot Tom.
Migrate data from PROD to DEV
SHGoh, July 21, 2004 - 6:01 am UTC
Dear Tom,
There is a requirement to migrate the data from PROD to DEV instance. There are about 100 tables in SCHEMA1 and the requirement is to get 70 tables over to DEV instance. The problem is some of the developer has already manually created and enter some data in some of the tables (20tables) in DEV(these tables are the same from the 70 tables which is going to move over). The user account for PROD and DEV is different and what is the best method to do it.
When we set the ignore=y for imp command, would it overwrite the existing data or append as the new record in the table. If so, then the import will fail half way if the is has the same primary key.
If there is a Parent-to-Child constraints being set for the table. Does it mean that we need to set imp them in sequence ? Thanks in advance.
Rgds
SHGoh
July 21, 2004 - 8:32 am UTC
just drop the offending tables from dev? so you get a consistent view of data? who knows what junk is in these 20 tables already -- that would not be good to have with the other 50.
Import erros with row rejected
Abdul, July 22, 2004 - 12:26 pm UTC
Hi Tom,
I exported full database. Now i have two casees.
1- I truncate all the tables in the schema then start importing using ignore=y . Import has some error ora-02291 integrity contraint violated.
2 - I drop the schema then start import its a same error.
Please tell me what i should do.
Abdul
July 23, 2004 - 8:01 am UTC
1) of course, because the objects come in in "any order imp felt like bringing them in" (alphabetical i think, but not relevant -- nothing you can control)
2) you would really need to reproduce that for me, import will import tables, create indexes, add constraints after all data is loaded.
Import errors
Abdul, July 22, 2004 - 3:49 pm UTC
Hi ,
Can any boddy have look please my above question.
Thanks its king of urgent
July 23, 2004 - 8:16 am UTC
you know -- i have a day job (there is just one of me).
Import Error
Nishant Choudhary, September 15, 2004 - 7:50 am UTC
i m facing the problem while import ... if i go with user level imp ..it goes fine (except at the contraint level) ..... i m sending u the error with the command fired ... plz help ..
D:\cmssdata>imp file=d:\cmssdata\cmssexp14Sep.dmp full=y ignore=y statistics=none
Import: Release 9.2.0.4.0 - Production on Tue Sep 14 22:11:17 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: / as sysdba
IMP-00058: ORACLE error 1031 encountered
ORA-01031: insufficient privilegesUsername: callcenter/callcenter@testcms
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 file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 3113:
"BEGIN "
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.CREATE_EVALUATIO"
"N_CONTEXT_OBJ, 'DBA',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.CREATE_EVALUATIO"
"N_CONTEXT_OBJ, 'AQ_ADMINISTRATOR_ROLE',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.CREATE_EVALUATIO"
"N_CONTEXT_OBJ, 'SYS',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.CREATE_ANY_EVALU"
"ATION_CONTEXT, 'DBA',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.CREATE_ANY_EVALU"
"ATION_CONTEXT, 'SYS',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.ALTER_ANY_EVALUA"
"TION_CONTEXT, 'DBA',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.ALTER_ANY_EVALUA"
"TION_CONTEXT, 'SYS',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.DROP_ANY_EVALUAT"
"ION_CONTEXT, 'DBA',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.DROP_ANY_EVALUAT"
"ION_CONTEXT, 'SYS',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.EXECUTE_ANY_EVAL"
"UATION_CONTEXT, 'DBA',TRUE);"
""
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(SYS.DBMS_RULE_ADM.EXECUTE_ANY_EVAL"
"UATION_CONTEXT, 'SYS',TRUE);"
""
"COMMIT; END;"
IMP-00003: ORACLE error 3113 encountered
ORA-03113: end-of-file on communication channel
IMP-00003: ORACLE error 3114 encountered
ORA-03114: not connected to ORACLE
D:\cmssdata>
September 15, 2004 - 9:53 am UTC
maybe it is because your keyboard is dropping vowels.
mb t s bcs yr kbrd s drppng vwls
(harder to read eh?)
3113 = please contact support.
IMP-00003: ORACLE error 1403 encountered
Amal, September 18, 2004 - 1:25 am UTC
Hi Tom,
I am trying to import data from an Oracle 8.1.7.4 database into another Oracle 8.1.7.3 database. And I am using the export/import version 8.1.7.3
The import fails for tables having LOB columsn giving error like:
IMP-00017: following statement failed with ORACLE error 1403:
"CREATE TABLE "MERX_NPOINT_ANNOUNCEMENTS" ("ANN_ID" VARCHAR2(70) NOT NULL EN"
"ABLE, "ANN_HEADLINE" VARCHAR2(2000) NOT NULL ENABLE, "ANN_DETAIL" CLOB NOT "
"NULL ENABLE, "CATEGORY_ID" VARCHAR2(70) NOT NULL ENABLE, "POSTED_DATE" DATE"
" NOT NULL ENABLE, "START_DATE" DATE, "END_DATE" DATE, "ISLEADSTORY" VARCHAR"
"2(1) NOT NULL ENABLE, "USER_NAME" VARCHAR2(300) NOT NULL ENABLE, "URL" VARC"
"HAR2(1000), "IMAGE" BLOB, "ATTACHMENT" BLOB, "IMG_WIDTH" VARCHAR2(3), "IMG_"
"HEIGHT" VARCHAR2(3), "ATTACHMENT_TYPE" VARCHAR2(30), "IMG_POSITION" VARCHAR"
"2(6), "VIEW_RESTRICTED" VARCHAR2(1), "ARTICLE_DATE" DATE, "DISPLAY_ORDER" N"
"UMBER(4, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE"
"(INITIAL 573440 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "NP30_DATA" LOB ("
""ANN_DETAIL") STORE AS (TABLESPACE "NP30_LOB" ENABLE STORAGE IN ROW CHUNK "
"8192 PCTVERSION 10 CACHE READS STORAGE(INITIAL 32768 FREELISTS 1 FREELIST "
"GROUPS 1)) LOB ("IMAGE") STORE AS (TABLESPACE "NP30_LOB" ENABLE STORAGE IN"
" ROW CHUNK 8192 PCTVERSION 10 CACHE READS STORAGE(INITIAL 32768 FREELISTS "
"1 FREELIST GROUPS 1)) LOB ("ATTACHMENT") STORE AS (TABLESPACE "NP30_LOB" E"
"NABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 CACHE READS STORAGE(INITIAL "
"32768 FREELISTS 1 FREELIST GROUPS 1))"
IMP-00003: ORACLE error 1403 encountered
ORA-01403: no data found
The tables already exist in the destination database and I am using ignore=y in my import parameter list.
Is this a bug in Oracle? Please help me out.
Thanks in advance!
Amal
September 18, 2004 - 10:30 am UTC
well, if it is a known bug - the best place to find that out would be, well, the support website -- metalink.oracle.com.
if you are curious to try and diagnose this, what I would do is:
o grant alter session to the userid doing the import (not via a role)
o create a logon trigger:
tkyte@ORA8I> create or replace trigger foo
2 after logon on schema
3 begin
4 execute immediate 'alter sessions et events ''10046 trace name context forever, level 12'' ';
5 end;
6 /
Trigger created.
o imp ... tables=thatTable ignore=y
review the trace file -- look for the 1403 error and see what statement it was on, it could be very telling and helpful.
IMPORT error
Nishant Choudhary, September 18, 2004 - 6:54 am UTC
Sir,
i m unable to understand the follow up ... can u plz describe it in detail .........
thanx in advance
September 18, 2004 - 10:35 am UTC
I know, when you try to communicate without vowels:
maybe it is because your keyboard is dropping vowels.
mb t s bcs yr kbrd s drppng vwls
(harder to read eh?)
it is hard to read.. but I did think that:
3113 = please contact support.
was as clear as crystal?
(and still, your keyboard is seriously falling apart, "plz"? "u"? "i m" -- if you cannot take the time to type in full words, well. i don't know what to say)
Please be kind to Tom
A reader, September 18, 2004 - 10:46 am UTC
Folks,
Tom spends his evenings, week-ends and any spare time he could find in helping all of us. Let's be at least kind and polite to Tom.
Please write with all the vowels. It is hard to read without them.
September 18, 2004 - 11:05 am UTC
It isn't about kindness, is it about "communication".
Over the years I've learned, especically in this sort of "global medium" that you have to pick your words carefully and precisely.
I still don't know what a "lak" is half the time.
And many people wouldn't understand "6 one way, 1/2 dozen the other" means or what I was trying to say...
and if you couple the language issues with arbitrary "instant messenger speak", it gets really annoying.
I recently recieved an email with a document attached. The email was so ful of abbreviations and "IM speak" as to be barely intelligible. Funny thing was, the attachment (for his professor) was the outline describing his masters project. Interestingly -- that contained full use of vowels. Hmmm, very nice -- he is asking me to critique/help on his project, yet I don't get the courtesy of real words!! My only suggestion was that in the future, if you want to make a positive impression on someone -- you'll use real words. It makes you sound smarter too.
(oh, and I cannot respond to unsolicitated email either, so please don't take the above paragraph as an indication that I do)
ignore=y :
Pravesh Karthik from chennai, September 18, 2004 - 2:41 pm UTC
Tom,
i have a doubt.
full database export - done on 'say' - 10/08/2004
Suppose someone updates one or more table with wrong logic or the table is truncated. 'say' on - 17/08/2004.
when a dba wants to retrieve the table from the last backup he has .. in our case as on 10/08/2004. The tables which lost the data will need to be imported with that dmp file. In case if there are records in the related table which refrences the lost tables, and those are lost ... the lost tables being child tables ...
if i say ignore=y :
will the import allow us to insert all those records that are statisfying the integrity of data and skipping those records which doesnot have the corresponding data in the master table ? If not - how can i accompalish that?
Thanks
Pravesh Karthik
September 18, 2004 - 5:17 pm UTC
bzzt -- export is not a physical backup. in fact, I refuse to even call it a backup. It is a logical dump of data at a point in time.
If the DBA did proper and correct backups -- they would do a tablespace point in time recovery to the point in time right before the nasty action and recover everything. You would still have issues with child tables -- you would need to drop and recreate the foreign keys (unless the child tables were all part of this tablespace and you decided to restore the entire tablespace to this point in time)
ignore=y just ignores errors on the table creates. the rows that fail the import will be logged to the logfile (if you specify one) and on screen.
I would truly hope you would have proper backups, and you would be running in archive log mode. If you do -- you will not lose a single BYTE of data.
Otherwise, you pretty much wiped out 7 days worth of work.
Pravesh Karthik from chennai, September 18, 2004 - 9:05 pm UTC
Tom,
Thankyou very much for your infor.
We have pretty good backup startegy as you suggested with production databases. The one i was referring was a development database.
Thanks again
Pravesh Karthik
September 19, 2004 - 10:20 am UTC
why would you not backup your development database? your guys downtime is free? backing up is cheap. not being able to work is not.
IMP-00018
Pravesh Karthik from chennai, September 19, 2004 - 2:07 pm UTC
Tom,
Import: Release 9.2.0.3.0 - Production on Sat Sep 18 12:36:03 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export file created by EXPORT:V09.02.00 via direct path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses WE8ISO8859P1 character set (possible charset conversion)
. importing DWH's objects into DWH
. . importing partition "TB_FCT_SALES":"PREVYEAR"
.............................................................
611064 rows imported
. . importing partition "TB_FCT_SALES":"CURRYEAR"
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
.................................
7087485 rows imported
. . importing partition "TB_FCT_SALES":"NEXTYEAR"
...........................................................................
...........................................................................
.............................
IMP-00058: ORACLE error 1654 encountered
ORA-01654: unable to extend index DWH.IX_FCT_SALES_24 by 1024 in tablespace USERS_DEV01
IMP-00018: partial import of previous table completed: 1795913 rows imported
. . importing partition "TB_FCT_SALES":"TO_CURRYEAR"
1 rows imported
About to enable constraints...
Import terminated successfully with warnings.
I got the above error ... I am not sure if i got all the data into the table, thats a partitioned table.
I guess that i have got the table data and rebuild of index would be suffiecient from the above import. Please confirm whether i am right here.
Thanks as always.
Pravesh Karthik
September 19, 2004 - 2:26 pm UTC
IMP-00058: ORACLE error 1654 encountered
ORA-01654: unable to extend index DWH.IX_FCT_SALES_24 by 1024 in tablespace
USERS_DEV01
IMP-00018: partial import of previous table completed: 1795913 rows imported
. . importing partition "TB_FCT_SALES":"TO_CURRYEAR"
that says "sorry, ran out of space, stop"
that table did not "finish"
imp question
Rob, October 09, 2004 - 10:29 pm UTC
In general, before I do the import, the tablespaces should be precreated? Is this right? Assuming it is a full database export from unix platform that I am importing into windows. Thanks.
October 10, 2004 - 9:27 am UTC
if you are doing things across OS's where filenames do not match, yes, you better pretty much have the tablespaces pre-created as the CREATE TABLESPACE commands from unix with unix file names won't work on windows.
Exclude one schema during the import
Sean, November 08, 2004 - 9:04 pm UTC
Hi Tom,
I used to use full database export for import (about thirty schemas). It works fine.
Now we need to skip one schema. It is quite tedious to list all schemas I want during the import. Is there a way to exclude one schema during the import?
Solaris 5.9, Oracle 9204.
Thanks so much for your help.
November 08, 2004 - 9:34 pm UTC
sorry -- tedium rules.
you can use a parfile to make it "easier" -- don't need to do it on the command line.
Exclude one schema alternative...
Dana Day, November 08, 2004 - 10:56 pm UTC
Could you do a full db import, then drop the one schema/tablespaces?
November 09, 2004 - 8:38 am UTC
of course -- but I would assume there is a reason they don't want that one schema, like for example "it was our audit trail schema and is 5 terabytes, the rest of the data is 500 meg" or something like that.
with IMP, you cannot "skip" a schema.
import performance with commit=n
Sean, November 24, 2004 - 10:55 am UTC
Hi Tom,
I am importing the whole schema with a lot of million rows tables. I found that import with commit=n is much faster than import with commit=y. But the Oracle Doc (Utility manual) states that "Specifying COMMIT=y prevents rollback segments from growing inordinately large and improves the performance of large imports."
Thanks so much for your help.
Sean
November 24, 2004 - 12:00 pm UTC
the documentation is wrong on the performance thing. commit=n is the only way to go.
they got it backwards.
I filed a doc bug with the following points:
Point 1: the caveat about rbs growing "inordinately" is incorrect since an
insert generates the least amount of undo (these tables are un-indexed when
import goes against them). It makes people think "commit=y" is "good" but it
is not.
Point 2: commit=y is provably *slower* than commit=n. everytime IMP
commits, we have to wait for a log file sync to occur, if we commit 1,000
times --we wait 1,000 times. If we commit once at the end -- we wait once
(for about the same amount of time as one of the 1,000 would take). commit=y
is a way to SLOW DOWN import, not speed it up.
Point 3: The comment about the uniqueness constraint is not very accurate
either. The "advice" isn't a good practice really -- it is wrong advice. If
the table is to have a unique constraint AND the import fails during the load
of that table (only 1/2 was loaded) then when import RESTARTS it will reload
those same rows!! (since the import failed, the unique constraint has not be
placed on it!) Now import will fail on the constraint, not the load and you
are left with a mess. If you had left commit=n, this would not happen.
A reader, November 24, 2004 - 12:28 pm UTC
Tom,
if commit=n, will it commit at the end of all records inserted? -- yes right? .. in that case, i(f) .. i have 12 million rows, it will commit, only after the end. also, i am creating the indexes along with the import, not after the import. So, that case my rollback tablespace will throw error for insufficient space.
In this scenario, should i say commit='y' or 'n' ?
Thanks a lot
November 24, 2004 - 1:14 pm UTC
it'll commit the segment it is loading into (the table in general), yes.
why would you create indexes during the import??? that'll be the slowest way to go. disable then, imp (skip_unusable_indexes is there just for that) and rebuild them afterwards (perhaps with nologging if you can backup after)
IMP-00003: ORACLE error 959 encountered! ORA-00959: tablespace 'INFOMATRIX' does not exist
A reader, November 30, 2004 - 11:34 am UTC
Hi Tom,
I exported user povsport_ddl using:
exp system/god_on_leto file=/dbBackup/dev2/povsports.exp owner=povsports_ddl
Export terminated successfully without warnings.
and then import using:
imp system/manager file=/dbBackup/dev2/povsports.exp fromuser=povsports_ddl touser=povsports_ddl commit=y grants=n
Export file created by EXPORT:V09.00.01 via conventional path
import done in US7ASCII character set and UTF8 NCHAR character set
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
. importing POVSPORTS_DDL's objects into POVSPORTS_DDL
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "ARTICLES" ("ARTICLEID" NUMBER(10, 0) NOT NULL ENABLE, "CATEGO"
"RY" VARCHAR2(256), "TITLE" VARCHAR2(256), "AIRDATE" DATE, "POSTDATE" DATE, "
""EPISODENUMBER" NUMBER(10, 0), "SEASONNUMBER" NUMBER(10, 0), "BODYTEXT" CLO"
"B, "SUMMARY" VARCHAR2(256)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 "
"STORAGE(INITIAL 262144 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "INFOMATRI"
"X" LOGGING LOB ("BODYTEXT") STORE AS (TABLESPACE "INFOMATRIX" ENABLE STORA"
"GE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE STORAGE(INITIAL 262144 FREELIST"
"S 1 FREELIST GROUPS 1))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'INFOMATRIX' does not exist
. . importing table "ATHLETES" 4 rows imported
. . importing table "ATHLETESARTICLES" 7 rows imported
. . importing table "HISCORES" 4 rows imported
. . importing table "KIDSTATUS" 4 rows imported
. . importing table "KIDUSER" 7 rows imported
. . importing table "KIDUSERLOG" 0 rows imported
. . importing table "LANGUAGEFILTER" 45 rows imported
. . importing table "OBJECTIONABLEWORDS" 0 rows imported
. . importing table "PLAYBYPLAY" 0 rows imported
. . importing table "POLL" 0 rows imported
. . importing table "PROVINCES" 13 rows imported
. . importing table "SPORTS" 4 rows imported
. . importing table "SPORTSARTICLES" 7 rows imported
About to enable constraints...
Import terminated successfully with warnings.
All the tables in origin schema have 'INFOMATRIX' as tablespace name. My questions are:
1- why does it fail for just one table and the rest are ok?
2- How can I avoid this error? I used ignore=y Destroy=Y but didn't fix the issue.
Thanks for your time,
- Arash
November 30, 2004 - 11:53 am UTC
"CREATE TABLE "ARTICLES" ("ARTICLEID" NUMBER(10, 0) NOT NULL ENABLE, "CATEGO"
"RY" VARCHAR2(256), "TITLE" VARCHAR2(256), "AIRDATE" DATE, "POSTDATE" DATE, "
""EPISODENUMBER" NUMBER(10, 0), "SEASONNUMBER" NUMBER(10, 0), "BODYTEXT" CLO"
"B, "SUMMARY" VARCHAR2(256)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 "
"STORAGE(INITIAL 262144 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "INFOMATRI"
"X" LOGGING LOB ("BODYTEXT") STORE AS (TABLESPACE "INFOMATRIX" ENABLE STORA"
"GE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE STORAGE(INITIAL 262144 FREELIST"
"S 1 FREELIST GROUPS 1))"
Any time you have multi-tablespace objects (partitioned tables, tables with lobs, IOT's with overflows for example), you need to have the tablespaces in place OR you have to precreate them
The reason:
imp will take simple single segement objects (objects that do not span tablespaces) and attempt to execute the CREATE statement. If that fails due to "tablespace doesn't exist", it wipes out the first tablespace and tries again. If that fails, it bails (on the assumption that you didn't want a multi-tablespace object to be in a single tablespace)
So, just precreate this table in the appropriate tablespace(s).
A reader, November 30, 2004 - 11:36 am UTC
sorry! I am importing Oracle 9.0 on HP-UX to oracle 9.2 on HP-UX
very good! Thank you very much.
A reader, November 30, 2004 - 2:03 pm UTC
Just my 2 cents
Oleksandr Alesinskyy, February 11, 2005 - 4:29 am UTC
Some my observation concerning ORA-01114 problem on Windows on 4GB boundary
1. These error has some tendency to reccurency - it was patched in 8.1.7, then reappearded in 9.0, then patched agaib (don't remember 9.1.03 or 9.1.0.4) then reppeared in 9.2, ...
2. It may occurs not only when file cross 4GB biundary, but on any multiplier of it (8GB, 12GB and so on)
3. It occurs ONLY during autoextend process, if you extend file manully (alter database datafile ... RESIZE ...) all works Ok (at least in 9.x). So, if you hit this error there is no need to create new datafile - it is enough to extend existing one (is it better or worse then creating
additianal datafile is another story).
And at the end a question -
I have received recently ORA-1114 error for another reason (real hardware problem), but I can not identify a file that has caused it - it reports file number 202 and maximum file number in SYS.FILE$ is 14. Any suggestions?
ORA-01114: IO error writing block to file 202 (block # 597829)
ORA-27070: skgfdisp: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 1117) The request could not be performed because of an I/O device error.
February 11, 2005 - 8:10 pm UTC
temporary file # is what that looks like.
tempfiles are numbered sequentially starting AFTER max files. You probably have 200 as max files and this is just the second tempfile.
I hope that have answered my question myself :)
A reader, February 11, 2005 - 4:38 am UTC
After some thoughts I have guessed that 202 should be a second tempfile (i.e. that tempfiles are reported as
DB_FILES+number_of_tempfile, where DB_FILES represents a value of corresponding init.ora parameter).
Is this guess correct?
February 11, 2005 - 8:11 pm UTC
yes :)
Can I import the skipped records into a different table
Ignatius, March 01, 2005 - 5:47 am UTC
Dear Sir,
Question : "Can I import the skipped records into a different table."
I have a table with Primary Key defined (Table = Emp - Database_A).
I have a latest export dump file on "Emp" Table from another Database.
Now I want to import the dump file into the Emp table of Database_A.
I use the Ignore = Y parameter to skip duplicates and to carry on importing
My question is can i insert only the skipped records into another table.
Thank you very much and God Bless you
Ignatius
March 01, 2005 - 8:31 am UTC
you would have to import into a VIEW with an instead of trigger. The view would try to insert into the real table, upon failure, into the other table.
SKIP_UNUSABLE_INDEXES
Ronald, March 02, 2005 - 6:42 am UTC
Dear Mr Kyte,
Thank you very much for pointing out the use of nologging and SKIP_UNUSABLE_INDEXES.
I just did an import of a relatively small schema. It is around 50mb.
I took me 2 hours.
I have disabled all the foreign key constraints and triggers, unusable-ed all the indexed and altered them nologging beforehand.
but I have noticed after the import all indexes were showing ‘VALID’, I guess I migth have missed something, just wonder if you could kindly point out how to make skipping of index maintenance work when importing.
March 02, 2005 - 7:39 am UTC
import uses traditional (conventional path) inserts and will never be "nologging" (impdp in 10g, different tool -- different story)
what kind of machine is this? 50mb (pendrive size) taking 2 hours doesn't seem even remotely reasonable.
Re: SKIP_UNUSABLE_INDEXES
Ronald, March 02, 2005 - 11:37 pm UTC
I expect after the import,
all the non PK indices will still be ‘UNUSABLE’ waiting for me to rebuild.
however all of them were ‘VALID’
I just wanted to know from you, if I was wasting time setting all the index unusable, while the import is still maintaining those and, ignoring the __SKIP_UNUSABLE_INDEXES__ parameter
Rgds
March 03, 2005 - 7:23 am UTC
SKIP_UNUSABLE_INDEXES
Ronald, March 04, 2005 - 1:56 pm UTC
Please kindly tell me how to quickly import, it's rather hard to drop all to PK UK and indices and safely put them it, if there are examples, you can just tell me which chapter to look, for that I have both of your books ;^P
March 04, 2005 - 2:04 pm UTC
why are there even tables if you are importing?
let import create them after it is done? that is the normal way.
_SKIP_UNUSABLE_INDEXES_
Ronald, March 06, 2005 - 8:27 am UTC
Dear Mr. Kyte,
It is not up to this small DBA to remove all the tables before doing the import, you see, we are using import to restore a set of tables in the db to one of those snapshots created earlier, from some other teams. That is why I wish to find a better way.
March 06, 2005 - 2:56 pm UTC
sorry, the only way(s) will be
a) drop the tables instead of truncating, letting export recreate them
b) create the primary keys as DEFERRABLE (this will use non-unique indexes) but I would not recommand this, has other side effects that might not be desirable
c) drop the constraints -- and import will put them back.
Check Constraints & Cursors
Satish Kumar, April 16, 2005 - 2:19 am UTC
How many check constraints in a table?
How many cursors can use in a stored procedure?
April 16, 2005 - 8:49 am UTC
a) more than you could ever want to use (no defined limit other than practicality imposes)
b) more than you could ever want to use (same caveat). If the question is "open simultaneously", your dba limits that via open_cursors, a parameter setting.
import ignore=y constraints problem
Js, December 09, 2005 - 6:36 pm UTC
Hi,
I have seen some time norow import with ignore=y does not import all the constraints.
for testing purpose.
I took norow export of one schema.
Dropped all the constraints foreign + check + unique + primary
them import with that norow dump using ignore=y
It imported only 6 constraints out of 3099 + check constraints.
And in one case I have to drop the primary key of some tables [ with cascade constr.. because some tables were having child tables ] and then I tried to fix all
the constraints with norow + ignore=y , It imported the primary key but not foreign key.
December 10, 2005 - 5:16 am UTC
you would have to give me a "for example, create this table, export it, do this and you'll see what I mean"
Example
Jagjeet Singh, December 11, 2005 - 3:56 am UTC
For check constraints :
SQL> select count(*),table_name,constraint_Type from user_constraints
2 group by table_name,constraint_Type ;
COUNT(*) TABLE_NAME C
---------- ------------------------------ -
3 T C
SQL> ho exp file=f tables=rnd.t userid=/
Export: Release 9.2.0.4.0 - Production on Sun Dec 11 13:54:14 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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
About to export specified tables via Conventional Path ...
Current user changed to RND
. . exporting table T 0 rows exported
Export terminated successfully without warnings.
SQL> begin
2 for i in ( select * from user_constraints ) loop
3 execute immediate ' alter table '||i.table_name ||' drop constraint '||i.constraint_name ;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select count(*),table_name,constraint_Type from user_constraints
2 group by table_name,constraint_Type;
no rows selected
SQL> ho imp file=f tables=t fromuser=rnd touser=rnd ignore=y userid=/
Import: Release 9.2.0.4.0 - Production on Sun Dec 11 13:56:13 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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 file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing RND's objects into RND
. . importing table "T" 0 rows imported
Import terminated successfully without warnings.
SQL> select count(*) from user_constraints;
COUNT(*)
----------
0
. . trying to find that test case for foreign keys..also ..
December 11, 2005 - 4:32 am UTC
well, that is hardly a complete example.
I cannot reproduce, I used this table:
create table t ( x int check (x>0) );
So, I shall be forced to "guess", your constraints were NOT NULL constraints.
If you imp userid=u/p full=y show=y you'll discover the not null constraints are part of the create table command. You did ignore=y, table was already created, hence the constraints will be skipped over - not really skipped over so much as "ignored, you said to ignore things".
Full examples include, well, everything. Hope I guessed right - this is the way it works.
Yes, It was not a good example.
Jagjeet Singh, December 11, 2005 - 5:03 am UTC
I know, it was not a good example.
Knowingly, I did not provide the create table command and even "desc T" and you are right
these 3 constraints are not null. :)
Actually, I had faced two issues regarding check and foreign keys. Our norow export was not creating foreign keys + check cons. That time i could not go in to find out the things.
But when you said to give example I tried with all type of check constraints but could not get the desired result and after spending 40 minutes I could reach to it.
Still I am looking for foriegn key test case. It would be great if you can give me example ..
December 11, 2005 - 5:23 pm UTC
I cannot give you an example - because I cannot reproduce it...
PARFILE size
Marcio Portes, January 12, 2006 - 2:20 pm UTC
Does Oracle have a limit to the size of a parfile as of 9ir2?
Regards,
January 13, 2006 - 10:18 am UTC
not that I am aware of.
Skipping Tables while importing
Sudershan, February 21, 2006 - 2:29 pm UTC
Hi Tom..
--Oracle 9.2.0.7 on Unix--
--Data only import from Production to Development--
--about 170 gigs--
Is there any way to skip a few tables (about 10 out of 115 or so) while importing...like may be lock them so the data
will not get imported for them..
Also, I will be just disabling all the constraints and triggeres in the destination database..and doing this import..and enabling them back...
Are there any caveats for the constraints or with this approach?
Thanks...
February 22, 2006 - 8:19 am UTC
I'd be using a restore of a hot backup of production personally - that way you
a) get it done really fast
b) prove to yourself that YOU CAN IN FACT DO IT (the restore)
you can precreate the tables in a fashion that doesn't permit inserts, but be prepared for import to list out every failed row.
Or you can
a) rename table t to t_hold
b) create view t as select * from t_hold
c) create an insert of insert trigger on the view t and "do nothing" in it. imp will insert into the view, which does nothing.
but seriously - use your BACKUPS!!!!
import fails to create database links
A reader, September 14, 2006 - 6:39 am UTC
Hello Tom,
I did a full database export and then import, which imports all objects except database links. THe export was done from oracle version 9.0.1.4.0 and imported into oracle 10g Rel 2 on SLES9.
THe user childscreen has connect role which includes the create database link privilige, why does the database link creation fail
Part of import log:
. importing CHILDSCREEN's objects into CHILDSCREEN
IMP-00017: following statement failed with ORACLE error 1031:
"CREATE DATABASE LINK "DEV.HEALTH.NT.GOV.AU" USING 'oakd'"
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
IMP-00017: following statement failed with ORACLE error 1031:
"CREATE DATABASE LINK "OAKCODE.HEALTH.NT.GOV.AU" CONNECT TO "OAK_CODE" IDENT"
"IFIED BY "CODE" USING 'oakp'"
However i noticed that a user who had a DBA role had all its database links imported. what other privilige do users need to import them.
i tried creating a db link in 9i to ensure childscreen user has the required privileges and it works.
thanks
September 14, 2006 - 9:13 am UTC
they would need create database link.
import failes to create database links
A reader, September 15, 2006 - 2:22 am UTC
Hello Tom,
AS i mentioned user childscreen has the create database link privilige.
Thanks
Preeti
September 15, 2006 - 6:54 am UTC
doubt it.
RE: connect role and db link
Mark A. Williams, September 15, 2006 - 9:41 am UTC
A Reader from AU Said...
"THe user childscreen has connect role which includes the create database link privilige"
In 10GR2 survey says:
SQL> select *
2 from dba_sys_privs
3 where grantee = 'CONNECT'
4 order by privilege;
GRANTEE PRIVILEGE ADM
------- -------------- ---
CONNECT CREATE SESSION NO
1 row selected.
- Mark
import failes to create database links
A reader, September 15, 2006 - 10:16 pm UTC
THanks for your response Marvin, i just noticed another issue. tHe connect role in 9i (exporting database) has the follwing granted to connect role:
GRANTEE PRIVILEGE ADMIN_OPTION
CONNECT ALTER SESSION NO
CONNECT CREATE CLUSTER NO
CONNECT CREATE DATABASE LINK NO
CONNECT CREATE SEQUENCE NO
CONNECT CREATE SESSION NO
CONNECT CREATE SYNONYM NO
CONNECT CREATE TABLE NO
CONNECT CREATE VIEW NO
But Connect only ends up with CREate Session in 10g
Why have the rest of the system privileges granted to Connect gone missing. It was a full database export and import, ideally should have sucked in everything....
Thanks
September 16, 2006 - 2:37 pm UTC
ideally, it would not - connect, resource and dba are "special builtin roles"
RE: Connect role and db link
Mark A. Williams, September 16, 2006 - 10:32 am UTC
Marvin?
Anyway, it was changed in 10gr2 as a security measure. From the New Features Guide:
[start quote]
Connect Role Privilege Reduction
The connect role privilege reduction feature reduces the number of privileges granted to the connect role to one, the CREATE SESSION privilege. The privileges have been removed from the connect role:
CREATE CLUSTER
CREATE DATABASE LINK
CREATE SEQUENCE
ALTER SESSION
CREATE SYNONYM
CREATE TABLE
CREATE VIEW
This feature helps you deploy secure configurations by helping enforce the least privileged principle.
[end quote]
</code>
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14214/chapter1.htm#sthref505 <code>
- Mark
Marvin
Mark A. Williams, September 16, 2006 - 4:17 pm UTC
[marvin the martian voice on]
I wish to blow up your measly planet Earth with my Illudium Q-36 Explosive Space Modulator, because the Earth is blocking my view of Venus.
[marvin the martian voice off]
:)
import failes to create database links
A reader, September 17, 2006 - 7:41 pm UTC
THanks for your response Marvin, i just noticed another issue. tHe connect role in 9i (exporting database) has the follwing granted to connect role:
GRANTEE PRIVILEGE ADMIN_OPTION
CONNECT ALTER SESSION NO
CONNECT CREATE CLUSTER NO
CONNECT CREATE DATABASE LINK NO
CONNECT CREATE SEQUENCE NO
CONNECT CREATE SESSION NO
CONNECT CREATE SYNONYM NO
CONNECT CREATE TABLE NO
CONNECT CREATE VIEW NO
But Connect only ends up with CREate Session in 10g
Why have the rest of the system privileges granted to Connect gone missing. It was a full database export and import, ideally should have sucked in everything....
Thanks
September 18, 2006 - 1:04 am UTC
because CONNECT is "a built in special role" and export knows better than to export it, resource and DBA.
ORA-01114: IO error writing block to file
Mahalingesh Daddi, July 27, 2007 - 4:34 pm UTC
I am getting the following error while update statement please help.
update dws.account_transactions
*
ERROR at line 1:
ORA-01114: IO error writing block to file 1023 (block # 363125)
ORA-27063: skgfospo: number of bytes read/written is incorrect
Additional information: 108544
Additional information: 253952
ORA-01114: IO error writing block to file 1023 (block # 363125)
ORA-27063: skgfospo: number of bytes read/written is incorrect
Additional information: 108544
Additional information: 253952
July 27, 2007 - 8:21 pm UTC
I'm suspicious by the high file number, looks like a temporary file, perhaps your temp tablespace is botched.