Scripts

Script to set max_extents to unlimited on tablespace

select 'alter tablespace '||tablespace_name||' default storage (maxextents unlimited);'
from dba_tablespaces 
where max_extents <> 2147483645
and tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP')
order by tablespace_name;

Script to get SID, SERIAL# and Unix PID based on an oracle username
Note: change LUSIFER for the username that you’re looking

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
and s.username = 'LUSIFER';

Script to find PID, SID and SERIAL# for a KILLED session

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
and s.status ='KILLED'
/

MetaScript to create Script to kill sessions with KILLED status

select 'alter system kill session '||''''||sid||','||serial#||''''||' immediate;' 
from v$session where status='KILLED'
/

Script to get all the code to clone an username
Note: change LUSIFER for the username that you’re looking

SET LONG 100000 PAGESIZE 0 head off verify off feedback off linesize 132
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
SELECT dbms_metadata.get_ddl('USER','LUFERNAN') FROM dual
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','LUFERNAN') from dual
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','LUFERNAN') from dual;
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','LUFERNAN') from dual;

Script to get the DB size

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Script to get the TBS size (DICT & LOCAL Management)

select df.tablespace_name "Tablespace",
df.totalspace "Total MB",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
order by 5;

Script to get the TBS size (ALL LOCAL Management 11.1+)
Opt 1

select tablespace_name, 
used_space used_blocks, 
(used_space*8192)/(1024*1024) used_mb, 
tablespace_size tablespace_blocks, 
(tablespace_size*8192)/(1024*1024) tablespace_mb,
used_percent
from dba_tablespace_usage_metrics;

Opt 2

select t1.contents,
t0.tablespace_name,
round(t0.tablespace_size*t1.block_size/1024/1024,2) total_mb,
round(t0.used_space*t1.block_size/1024/1024,2) used_mb,
round(t0.used_percent, 2) used_percent
from dba_tablespace_usage_metrics t0,
dba_tablespaces t1
where t0.tablespace_name = t1.tablespace_name
order by 1, 5;

Script to get the details from all the data and temp files

select DECODE(2, 1, 'DATAFile',2, 'TEMPFile') FileType, file_id, tablespace_name, 
file_name, bytes/1024/1024 as MB, status
from dba_temp_files 
union all
select DECODE(1, 1, 'DATAFile',2, 'TEMPFile') FileType, file_id, tablespace_name, 
file_name, bytes/1024/1024 as MB, status
from dba_data_files
order by filetype,file_id,tablespace_name
/

Script to get the details from all backups (full, incremental and archivelog)

col STATUS format a9
col hrs format 999.99

select SESSION_KEY, INPUT_TYPE, STATUS, 
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, 
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, 
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key
/

Script to get the details (I/O Throughput, Size) from backups (Incremental)

col STATUS for a10
COL START_TIME FOR A20
COL END_TIME FOR A20
COL TIME_TAKEN_DISPLAY FOR A10
COL INPUTTHROUGHPUT FOR A10
COL OUTPUTTHROUGHPUT FOR A10
col INPUTSIZE for a10
col OUTPUTSIZE for a10

select INPUT_TYPE AS BACKUPTYPE, STATUS,
INPUT_BYTES_DISPLAY AS INPUTSize,INPUT_BYTES_PER_SEC_DISPLAY AS INPUTThroughput,
OUTPUT_BYTES_DISPLAY AS OUTPUTSize,OUTPUT_BYTES_PER_SEC_DISPLAY AS OUTPUTThroughput,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, 
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, 
TIME_TAKEN_DISPLAY
from V$RMAN_BACKUP_JOB_DETAILS
where INPUT_TYPE like '%INCR%'
order by session_key
/

Script to monitoring RMAN proccess

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0
  AND SOFAR <> TOTALWORK
/

Sentence to enable trace on specific session
Note: you need SID & SERIAL#

execute dbms_system.set_sql_trace_in_session( SID, SERIAL#, true);

Script to get Temporary Usage per Tablespace

SET PAGESIZE 60
SET LINESIZE 300
COL TABLESPACE_SIZE FOR 999,999,999,999
COL ALLOCATED_SPACE FOR 999,999,999,999
COL FREE_SPACE FOR 999,999,999,999
 
SELECT *
FROM   dba_temp_free_space
/

Script to show Temporary Tablespace Sort Usage

SELECT 
   A.tablespace_name tablespace, 
   D.mb_total,
   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM 
   v$sort_segment A,
(
SELECT 
   B.name, 
   C.block_size, 
   SUM (C.bytes) / 1024 / 1024 mb_total
FROM 
   v$tablespace B, 
   v$tempfile C
WHERE 
   B.ts#= C.ts#
GROUP BY 
   B.name, 
   C.block_size
) D
WHERE 
   A.tablespace_name = D.name
GROUP by 
   A.tablespace_name, 
   D.mb_total
/

Script to show Tables with Next Extent greater than Free Space on Tablespace

select distinct a.owner,b.tablespace_name,a.Table_name,a.next_extent NETABLE,d.fs_max_extent
  from dba_tables a, dba_tablespaces b, (select max(bytes) fs_max_extent, tablespace_name
                                           from sys.dba_free_space
                                          group by tablespace_name) d
   where a.tablespace_name = b.tablespace_name
     and d.tablespace_name = b.tablespace_name
     and a.next_extent > d.fs_max_extent
  order by a.owner,b.tablespace_name,a.table_name;
/

Script to show SQL Text
Note: You have to pass the SID

select substr(username,1,10) usr,
       substr(sid,1,5) sid,
       sql_text
  from v$session,v$sqltext
 where sql_address = address and sid in (&sid) order by sid, piece
/

Undo Management
Check the overall status for undo segments

select tablespace_name, 
       status, 
       sum(blocks) * 8192/1024/1024 MB
from dba_undo_extents
group by tablespace_name, status
/

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat
/

Optimal Undo Retention/Needed Undo Size for given Database Activity

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MB]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]",
       (to_number(e.value) * to_number(f.value) * g.undo_block_per_sec)/(1024*1024) "Needed Undo Size [MB]"
  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'
/

To find Percentage Usage of Undo Tablespace which considers Expired Space

SELECT d.tablespace_name, 
            round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+ u.exp_space),2) as max_free_mb, 
            round(((a.bytes - (NVL(f.bytes,0)+ (1024*1024*u.exp_space)))*100/a.maxbytes),2) used_pct
FROM sys.dba_tablespaces d, 
     (select tablespace_name, 
             sum(bytes) bytes, 
             sum(greatest(maxbytes,bytes)) maxbytes 
             from sys.dba_data_files
             group by tablespace_name) a,
     (select tablespace_name, sum(bytes) bytes from sys.dba_free_space group by tablespace_name) f ,
     (select tablespace_name , 
             sum(blocks)*8/(1024) exp_space 
             from dba_undo_extents 
             where status NOT IN ('ACTIVE','UNEXPIRED')
             group by  tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+) 
AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name=u.tablespace_name 
AND d.contents = 'UNDO' 
AND u.tablespace_name = (select UPPER(value) from v$parameter where name = 'undo_tablespace')
/