Estimate Tablespace Growth

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;

Locks DML

-----------------------------Performance - Locks DML----------------------------------------------------------

----------Show sessions that are blocking each other-------------------------------------

select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
/


----------------------Show locked objects-------------------------------

set lines 100 pages 999
col username format a20
col sess_id format a10
col object format a25
col mode_held format a10
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id
/


----------------------------Show which row is locked-------------------


select do.object_name
, row_wait_obj#
, row_wait_file#
, row_wait_block#
, row_wait_row#
, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from v$session s
, dba_objects do
where sid=&sid
and s.ROW_WAIT_OBJ# = do.OBJECT_ID
/



---------Then select the row with that rowid...

select * from table where rowid=;


-----------------List locks--------------------------------

column lock_type format a12
column mode_held format a10
column mode_requested format a10
column blocking_others format a20
column username format a10
SELECT session_id
, lock_type
, mode_held
, mode_requested
, blocking_others
, lock_id1
FROM dba_lock l
WHERE lock_type NOT IN ('Media Recovery', 'Redo Thread')
/

Locks - DDL

--------------------------------------------------Performance - Locks DDL---------------------------------------------------


-----------------------------Show all ddl locks in the system---------------------------------------


select decode(lob.kglobtyp,
0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT','UNDEFINED'
) object_type
, lob.kglnaobj object_name
, pn.kglpnmod lock_mode_held
, pn.kglpnreq lock_mode_requested
, ses.sid
, ses.serial#
, ses.username
from v$session_wait vsw
, x$kglob lob
, x$kglpn pn
, v$session ses
where vsw.event = 'library cache lock'
and vsw.p1raw = lob.kglhdadr
and lob.kglhdadr = pn.kglpnhdl
and pn.kglpnmod != 0
and pn.kglpnuse = ses.saddr
/



--------------------------------Slightly more simple version of the above------------------------


select ses.username
, ddl.session_id
, ses.serial#
, owner || '.' || ddl.name object
, ddl.type
, ddl.mode_held
from dba_ddl_locks ddl
, v$session ses
where owner like '%userid%'
and ddl.session_id = ses.sid
/


--------------------Generate kill statement for ddl locking sessions------------------------------



select 'alter system kill session ''' || ddl.session_id || ',' || ses.serial# || ''' immediate;'
from dba_ddl_locks ddl
, v$session ses
where owner like '%userid%'
and ddl.session_id = ses.sid
/

Locked sessions and locked objects

###############PROMPT Blocked and Blocker Sessions################3

col blocker_sid format 99999999999
col blocked_sid format 99999999999
col min_blocked format 99999999999
col request format 9999999
select /*+ ORDERED */
blocker.sid blocker_sid
, blocked.sid blocked_sid
, TRUNC(blocked.ctime/60) min_blocked
, blocked.request
from (select *
from v$lock
where block != 0
and type = 'TX') blocker
, v$lock blocked
where
blocked.type='TX'
and blocked.block = 0
and blocked.id1 = blocker.id1;

prompt blocked objects from V$LOCK and SYS.OBJ$

set lines 132
col BLOCKED_OBJ format a35 trunc
select /*+ ORDERED */
l.sid
, l.lmode
, TRUNC(l.ctime/60) min_blocked
, u.name||'.'||o.NAME blocked_obj
from (select *
from v$lock
where type='TM'
and sid in (select sid
from v$lock
where block!=0)) l
, sys.obj$ o
, sys.user$ u
where o.obj# = l.ID1
and o.OWNER# = u.user#
/

prompt blocked sessions from V$LOCK

select /*+ ORDERED */
blocker.sid blocker_sid
, blocked.sid blocked_sid
, TRUNC(blocked.ctime/60) min_blocked
, blocked.request
from (select *
from v$lock
where block != 0
and type = 'TX') blocker
, v$lock blocked
where blocked.type='TX'
and blocked.block = 0
and blocked.id1 = blocker.id1
/

prompt blokers session details from V$SESSION

set lines 132
col username format a10 trunc
col osuser format a12 trunc
col machine format a15 trunc
col process format a15 trunc
col action format a50 trunc
SELECT sid
, serial#
, username
, osuser
, machine
FROM v$session
WHERE sid IN (select sid
from v$lock
where block != 0
and type = 'TX')
/

Script – Check RMAN Backup Status

------------Script – Check RMAN Backup Status

--------------Scripts to check backup status and timings of database backups -

#############This script will be run in the database, not the catalog.

-----Login as sysdba -

---------This script will report on all backups – full, incremental and archivelog backups -

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;



--------This script will report all on full and incremental backups, not archivelog backups -

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
where input_type='DB INCR'
order by session_key;

Estimate tablespace growth

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;