User idle time

SELECT sid, osuser, username, status,
TO_CHAR(logon_time, ‘DAY HH24:MI:SS’) LOGON_TIME,
FLOOR(last_call_et/3600)||’:’||
FLOOR(MOD(last_call_et,3600)/60)||’:’||
MOD(MOD(last_call_et,3600),60) IDLE, program
FROM v_$session
WHERE username IS NOT NULL
ORDER BY last_call_et;

Temp space usage per session

–how who is using all sort ( TEMP ) space. See showTempUsers.sql
set pagesize 10000
set linesize 133
col tablespace format a15 heading ‘Tablespace Name’
col segfile# format 9,999 heading ‘File|ID’
col segblk# format 999,999,999 heading ‘Block|ID’
col blocks format 999,999,999 heading ‘Blocks’
col username format a15
select b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,a.username,a.osuser,a.status
from V$SESSION a,V$SORT_USAGE b where a.saddr = b.session_addr order by b.tablespace,b.segfile#,b.segblk#,b.blocks;

Optimal undo sizing

/*
Note: Only run this query after the database has
been running for a significant/representative
period of time.

Note2: This script is for when space is no issue and
you want to determine the optimal size based
on database activity and your desired
undo retention time.

Note3: If “NEEDED UNDO SIZE” is less than “ACTUAL UNDO SIZE”
then this is generally and indication of wasted space.
*/

set linesize 150
column “UNDO RETENTION (Secs)” format a25

select
d.undo_size/(1024*1024) “ACTUAL UNDO SIZE (MEGS)”,
substr(e.value,1,25) “UNDO RETENTION (Secs)”,
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) “NEEDED UNDO SIZE (MEGS)”
from
(
select
sum(a.bytes) undo_size
from
v$datafile a,
v$tablespace b,
dba_tablespaces c
where
c.contents = ‘UNDO’ and
c.status = ‘ONLINE’ and
b.name = c.tablespace_name and
a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
select
max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
from
v$undostat
) g
where
e.name = ‘undo_retention’ and
f.name = ‘db_block_size’;

Temp space usage per session and per sql statement

– Temp segment usage per session.

SQL> SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;

2. Identify temp segment usages per statement

– Temp segment usage per statement.

SQL> SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY mb_used;

Temporary space usage

set pagesize 10000
set linesize 135

column tablespace format a15 heading ‘TABLESPACE NAME’
column segfile# format 9,999 heading ‘FILE|ID’
column spid format 9,999 heading ‘UNIX|ID’
column segblk# format 999,999,999 heading ‘BLOCK|ID’
column size_mb format 999,999,990.00 heading “MB|USED”
column username format a15
column osuser format a10
column program format a15

SELECT
b.tablespace,
b.segfile#,
b.segblk#,
round(((b.blocks*p.value)/1024/1024),2) size_mb,
a.sid,
a.serial#,
a.username,
a.osuser,
a.program,
a.status
FROM
v$session a,
v$sort_usage b,
v$process c,
v$parameter p
WHERE
p.name=’db_block_size’ AND
a.saddr = b.session_addr AND
a.paddr=c.addr
ORDER BY
b.tablespace,
b.segfile#,
b.segblk#,
b.blocks;

Roles and Privileges

set linesize 132
set verify off
— set feedback off
set pagesize 60

col grantee for a20 wrap
col owner for a20 wrap
col table_name for a30 wrap
col column_name for a30 wrap
col privilege for a30 wrap
col granted_rol for a20 wrap
col grantable for a10 wrap heading ‘WITH GRANT’
col admin_option for a10 wrap heading ‘WITH ADMIN’

accept grantee_nm prompt ‘User or Role Name > ‘

prompt
prompt Object Privileges for &&grantee_nm….

select grantee,owner,table_name,privilege,grantable from sys.DBA_TAB_PRIVS where grantee=upper(‘&&grantee_nm’) order by 2, 3, 1, 4;

prompt
prompt Column privileges for &&grantee_nm….

select grantee,owner,table_name,column_name,privilege,grantable from sys.DBA_COL_PRIVS where grantee=upper(‘&&grantee_nm’) order by 2, 3,
4, 5, 1;

prompt
prompt System privileges for &&grantee_nm….

select grantee,privilege,admin_option from sys.DBA_SYS_PRIVS where grantee=upper(‘&&grantee_nm’) order by 1, 2;

prompt
prompt Role privileges for &&grantee_nm….

select grantee,granted_role,admin_option from sys.DBA_ROLE_PRIVS where grantee=upper(‘&&grantee_nm’) order by 1, 2;

Segments that cannot extend or have reached max extents

clear buffer
clear columns
clear breaks
set verify off
set pause off
set timing off
set pagesize 200
set linesize 80

Column Segment_Name Format A30 Heading “Segment”
Column Owner Format A30 Heading “Owner”
Column Segment_Type Format A20 Heading “Type”
Column Tablespace_Name Format A30 Heading “Tablespace”
Column Nxt_Ext Format A10 Heading ” Next| Extent”

spool not_extd.lis

Select TableSpace_Name, Owner, Segment_Name, Segment_Type,
To_Char((Next_Extent * (1 + (Decode(Extents, 1, 0,
A.Pct_Increase) / 100))/1024), ‘999,999’) || ‘K’ Nxt_Ext
From Sys.DBA_Segments A
Where Not Exists (Select ‘x’
From Sys.DBA_Free_Space B
Where A.TableSpace_Name = B.TableSpace_Name
and B.Bytes >= (A.Next_Extent *
(1 + (Decode(Extents, 1, 0,
A.Pct_Increase) / 100))))
Order By TableSpace_Name, Segment_Name;
spool off;
/