who lock the row procedure

CREATE OR REPLACE procedure BASEL2.who_lock_the_row_1
is
v_sid varchar2(200);
v_object_name varchar2(200);
v_roid varchar(200);
v_actnum varchar2(200);
v_sql varchar2(400);
begin

select do.object_name,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) roid
into v_object_name,v_roid
from v$session s, dba_objects do
where sid=(select s2.sid
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 )
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;


v_sql:= 'select actnum from '||v_object_name ||' where rowid='||v_roid||';';

execute immediate v_sql;

dbms_output.put_line(v_actnum);

end;

Database User Profile resource_type = 'PASSWORD'

Database User Profile resource_type = 'PASSWORD'
================================================
syntax:-

CREATE PROFILE profile
LIMIT
{ { FAILED_LOGIN_ATTEMPTS
| PASSWORD_LIFE_TIME
| PASSWORD_REUSE_TIME
| PASSWORD_REUSE_MAX
| PASSWORD_LOCK_TIME
| PASSWORD_GRACE_TIME } { expr | UNLIMITED | DEFAULT }
| PASSWORD_VERIFY_FUNCTION { function | NULL | DEFAULT }
};

FAILED_LOGIN_ATTEMPTS
-------------------
if a user attempts to login more than the specified number of times the account
will be locked. Default is 10 days.

PASSWORD_LIFE_TIME
------------------
number of days the same password can be used unless a grace period is specified.
Default is 108 days.

PASSWORD_REUSE_TIME
------------------
number of days that must pass before the same password can be used again.
Default is unlimited.

PASSWORD_REUSE_MAX
-----------------
number of times a password must be changed before a previous password can be used again.
Default is unlimited.

PASSWORD_LOCK_TIME
-----------------
number of days an account will remain locked after failed login attempts has been triggered.
Default is 1 day.

PASSWORD_GRACE_TIME
-----------------
number of grace days for user to change password.
Default is 7 days.

PASSWORD_VERIFY_FUNCTION
-----------------
allows you to define PL/SQL that can be used for password verification.


===========================Example==============================

SQL> SELECT * FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_type = 'PASSWORD';


SQL> CREATE USER auser IDENTIFIED BY apassword
DEFAULT TABLESPACE dfts
TEMPORARY TABLESPACE tpts
PROFILE aprofile;


SQL> CREATE PROFILE restrictive
LIMIT FAILED_LOGIN_ATTEMPTS 1
PASSWORD_LIFE_TIME 30
PASSWORD_LOCK_TIME 7
PASSWORD_GRACE_TIME 0;


SQL> ALTER USER scott PROFILE restrictive;

SQL> ALTER USER scott PROFILE default;


SQL> ALTER PROFILE default
LIMIT PASSWORD_REUSE_TIME 3
PASSWORD_REUSE_MAX unlimited;

how to collect values from multiple rows into a single, comma delimited string. (using stragg = string aggregation)

Prerequisites Of STRAGG (Tom Kyte's)
==========================================

create or replace type stragg_type as object
(
string varchar2(4000),

static function ODCIAggregateInitialize
( sctx in out stragg_type )
return number ,

member function ODCIAggregateIterate
( self in out stragg_type ,
value in varchar2
) return number ,

member function ODCIAggregateTerminate
( self in stragg_type,
returnvalue out varchar2,
flags in number
) return number ,

member function ODCIAggregateMerge
( self in out stragg_type,
ctx2 in stragg_type
) return number
);
/

create or replace type body stragg_type
is

static function ODCIAggregateInitialize
( sctx in out stragg_type )
return number
is
begin

sctx := stragg_type( null ) ;

return ODCIConst.Success ;

end;

member function ODCIAggregateIterate
( self in out stragg_type ,
value in varchar2
) return number
is
begin

self.string := self.string || ',' || value ;

return ODCIConst.Success;

end;

member function ODCIAggregateTerminate
( self in stragg_type ,
returnvalue out varchar2 ,
flags in number
) return number
is
begin

returnValue := ltrim( self.string, ',' );

return ODCIConst.Success;

end;

member function ODCIAggregateMerge
( self in out stragg_type ,
ctx2 in stragg_type
) return number
is
begin

self.string := self.string || ctx2.string;

return ODCIConst.Success;

end;

end;
/

create or replace function stragg
( input varchar2 )
return varchar2
deterministic
parallel_enable
aggregate using stragg_type
;
/

Data:-
=========
GROUP_KEY VAL
---------- ----------
Group 1 a

Group 2 a
Group 2 b

Group 3 a
Group 3 b

use of stragg
================
select
group_key ,
STRAGG( VAL ) as string
from
t
group by
group_key
;


select
group_key ,
stragg( DISTINCT val ) as string
from
t
group by
group_key
;

How to collect values from multiple rows into a single, comma delimited string (using vm_concat ,stragg)

SQL> select deptno, job, ename from emp order by 1,2;
DEPTNO JOB ENAME
---------- --------- ----------
10 PRESIDENT KING
20 ANALYST FORD
20 CLERK ADAMS
20 MANAGER JONES
30 CLERK JAMES
30 MANAGER BLAKE
30 SALESMAN ALLEN

7 rows selected.

SQL> col names format a80 heading "JOB/EMPLOYEE_NAME LIST"
SQL> select deptno, wm_concat(job||','||ename) names
2 from emp
3 group by deptno
4 order by deptno
5 /
DEPTNO JOB/EMPLOYEE_NAME LIST
---------- --------------------------------------------
10 PRESIDENT,KING
20 ANALYST,FORD,MANAGER,JONES,CLERK,ADAMS
30 CLERK,JAMES,MANAGER,BLAKE,SALESMAN,ALLEN

3 rows selected.

SQL> select deptno,
2 substr(replace(sys_connect_by_path(name,'/'),'/',','),2) names
3 from ( select deptno, job||','||ename name,
4 row_number() over(partition by deptno order by job, ename) rn
5 from emp
6 )
7 where connect_by_isleaf = 1
8 connect by prior deptno = deptno and prior rn = rn-1
9 start with rn = 1
10 /
DEPTNO JOB/EMPLOYEE_NAME LIST
---------- ----------------------------------------------------------------------
10 PRESIDENT,KING
20 ANALYST,FORD,CLERK,ADAMS,MANAGER,JONES
30 CLERK,JAMES,MANAGER,BLAKE,SALESMAN,ALLEN

3 rows selected.

SQL> select deptno,
2 trim (both ',' from
3 replace(
4 replace(
5 max(decode(job,'ANALYST',job))||','||max(decode(job,'ANALYST',ename))||','||
6 max(decode(job,'CLERK',job))||','||max(decode(job,'CLERK',ename))||','||
7 max(decode(job,'MANAGER',job))||','||max(decode(job,'MANAGER',ename))||','||
8 max(decode(job,'PRESIDENT',job))||','||max(decode(job,'PRESIDENT',ename))||','||
9 max(decode(job,'SALESMAN',job))||','||max(decode(job,'SALESMAN',ename)),
10 ',,,',','),
11 ',,',',')
12 ) names
13 from emp
14 group by deptno
15 order by deptno
16 /
DEPTNO JOB/EMPLOYEE_NAME LIST
---------- --------------------------------------------------------------------------------
10 PRESIDENT,KING
20 ANALYST,SCOTT,CLERK,SMITH,MANAGER,JONES
30 CLERK,JAMES,MANAGER,BLAKE,SALESMAN,WARD

3 rows selected.


---------------------------------------------------------------
----another Example - 2

create table temp
(
source_1 varchar2(2),
period varchar2(4),
id_l number
)

insert into temp values ('A','2008',1);
insert into temp values ('A','2008',2);
insert into temp values ('A','2008',3);
insert into temp values ('A','2008',7);
insert into temp values ('B','2008',4);
insert into temp values ('B','2008',5);
insert into temp values ('B','2008',6);


select source_1,period,
rtrim(replace(replace(xmlagg(xmlelement("a",id_l)).getstringval(),'',NULL),'',','),',')
from temp
group by source_1,period


gives the output that you asked for..

A 2008 1,2,3,7
B 2008 4,5,6,8

======================

select deptno,ename,wm_concat(ename||' '||job) over (partition by deptno order by deptno) "ename/job"
from emp

Rows to columns wise query

Oracle 9i xmlagg

In Oracle 9i we can use the xmlagg function to aggregate multiple rows onto one column:

select
deptno,
rtrim (xmlagg (xmlelement (e, ename || ',')).extract ('//text()'), ',') enames
from
emp
group by
deptno
;

DEPTNO ENAMES
---------- ----------------------------------------
10 CLARK,MILLER,KING
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD



Use 11g SQL pivot for single row output


The SQL pivot operator allows you to take multiple rows and display them on a single line.

select *
from
(select fk_department
from employee)
pivot
(count(fk_department)
for fk_department in ('INT', 'WEL', 'CEN', 'POL'));

'INT' 'WEL' 'CEN' 'POL'
---------- ---------- ---------- -------
7 6 0 8


Use SQL within group for moving rows onto one line and listagg to display multiple column values in a single column

In Oracle 11g, we have the within group SQL clause to pivot multiple rows onto a single row. We also a have direct SQL mechanism for non first-normal form SQL display. This allows multiple table column values to be displayed in a single column, using the listagg built-in function :

select
deptno,
listagg (ename, ',')
WITHIN GROUP
(ORDER BY ename) enames
FROM
emp
GROUP BY
deptno
/
DEPTNO ENAMES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD


Use the SYS_CONNECT_BY_PATH operator



select
deptno,
substr(SYS_CONNECT_BY_PATH(lname, ','),2) name_list
from
(
select
lname,
deptno,
count(*) OVER ( partition by deptno ) cnt,
ROW_NUMBER () OVER ( partition by deptno order by lname) seq
from emp
where
deptno is not null)
where
seq=cnt
start with
seq=1
connect by prior
seq+1=seq
and prior
deptno=deptno;

DEPTNO NAME_LIST
1 Komers,Mokrel,Stenko
2 Hung,Tong
3 Hamer
4 Mansur


Use a Cross join


Matt contributed this handy SQL techniques to pivot one row of several columns into a single column with several row, using the Oracle Cross join syntax. Matt notes that the Cross join "has other uses in conjunction with a WHERE clause to create triangular result sets for rolling totals etc (though thanks to analytic functions those things are very nice and easy)".

SELECT
ite,
case
when ite = 'item1' then item1
when ite = 'item2' then item2
when ite = 'item3' then item3
end as val
FROM
(
SELECT
pivoter.ite,
item1,
item2,
item3
FROM
someTable
CROSS JOIN
(
select 'item1' as ite from dual
UNION ALL
select 'item2' as ite from dual
UNION ALL
select 'item3' as ite from dual
)pivoter
)



Use the Oracle analytic Lag-Over Function


Analytic functions have a pronounced performance improvement since they avoid an expensive self-join and only make one full-table scan to get the results. This site shows an example of using the Oracle LAG function to display multiple rows on a single column:

SELECT
ename,
hiredate,sal,LAG (sal, 1, 0)
OVER (ORDER BY hiredate) AS PrevSal
FROM
emp
WHERE
job = 'CLERK';

Use the SQL CASE operator to pivot rows onto one line

You can use the CASE statement to create a crosstab to convert the rows to columns. Below, the Oracle CASE function to create a "crosstab" of the results, such as this example from SearchOracle:

select Sales.ItemKey
, sum(Sales.QtySold) as Qty
, sum(
case when OH.MOHClass = 'Fixed'
then OH.Amt
else .00 end ) as MOHFixed
, sum(
case when OH.MOHClass = 'Var'
then OH.Amt
else .00 end ) as MOHVar
, sum(
case when OH.MOHClass = 'Cap'
then OH.Amt
else .00 end ) as MOHCap
from Sales
left outer
join OH
on Sales.ItemKey = OH.ItemKey
group
by Sales.ItemKey