81.Script: locked_objects_internal.sql
-- Description : Lists all locks on the specific object.
[oracle@MaxwellDBA monitoring]$ cat locked_objects_internal.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/locked_objects_internal.sql
-- Author : Maxwell
-- Description : Lists all locks on the specific object.
-- Requirements : Access to the DBA views.
-- Call Syntax : @locked_objects_internal (object-name)
-- Last Modified: 08-AUG-2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 1000 VERIFY OFF
COLUMN lock_type FORMAT A20
COLUMN mode_held FORMAT A10
COLUMN mode_requested FORMAT A10
COLUMN lock_id1 FORMAT A50
COLUMN lock_id2 FORMAT A30
SELECT li.session_id AS sid,
s.serial#,
li.lock_type,
li.mode_held,
li.mode_requested,
li.lock_id1,
li.lock_id2
FROM dba_lock_internal li
JOIN v$session s ON li.session_id = s.sid
WHERE UPPER(lock_id1) LIKE UPPER('%&1%');
SET VERIFY ON
[oracle@MaxwellDBA monitoring]$
82.Script: logfiles.sql
-- Description : Displays information about redo log files.
[oracle@MaxwellDBA monitoring]$ cat logfiles.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/logfiles.sql
-- Author : Maxwell
-- Description : Displays information about redo log files.
-- Requirements : Access to the V$ views.
-- Call Syntax : @logfiles
-- Last Modified: 08-AUG-2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 200
COLUMN member FORMAT A50
COLUMN first_change# FORMAT 99999999999999999999
COLUMN next_change# FORMAT 99999999999999999999
SELECT l.thread#,
lf.group#,
lf.member,
TRUNC(l.bytes/1024/1024) AS size_mb,
l.status,
l.archived,
lf.type,
lf.is_recovery_dest_file AS rdf,
l.sequence#,
l.first_change#,
l.next_change#
FROM v$logfile lf
JOIN v$log l ON l.group# = lf.group#
ORDER BY l.thread#,lf.group#, lf.member;
SET LINESIZE 80
[oracle@MaxwellDBA monitoring]$
83.Script: longops.sql
-- Description : Displays information on all long operations.
[oracle@MaxwellDBA monitoring]$ cat longops.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/longops.sql
-- Author : Maxwell
-- Description : Displays information on all long operations.
-- Requirements : Access to the V$ views.
-- Call Syntax : @longops
-- Last Modified: 08-AUG-2022
-- -----------------------------------------------------------------------------------
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
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 v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;
[oracle@MaxwellDBA monitoring]$
84.Script: lru_latch_ratio.sql
-- Description : Displays current LRU latch ratios.
[oracle@MaxwellDBA monitoring]$ cat lru_latch_ratio.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/lru_latch_ratio.sql
-- Author : Maxwell
-- Description : Displays current LRU latch ratios.
-- Requirements : Access to the V$ views.
-- Call Syntax : @lru_latch_hit_ratio
-- Last Modified: 08-AUG-2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
COLUMN "Ratio %" FORMAT 990.00
PROMPT
PROMPT Values greater than 3% indicate contention.
SELECT a.child#,
(a.SLEEPS / a.GETS) * 100 "Ratio %"
FROM v$latch_children a
WHERE a.name = 'cache buffers lru chain'
ORDER BY 1;
SET PAGESIZE 14
[oracle@MaxwellDBA monitoring]$
85.Script: max_extents.sql
-- Description : Displays all tables and indexes nearing their MAX_EXTENTS setting.
[oracle@MaxwellDBA monitoring]$ cat max_extents.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/max_extents.sql
-- Author : Maxwell
-- Description : Displays all tables and indexes nearing their MAX_EXTENTS setting.
-- Requirements : Access to the DBA views.
-- Call Syntax : @max_extents
-- Last Modified: 08-AUG-2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
PROMPT
PROMPT Tables and Indexes nearing MAX_EXTENTS
PROMPT **************************************
SELECT e.owner,
e.segment_type,
Substr(e.segment_name, 1, 30) segment_name,
Trunc(s.initial_extent/1024) "INITIAL K",
Trunc(s.next_extent/1024) "NEXT K",
s.max_extents,
Count(*) as extents
FROM dba_extents e,
dba_segments s
WHERE e.owner = s.owner
AND e.segment_name = s.segment_name
AND e.owner NOT IN ('SYS', 'SYSTEM')
GROUP BY e.owner, e.segment_type, e.segment_name, s.initial_extent, s.next_extent, s.max_extents
HAVING Count(*) > s.max_extents - 10
ORDER BY e.owner, e.segment_type, Count(*) DESC;
[oracle@MaxwellDBA monitoring]$
86.Script: min_datafile_size.sql
-- Description : Displays smallest size the datafiles can shrink to without a reorg.
[oracle@MaxwellDBA monitoring]$ cat min_datafile_size.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/min_datafile_size.sql
-- Author : Maxwell
-- Description : Displays smallest size the datafiles can shrink to without a reorg.
-- Requirements : Access to the V$ and DBA views.
-- Call Syntax : @min_datafile_size
-- Last Modified: 08-AUG-2022
-- -----------------------------------------------------------------------------------
COLUMN block_size NEW_VALUE v_block_size
SELECT TO_NUMBER(value) AS block_size
FROM v$parameter
WHERE name = 'db_block_size';
COLUMN tablespace_name FORMAT A20
COLUMN file_name FORMAT A50
COLUMN current_bytes FORMAT 999999999999999
COLUMN shrink_by_bytes FORMAT 999999999999999
COLUMN resize_to_bytes FORMAT 999999999999999
SET VERIFY OFF
SET LINESIZE 200
SELECT a.tablespace_name,
a.file_name,
a.bytes AS current_bytes,
a.bytes - b.resize_to AS shrink_by_bytes,
b.resize_to AS resize_to_bytes
FROM dba_data_files a,
(SELECT file_id, MAX((block_id+blocks-1)*&v_block_size) AS resize_to
FROM dba_extents
GROUP by file_id) b
WHERE a.file_id = b.file_id
ORDER BY a.tablespace_name, a.file_name;
[oracle@MaxwellDBA monitoring]$
87.Script: part_tables.sql
-- Description : Displays information about all partitioned tables.
[oracle@MaxwellDBA monitoring]$ cat part_tables.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/part_tables.sql
-- Author : Maxwell
-- Description : Displays information about all partitioned tables.
-- Requirements : Access to the DBA views.
-- Call Syntax : @part_tables
-- Last Modified: 08-AUG-2022
-- -----------------------------------------------------------------------------------
SELECT owner, table_name, partitioning_type, partition_count
FROM dba_part_tables
WHERE owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, table_name;
[oracle@MaxwellDBA monitoring]$
88.Script: page_target_advice.sql
-- Description : Predicts how changes to the PGA_AGGREGATE_TARGET will affect PGA usage.
[oracle@MaxwellDBA monitoring]$ cat pga_target_advice.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/pga_target_advice.sql
-- Author : Maxwell
-- Description : Predicts how changes to the PGA_AGGREGATE_TARGET will affect PGA usage.
-- Requirements : Access to the V$ views.
-- Call Syntax : @pga_target_advice
-- Last Modified: 08-AUG-2022
-- -----------------------------------------------------------------------------------
SELECT ROUND(pga_target_for_estimate/1024/1024) target_mb,
estd_pga_cache_hit_percentage cache_hit_perc,
estd_overalloc_count
FROM v$pga_target_advice;
[oracle@MaxwellDBA monitoring]$
89.Script: pipes.sql
-- Description : Displays a list of all database pipes.
[oracle@MaxwellDBA monitoring]$ cat pipes.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/pipes.sql
-- Author : Maxwell
-- Description : Displays a list of all database pipes.
-- Requirements : Access to the V$ views.
-- Call Syntax : @pipes
-- Last Modified: 08-AUG-2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 100
COLUMN name FORMAT A40
SELECT ownerid,
name,
type,
pipe_size
FROM v$db_pipes
ORDER BY 1,2;
[oracle@MaxwellDBA monitoring]$
90.Script: profiler_run_details.sql
-- Description : Displays details of a specified profiler run.
[oracle@MaxwellDBA monitoring]$ cat profiler_run_details.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/profiler_run_details.sql
-- Author : Maxwell
-- Description : Displays details of a specified profiler run.
-- Requirements : Access to the V$ views.
-- Call Syntax : @profiler_run_details.sql (runid)
-- Last Modified: 08-AUG-2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 200
SET VERIFY OFF
COLUMN runid FORMAT 99999
COLUMN unit_number FORMAT 99999
COLUMN unit_type FORMAT A20
COLUMN unit_owner FORMAT A20
SELECT u.runid,
u.unit_number,
u.unit_type,
u.unit_owner,
u.unit_name,
d.line#,
d.total_occur,
ROUND(d.total_time/d.total_occur) as time_per_occur,
d.total_time,
d.min_time,
d.max_time
FROM plsql_profiler_units u
JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE u.runid = &1
AND d.total_time > 0
AND d.total_occur > 0
ORDER BY (d.total_time/d.total_occur) DESC, u.unit_number, d.line#;
[oracle@MaxwellDBA monitoring]$