Frequently Used sql queries

Frequently Used sqls:

1. Active_sessions.sql

SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;

2.inactive_sessions.sql

SET LINESIZE 132
SET PAGESIZE 1000

COLUMN username FORMAT A12
COLUMN s.osuser FORMAT A14
COLUMN logon_time FORMAT A15
column s.sid for 9999999
column p.spid for 9999999

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'INACTIVE'
ORDER BY s.username, s.osuser,logon_time;

3.active_session1.sql

SET LINESIZE 132
SET PAGESIZE 1000

COLUMN username FORMAT A8
COLUMN OSUSER for a8
COLUMN logon_time FORMAT A20
column s.sid for 9999999
column p.spid for 9999999

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser,logon_time;

4.longops_rac.sql

SET LINESIZE 132
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.inst_id,
s.sid,
s.serial#,
s.username,
s.module,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.inst_id = sl.inst_id
AND s.serial# = sl.serial#;

5.top_sqls.sql

-- Description : Displays a list of SQL statements that are using the most resources.

SET LINESIZE 500
SET PAGESIZE 1000
col sql_text for a300
SET VERIFY OFF

SELECT *
FROM (SELECT Substr(a.sql_text,1,500) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= 5;

SET PAGESIZE 14

6.locked_objects.sql

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15

SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

SET PAGESIZE 14
SET VERIFY ON

7.file_io.sql

SET PAGESIZE 1000

SELECT Substr(d.name,1,50) "File Name",
f.phyblkrd "Blocks Read",
f.phyblkwrt "Blocks Writen",
f.phyblkrd + f.phyblkwrt "Total I/O"
FROM v$filestat f,
v$datafile d
WHERE d.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;

SET PAGESIZE 18

8.db_links.sql

SET LINESIZE 132
COLUMN db_link FORMAT A30
COLUMN host FORMAT a20
COLUMN owner FORMAT a10
COLUMN USERNAME FORMAT a10
set pagesize 200


SELECT owner,
db_link,
username,
host
FROM dba_db_links
ORDER BY owner, db_link;

9.db_properties.sql
SET PAGESIZE 200
SET LINESIZE 132
COLUMN property_value FORMAT A30
COLUMN property_name FORMAT A40


SELECT property_name, property_value FROM database_properties ORDER BY property_name;

10.dispatchers.sql

SET LINESIZE 500
SET PAGESIZE 1000
COLUMN a.status for a8
SET VERIFY OFF

SELECT a.name "Name",
a.status "Status",
a.bytes "Total Bytes",
a.idle "Total Idle Time",
a.busy "Total Busy Time",
Round(a.busy/(a.busy + a.idle),2) "Load"
FROM v$dispatcher a ORDER BY 1;

SET PAGESIZE 14
SET VERIFY ON

11. tablespace_monitoring.sql

select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
group by tablespace_name) b
Where a.tbl=b.tblsp
order by 4;

12. highly_accessed_tables.sql

select c.owner, c.name name, t.buffer_pool, c.executions, c.loads, s.bytes/1024kb,t.tablespace_name tablespace
from dba_tables t,
dba_segments s,
v$db_object_cache c
where c.type = 'TABLE'
and c.loads > 100
and c.executions >100
and c.name = t.table_name
and c.owner = t.owner
and c.name = s.segment_name
and c.owner = s.owner;

13. invalid_objects.sql

col owner for a10
set pagesize 100
set linesize 132

COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

14. sid_sqltext.sql

set linesize 132
set pagesize 200
col status for a10
col schemaname for a12
col OSUSER for a10
col PROGRAM for a18


SELECT s.sid,
s.status,
s.process,
s.schemaname,
s.osuser,
p.program
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR;

set pagesize 200
set linesize 132
SELECT s.sid,a.sql_text,p.program
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR;

col username for a15
col MACHINE for a30
col status for a10
set linesize 132

select username,MACHINE,count(1),status from v$session group by username,machine,status order by 3;
select username,MACHINE,count(1) from v$session group by username,machine orderby 3;