Home » RDBMS Server » Server Utilities » importing data
importing data [message #255257] Tue, 31 July 2007 03:55 Go to next message
Thando
Messages: 12
Registered: March 2007
Location: South Africa
Junior Member
Hi There


I'm trying to import data i.e dwh1 to dwh3. using the command
imp parfile=dwh3_imp.par it doesn't go through i get a warning that says:


. importing OUTLN's objects into OUTLN
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
Import terminated successfully with warnings.
Re: importing data [message #255279 is a reply to message #255257] Tue, 31 July 2007 04:52 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the contents of the parameter file? Is this the WHOLE log file?
Re: importing data [message #255283 is a reply to message #255257] Tue, 31 July 2007 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any error/warning.

Regards
Michel
Re: importing data [message #255329 is a reply to message #255283] Tue, 31 July 2007 07:43 Go to previous messageGo to next message
Thando
Messages: 12
Registered: March 2007
Location: South Africa
Junior Member
This is what i get there are no errors but it cuts halfway through.

Username:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
export client uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
IMP-00015: following statement failed because the object already exists:
"CREATE TEMPORARY TABLESPACE "PSTEMP" BLOCKSIZE 8192 TEMPFILE '/data16/orad"
"ata/dwh3/temp/pstemp01.dbf' SIZE 419430400 AUTOEXTEND ON NEXT 655360 "
" MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLESPACE "CSS" BLOCKSIZE 8192 DATAFILE '/data18/oradata/dwh3/data"
"files/CSS_TBS.DBF' SIZE 62914560 EXTENT MANAGEMENT LOCAL AUTOALLOCAT"
"E ONLINE PERMANENT NOLOGGING"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLESPACE "RBS" BLOCKSIZE 8192 DATAFILE '/data18/oradata/dwh3/data"
"files/RBS01.DBF' SIZE 838860800 EXTENT MANAGEMENT LOCAL AUTOALLOCATE"
" ONLINE PERMANENT NOLOGGING"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLESPACE "EMPL" BLOCKSIZE 8192 DATAFILE '/data18/oradata/dwh3/dat"
"afiles/empl.dbf' SIZE 503316480 EXTENT MANAGEMENT LOCAL AUTOALLOCATE"
" ONLINE PERMANENT NOLOGGING"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLESPACE "INDX" BLOCKSIZE 8192 DATAFILE '/data18/oradata/dwh3/dat"
"afiles/INDX01.DBF' SIZE 36700160 EXTENT MANAGEMENT LOCAL AUTOALLOCAT"
"E ONLINE PERMANENT NOLOGGING"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLESPACE "PERS" BLOCKSIZE 8192 DATAFILE '/data20/oradata/dwh3/dat"
"afiles/pers.dbf' SIZE 524288000 EXTENT MANAGEMENT LOCAL AUTOALLOCATE"
" ONLINE PERMANENT NOLOGGING"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLESPACE "PEST" BLOCKSIZE 8192 DATAFILE '/data18/oradata/dwh3/dat"
"afiles/pest.dbf' SIZE 20971520 EXTENT MANAGEMENT LOCAL AUTOALLOCATE "
" ONLINE PERMANENT NOLOGGING"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLESPACE "CT_01" BLOCKSIZE 8192 DATAFILE '/data18/oradata/dwh3/da"
"tafiles/CT_01.DBF' SIZE 1048576 EXTENT MANAGEMENT LOCAL AUTOALLOCATE"
" ONLINE PERMANENT NOLOGGING"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLESPACE "CT_02" BLOCKSIZE 8192 DATAFILE '/data18/oradata/dwh3/da"
"tafiles/CT_02.DBF' SIZE 1048576 EXTENT MANAGEMENT LOCAL AUTOALLOCATE"
" ONLINE PERMANENT NOLOGGING"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLESPACE "CT_03" BLOCKSIZE 8192 DATAFILE '/data18/oradata/dwh3/da"
"tafiles/CT_03.DBF' SIZE 1048576 EXTENT MANAGEMENT LOCAL AUTOALLOCATE"
" ONLINE PERMANENT NOLOGGING"
"dwh3_imp.log" 6087 lines, 360210 characters


Re: importing data [message #255341 is a reply to message #255329] Tue, 31 July 2007 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
These are warnings saying that you already created tablespaces it is trying to create.
You can use "ignore=y" if you don't want to see these messages.

Regards
Michel
Re: importing data [message #255348 is a reply to message #255257] Tue, 31 July 2007 08:59 Go to previous messageGo to next message
Thando
Messages: 12
Registered: March 2007
Location: South Africa
Junior Member
Thanks Michele,but i still get the warning.


. . importing table "REPCAT$_RESOL_STATS_CONTROL" 0 rows imported
. . importing table "REPCAT$_RUNTIME_PARMS" 0 rows imported
. . importing table "REPCAT$_SITES_NEW" 0 rows imported
IMP-00017: following statement failed with ORACLE error 2264:
"ALTER TABLE "REPCAT$_SITES_NEW" ADD CONSTRAINT "REPCAT$_SITES_NEW_FULL_INST"
"" CHECK (full_instantiation IN ('Y', 'N')) ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
. . importing table "REPCAT$_SITE_OBJECTS" 0 rows imported
. . importing table "REPCAT$_SNAPGROUP" 0 rows imported
. . importing table "REPCAT$_TEMPLATE_OBJECTS" 0 rows imported
IMP-00017: following statement failed with ORACLE error 2264:
"ALTER TABLE "REPCAT$_TEMPLATE_OBJECTS" ADD CONSTRAINT "REPCAT$_TEMPLATE_OBJ"
"ECTS_VER" CHECK (object_version# >= 0 AND object_version# < 65536) ENABLE N"
"OVALIDATE"
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
. . importing table "REPCAT$_TEMPLATE_PARMS" 0 rows imported
IMP-00017: following statement failed with ORACLE error 2264:
"ALTER TABLE "REPCAT$_TEMPLATE_PARMS" ADD CONSTRAINT "REPCAT$_TEMPLATE_PARMS"
"_C1" CHECK (user_override in ('Y','N')) ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
. . importing table "REPCAT$_TEMPLATE_REFGROUPS" 0 rows imported
. . importing table "REPCAT$_TEMPLATE_SITES" 0 rows imported
IMP-00017: following statement failed with ORACLE error 2264:
"ALTER TABLE "REPCAT$_TEMPLATE_SITES" ADD CONSTRAINT "REPCAT$_TEMPLATE_SITES"
"_C1" CHECK (status in (-100,-1,0,1)) ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
IMP-00017: following statement failed with ORACLE error 2264:
"ALTER TABLE "REPCAT$_TEMPLATE_SITES" ADD CONSTRAINT "REPCAT$_TEMPLATE_SITES"
"_C2" CHECK ((site_name is not null and repapi_site_id is null) or"
" (site_name is null and repapi_site_id is not null)) ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
. . importing table "REPCAT$_TEMPLATE_STATUS"
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.REPCAT$_TEMPLATE_STATUS_PK) violated
Column 1 0
Column 2 Modifiable
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.REPCAT$_TEMPLATE_STATUS_PK) violated
Column 1 1
Column 2 Frozen
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.REPCAT$_TEMPLATE_STATUS_PK) violated
Column 1 2
Column 2 Deleted 0 rows imported
. . importing table "REPCAT$_TEMPLATE_TARGETS" 0 rows imported
. . importing table "REPCAT$_TEMPLATE_TYPES"
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.REPCAT$_TEMPLATE_TYPES_PK) violated
Column 1 1
Column 2 Deployment template
Column 3 01
Column 4
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.REPCAT$_TEMPLATE_TYPES_PK) violated
Column 1 2
Column 2 IAS template
Column 3 02
Column 4 0 rows imported
. . importing table "REPCAT$_USER_AUTHORIZATIONS" 0 rows imported
. . importing table "REPCAT$_USER_PARM_VALUES" 0 rows imported
. importing OUTLN's objects into OUTLN
. . importing table "OL$" 0 rows imported
. . importing table "OL$HINTS" 0 rows imported
. . importing table "OL$NODES" 0 rows imported
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. importing OUTLN's objects into OUTLN
. importing SYSTEM's objects into SYSTEM
. importing OUTLN's objects into OUTLN
. importing SYSTEM's objects into SYSTEM
. importing OUTLN's objects into OUTLN
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
About to enable constraints...
Import terminated successfully with warnings.
oracle@hyde:/data2/oradata/ora_back/dwh3/scripts=>
Re: importing data [message #255390 is a reply to message #255348] Tue, 31 July 2007 11:39 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There are two unique kinds of errors here (unless I'm mistaken):

Quote:
ORA-02264: name already used by an existing constraint

Probably doesn't matter; constraint already exists and Import can not create another one with the same name.

Quote:
ORA-00001: unique constraint (SYSTEM.REPCAT$_TEMPLATE_STATUS_PK) violated

It means that the table isn't empty. Import utility is adding records into the table, but new record violates uniqueness. What to do? I don't know, but you should. If you don't want duplicates, fine. If you'd like to duplicate records, disable unique constraint.
Re: importing data [message #255530 is a reply to message #255257] Wed, 01 August 2007 02:20 Go to previous messageGo to next message
Thando
Messages: 12
Registered: March 2007
Location: South Africa
Junior Member
Hi Littlefoot,

Thanks for the reply.How do i disable duplicates?


Re: importing data [message #255553 is a reply to message #255530] Wed, 01 August 2007 03:31 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Duplicates are already "disabled" with the constraint.

I believe you'd like to disable a constraint. If so, use ALTER TABLE command. Here is an example:
SQL> create table brisime (a number constraint pk_br primary key);

Table created.

SQL> insert into brisime values (1);

1 row created.

SQL> -- you can not enter another value = 1
SQL> insert into brisime values (1);
insert into brisime values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_BR) violated


SQL> alter table brisime disable constraint pk_br;

Table altered.

SQL> -- after we disabled the constraint, you can add as many 1s as you want
SQL> insert into brisime values (1);

1 row created.

SQL> insert into brisime values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>
Re: importing data [message #255852 is a reply to message #255257] Thu, 02 August 2007 02:18 Go to previous message
Thando
Messages: 12
Registered: March 2007
Location: South Africa
Junior Member
Thank you so much. It worked.
Previous Topic: Export/import problem (merged)
Next Topic: SQL*Loader: constraints and PL/SQL function (merged by LF)
Goto Forum:
  


Current Time: Wed Dec 07 03:19:45 CST 2016

Total time taken to generate the page: 0.11350 seconds