I have to update a column for all the records in the table with a unique contraint
ex-- select rec_no from tablename;
9
8
7
6
5
now i have to update this like
select rec_no from tablename;
8
7
6
5
4
but when i am doing update tablename set rec_no=rec_no-1 i am getting unique constraint error
The current update store procedure that I have updates a list of input provided, but it there are fields that are left blank, they are being updated as null in the database.
I'm having a trouble creating a store procedure that will just update the provided fields only.
There's a fairly popular Ordered Hint example on the web as follows:
<CODE>
select /*+ ordered use_nl(bonus) parallel(e, 4) */
e.ename,
hiredate,
b.comm
from
emp e,
bonus b
where
e.ename = b.ename
;
</CODE>
I would like to know what the "parallel(e, 4)" clause does. Where does the "4" come from? What is "parallel" here? I also have another question: If I have 5 tables--T_OREGON, T_UTAH, T_VIRGINIA, T_TEXAS, and T_OKLAHOMA--lined up in a join right behind a FROM, coming in at row counts of
T_OREGON: [a lot of rows; a lot more than T_UTAH]
T_UTAH: [smaller than T_OREGON] 40550 rows
T_VIRGINIA: 14 rows
T_TEXAS: 66 rows
T_OKLAHOMA: 8 rows
from T_OREGON or, T_UTAH ut, T_VIRGINIA va, T_TEXAS tx, T_OKLAHOMA ok...
my question is, if an Ordered hint can be used here, is it that the smallest table (in this case T_OKLAHOMA) gets placed first in the join and the rest of the table ascension in the join doesn't matter? Or is it that T_OKLAHOMA gets placed first in the join, followed by T_VIRGINIA [at 14 rows], followed by T_TEXAS, T_UTAH, and finally T_OREGON?
I have following data
WITH t AS
(
select 1 rn, 'A' val from dual UNION ALL
select 2 rn, 'A' val from dual UNION ALL
select 3 rn, 'B' val from dual UNION ALL
[Code]....
I need to calculate a new column "orderedRn" as
Rn Val OrderedRn
------------
1 A 1
2 A 1
3 B 2
4 C 3
5 C 3
6 C 3
7 A 4
8 A 4
9 B 5
10 B 5
AWR Report regarding ' SQL ordered by Physical Reads (UnOptimized))'. SQL ordered by Physical Reads (UnOptimized)UnOptimized Read Reqs =
Physical Read Reqts - Optimized Read Reqs %Opt - Optimized Reads as percentage of SQL Read Requests %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs Total Physical Read Requests: 3,311,261 Captured SQL account for 13.5% of Total Total UnOptimized Read Requests: 3,311,261 Captured SQL account for 13.5% of Total Total Optimized Read Requests: 1 Captured SQL account for 0.0% of Total UnOptimized Read ReqsPhysical Read ReqsExecutionsUnOptimized Reqs per Exec%Opt%TotalSQL IdSQL ModuleSQL
[code]....
I want to know what is the mean by 'UnOptimized Read Reqs' and 'Optimized Read Reqs 'Question 2) what is 'SQL ordered by Physical Reads tuning'.
Elapsed Time (s)CPU Time (s)Executions Elap per Exec (s) % Total DB TimeSQL IdSQL ModuleSQL Text
3,263 32 1 3263.49 2.79 3ta0ms19fvgds httpd.exe SELECT CASE WHEN COUNT >= 1...Elapsed
6,360 164 2 3180.17 5.44 51jx99dm0swv7 cpm_srvscript@ahcaxasmil1b (TNS V1-V3) SELECT /*+ CCL<PFT_GET_RP...
On AWR, I see two script that are out of ordinary, and I want to make sure that I interpret them correctly.
1) "Elap per Exec (s)" shows 3263.49 with 1 "Executions".
2) "Elap per Exec (s)" shows 3180.17 each execution with 2 "Executions".
Does this mean that this script ran for ~ 54 minutes (3263.49 / 60 seconds) for 1 "Execution" and ~ 53 minutes (3180.17 / 60 seconds) per each execution? I need to understand "Elapsed Time (s),CPU Time (s),Executions ,Elap per Exec (s), % Total DB Time" represent.
I am trying to understand "ordered" hint. I want to use it in my sql where I am using some left outer joins. I believe if I use this ordered hint it will be much faster.
But the problem is I am not able to understand the concept of this hint. As i did alot of search on this eveyone says it will be , even I got some similar references where it worked But I am getting contradicting explanation for on this.
is any way to export an Oracle database organized in manner that, both tables and constraints would be exported in the correct order.An easy sample:
- An database with 2 tables, with constraints between them. Table 1 has a FK on Table 2.
Is it possible to export both structure and data regarding the constraints, resulting in an script that makes it possibly to import it in a way that would not give me problems about constraints?
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
2,423 1 3,919 0.62 1.83 gt49gg0fnc5x8 srv_dr@ahs (TNS V1-V3) UPDATE /*+ CCL<OENDB_FILE...
2,227 14 1 2227.16 1.68 bggfx8a04prj9 SQL*Plus select * from (select n.source...
.........
On [SQL ordered by Elapsed Time], [SQL Module] shows an indication that a SQL was executed by which process (i.e. srv_dr@ahs)outside of SQL*PLUS.If [SQL Modeule] shows as [SQL*Plus], does it mean the query was run in SQL*PLUS manually or directly?I have the SQL ID. How do I find out who, how, and exactly what time it was run?
i want to get table name, constraint name, constraint type with join processes in string type. this is what i want: alter table tablename add constraint constraintname constrainttype(columnname)
View 1 Replies View RelatedI just switched my STBDY DB to a PRIMARY DB and I am ran the following:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters are successfully stored
ORA-00245: control file backup operation failed
RMAN-08132: WARNING: cannot update recovery area reclaimable file list
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORARZULB are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
[code]....
what causes this warning and how to get rid of it?
I am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".
I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.
Im trying to list the products list of a client grouped by type of the product. Ex:
product type
prod.A acid
prod.B flavour
prod.C acid
prod.D cleaner
prod.E flavour
I want to list something as:
Acid
Prod.A
Prod.C
Cleaner
prod.D
Flavour
prod.B
prod.E
DECLARE
CURSOR GRP IS
SELECT RowNum rn, Letter_Group_ID||'-'||A_Desc AName,Letter_Group_ID
FROM Hrs_Group;
BEGIN
Clear_list('Letter_Group_ID');
FOR I IN GRP LOOP
Add_List_Element('Letter_Group_ID',I.rn,I.AName,I.Letter_Group_ID);
end loop;
END;
FRM-30351: No list elements defined for list item.
List LETTER_GROUP_ID
I just want to list and group all my tables that are linked together by constraints. I just want my tables to be able to be listed together as one particular database. my tables are , CUSTOMER, ORDER_INFO, ORDER_LINE, PRODUCT. They're all linked together by way of constraint and I want to list and print them all together as one DB. HOW DO I put them all in one schema and then also list them all together and print/illustrate them as one. also, I tried to import them into their own scheme but i ran into a series of probs regaurding the .dmp file being read.
View 2 Replies View Related i have a problem that i have created a table named PROJECT and structure is like-
CREATE TABLE PROJECT
(
id VARCHAR2(250 BYTE) NOT NULL,
NAME VARCHAR2(250 BYTE) NOT NULL,
COMPID NUMBER NOT NULL,
[code]...
and this table is created in some number of different database then i want to delete the not null constraint of "id" column and there is 3 not null constraint in my table and i didnot gave name at the time of creation, so we cant delete it by its name in the script because there will be different name in another database for the same constraint. i want to make a script to delete the not null constraint because it is the part of my script.
how should i get proper information for deleting the constraint. and i used "user_constraints" but it shows same information for the all three "not null" constraint.
Create table students
(
Sid number(3),
Sname varchar(20) not null,
Email varchar(20) not null,
Doj Timestamp default sysdate not null,
Dob date not null,
[code]....
when i run the above code i m getting error like this...I dont understand wat error it is...Because i didnt reference any column in the check constraint...Thn why it says that i m referencing some column..
Error:
SQL>
SQL> Create table students
2 (
3 Sid number(3),
4 Sname varchar(20) not null,
[code]...
I have a parent table called dept and one child table emp which is referenced on deptno and now both have data . Now i want to truncate the dept table and i have truncated emp table after it when i am trying to truncate dept table then oracle throws a error that is-
"ORA-02266: unique/primary keys in table referenced by enabled foreign keys" ,
but when i tried to delete some records 1 by 1 then these have delete but truncate is not still working. why? if i have 10 millon records.
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
I want to get the list of values which is not the given list .
I have the below data in my emp table
SQL> select emp_id from emp;
EMP_ID
----------
101
102
103
104
105
106
108
110
120
220
225
11 rows selected.
But I need to display the values from the list of values which are not in the emp table . So the result will be '3000,3002,3003'.
SQL> select emp_id from emp where emp_id ?;
EMP_ID
----------
3000
3002
3003
how to i insert a constraint of words into the table example below.I am new to this stuff man.
Create table Orders
(
orderID Number(8) Primary Key,
orderDate Date Not Null,
methPmt Varchar2(10),
custID Number(5),
orderSource Number(2),
Foreign Key(custID) Reference Customer(custID),
Foreign Key(ordersource) Reference OrderSource(ordersource)
);
The catch is I am required to enter a constraint of the methPmt will only take values of "CASH", "CREDIT" or "CHEQUE" only.
How am I suppose to enter this constraint value into the creation of this table?
Can I apply Referential Integrity to only selected rows of a particular column? This is the reference key to the primary key to another table. But, the issue is, this reference column is not having mandatory data for all the rows. So, whenever this is null, I dont want it to be referred by parent table.
View 4 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?
SQL> create table emp_column_level_cons(empno number(4) constraint empcol_empno_pk primary key
2 ename varchar2(15) unique,
3 job varchar2(20) not null,
4 sal number(7,2) check (sal between 1500 and 25000),
5 deptno number(4) constraint empcol_deptno_fk foreign key re
ferences dept(deptno) on delete cascade);
deptno number(4) constraint empcol_deptno_fk foreign key references dept(deptno) on delete c
*
ERROR at line 5:
ORA-02253: constraint specification not allowed here
================================================
I want to know constraint name of a particular table if it exists.Here are the queries that i tried to find the constraint name of a particular table but the results were 'no rows selected'.
Queries:
select constraint_name from user_cons_columns where table_name='x';
select constraint_name from user_constraints where table_name='x';
I have three tables:
A {op_id, op_name, .}
B {b_id, op_id, supplier_id, .}
C {c_id, op_id, op_id2, supplier_id, relation, .}
Table B and C have op_id which is foreign key from table A. In table A op_id is primary key, in table B b_id is primary key and in table C c_id is primary key. In table B supplier_id and op_id may have duplicate records. Now I want to add constraint so that if I delete records from Table B for op_id and if a relationship record exists for op_id in table C then it should not allow me to delete. Is it possible through referential integrity constraint ?
NOVALIDATE CONSTRAINT METHOD.
Below is the step i gone through:
1) CREATE TABLE mutu (id NUMBER, text VARCHAR2(20));
2) INSERT INTO mutu SELECT rownum , 'mutu' FROM dual CONNECT BY LEVEL <= 10000;
3) Insert Duplicate row.
INSERT INTO mutu VALUES (42, 'DUPLICATE');
4) COMMIT;
5)Create a NON-UNIQUE INDEX's CONSTRAINT as below.
ALTER TABLE mutu ADD CONSTRAINT mutu_pk PRIMARY KEY(id)
USING INDEX(CREATE INDEX mutu_pk ON mutu(id)) ENABLE NOVALIDATE;
6) Insert Duplicate row.
INSERT INTO mutu VALUES (42, 'DUPLICATE');
7) ERROR at line 1:
ORA-00001: unique constraint (SYS.ZIGGY_PK) violated
Why it shows the error. Even though I had created a NON-UNIQUE INDEX's Constraint only.
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]....
I have a table say EMP with 2 fields ID and Name, with ID being the primary key. The name of the primary key constraint is ID_PK.Now my scenario is :
1. Rename the table EMP to EMP_TEMP;
2. Create an empty table EMP
3. If (data successfully loaded into EMP)
{
Drop EMP_TEMP;
}
else
{
Drop EMP;
Rename EMP_TEMP to EMP;
}
The problem here for me is the primary key constraint. When I am in 2, I cannot create the primary key constraint again since the old one is still in tact. If I go ahead to drop the original pk constraint and re-create a new one in 2, then in case of else part , I need to get the constraint again while renaming the original table.
I have folliwng table
CREATE TABLE abcd
(
trans_type VARCHAR2(10) nn,
original_tran_no NUMBER,
original_line_item NUMBER(20),
original_trans_dt DATE);
I have to create a constraint when trans_type = 'R'then (original_tran_no and original_trans_dt ) should be not null else can be null able; is the follwing stmt right?
alter table abcd add constraint t1_chk1
check (
( case when trans_type = 'R' then (original_tran_no is not null
and original_trans_dt is not null))