Friday, November 25, 2011

who is locking whom oracle locks

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' )
' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

Thursday, November 17, 2011

Oracle alter table add or drop column

alter table supplier drop column supplier_id;

alter table
cust_table
add
cust_sex varchar2(1) NOT NULL;

Her is an example of Oracle "alter table" syntax to add multiple data columns.

ALTER TABLE
cust_table
ADD
(
cust_sex char(1) NOT NULL,
cust_credit_rating number
);

Oracle add or drop primary key

ALTER TABLE supplier
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

ALTER TABLE supplier
drop CONSTRAINT supplier_pk;

Blog Archive