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.
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 >
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 RelatedI 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.
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.
i am having a table with out pk along with data.
Now , i need to add one column to that table , and update this column with the sequence no
like 1,2,3...... upto the max no of records. but i have to do this with out using a sequence.
how can i do.
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)
I have a table DN_ACTIONS with 9.5 million records. Column DA_OBJECT_NAME is not unique and same object name can have multiple entries. Column DA_ACTION_STATUS can have any values between 1 to 5.
I want to find all the DA_OBJECT_NAME in DN_ACTIONS where all of its entries has DA_ACTION_STATUS=3.
CREATE TABLE DN_ACTIONS
(
DA_ID NUMBER,
DA_OBJECT_NAME VARCHAR2(192 BYTE),
DA_ACTION_STATUS NUMBER(1)
);
best way as it does a self join on big table of 9.5 million.
(select distinct a.DA_OBJECT_NAME from DN_ACTIONS a where not exists ( select distinct DA_OBJECT_NAME from DN_ACTIONS b where a.DA_ OBJECT_NAME = b.DA_OBJECT_NAME and b.DA_ACTION_STATUS != 3))
What are the better ways to rewrite this query?
I am facing a problem in leave_form report! want to show employee's previous leaves detail (leavedate,leavetype), i don't want to show leavetype 'PPP' repeating frame type is Across/Down, there are 22 records of leavetype 'PPP' and one record of leavetype 'CL'
problem is report is showing 22 records of leavetype 'CL' of same leavedate!
i want that report should show the actual leavedate and leavetype records.using 9i database 6i developer server2003
For leavetype
FUNCTION Cf_1formula
RETURN CHAR
IS
v_leavetype CHAR(40);
BEGIN
SELECT LT.description
INTO v_leavetype
FROM hrm_attendance L,
hrm_leavetype LT,
hrm_employees E
[code]....
I have a table say MY_TAB with columns as below
emp_id number,
name varchar2(30),
from_dt date,
remarks varchar2(60)
insert into MY_TAB values (1,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (1,'TOM','02-JAN-13', 'some remark');
insert into MY_TAB values (2,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (3,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','02-JAN-13', 'some remark');
How do I ensure that when a user tries to insert record with emp_id as 1, then he should only be allowed to enter another from_dt but the value in the name column have to be the same as in the previous row of emp_id 1.
insert into MY_TAB values (1,'TOOM','03-JAN-13') --shld not be allowed.
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.
Using Oracle 11g, below is the table, partitions, unique and non-unique local index:
CREATE TABLE DOCA( DOCA_ID NUMBER NOT NULL , DOCA_BKG_PAX_ID NUMBER NULL , ROW_PURGE_DATE DATE NULL ,)PARTITION BY RANGE(ROW_PURGE_DATE)INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))( PARTITION P2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'dd/mm/yyyy')), PARTITION P200801 VALUES LESS THAN (TO_DATE('01/02/2008', 'dd/mm/yyyy')),) TABLESPACE T0; ALTER TABLE DOCA ENABLE ROW MOVEMENT;
CREATE UNIQUE INDEX XPKDOCA ON DOCA( DOCA_ID ASC, ROW_PURGE_DATE ASC)LOCALREVERSE TABLESPACE I0; ALTER TABLE DOCA ADD CONSTRAINT XPKDOCA PRIMARY KEY (DOCA_ID); CREATE INDEX XFKDOCA_DOCA_BKG_PAX_ID ON DOCA( DOCA_BKG_PAX_ID ASC)LOCALREVERSETABLESPACE I0;
I would like to know the difference between the performance of the unique and non-unique local indexes?.
Can a table conatin composite Alternate key which is not unique for each record in that table i.e combination of these columns have unique values which are repeated for one or more rows in that table.
View 8 Replies View RelatedFrom a step by step instructions I'm asked to put the following into sql*plus:
CREATE TABLE Lab2Lecturer
(staffNO VarCHAR2(10) NOT NULL,
title VARCHAR2(3),
fName VARCHAR2(30),
[code]...
Then the following:
INSERT INTO Lab2Lecturer
(staffNO, title, fName, lName, streetAddress, suburb, city, postCode, country, lecturerLevel, bankNO, bankName, salary, workLoad, researchArea)
VALUES
('1000', 'Dr', 'Johanna','Santoso',
'3 Robinson Av', 'Kew', 'Melbourne', '3080', 'Australia', 'C', '1000567237', 'CommBank', 65000.00,1.0, 'O-R DB');
and finally,
INSERT INTO Lab2Lecturer
(staffNO, title, fName, lName, streetAddress, suburb, city, postCode,country, lecturerLevel, BankNO,bankName, salary, workLoad, researchArea)
VALUES
('1000', 'Dr', 'Justine', 'Martin', '6 Algorithm AV', 'Montmorency', 'Melbourne', '3089', 'Australia', 'D', '1000123456', 'CommBank', 89000.00, 1.0, 'CBR');
when I try entering in the second one I get an error 'unique constraint violated'.So whats wrong exactly?
I have a running application where i have a table with pk defined on it and the pk clumn is inde in asc order by default. Can i alter the index in desc order as i always need to see the data in desc order.
View 7 Replies View RelatedI have a simple question, but i can't resolve it.
I have a table like this :
ID_A | VALUE
-------------
A 1
A 2
B 1
B 2
B 3
C 1
C 3
D 1
I need to fid the unique ID that match perfectly VALUE = 1 or 2.
I tried SOME/ANY/ALL/IN
I have a table called pf_stock_txns which just stores all of a clients transactions
If I do select distinct(client_id) from pf_stock_txns then I get back a unqiue list of all clients.
However, I need a query that will give me the first transaction for every unqiue client.
I have data like this:
a1 a2 a3 a4
ABC ABC xx zz
ABC xx mm mm
fg ui ok pl
I want to give them unique codes like :
Code for column
a1 Code1
ABCC-1
ABCC-1
fg C-2
then column
a2 Code2
ABC C-1
xxC-3
uiC-4
then column
a3 Code3
xxC-3
mmC-5
okC-6
then column
a4 Code4
zzC-7
mmC-5
plC-8
I can add these `columns in the table Code1 , Code2 etc columns to update.
I've got a table which has no unique identifier for a row. I have a field for an ID value but somhow there are duplicate values in this field.
Now I want to create an unique index over this field but therefore I have to remove the duplicate values.
So what I need is an update statement, maybe in a for each loop, which updates the ID field in each row starting with 1 and increasing the value.
In Informix there is a rowid value which is automatically set by Informix. But I think there´s no correspondent function/value in Oracle....
I need a "solution", guidance to a problem I have to solve. I have different letters with a value associate to it like
A:10, B:20, ..., G:250 and I have a Target to reach TARGET= 90
and I need to find among all letters I have which combination is equal or closest to my target.
A:10
B:20
C:20
D:20
E:30
F:40
G:250
IF there is more than one solution possible, the first found is perfect. With my example I can reach 90 with different combinations:
A+B+C+F
A+B+D+F
A+C+D+F
B+C+D+E
B+E+F
C+E+F
D+E+F
To complicate the things I have up to 10 different letters that need to combine to match my target. If there is no combination that exactly match my target, the closest higher combination is picked.
Is there a way to generate a unique identifier(length 8), which can contain numbers (0-9) and letters(a-z) in pl/sql or sql ?
Note :- in oracle 9i.
My DB version is
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
These error codes I'm getting in production.First of all, I've no duplicates present in that table for which this error has been raised.I've checked the index and related columns as well. NO DATA is there.So NO CHANCE for unique constraint violation.
SELECT * FROM ORDER_OCC_REQUISITION_X_REF WHERE LAB_ORDER_OCC_TEST_ID IN(SELECT LAB_ORDER_OCC_TEST_ID FROM LAB_ORDER_OCC_TEST WHERE LAB_ORDER_OCC_ID = 7944858);
no rows selected
Now when I'm trying to insert one row inside this table I'm getting this error, as you are seeing no records for this occurrence_id.
SELECT * FROM USER_INDEXES WHERE INDEX_NAME = 'ORD_OCC_REQ_UQ_TEST_IX_04';
--ORDER_OCC_REQUISITION_X_REF (Table name)
--MERGE_DT, LAB_ORDER_OCC_TEST_ID, TEST_ID, ACTIVE_YN (columns for the index 'ORD_OCC_REQ_UQ_TEST_IX_04')
As you can see there is no data then this error should not be raised. Update procedure.
/*******************************************************************************************************************
* Name : UPDATE_REQUISITION_X_REF
* Description : This Procedure update ORDER_OCC_REQUISITION_X_REF table with requisition_id
* that was generated due to merge process.
* In parameters : IN_merge_id NUMBER The order_ref_no of the orders to be merged (comma seperated)
***********************************************************************************************************************/
PROCEDURE UPDATE_REQUISITION_X_REF ( IN_merge_id IN TT_ORD_REQUISITION_WORK_AREA.merge_id%TYPE)
IS
[Code]....
Oracle Database Version is 10g ( 10.2.0.4.0)
I have two table emp_perm & employee.
1) Table emp_perm is a lookup table with 2 columns, temp VARCHAR2 & perm VARCHAR2.
Table emp_perm have below data in it:-
temp perm
1064885 0349034
0099982 7399982
6476456 9170346
5252525 52525252)
Now table employee might have following combinations of rows.
Records for both Temps file (1064885) and perm file number (0349034) does exist in employee table, in this case Delete Temp File Number records i.e. 1064885 and keep Permanent File Number (0349034) records as it is.
Records for only temp file number(0099982) does exists and corresponding perm file(7399982) does not exists in employee table in this case convert these records i.e. update 0099982 with its perm file number 7399982 using lookup table emp_perm.
Records for only perm file number exists (9170346) and corresponding temp file number (6476456) does not then do not amend employee table, as we are interested in perm file numbers.
If file number does exist in lookup table in emp_perm and both of them are equal (5252525) then do not amend employee table as temp and per file number are same.
How to replace uniqueidentifier in PL/SQL ,I have query like this,
@EmployeeID uniqueidentifier = NULL.
I have contents like below:
BREAD,BISCUIT
BREAD,MILK,BISCUIT
COKE,MILK
MILK,SUGAR
Now combination from each row will be like below (Just for understanding):
BREAD,BISCUIT --> [COMBINATION] {BISCUIT} {BREAD} {BISCUIT,BREAD}
BREAD,MILK,BISCUIT --> [COMBINATION] {BISCUIT} {BREAD} {MILK} {BISCUIT,BREAD} {BISCUIT,MILK} {BREAD,MILK} {BISCUIT,BREAD,MILK}
COKE,MILK --> [COMBINATION] {COKE} {MILK} {COKE,MILK}
MILK,SUGAR --> [COMBINATION] {MILK} {SUGAR} {MILK,SUGAR}
Now the ultimate aim is to find unique combinations (even if the same combination is present in different rows, we have to consider it as one combination), along with its frequency, result will be like below:
BISCUIT [occurence in 2 transactions and total 4 transactions] = 2/4 = .5
BREAD [occurence in 2 transactions and total 4 transactions] = 2/4 = .5
COKE [occurence in 1 transactions and total 4 transactions] = 2/4 = .25
MILK = 3/4 = .75
[Code]....
writing query to find unique combination like above? What I have tried is as below:
create table test (row_no number, col_no number, item varchar2(50))
/
insert into test values (1,1,'BREAD');
insert into test values (1,2,'BISCUIT');
insert into test values (2,1,'BREAD');
[Code]....
But I am not able to form the exact query. let me know if this can't be done through a single query.
Our RMAN backup failed because of the error:
starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/22/2011 22:19:07
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 08/22/2011 22:19:07
ORA-00001: unique constraint (ODBA.DF_U2) violated
Searching metalink, its related to bug 6014994 and the proposed workaround is to delete the constraint:
Cause:
Dropping a datafile from a tablespace followed immediately by adding
another datafile to the same tablespace will cause this Unique Key violation.
Taking a RMAN debug trace will show the file# related to the error
This is reported as bug <<6014994>> Unpublished on Metalink and fixed in 11g
RMAN RESYNC catalog signals DF_U2 violated constraint when a file# is reused in
the same tablespace
Solution
WORKAROUND: Drop df_u2 constraint
where can I delete the constraint, is it possible to do in RMAN or in the target instance?
I've set up Oracle Streams replication between multiple N-way zones like this:
CODE------------------------------
Nway1 |Intersect| Nway2
DB1 --|-- DB3 --|-- DB5 DB1, DB2, DB3, DB4 - N-way1
| | | | | DB3, DB4, DB5, DB6 - N-way2
DB2 --|-- DB4 --|-- DB6 DB3, DB4 - intersection zone
| |
-------------------------------
Physically DB1 .... DBN connected sequentially, so I want to prevent segmentation if some DB is unaccessible, but at the same time fight unneeded redundancy which uses too much link bandwidth to send N-1 LCR-s to all members of a single N-way group (so I want to split one big N-way zone into smaller ones and sequentially connect them into chain - it significantly reduces load on link if N is big enough (>10)). Also I want to have 2 DB in intersection zone to prevent single point of failure.
This scheme has one drawback - if change originated on DB3 or DB4, then it will be propagated (more correctly - applied and captured again) to DB5 and DB6 by both DB1 and DB2 (and, as far as I know, I have no means in capture rules to detect state of DB2 from DB1 and vise versa), so on DB5 and DB6 I get:
CODEORA-00001: unique constraint (DUMMYUSR.UNIQUE_RECORDS) violated error
I've set up standard conflict handler for apply process:
CODE
declare
cols DBMS_UTILITY.NAME_ARRAY;
begin
cols(1) := 'no';
cols(2) := 'name';
cols(3) := 'ddate';
dbms_apply_adm.set_update_conflict_handler(
object_name => 'DUMMYUSR.DUMMYTBL',
method_name => 'DISCARD',
resolution_column => 'no',
column_list => cols
);
end;
but it seems that it does not handle uniqueness conflicts. What is the best way to handle uniqueness conflict (is there a better way than to write custom error handler) and how serious is the impact on insert performance of having unique constraint and corresponding error handler. (In real world I will have to deal with tables with metainformation and without any keys).
Also, how to proceed with no error or raise exception from apply error handler with error that caused this handler to run? In oracle docs I can find only example that modifies LCR and runs lcr.EXECUTE(TRUE), but what to do if I don't want to reexecute LCR, but merely check error code and propagate error if it is not ORA-00001?
I have a table which sees a lot of use for queries
CREATE TABLE CASE_STAGE
(
ID NUMBER(9) NOT NULL,
STAGE_ID NUMBER(9) NOT NULL,
CASE_PHASE_ID NUMBER(9) NOT NULL,
"CURRENT" NUMBER(1) NOT NULL,
--and other columns
)
ID is a primary key
CASE_PHASE_ID is a foreign key
"CURRENT" should only ever have values of 0 or 1. When it has a value of 1 it is unique for that CASE_PHASE_ID
What I have tried that doesn't work is
create unique index case_stage_F_IDX1 on case_stage("CURRENT", case_Phase_id) which gives me
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
What is the correct syntax, something like ("CURRENT"=1,case_phase_id) seems right but fails with an error about a missing bracket. Do I need to use a CASE statement here?
Let's say we have Table - A and we would like to replicate specific row transaction to Table B.
Here are the rows in *Table A*
Time: Lets say 15:00
A1 Just Updated @15:00
A2 Just inserted @15:01
A3
B1 - Daily Delete Row -i.e just deleted a while back - Non scheduled process --executed by application @15:02
B2 -
B3 - Daily Delete Row - i.e just deleted a while back -- Non Schduled process --executed by application @15:05
B4 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
B5 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
B6 -Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
Current Data in Table B (Before Replication)
@15:00
A1 (without updates)
A3
B1
B2
B3
B4
B5
B6
Expected rows in Table B (via replication/snapshot/materialized view / or any other method)
*Replication at 15:30*
Table B - Read Only
Expected rows after replication-
A1 -- Newly updated details
A2 -- Newly inserted row
A3
B1 - Daily delete row is expected to be replicated
B2
B3 - Daily delete row is expected to be replicated
***Note row B4 is not expected to be replicated to table B.
Questions:
1) How can we get updates, inserts and daily deletes replicated while ignore large purges?
2) How can large purge changes be reflected in replicated tables as well without deleting daily deletes?
I have faced an error as below:-
1 : 23000 : java.sql.BatchUpdateException: ORA-00001: unique constraint (SDS1.PK_EXP_TXT) violated
1 : 23000 : java.sql.SQLException: ORA-00001: unique constraint (SDS1.PK_EXP_TXT) violated
java.sql.BatchUpdateException: ORA-00001: unique constraint (SDS1.PK_EXP_TXT) violated
at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:367)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9119)
[Code] ......
After i check data in all_indexes table, i have found some old data which are belongs to 2008 and 2009. The sample as below:-
OWNER INDEX_NAME LAST_ANALYZED
SDS1PK_ACTION 31-OCT-09 07:04:49
SDS1AK_ACTION 31-OCT-09 07:04:49
SDS1PK_COND 31-OCT-09 07:04:50
SDS1AK_COND 31-OCT-09 07:04:50
SDS1COND_FK1 31-OCT-09 07:04:50
SDS1COND_FK2 31-OCT-09 07:04:50
is it the problem due to the old data not remove from the all_indexes table ?.. if YES is it I have to delete the old data manually from the all_indexes table ?