set pages 200
set lines 200
col PROGRAM for a28
col USERNAME for a18
col OSUSER for a12
col sql_text for a70
select distinct s.sid, s.serial#, osuser, program, username, program_id, s.sql_id, substr(q.sql_Text,1,50) sql_text
from v$session s, v$sql q
where status='ACTIVE'
and s.sql_id=q.sql_id
/Active SQL running
@?/rdbms/admin/awrrpt.sqlRun AWR Report
@?/rdbms/admin/addmrpt.sqlRun ADDM Report
@?/rdbms/admin/ashrpt.sql
Run ASH Report
— Enter value for begin_time: Format is MM/DD/YY HH24:SS
— Enter value for duration: <number in Minutes>
col force_logging for a14
col name for a32
col value for a40
col "alertlog dir" for a80
set lines 220
select name, log_mode, open_mode, protection_mode, force_logging, flashback_on, current_scn, db_unique_name
from v$database;
select name services from v$services where name not like 'SYS$%' order by 1;
show con_name
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') current_time from dual;
show user
select count(*) gv$database_count from gv$database;
select name, value from v$parameter
where name in ('log_archive_dest_2','cluster_database');
select value "alertlog dir" from v$diag_info where NAME='Diag Trace';
select sid MY_SID, serial# MY_SERIAL from v$session where sid=(select distinct sid from v$mystat);
Database info on logon to confirm environment
select dbms_metadata.get_ddl('USER','&USER') ddl from dual;Capture password Hash
select value from v$parameter where name = 'db_block_size';
select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0;Shrink DataFile to High Water Mark
Get archivelog locations:
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
col name for a70
set lines 220
select first_time, completion_time, name from gv$archived_log
where name is not null;
FIRST_TIME COMPLETION_TIME NAME
-------------------- -------------------- ----------------------------------------------------------------------
15-DEC-2023 16:00:31 15-DEC-2023 16:07:44 +FRA/TEST2A/ARCHIVELOG/2023_12_15/thread_1_seq_31.554.1155658065
15-DEC-2023 16:07:44 15-DEC-2023 16:07:45 +FRA/TEST2A/ARCHIVELOG/2023_12_15/thread_1_seq_32.591.1155658065
15-DEC-2023 16:07:45 15-DEC-2023 16:07:47 +FRA/TEST2A/ARCHIVELOG/2023_12_15/thread_1_seq_33.2210.1155658067
15-DEC-2023 16:07:47 15-DEC-2023 16:07:47 +FRA/TEST2A/ARCHIVELOG/2023_12_15/thread_1_seq_34.616.1155658067
15-DEC-2023 16:07:47 15-DEC-2023 16:07:48 +FRA/TEST2A/ARCHIVELOG/2023_12_15/thread_1_seq_35.436.1155658069
-- Add first archivelog file
exec DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+FRA/TEST2A/ARCHIVELOG/2023_12_15/thread_1_seq_31.554.1155658065', OPTIONS => DBMS_LOGMNR.NEW);
-- Add additional archivelogs
exec DBMS_LOGMNR.add_logfile (options => DBMS_LOGMNR.addfile, logfilename => '+FRA/TEST2A/ARCHIVELOG/2023_12_15/thread_1_seq_32.591.1155658065');
exec DBMS_LOGMNR.add_logfile (options => DBMS_LOGMNR.addfile, logfilename => '+FRA/TEST2A/ARCHIVELOG/2023_12_15/thread_1_seq_33.2210.1155658067');
exec DBMS_LOGMNR.add_logfile (options => DBMS_LOGMNR.addfile, logfilename => '+FRA/TEST2A/ARCHIVELOG/2023_12_15/thread_1_seq_34.616.1155658067');
exec DBMS_LOGMNR.add_logfile (options => DBMS_LOGMNR.addfile, logfilename => '+FRA/TEST2A/ARCHIVELOG/2023_12_15/thread_1_seq_35.436.1155658069');
-- Start logminer with online dictionary
exec DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + dbms_logmnr.committed_data_only);
-- Create a table to query after
create table logminer as select * from v$logmnr_contents;
-- or query directly
col seg_name for a12
col table_name for a12
col seg_owner for a12
col operation for a12
col sql_redo for a100
set lines 280
set pages 50
select TIMESTAMP, OPERATION, SEG_OWNER, SEG_NAME,
TABLE_NAME, SEG_TYPE, SQL_REDO
from v$logmnr_contents
where seg_owner='PSAMMY';Using Logminer Example
SELECT COUNT(*)
FROM dba_tab_privs AS OF TIMESTAMP TO_TIMESTAMP('2023-12-18 08:00:00', 'YYYY-MM-DD HH24:MI:SS');
Flashback query example
col owner for a16
col db_link for a32
col username for a16
col host for a120
set lines 220
set pages 100
select * from dba_db_links;
Find all db links in database
SET PAGESIZE 90
SET LINESIZE 150
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM <8;Log switches per Hour over 1 week
create table test_rename(first_name varchar2(128));
rename test_rename to test_rename_v2;
Rename a Table
col directory_name for a40
col directory_path for a80
set lines 220
select directory_name, directory_path from all_directories order by 1;
expdp psammy/oracle directory=DATA_PUMP_DIR tables=psammy.test_rename, psammy.test_rename_v2 \
dumpfile=test_rename.dmp logfile=exp_test_rename.log
Datapump export of 2 tables example
rename test_rename to test_rename_pre;
rename test_rename_v2 to test_rename_pre_v2;
-- or drop them or can use TABLE_EXISTS_ACTION of impdp to replace or truncate
impdp psammy/oracle directory=DATA_PUMP_DIR tables=psammy.test_rename, psammy.test_rename_v2 \
dumpfile=test_rename.dmp logfile=imp_test_rename.log
Datapump import of 2 tables example
col "Session Details" for a40
select 'HOST : '||SYS_CONTEXT ('USERENV', 'HOST') as "Session Details" from dual
union all
select 'DB_NAME : '||SYS_CONTEXT ('USERENV', 'DB_NAME') as "Session Details" from dual
union all
select 'Username : '||SYS_CONTEXT ('USERENV', 'SESSION_USER') USERNAME from dual
union all
select 'Time : '||to_char(sysdate,'DD-Mon-YY hh12:mi:ss am') from dual;
Capture details of logged on user who has minimal permissions
col BEGIN_INTERVAL_TIME for a32
set lines 200
set pages 1000
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&sql_id'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
SQL I believe from K Osbourne to view SQL ID run history
set pages 10000
set lines 132
col event_name for a32
col snapshot_time for a32
col avg_ms for 9999999.99
select
event_name,
btime snapshot_time,
round((time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0),1) avg_ms
from (
select
s.dbid,e.event_name,
to_char(s.BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24:MI') btime,
total_waits count_end,
time_waited_micro/1000 time_ms_end,
Lag (e.time_waited_micro/1000)
OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg,
Lag (e.total_waits)
OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg
from
DBA_HIST_SYSTEM_EVENT e,
DBA_HIST_SNAPSHOT s
where
s.snap_id=e.snap_id
and e.event_name in ('log file parallel write'
)
and s.dbid=e.dbid
)
order by btime asc
/
Redo Log Response Time to determine io latencies
col TO_CHAR(S.LOGON_TIME,'DD-MON-YYYYHH24:MI:SS') for a32
SET LINESIZE 200
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS'),
s.status,
s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where s.username='SYS'
and s.program like '%rman%'
/RMAN session check
set lines 132
col COLUMN_NAME for a32
select TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION from dba_ind_columns
where table_name='&table_name'
order by TABLE_NAME, INDEX_NAME, COLUMN_POSITION, COLUMN_NAME
/
Indexes for a Table
set lines 132
set pages 105
set pause off
set echo off
set feedb on
column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00
select
a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from
(select
tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from
dba_data_files
group by
tablespace_name ) a,
(select
tablespace_name,
sum(bytes) tot_used
from
dba_segments
group by
tablespace_name ) b
where
a.tablespace_name = b.tablespace_name (+)
and
a.tablespace_name not in
(select distinct
tablespace_name
from
dba_temp_files)
and
a.tablespace_name not like 'UNDO%'
order by 1;
Tablespace Space Report
col object_name for a32
set lines 132
set pages 1000
with tab_growth as
(SELECT ob.owner, ob.object_name
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
WHERE object_type in ('TABLE')
and object_id=obj#
and owner like '&owner'
and rowcnt>0
and object_name not like '&object'
and rowcnt>100000
group by ob.owner, ob.object_name
having (max(rowcnt)/min(rowcnt)>1.25) and (max(rowcnt)-min(rowcnt)>100000)
order by max(rowcnt))
SELECT distinct ob.owner, ob.object_name,to_date(analyzetime,'DD-MON-YYYY') analyzetime, rowcnt
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob, tab_growth tg
WHERE ob.owner=tg.owner
and ob.object_name=tg.object_name
and ob.object_type in ('TABLE')
and ob.object_id=obj#
order by ob.owner, ob.object_name, analyzetime
/
Table Growth Check
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
Unix PID for session
SET SERVEROUTPUT ON
-- Tuning task created for specific a statement from the cursor cache.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'rty3wvdgd39qjsf8n',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1000,
task_name => 'rty3wvdgd39qjsf8n_tuning_task',
description => 'Tuning task for statement rty3wvdgd39qjsf8n');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'rty3wvdgd39qjsf8n_tuning_task');
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('rty3wvdgd39qjsf8n_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24
exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'rty3wvdgd39qjsf8n_tuning_task');SQL Tuning Task Example
SELECT b.inst_id,LPAD('--->',DECODE(A.request,0,0,5))||A.SID SID, a.id1, a.id2, a.lmode, a.BLOCK, a.request,
DECODE(a.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table', a.TYPE) lock_type,
b.PROGRAM,b.OSUSER ,b.USERNAME,b.status, b.module,b.action ,b.LOGON_TIME,b.LAST_CALL_ET,
'alter system kill session ' || '''' || a.SID || ', ' || b.serial# ||'''' || ' immediate;' kill_session,
DECODE(object_type, NULL, NULL, 'Dbms_Rowid.rowid_create(1, ' || row_wait_obj# || ', '
|| row_wait_file# ||', ' || row_wait_block#||', ' || row_wait_row# ||')') row_id
FROM gV$LOCK a, gv$session b, dba_objects o
WHERE (a.id1,a.id2) IN (SELECT id1,id2 FROM gV$LOCK WHERE lmode=0)
AND a.INST_ID=b.INST_ID
AND a.SID=b.SID
AND o.object_id (+) = DECODE(b.ROW_WAIT_OBJ#, -1, NULL, b.ROW_WAIT_OBJ#)
ORDER BY a.id1,a.id2,a.request;
SELECT s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
AND l1.BLOCK=1 AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2
/
select sid, BLOCKER_SID from v$session_blockers
/
select * from dba_blockers
/Blocking Sessions Kill
set lines 200
col OS_USERNAME for a20
col OBJ_NAME for a32
col time for a24
set pages 10000
spool audit_check.log
select OS_USERNAME, USERNAME, OWNER, OBJ_NAME, ACTION_NAME,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') TIME from dba_audit_trail where TIMESTAMP > (sysdate -1)
and username not in ('DBSNMP')
and timestamp>to_date('30-MAR-2015 19:00:00','DD-MON-YYYY HH24:MI:SS')
and timestamp<to_date('30-MAR-2015 20:00:00','DD-MON-YYYY HH24:MI:SS')
order by TIMESTAMP
/
Query Audit trail based on time condition
SET trimspool ON
ttitle off
SET linesize 155
SET pagesize 60
column osuser heading 'OS|Username' format a7 truncate
column process heading 'OS|Process' format a7 truncate
column machine heading 'OS|Machine' format a10 truncate
column program heading 'OS|Program' format a25 truncate
column object heading 'Database|Object' format a25 truncate
column lock_type heading 'Lock|Type' format a4 truncate
column mode_held heading 'Mode|Held' format a15 truncate
column mode_requested heading 'Mode|Requested' format a10 truncate
column sid heading 'SID' format 999
column username heading 'Oracle|Username' format a7 truncate
column image heading 'Active Image' format a20 truncate
column sid format 99999
col waiting_session head 'WAITER' format 9999
col holding_session head 'BLOCKER' format 9999
SELECT /*+ ordered */
--b.kaddr,
c.sid,
lock_waiter.waiting_session,
lock_blocker.holding_session,
c.program,
c.osuser,
c.machine,
c.process,
DECODE(u.name,
NULL,'',
u.name||'.'||o.name
) object,
c.username,
DECODE
(
b.TYPE,
'BL', 'Buffer hash table instance lock',
'CF', 'Control file schema global enqueue lock',
'CI', 'Cross-instance function invocation instance lock',
'CU', 'Cursor bind lock',
'DF', 'Data file instance lock',
'DL', 'direct loader parallel index create lock',
'DM', 'Mount/startup db primary/secondary instance lock',
'DR', 'Distributed recovery process lock',
'DX', 'Distributed transaction entry lock',
'FS', 'File set lock',
'IN', 'Instance number lock',
'IR', 'Instance recovery serialization global enqueue lock',
'IS', 'Instance state lock',
'IV', 'Library cache invalidation instance lock',
'JQ', 'Job queue lock',
'KK', 'Thread kick lock',
'LA','Library cache lock instance lock (A..P=namespace);',
'LB','Library cache lock instance lock (A..P=namespace);',
'LC','Library cache lock instance lock (A..P=namespace);',
'LD','Library cache lock instance lock (A..P=namespace);',
'LE','Library cache lock instance lock (A..P=namespace);',
'LF','Library cache lock instance lock (A..P=namespace);',
'LG','Library cache lock instance lock (A..P=namespace);',
'LH','Library cache lock instance lock (A..P=namespace);',
'LI','Library cache lock instance lock (A..P=namespace);',
'LJ','Library cache lock instance lock (A..P=namespace);',
'LK','Library cache lock instance lock (A..P=namespace);',
'LL','Library cache lock instance lock (A..P=namespace);',
'LM','Library cache lock instance lock (A..P=namespace);',
'LN','Library cache lock instance lock (A..P=namespace);',
'LO','Library cache lock instance lock (A..P=namespace);',
'LP','Library cache lock instance lock (A..P=namespace);',
'MM', 'Mount definition global enqueue lock',
'MR', 'Media recovery lock',
'NA', 'Library cache pin instance lock (A..Z=namespace)',
'NB', 'Library cache pin instance lock (A..Z=namespace)',
'NC', 'Library cache pin instance lock (A..Z=namespace)',
'ND', 'Library cache pin instance lock (A..Z=namespace)',
'NE', 'Library cache pin instance lock (A..Z=namespace)',
'NF', 'Library cache pin instance lock (A..Z=namespace)',
'NG', 'Library cache pin instance lock (A..Z=namespace)',
'NH', 'Library cache pin instance lock (A..Z=namespace)',
'NI', 'Library cache pin instance lock (A..Z=namespace)',
'NJ', 'Library cache pin instance lock (A..Z=namespace)',
'NK', 'Library cache pin instance lock (A..Z=namespace)',
'NL', 'Library cache pin instance lock (A..Z=namespace)',
'NM', 'Library cache pin instance lock (A..Z=namespace)',
'NN', 'Library cache pin instance lock (A..Z=namespace)',
'NO', 'Library cache pin instance lock (A..Z=namespace)',
'NP', 'Library cache pin instance lock (A..Z=namespace)',
'NQ', 'Library cache pin instance lock (A..Z=namespace)',
'NR', 'Library cache pin instance lock (A..Z=namespace)',
'NS', 'Library cache pin instance lock (A..Z=namespace)',
'NT', 'Library cache pin instance lock (A..Z=namespace)',
'NU', 'Library cache pin instance lock (A..Z=namespace)',
'NV', 'Library cache pin instance lock (A..Z=namespace)',
'NW', 'Library cache pin instance lock (A..Z=namespace)',
'NX', 'Library cache pin instance lock (A..Z=namespace)',
'NY', 'Library cache pin instance lock (A..Z=namespace)',
'NZ', 'Library cache pin instance lock (A..Z=namespace)',
'PF', 'Password File lock',
'PI', 'Parallel operation locks',
'PS', 'Parallel operation locks',
'PR', 'Process startup lock',
'QA','Row cache instance lock (A..Z=cache)',
'QB','Row cache instance lock (A..Z=cache)',
'QC','Row cache instance lock (A..Z=cache)',
'QD','Row cache instance lock (A..Z=cache)',
'QE','Row cache instance lock (A..Z=cache)',
'QF','Row cache instance lock (A..Z=cache)',
'QG','Row cache instance lock (A..Z=cache)',
'QH','Row cache instance lock (A..Z=cache)',
'QI','Row cache instance lock (A..Z=cache)',
'QJ','Row cache instance lock (A..Z=cache)',
'QK','Row cache instance lock (A..Z=cache)',
'QL','Row cache instance lock (A..Z=cache)',
'QM','Row cache instance lock (A..Z=cache)',
'QN','Row cache instance lock (A..Z=cache)',
'QP','Row cache instance lock (A..Z=cache)',
'QQ','Row cache instance lock (A..Z=cache)',
'QR','Row cache instance lock (A..Z=cache)',
'QS','Row cache instance lock (A..Z=cache)',
'QT','Row cache instance lock (A..Z=cache)',
'QU','Row cache instance lock (A..Z=cache)',
'QV','Row cache instance lock (A..Z=cache)',
'QW','Row cache instance lock (A..Z=cache)',
'QX','Row cache instance lock (A..Z=cache)',
'QY','Row cache instance lock (A..Z=cache)',
'QZ','Row cache instance lock (A..Z=cache)',
'RT', 'Redo thread global enqueue lock',
'SC', 'System commit number instance lock',
'SM', 'SMON lock',
'SN', 'Sequence number instance lock',
'SQ', 'Sequence number enqueue lock',
'SS', 'Sort segment locks',
'ST', 'Space transaction enqueue lock',
'SV', 'Sequence number value lock',
'TA', 'Generic enqueue lock',
'TS', 'Temporary segment enqueue lock (ID2=0)',
'TS', 'New block allocation enqueue lock (ID2=1)',
'TT', 'Temporary table enqueue lock',
'UN', 'User name lock',
'US', 'Undo segment DDL lock',
'WL', 'Being-written redo log instance lock',
b.TYPE
) lock_type,
DECODE
(
b.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SRX)', /* C */
6, 'Exclusive', /* X */
TO_CHAR(b.lmode)
) mode_held,
DECODE
(
b.request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
TO_CHAR(b.request)
) mode_requested
FROM
v$lock b
,v$session c
,sys.USER$ u
,sys.obj$ o
,( SELECT * FROM sys.dba_waiters) lock_blocker
,( SELECT * FROM sys.dba_waiters) lock_waiter
WHERE
b.sid = c.sid
AND u.USER# = c.USER#
AND o.obj#(+) = b.id1
AND lock_blocker.waiting_session(+) = c.sid
AND lock_waiter.holding_session(+) = c.sid
AND c.username != 'SYS'
ORDER BY kaddr, lockwait
/
All instance locks
create or replace trigger TRACE_USER after logon on database
begin
if user in ('&USER_TO_TRACE') then
execute immediate 'alter session set timed_statistics = true';
execute immediate 'alter session set statistics_level=ALL';
execute immediate 'alter session set max_dump_file_size=UNLIMITED';
execute immediate 'alter session set tracefile_identifier="11204_10046_10053"';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
EXECUTE IMMEDIATE 'alter session set events ''10053 trace name context forever, level 1''';
end if;
exception
when others then
null;
end;
/
Database Logon trigger to trace a user
exec dbms_workload_repository.create_snapshot;
Adhoc AWR Snap
select ses.username , substr(ses.program, 1, 19) command , tra.used_ublk
from v$session ses, v$transaction tra
where ses.saddr = tra.ses_addr
/select s.username, rn.name, rs.extents
,rs.status, t.used_ublk, t.used_urec
,do.object_name
from v$transaction t
,v$session s
,v$rollname rn
,v$rollstat rs
,v$locked_object lo
,dba_objects do
where t.addr = s.taddr
and t.xidusn = rn.usn
and rn.usn = rs.usn
and t.xidusn = lo.xidusn(+)
and do.object_id = lo.object_id
/select s.username, rn.name, rs.curext
,rs.curblk, t.used_ublk, t.used_urec
from v$transaction t
,v$session s
,v$rollname rn
,v$rollstat rs
where t.addr = s.taddr
and t.xidusn = rn.usn
and rn.usn = rs.usn
/Rollback Check
select sql_text||chr(10)|| 'exec dbms_shared_pool.purge ('||''''||address||','||hash_value||''''||','||''''||'C'||''''||');'
from v$sqlarea
where sql_id='&&sql_id'
/
Flush SQL Plan from Cache
set verify off
set lines 200
set echo on
select * from dba_sys_privs
where grantee=upper('&&user')
/
select * from dba_role_privs
where grantee=upper('&&user')
/
select * from dba_tab_privs
where grantee=upper('&&user')
/Check a users privs
set lines 200
col machine for a20
col program for a20
col osuser for a10
select distinct substr(a.osuser,1,10) osuser,a.sql_id, substr(a.program,1,20) program,a.sid,a.serial#,a.username,a.status,a.machine,b.BLOCKS*d.block_size/1024/1024 TEMP_SPACE_IN_MB from gv$session a, v$sort_usage b, v$sqlarea c, dba_tablespaces d
where a.saddr=b.SESSION_ADDR and a.sql_address=c.address and b.tablespace=d.TABLESPACE_NAME
and b.BLOCKS*d.block_size/1024/1024>0
order by 6 desc
/
Temp Tablespace Usage
col table_name for a32
col column_name for a32
col owner for a20
set lines 220
SELECT cons.owner, cols.table_name, cols.column_name, cols.position, cons.status
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = upper('&tablename')
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;Check a tables Primary Key Name and columns
col name for a40
col value for a32
set pages 50
select name, value, ISDEFAULT, ISSYS_MODIFIABLE from v$parameter
where lower(name) in
('db_16k_cache_size',
'db_2k_cache_size',
'db_32k_cache_size',
'db_4k_cache_size',
'db_8k_cache_size',
'db_big_table_cache_percent_target',
'db_cache_size',
'db_keep_cache_size',
'db_recycle_cache_size',
'java_pool_size',
'large_pool_size',
'memory_max_target',
'memory_target',
'pga_aggregate_limit',
'pga_aggregate_target',
'result_cache_max_size',
'sga_max_size',
'sga_target',
'shared_pool_reserved_size',
'shared_pool_size',
'streams_pool_size')
order by name;
set pages 50
select * from v$sgainfo order by name;
set lines 260
col COMPONENT for a40
col parameter for a40
select * from V$MEMORY_DYNAMIC_COMPONENTS order by COMPONENT ;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select * from V$MEMORY_RESIZE_OPS order by component;
select * from V$SGA_RESIZE_OPS order by component;Review Oracle Memory resizes
set lines 200
col PROGRAM_NAME for a30
col SCHEDULE_NAME for a30
col REPEAT_INTERVAL for a70
col START_DATE for a34
col DURATION for a16
select name from v$database
/
select window_name, SCHEDULE_NAME, RESOURCE_PLAN, REPEAT_INTERVAL, DURATION from DBA_SCHEDULER_WINDOWS
/
col job_name for a40
select JOB_NAME, to_char(LOG_DATE,'DD-MON-YYYY HH24:MI:SS') "DATE" from dba_scheduler_job_log
where LOG_DATE > (sysdate-2)
order by 2 desc
/
set pages 50
select JOB_NAME, SCHEDULE_NAME, NEXT_RUN_DATE, state from dba_scheduler_jobs
where state<>'DISABLED'
/
select SCHEDULE_NAME, REPEAT_INTERVAL from dba_scheduler_schedules
/
Check Scheduler window
set lines 200
set pages 200
select 'alter database datafile '||''''||file_name||''''||' autoextend on next 100M maxsize 32000M;' from dba_data_files
/
Autoextend datafiles SQL
set lines 132
set pages 1000
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
set pages 0
set lines 132
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id'));set lines 180
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats +peeked_binds'))
/
View an explain plan also see : https://github.com/oracle-developer/xplan
col object_name for a32
set lines 132
set pages 1000
with tab_growth as
(SELECT ob.owner, ob.object_name
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
WHERE object_type in ('TABLE')
and object_id=obj#
and owner = '&1'
and rowcnt>0
group by ob.owner, ob.object_name
having (max(rowcnt)/min(rowcnt)>1.25) and (max(rowcnt)-min(rowcnt)>100000)
order by max(rowcnt))
SELECT distinct ob.owner, ob.object_name,to_date(analyzetime,'DD-MON-YYYY') analyzetime, rowcnt
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob, tab_growth tg
WHERE ob.owner=tg.owner
and ob.object_name=tg.object_name
and ob.object_type in ('TABLE')
and ob.object_id=obj#
order by ob.owner, ob.object_name, analyzetime
/Table Growth history
set pages 5000
set lines 200
set feedback off
col TIMESTAMP for a20
col MESSAGE_TEXT for a90
col DATABASE_LOG for a90
col LISTENER_LOG for a90
select to_char(ORIGINATING_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP, MESSAGE_TEXT "DATABASE_LOG" from V$DIAG_ALERT_EXT
where ORIGINATING_TIMESTAMP>(sysdate-1)
and (message_text like '%ORA-%' or message_text like '%TNS-%')
and COMPONENT_ID='rdbms'
/
select count(*) "24HRS_COUNT", MESSAGE_TEXT "LISTENER_LOG"
from V$DIAG_ALERT_EXT
where ORIGINATING_TIMESTAMP>(sysdate-1)
and (message_text like '%ORA-%' or message_text like '%TNS-%')
and COMPONENT_ID='tnslsnr'
group by MESSAGE_TEXT
/Database logs check
set feedback off
set sqlblanklines on
accept sql_id -
prompt 'Enter value for sql_id: ' -
default 'X0X0X0X0'
accept plan_hash_value -
prompt 'Enter value for plan_hash_value: '
accept profile_name -
prompt 'Enter value for profile_name (PROF_sqlid_planhash): ' -
default 'X0X0X0X0'
accept category -
prompt 'Enter value for category (DEFAULT): ' -
default 'DEFAULT'
accept force_matching -
prompt 'Enter value for force_matching (FALSE): ' -
default 'false'
declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
l_profile_name varchar2(30);
begin
select
extractvalue(value(d), '/hint') as outline_hints
bulk collect
into
ar_profile_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id = '&&sql_id'
and plan_hash_value = &&plan_hash_value
and other_xml is not null
)
) d;
select
sql_text,
decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||'&&plan_hash_value','&&profile_name')
into
cl_sql_text, l_profile_name
from
dba_hist_sqltext
where
sql_id = '&&sql_id';
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
category => '&&category',
name => l_profile_name,
force_match => &&force_matching
-- replace => true
);
dbms_output.put_line(' ');
dbms_output.put_line('SQL Profile '||l_profile_name||' created.');
dbms_output.put_line(' ');
exception
when NO_DATA_FOUND then
dbms_output.put_line(' ');
dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Plan: '||'&&plan_hash_value'||' not found in AWR.');
dbms_output.put_line(' ');
end;
/
undef sql_id
undef plan_hash_value
undef profile_name
undef category
undef force_matching
set sqlblanklines off
set feedback onPin SQL to a Profile
select owner, object_name, object_type, status from dba_objects
where status<>'VALID'
order by 1,2
/View Invalid Objects
set serverout on
set feedback off
DECLARE
DBNAME VARCHAR2(16);
DATAFILES NUMBER;
TEMPFILES NUMBER;
REDO NUMBER;
BEGIN
SELECT NAME INTO DBNAME from sys.v_$database;
SELECT round(sum(bytes)/1024/1024/1024) INTO DATAFILES from dba_data_files;
SELECT round(sum(bytes)/1024/1024/1024) INTO TEMPFILES from dba_temp_files;
select nvl(round((sum(blocks*block_size)/1024/1024/1024)),0) into REDO from v$archived_log where COMPLETION_TIME>(sysdate-1);
DBMS_OUTPUT.PUT_LINE('DATABASE:'||DBNAME||' DATAFILE_SIZE:'||DATAFILES||'Gb TEMPFILE_SIZE:'||TEMPFILES||'Gb REDO_SIZE_per_day:'||REDO||'Gb');
END;
/
Database Size