— In Flight SQL
SELECT nvl(ses.username,’ORACLE PROC’)||’ (‘||ses.sid||’)’ USERNAME,
SID,
MACHINE,
REPLACE(SQL.SQL_TEXT,CHR(10),”) STMT,
ltrim(to_char(floor(SES.LAST_CALL_ET/3600), ’09’)) || ‘:’
|| ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), ’09’)) || ‘:’
|| ltrim(to_char(mod(SES.LAST_CALL_ET, 60), ’09’)) RUNT
FROM V$SESSION SES,
V$SQLtext_with_newlines SQL
where SES.STATUS = ‘ACTIVE’
and SES.USERNAME is not null
and SES.SQL_ADDRESS = SQL.ADDRESS
and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
and Ses.AUDSID <> userenv(‘SESSIONID’)
order by runt desc, 1,sql.piece;
Tag Archives: SQL
SQL | ORACLE | How-To | Snippets
SQL | ORACLE 10g+ |
To get the next N months (text) from a start Date.Here the Next 12 months given a start_date of 08/2013
-
select add_months( start_date, level-1 ) as monthName from (select to_date(’08/2013′,’mm-yyyy’) start_date, ADD_MONTHS(to_date(’08/2013′, ‘mm-yyyy’), 12) end_date from dual) connect by level <= months_between(trunc(end_date,’MM’), trunc(start_date,’MM’) ) * + 1;
To detect which tables are referencing a table via FKs
select table_name, constraint_name, status, owner
from all_constraints
where r_owner = :r_owner
and constraint_type = ‘R’
and r_constraint_name in
(
select constraint_name from all_constraints
where constraint_type in (‘P’, ‘U’)
and table_name = :r_table_name
and owner = :r_owner
)
order by table_name, constraint_name;
from all_constraints
where r_owner = :r_owner
and constraint_type = ‘R’
and r_constraint_name in
(
select constraint_name from all_constraints
where constraint_type in (‘P’, ‘U’)
and table_name = :r_table_name
and owner = :r_owner
)
order by table_name, constraint_name;
To detect and remove duplicates via self-join
SELF JOIN TO DETECT AND REMOVE DUPLICATES
select
FIELD_A,
FIELD_B,
FIELD_C
from
MY_TABLE a
where
rowid >
(select min(rowid) from MY_TABLE b
where
b.FIELD_A = a.FIELD_A
and
b.FIELD_B = a.FIELD_B
and
b.FIELD_C = a.FIELD_C
);
Techno Super Scopes 