SQL & PL/SQL :: Use Index To Minimize Lock On Parent Table?
Mar 27, 2013I want to know that what oracle server do to minimize the lock on parent table, when we use Index on foreign key column ?
View 2 Replies
ADVERTISEMENT
Alter Index Shrink Space - Table Lock
Oct 18, 2012alter index test_idx1 shrink space;
I've heard that this statement causes a table lock but cant find any information on this.if it is so, is it a write lock or also a read lock of the table?
PL/SQL :: Index-Organized Table Truncate Vs Lock / Stage / Drop / Recreate?
Apr 24, 2013I ran into an issue in a project where a function is recreating an index-organized table by doing:Table Structure:
CREATE TABLE table_iot(
...)
ORGANIZATION INDEX
OVERFLOW ...;
Recreate Steps:
1) Populate global temporary staging table (gtt) with data
-- where gtt is staging for target index-organized table (iot)
2) Lock the target index-organized table (iot)
3) Copy old iot data to gtt
-- gtt now contains old and new data
4) Create new index-organized table (iot2) from gtt
-- iot2 now contains old and new data
[code]...
Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.The following statement rebuilds the index-organized table admin_docindex:
ALTER TABLE admin_docindex MOVE;
When Index Lock Contention Occurs / How To Know Which Index Is Causing
Aug 27, 2012We have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.
This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.
Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.
PL/SQL :: How To Update Parent And Child Tables While Updating Parent Table
Jun 13, 2012I have a parent table EMPLOYEE which includes columns (sysid, serviceno,employeename...) sysid is Primary key, serviceno is Unique key and I have child table DEPENDENT includes columns (sysid,employee_sysid,name,dob...) here again SYSID is primary key for DEPENDENTS table, employee_sysid is Foreign key of EMPLOYEE table.
Now I want to change SYSID (using sequence) in EMPLOYEE table which need to be update in DEPENDENTS table as well
Note: I have 10000 records in EMPLOYEE table as well as I have 5 more child tables which need to update new SYSID.
Minimize Impact As Users Are Complaining For Performance
Sep 9, 2011in my oracle enterprise manager under " user i/o " .i am having basically four category:
if we rank them out of ten it would be like :
read by other session 2/10
db file scattered read 1/10
direct path read .5/10
db file sequential read 6.5/10
and all these are comming for 2 tables involved for almost all time .some way to handle "read by other session" and " db file sequential read " .
i am rebuilding indexes of these involved table once in 10days and statistics for the these tables are collected every day using "analyze table "xxx" compute statistics."tell me the indepth approach i should take to minimize the impact as users are complaing for performance.
SQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child
Aug 13, 2012We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.
SQL & PL/SQL :: Parent Table Relationship
Feb 6, 2012Would there be any problem / error when we import the dump file (which has parent table) on target database ?
View 11 Replies View RelatedServer Administration :: Use Of LM ( Lock Mode) In V$lock?
Oct 4, 2012we know we can see lock mode held in session can be analysed using LM column in v$lock.But i confused in seeing LM column it all shows in numbers from 0 to 6.
eg
0,'None(0)',
1,'Null(1)',
2,'Row Share(2)',
3,'Row Exclu(3)',
4,'Share(4)',
5,'Share Row Ex(5)',
6,'Exclusive(6)')
SQL & PL/SQL :: Way Of Getting Only Parent And Child Record From Table
Jul 27, 2010i have a table with data as follows:
select genres.* from genres
data is as follows
INTPRODUCTIDVCHGENRENAME INTGENREPAGEIDINTPARENTIDINTGENREID
14430015Biography 157 0100
14430015Classics & Poetry 173 0116
14430015Literature & Anthologies 175 173118
now when i give
select level,genres.* from genres connect by prior INTGENREPAGEID= INTPARENTID
i get
LEVELINTPRODUCTIDVCHGENRENAME INTGENREPAGEIDINTPARENTIDINTGENREID
114430015Biography 157 0100
114430015Classics & Poetry 173 0116
214430015Literature & Anthologies 175 173118
114430015Literature & Anthologies 175 173118
i need to find the parent and child from the table in this case the parent is Classics & Poetry and child is Literature & Anthologies..the way of getting only the parent and child record from this table.
SQL & PL/SQL :: Parent And Child Table - Delete Row
May 18, 2010I have a parent table and child table. I want a row to be deleted from the parent table which is referenced by a child row. Is there a way to achieve this. I dont have permission to re create the table or alter the table using delete cascade option. Is there a way to do it in sql.
SQL> create table t1(a number primary key, b number);
SQL> create table t2(c number, d number references t1(a));
SQL> insert into t1 values(1,2);
SQL> insert into t1 values(2,3);
SQL> insert into t1 values(3,4);
SQL> insert into t2 values(10,3);
SQL> insert into t2 values(20,2);
SQL> delete from t1 where a=2;
delete from t1 where a=2
*
ERROR at line 1:
ORA-02292: integrity constraint (CISBATCH.SYS_C00763501) violated - child
record found
SQL & PL/SQL :: How To Delete Parent Table Record
Jul 10, 2012how to delete Parent table records without affecting to child table dependent records?..
View 5 Replies View RelatedSQL & PL/SQL :: Update Parent Child Table?
May 26, 2011i want to update primary key of 1 master and 2 child table is it possible if i do it simultaniously if not
View 9 Replies View RelatedPL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full
Oct 5, 2013Let's consider such table that all rows fit into single block:
SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin
[code].....
why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2
Performance Tuning :: Local Index Versus Global Index On Partitioned Table
Jun 28, 2011I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies View RelatedSQL & PL/SQL :: Delete Records From Child And Parent Table
Jan 2, 2013I want to delete records from parent table which are less than 2 years. Before deleting records from parent table we have to delete records from child table. How can we delete those records. I don't want to use ON DELETE CASCADE.
MASS_MASTER --parent table.
MASS_CHILD --child table.
The below query is used to delete records from parent table.
DELETE FROM mass_master WHERE last_date<=ADD_MONTHS(sysdate,-24);
The child table MASS_CHILD is not having last_date column. provide me the query to delete same records from child table.
SQL & PL/SQL :: Finding Child And Parent Rows In Table?
Jun 6, 2011I have table emp that contains empno, empname, mgr .what i want is a general procedure that will take empno as input and will give all the child rows and parent for entered empno.
for ex
E
A-->B-->C-->D
F-->G
H
When i will pass d as node it will return c,b,a,e,f,g,h
SQL & PL/SQL :: Multiple Of Same Constraint Keys In Parent Table?
Jun 9, 2011I used
SELECT USER_CONS_COLUMNS.CONSTRAINT_NAME,
COLUMN_NAME,
CONSTRAINT_TYPE,
[Code]...
to create a parent key table sort of thing, but for some reason there are rows that display multiple times
CONSTRAINT_NAME COLUMN_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME DELETE_RU
-------------------- -------------------- --------------- ------------------------------ ---------
SYS_C00135225 PATIENT_STATUS C
SYS_C00135226 BALANCE C
SYS_C00135227 PATIENT_STATUS C
PK_PATIENT PATIENT_NUMBER C
[Code] ..........
is it something to do with the join command
Find Missing Data From Parent Table?
Aug 8, 2013SELECT c.table_name CHILD_TABLE, p.table_name PARENT_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND c.table_name = UPPER('ODS_TSAF_MES_PC');
and output is
child table parent table
ODS_TSAF_MES_PC ODS_TSAF_MES_PCTYP
ODS_TSAF_MES_PC ODS_TSAF_MES_PC
ODS_TSAF_MES_PC ODS_TSAF_MES_PCSTAT
i tried
SELECT A.piecestatus from ods_TSAF_MES_PCSTAT A WHERE NOT EXISTS
(SELECT * FROM ODS_TSAF_MES_PC B WHERE B.piecestatus = A.piecestatus);
and i found one piecestatus values is 'I' but i am not getting where it is related to the table and in which row it is getting affected?
SQL & PL/SQL :: How To Lock Particular Row In A Table
Jul 19, 2012how one can lock a particular row in a table.for example i have a employee table in which 50 records. now i want to lock only 10 records of the employee table.
View 8 Replies View RelatedServer Utilities :: How To Get Parent Table Column Reference
Sep 24, 2013I 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.
Performance Tuning :: Force Index If Table Not Using Index?
Aug 9, 2013How to force an index if the table not using the index?
View 10 Replies View RelatedSQL & PL/SQL :: How To Release Lock On Table
Apr 12, 2010How to Release Lock on table ? ( without killing session )
create table x ( a number);
insert into x(a) values (1);
Lets lock table.
declare
cursor c is select * from x for update;
begin
open c ;
end;
[code]....
SQL & PL/SQL :: Lock Table Manually?
Jul 15, 2010How can one lock a table manually?
View 7 Replies View RelatedXML DB :: How To Select Parent / Child Related Data From XMLTYPE Table
Jan 9, 2013select * from V$VERSION
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
I have loaded into an Oracle table defined as XMLTYPE. I'm okay with PL-SQL and stored procedures, where I will ultimately do this processing once I get a handle on XML DB querying from an XMLTYPE table.
Based on reading the oracle documentation about XML DB, and more importantly, reading dozens of posts to this forum,I have been successful in loading the XML files into a SQL XMLTYPE table and doing simple queries against that table to retrieve some of its data so that I can then insert that data into other target oracle relational tables.
how to select parent/child data from XMLTYPE tables. I am able to follow the forum examples and can replicate the methods shown on the many example XML contents shown on this forum, but not against the XML that I have to process. I am wondering if my struggle is caused by my lack of knowledge, or by ill-formed XML content supplied to me by the educational vendor.The XML content has structured the XML content nodes in such a way that I do not seem to be able to apply the parent/child sql methods.I have been able to use for other XML examples I have tested against.
My XML file shown below represents High School Transcript data, for which I need to be able to parse out into my own oracle relational tables for that student, his personal info, and his course info, etc. i.e., for our example, which courses he has taken for which High School grade levels. The vendor-supplied XML seems to put the Courses and the High School grade level in "parallel nodes," instead of parent/child nodes, so I am struggling to be able to use SQL to differentiate which course the student took in NinthGrade versus TenthGrade.
-- WHat I would like to determine from a select statement:
LASTNAME GradeLevel COURSETITLE
=============================
Smith NinthGrade PHYS ED 101
Smith TenthGrade CALCULUS 201
Smith TenthGrade ZOOLOGY 202
(The data has been simpliied and masked, but is true to the content and is queryable).
select * from V$VERSION
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]....
PL/SQL :: Lock Row In 1 Table While Update Other Tables
Oct 15, 2012I want to read 1 table. If the date is less than today, I want to update 4 other tables. I only want to do this update once a day.
While the 4 other tables are being updated, I want the other web users to pause for the update while this procedure runs.
Is there a better way to do this?
Here is what I have:
CREATE OR REPLACE PROCEDURE TEST_TODAY2 AS
-- to create the table
-- create table test_today(updated_date date);
-- insert into test_today(updated_date) values (sysdate-1);
-- select * from test_today;
cursor daily_update_cur is
select updated_date from test_today
for update of updated_date;
[Code]...
Please use {noformat}{noformat} tags before and after your code as described in the FAQ: {message:id=9360002}.
I've corrected it this time for you.
SQL & PL/SQL :: Can Lock Data In Global Temporary Table
Nov 15, 2011Can we lock data in global temporary table?
View 4 Replies View RelatedLock Child Table By FOR Update Clause?
May 7, 2013I am using the Oracle 10g and I have question related to "for Update" clause.We have the data warehouse db, so no foreign key constraint between parent and child.We process the data files every hour, the condition is If we find the row in parent table then we go and look into child tables and perform insertion (if no corresponding record is present) or updation (if one corresponding record is present) in the child table.
The problem is If I run the two process simultaneously for the same kind of data, and if no record is present in the child table then it create the duplicate in child table.My question is if I use FOR Update clause while selecting the data in parent table will it lock the child table for any insertion or updation?
Ex- We have employee table for employee 1
In my data files I have the row for employee 1, so when I run the select query on employee table I found 1 row.The I look the child table "Salary" as there is no record for emp_id =1 in this table I insert the record for this
Emp_id Salary
1 500
The problem is if both the process run at same time then I get duplicate rows in child table
Emp_id Salary
1 500
1 500
we do not want the duplicate row insertion. Can I lock the child table during first process run
Updating Table In Session - Shared Versus Exclusive Lock
Jan 28, 2013I have a question. If we have two scott sessions. I am updating a table EMP in session
1. It means it is exclusively locked.It cannot be used by session 2. Then can we use select command on table EMP in session
2.? This command should not work according to me. But it is working.
Server Administration :: Reorganize A Table And Index After The Deletion Of Records From Table?
Feb 7, 2012We deleted millions of records from a table.
1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.
2.Will re org table and index improve the database performance ?