Server Utilities :: ORA-02270 / No Matching Unique Or Primary Key For Column-list

Jul 6, 2010

I was cloning a schema user1 as user2 in the same database.

user1 had quota on 2 tablespaces user1_data and user1_index.

I created user with name as user2.

I created tablespace user2_data only and granted user2 unlimited quota on that tablespace only (did not grant him 'resource' role or unlimited tablespace privilege) Now exported user1 schema as follows

exp system/<passowrd> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user1_schema_exp.log owner=user1 rows=y constraints=y triggers=y indexes=y statistics=none recordlength=65535 compress=no consistent=n grants=y

then imported in in user2 schema as follows

imp system/<password> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user2_schema_imp.log fromuser=user1 touser=user2 rows=y constraints=y indexes=y statistics=none recordlength=65535 grants=y

during import i encountered following errors for so many constraints

"ALTER TABLE "table2" ADD CONSTRAINT "constraint_name1" FOREIGN KEY ("CTR_ID") REFERENCES "table1" ("CTR_ID") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
IMP-00017: following statement failed with ORACLE error 2270:

I found that the it happened as the primary key of table1 was not created for which error was logged in the log file

. . importing table "table1" 19441 rows imported
IMP-00015: following statement failed because the object already exists:
"ALTER TABLE "table1" ADD CONSTRAINT "T1_PK79" PRIMARY KEY ("CTR_"
"ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 F"
"REELISTS 1 FREELIST GROUPS 1) TABLESPACE "USER1_INDEX" LOGGING ENABLE "
. . importing table "table5" 0 rows imported

However, I checked that the T1_PK79 does not exist in the user2 schema though it exists in user1 schema Neither the index for priamry key (T1_PK79) existed in user2 schema not the table <table1> existed before this import Then what could be the reason that I am getting an error "IMP-00015: following statement failed because the object already exists"?

I assume tablespace for index would not be an issue here as other indexes got created properly in user2_index tablespace during this import.

I tried this twice, once with user2 schema and then with user3 schema as well (with different tablespace), but result is the same.

There were no users connected to the database during export and no background jobs were modiying any data in schema user1 while export.

View 4 Replies


ADVERTISEMENT

ORA-02270 / No Matching Unique Or Primary Key For Column-list

Jan 14, 2013

I'm trying to make a foreign key against a table which has a unique index instead of a primary key.

i get this error:

Error SQL: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.

my question is (i've searched several times with no results) can i create a foreign key with a table wich has no primary key but a unique index ?example:

CREATE TABLE TABLE1(
IDTABLE1 NUMBER(5),
NAME VARCHAR2(30)
);
CREATE TABLE TABLE2(

[code]....

or TABLE1.IDTABLE1 must be a 'Primary Key' instead of unique index ?I know it should be primary, but i need to know if it would work with the index somehow. The reason is i'm migrating a large database and the original structure in mysql uses fk with indexes and no pks in some tables (48 to be precise)

View 11 Replies View Related

SQL & PL/SQL :: Unique Identifiers Matching Datatype In Oracle?

Jan 19, 2011

what is the matching datatype of unique identifier in SQL to Oracle ques regd SSIS when I map a unique identifier in SQL to Oracle via SSIS, I get curly braces at both ends in the end result.

1B66FE97-A9CA-4E0D-9593-00046E2AF7E1 - i/p(SQL Server)
{1B66FE97-A9CA-4E0D-9593-00046E2AF7E1} - end result(Oracle)

In SSIS, I convert the unique identifier to string data type.

View 1 Replies View Related

Application Express :: Matching Against A Multi-select List

Mar 8, 2013

I have a 'Select List' widget (P_FILTER) which I have set to return multiple values. In my report region, I have something like this

Select A from B where B.Col_1 IN upper(:P_FILTER)

When user selects only 1 value, the report is correct but if user selects more than 1 value (e.g. 2), the report does not return any rows. How do I get the report to recognize the multiple values returned by the LOV?

View 2 Replies View Related

Server Utilities :: ORA-00001 - Unique Constraint Violated Error During Impdp?

Oct 17, 2011

I am trying to import the dump using the impdp utility and followed the below steps I have disabled all the constraints and executed the below command

impdp DIRECTORY=EXPDP_DAILY_BACKUP_DIR DUMPFILE=expdp_QA_full_11102011_214501.dmp
logfile=imp_EXPDP_QA_full_12102011_203254.log remap_schema=prod:prod remap_tablespace=prod:prod
schemas=prod TABLE_EXISTS_ACTION=truncate content=data_only.

but I am getting the below error like this for 1 or 2 tables . and If I import those tables seperately its getting imported successfully. i am not getting the below error always .

ORA-31693: Table data object "PROD"."DAS_ID_GENERATOR" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (PROD.DAS_ID_GENERATOR_P) violated
ORA-31693: Table data object "PROD"."TKT_DIST_SRV_STAT" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (PROD.SERVER_STATS_P) violated

View 8 Replies View Related

SQL & PL/SQL :: Difference Between Primary Key And Unique / Not Null

Dec 10, 2010

what is difference between primary key and unique+not null

View 12 Replies View Related

Forms :: Create Unique Primary Key

Nov 28, 2010

A Master form in a tabular format. Id is a display item. I want to auto generate Id with following query.

select nvl(max(issue_id),0)+1 into :issue_mt.issue_id from issue_mt;

It works:

1)When each record is saved before creating new record.

It doesn't work:

2)If multiple records are entered with out saving each record.

What to do to generate unique Id in both cases?

View 7 Replies View Related

Server Utilities :: Primary Keys Are Not Exporting When Export Using EXP Command

Dec 27, 2011

I have taken database backup using exp command and when I try to import in other pc the foreign keys are not imported. It saying error message that no matching unique key or primary key for this column.

how will i take backup including with primary keys?

View 7 Replies View Related

PL/SQL :: ORA-00001 / Unique Constraint Because ID Is Primary Key On TABLE1

Jun 27, 2012

I have in a plsql block somewhere a statement like

INSERT INTO TABLE1( id , col)
SELECT id, col
FROM TABLE2;

This statement returns an error ORA-00001: unique constraint because id is a primary key on TABLE1. I would like to know what is the value of id that raised the exception.

View 15 Replies View Related

Performance Tuning :: How Expensive (speed) Is Unique Versus Primary Key In Large Table

Aug 15, 2011

I have two design alternatives and need to understand how expensive (speed) is one of them against the other for a medium size table (100K-200K records):

create table xyz
(
f1 number not null,
f2 varchar2(20) not null,
f3 number not null,
f4 varchar2(50),

[code]....

the idea is to optimize the design by using a PK instead of the 3 keys and there is a debate that searching a unique index field(2nd scenario) is of the same speed than searching a PK field (1st scenario).

View 5 Replies View Related

SQL & PL/SQL :: Get Unique Row Number For List Of Values?

Apr 9, 2012

I need a query to get the below.

Source :

select * from test;
LVL
1
2
3
1
2
3
4

Output:

LVLSEQ
11
21
31
12
22
32
42

I need above to uniquely identify the set of data.

View 25 Replies View Related

PL/SQL :: Unique Schemas - Users List

Aug 5, 2013

find the below task. I have unique schemas. and every schema we have many tables.I would like to know

1. I have Scott schema and here I have 10 tables. EMP,DEPT,SALGARDE ( and these tables having different priviliges).

2. another schema MACIN having table DEPT, SALS, GRADE ( having different priviliges) Now I would like to know Schema 1 having permissions to see the tables and he is doing some modifications,inserting,deleting .... on tables containing in MACIN schema.like this it is happening in vice verse in max schemas. now I would like to know who all are having access to login to another schema and what are the priviliges they have till now on what  tables they applied changes...is it possible to figure out? if yes how can we?

View 6 Replies View Related

Server Utilities :: Mapped 100th Column From CSV File After 5th Column

Sep 10, 2012

I have written the code for sql loader, I have given termination of file is | , i have given five columns name in sequence...My Requirement is that i want to mapped 100th column from Csv file after 5th column how to do it.

OPTIONS (ERRORS=100000000)
LOAD DATA
CHARACTERSET UTF8
INTO TABLE "TARGET_STG_AMC"
TRUNCATE
[code]....

View 10 Replies View Related

Server Utilities :: DBMS-DATAPUMP.metadata-filter And Long Table List?

Jun 9, 2010

I have a problem with DBMS_DATAPUMP.metadata_filter.Let's suppose that I need to export a huge list of tables (a,b,c,d,e,f,g,h,i...). Let's suppose that the list is dynamic do NOT want to use

DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN (''a'', ''b'', ...)',
object_type => NULL);

In my_export_table there is the list:

CREATE TABLE my_export_table
(
EXPORT_OBJECT_NAME VARCHAR2(50 BYTE)
)

Now I'm trying to use this form:

DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN (SELECT a.export_object_name FROM my_export_table a, user_objects b WHERE a.export_object_name = b.object_name AND b.object_type = ''TABLE'')',
object_type => NULL
);

but it results in error.

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-00942: table or view does not exist

[code]...

View 1 Replies View Related

Creating List Of Items And Using Foreign Keys As Primary

May 19, 2007

How can I create a list of items in a field, for instance lets say I have a table called car and one of the sub categories is parts. How can I make it so that parts can be any number of pre-defined entities? Or even table, for instance if I make a table called parts how can I use that in the car table in place of parts?

My second question is about using foreign keys as primary keys. If I am writing an email sql DB and I decided to use the members usrname as the primary key in the member table but then made another table that lists all the emails and decided to make the foreign key member username the primary key there.

Is that safe to do or should i create a sequence in which to identify a primary key for the email list table? Also what if I extend member to several other tables and use it as a primary key there too, seems like a kind of dangerous way to do things...

View 1 Replies View Related

ORA-01747 - Oracle Reserved Words - Query Table Matching Database With NUMBER Column?

Oct 1, 2012

I am having issue with Oracle reserved words, one of the application is using table which has NUMBER as column. I am not able to query that table matching database with NUMBER column.

HERE

select a.*
from DOC a , FOLDER B
where a.NUMBER= B.INCIDENT_ID
and b.open = 'Closed'; I tried double quotes (“”) and sigle quotes too, none of them worked.

View 12 Replies View Related

SQL & PL/SQL :: How To Get Unique Value In Column

Oct 25, 2012

i am using one query which should return unique value. I have one table that has one column punch date datatype is date.

here value is stored in two rows that is

1. 24-10-12
2. 24-10-12

and my query is this:

SELECT distinct(PunchDate) FROM Trans_RawProcessDailyData ORDER BY PunchDate ASC;

but still getting two values.

View 11 Replies View Related

Server Utilities :: Checking Column Value In SQL Loader?

Jul 12, 2011

My requirement is to load the data from feed file into two tables based on the value of a column in feed file.

say column in feed file is "Activity_Value" . If the Activity_value is 10, the data from feed file should be loaded into Table A and If the Activity Value is 20 the data from feed file should be loaded into Table B.

View 3 Replies View Related

SQL & PL/SQL :: How To Add Column In Unique Index

Jan 17, 2011

I have created one unique index on one column of my table. Now i would like to add one more column in the same index without dropping the index.

SQL > CREATE TABLE DEBUG_TABLE
2 (
3 SLNO NUMBER,
4 MESSAGE VARCHAR2(4000 BYTE),
5 CREATED_DATE DATE DEFAULT SYSDATE,
6 CREATED_TIME TIMESTAMP(6) DEFAULT SYSDATE
7 );

Table created.

SQL > CREATE UNIQUE INDEX index_debug1 ON debug_table (SLNO);

Index created.

SQL > ALTER INDEX index_debug1 ADD COLUMN MESSAGE;
ALTER INDEX index_debug1 ADD COLUMN MESSAGE
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL >

View 6 Replies View Related

PL/SQL :: Unique Values Of Each Column

Aug 21, 2013

I have a problem .I have a oracle table its column count more then 500 . I want unique values of each column and insert one table.

View 44 Replies View Related

Server Utilities :: Concatenating Dashes In A Column Using Sqlldr

May 20, 2011

I'd like to load ss_numbers, but concatenate dashes in between.

The ssn currently looks like this in the file: 123456789

I'd like to put dashes in there to make it load like this. 123-45-6789

The data is positional, so I have the column in the ctl file defined as:

ssn position(1:9) char nullif ssn=blanks ,

I know how to concat using the other method of loading this way, but this is not how the data file looks:

"substr(lpad(:ssn,9,'0'),1,3)||'-'||substr(lpad(:ssn,9,'0'),4,2) ||'-'||substr(lpad(:ssn,9,'0'),6,4)",

how to get the dashes in there while loading with the positional method?

View 4 Replies View Related

Server Utilities :: SQL Loader - Skipping One Column Which Shouldn't

Mar 25, 2013

CONTROL FILE:
LOAD DATA
INFILE 'sample.txt'
INSERT
INTO TABLE TEST_RECORDS
WHEN REC_TYPE_HDR='HDR'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
[code]....

The value 603 should have loaded into TRAN_TYPE field or column, instead it loaded into the next field or column LINE_COMP.

View 3 Replies View Related

Server Utilities :: Calculated Column Population Using Sql Loader?

Aug 1, 2012

I want to insert calculated data while loading the data from sql loader.

eg let say column INTERNAL_TRANSACTION_ID,WAIT_TIME,MESSAGE_GUID,TRS_SIZE,RETURN_MESSAGE_GUID are null in input file then i want to populate column DEV_FLAG as 'U' ELSE 'D'

My control file looks like below but doesn't have DEV_FLAG.

load data
BADFILE '/backup/temp/rajesh/RIO/BadFiles/FILENAME'
append into table TEMP_rio_RESP_TIME_LND
TRAILING NULLCOLS
(
INSTALLATION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
TRANSACTION_ID CHAR

[code]....

View 2 Replies View Related

Server Utilities :: Return A Column Value Using Sqlloader After Loading

Dec 1, 2011

I have the following table intra_trades with t_id as the primary key. There is a trigger on that table that gets the next sequence and inserts it into the t_id column for every insert. I need to load data into that table using SqlLoader as chunks of 3000 rows and return the t_id back the script that Sqlload the data so that it can use that t_id's for the next process in the script.

intra_trades
t_id NUMBER(15) pk
t_name VARCHAR2(30)
t_loc VARCHAR2(40)
t_start TIMESTSTAMP
t_end TIMESTSTAMP
[code]....

The problem is that the only unique key on that table is the t_id which has a sequence on it and it is the pk. There can be duplicate rows in that table to meet the business needs for the company. So it is hard to associate the rest of the data in a row with t_id. The only thing I can think of is return the t_ids in the order it inserted so if the script keeps the order of rows in the memory it can associate the tid with the rest of the intra_trades info.How can I make the sqlloader return an array of t_ids that inserted? I need to return the t_ids's in the order it inserted so that the script can associate the t_id with the rest of the rest of the data in a row.

View 4 Replies View Related

Server Utilities :: Sqlldr Loading Into A Clob Column

Oct 26, 2011

I am doing a simple test and need to populate a small table with some data.

My table looks like this:

SQL> desc clob_test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(20)
C1 CLOB

I have a pipe delimited data file ID range 1-50000 and random characters with a length of 100-4000 bytes for the clob field.

My control file looks like this but I am guessing it is wrong:

LOAD DATA
INFILE 'clob_test.dat'
INTO TABLE CLOB_TEST
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
ID INTEGER EXTERNAL NULLIF (ID=BLANKS)
, C1
)

Let me know what I need to do to my .ctl file in order to load this data?

View 4 Replies View Related

Server Utilities :: How To Get Parent Table Column Reference

Sep 24, 2013

I have csv file which having parent and child related data.i need to load parent data in to parent table and get parent reference id and store in child table with child data. i am not able to find how to get parenet refernce id using control file. In my csv file i have 2 parent name rows. I need to create one A record in parent table and get that parent primary(P_ID) for 'A' record and put into child table for c_name test1 and test2 records.

mydata.csv
---------------
P_Name C_Name
------ ------
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;

1. table parent (P_ID, P_Name)

2. table child (C_ID, C_Name, P_ID)

i need data bello way.

Parent Table data:

P_ID P_Name
----- ------
1A
2B
3C
4D

Child Table data:

C_IDC_NameP_ID
--------------
1test11
2test21
3test32
4test42
5test53
6test53
7test64
8test64

My controll file
-------------------
LOAD DATA
INFILE 'mydata.data'
APPEND
INTO TABLE parent
when (1:1) = 'p'
fields terminated by ',' optionally enclosed by '"' trailing nullcols

[code].....

able to load parent data and generate P_ID, but i am not able to get P_ID for child records.

View 6 Replies View Related

SQL & PL/SQL :: Unique Constraint When 1 Column Is Not Null

Jun 15, 2012

I have 3 columns in a table: colX, colY, colZ.

Trying to find a way to prevent duplicates with these, but only if colX is not null.

For example, if there are already values for: colX = 1, colY = 1, colZ = 1

then:

Allowed: colX = null, colY = 1, colZ = 1
Not allowed: colX = 1, colY = 1, colZ = 1

I can't create a unique constraint on these columns because there are many null values for column colX, and as mentioned, when colX is null, colY and colZ can be any values.

I also tried using a before insert trigger to find duplicates before posting and raise an error if found, but this causes an ORA-04091 mutating error since the trigger in the table is referencing itself to check for duplicates.

Also, I know there is something called a function based index, but I cannot use those with my code, so I need another solution if possible.

View 4 Replies View Related

SQL & PL/SQL :: Multiple Unique Values In One Column?

Aug 26, 2011

I have a problem with some tables in database.

Table has three columns: userid, pname, pvalue.

Userid has unique values, for example: 234, 123, 587, etc.

In the field pname, there is three possible values: MAC, IP, S/N.

So if I go like this:

select pvalue
from table
where pname = 'MAC';

i get the values of MAC.

If I go like this:

select pvalue
from table
where pname = 'IP';

i get the values of IP.How can I join MAC with IP that is matching this MAC? I need an SQL statement for this.

View 11 Replies View Related

Server Utilities :: Skip Column While Loading Data Using SQL Loader

Feb 5, 2006

Data

Sl#Emp_noNameAddress
00101Tom1/B-XYZ street
00202Jon1/C-XYZ Street

Employee Datafile
001, 01, Tom, 1/B-XYZ street
002,02,Jon, 1/C-XYZ Street

Above is a sample data file. Now I would like to import the data into an Oracle table called employee using Oracle 9i SQL Loader utility. But the table has only 3 fields (Emp_no,Name & Address), so I would like to skip Sl# while loading data. I do not want to manually modify data file. How should I write .ctl file.

Sample .ctl file.

load data
INFILE 'dataEmployee'
BADFILE 'Employee.bad'
DISCARDFILE 'Employee.dis'
into table Employee
fields terminated by ','
TRAILING NULLCOLS
(Emp_no NULLIF Emp_no = BLANKS,
Name NULLIF Name = BLANKS,
Address NULLIF Address = BLANKS
)

View 4 Replies View Related

Server Utilities :: Copy Table With NCLOB Column Into Another Database (10g To 11g)?

Dec 4, 2012

I need to copy one table which is having NCLOB column into our new DB(11g) from old DB (10g). As this table having huge records (1 crore) it is difficult to use direct insert statement (INSERT INTO ... SELECT * FROM @remotedb). I don't have DBA privilege to use loader.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved