Contents
- Admin active sessions.sql
- Admin all sessions.sql
- Admin check dblinks.sql
- Dbinfo.sql
- Admin param hidden.sql
- Admin check registry.sql
- Admin stats stale
- Admin db maintenance jobs.sql
- ASM info1.sql
- Brec show archive scn.sql
- Dev show table locks.sql
- Admin user grants.sql
- Dev find child.sql
- show index columns
- Find Primary Key
- Perf active session cpu.sql
- Show db links.sql
- Perf buffer pool.sql
- Perf log switches.sql
- Perf redo volume.sql
- Perf longops.sql
- Perf topsql running
Admin active sessions.sql
- shows active foreground sessions in the database
col username format a20 col osuser format a20 col schemaname format a20 set linesize 200 select sid, serial#, username, osuser, status, schemaname, to_char (logon_time,'DD-MON-YYYY HH24:MI:SS') logon_time from v$session where status = 'ACTIVE' and username IS NOT NULL order by logon_time /
Admin all sessions.sql
- shows all active and inactive sessions including last active time
col username format a15
col module format a30
col machine format a50
col program format a30
col osuser format a15
set linesize 300
select username,
status,
floor(last_call_et / 60) "Minutes Inactive",
sid,
serial#,
osuser,
machine,
substr(program,1,30) program,
substr(module,1,30) module
from v$session
where username not in (select username from dba_users where oracle_maintained='YES')
and username is not null
and username not in ('DBSNMP')
and type = 'USER'
order by status, username
/
Admin check dblinks.sql
- lists private and public database links and checks the status
SET SERVEROUT ON
prompt *** list of available database links ***
col owner format a15
col db_link format a20
col host format a30
select owner, db_link, host from dba_db_links order by 1;
prompt *** status of the private database links ***
prompt
prompt OWNER DATABASE LINK STATUS
prompt ------------------------------
BEGIN
FOR f IN ( SELECT *
FROM dba_db_links
WHERE OWNER <> 'PUBLIC'
ORDER BY owner, db_link)
LOOP
DBMS_SCHEDULER.create_job (
job_name => f.owner || '.CHECK_DBLINK_PRIVATE',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE '
|| ' X CHAR; '
|| 'BEGIN '
|| ' SELECT dummy into x from dual@'
|| f.db_link
|| ' ;'
|| ' DBMS_OUTPUT.put_line('''
|| f.owner
|| ' '
|| f.db_link
|| ' VALID'');'
|| 'END ; ');
BEGIN
DBMS_SCHEDULER.run_job (f.owner || '.CHECK_DBLINK_PRIVATE ', TRUE);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
f.owner
|| ' '
|| f.db_link
|| ' INVALID (ORA'
|| SQLCODE
|| ')');
END;
DBMS_SCHEDULER.drop_job (f.owner || ' . CHECK_DBLINK_PRIVATE ');
END LOOP;
END;
/
prompt *** status of the public database links ***
prompt
prompt OWNER DATABASE LINK STATUS
prompt ------------------------------
BEGIN
FOR f IN ( SELECT *
FROM dba_db_links
WHERE OWNER = 'PUBLIC'
ORDER BY db_link)
LOOP
DBMS_SCHEDULER.create_job (
job_name => 'system.CHECK_DBLINK_PUBLIC',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE '
|| ' X CHAR; '
|| 'BEGIN '
|| ' SELECT dummy into x from dual@'
|| f.db_link
|| ' ;'
|| ' DBMS_OUTPUT.put_line('''
|| 'SYSTEM'
|| ' '
|| f.db_link
|| ' VALID'');'
|| 'END ; ');
BEGIN
DBMS_SCHEDULER.run_job ( 'system.CHECK_DBLINK_PUBLIC ', TRUE);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'PUBLIC'
|| ' '
|| f.db_link
|| ' INVALID (ORA'
|| SQLCODE
|| ')');
END;
DBMS_SCHEDULER.drop_job ('system.CHECK_DBLINK_PUBLIC ');
END LOOP;
END;
/
Dbinfo.sql
- gathers info about the database in html format, good for database intakes
column k_time new_value d_time noprint
column k_sid new_value d_sid noprint
select to_char(sysdate, 'dd-mm-yyyy hh24:mi') k_time from dual
/
select name k_sid from v$database
/
spool &d_sid..html
SET SERVEROUTPUT on SIZE 100000
SET MARKUP HTML OFF
prompt <style>table {font-size: 9pt;} background-color: #d8da3d</style>
prompt <style>p,table,body,html { font-size: 10pt; font-family: arial; color: #000099;}</style>
prompt <style>A:link, A:visited, A:active { color: #000099}A:hover { text-decoration: underline; ; color: #FF9900}</style>
prompt <a name="Index"></a>
prompt <img src="https://source.atosorigin.com/ao_messages/atosorigin_logo.gif">
prompt <TABLE cellSpacing=0 cellPadding=0 width=996 border=0>
prompt <TR>
prompt <TD bgcolor="#3190CA"><img src="https://source.atosorigin.com/shared/images/spacer.gif" width="166" height="12" border="0"></TD>
prompt <TD bgcolor="#FF9620" width="166"></TD>
prompt <TD bgcolor="#CC0000" width="166"></TD>
prompt <TD bgcolor="#B1B5B8" width="166"></TD>
prompt <TD bgcolor="#4E3EA3" width="166"></TD>
prompt <TD bgcolor="#B8CC33" width="166"></TD>
prompt </TR></TABLE>
prompt Database layout of database &d_sid taken on &d_time. (db2html.sql version 0.3)
prompt <h1>Index</h1><hr>
prompt <LI><A href="#Version information">Version information</A></LI>
prompt <LI><A href="#Logmode">Database and logmode</A></LI>
prompt <LI><A href="#Storage">Storage usage</A></LI>
prompt <LI><A href="#Storage">Memory usage</A></LI>
prompt <LI><A href="#Tablespaces">Tablespaces</A></LI>
prompt <LI><A href="#Datafiles">Datafiles</A></LI>
prompt <LI><A href="#Controlfiles">Controlfiles</A></LI>
prompt <LI><A href="#Redolog">Redolog</A></LI>
prompt <LI><A href="#Archivelog">Archivelog</A></LI>
prompt <LI><A href="#Parameters">Parameters</A></LI>
prompt <LI><A href="#Nondefaultparameters">Nondefaultparameters</A></LI>
prompt <LI><A href="#Nlsparameters">Nlsparameters</A></LI>
prompt <LI><A href="#Options">Options</A></LI>
prompt <LI><A href="#Properties">Properties</A></LI>
prompt <LI><A href="#Registry">Registry</A></LI>
prompt <LI><A href="#Schemas">Schemas</A></LI>
prompt <LI><A href="#Database links">Database links</A></LI>
prompt <LI><A href="#Directories">Directories</A></LI>
prompt <LI><A href="#Jobs">DBA Jobs</A></LI>
prompt <LI><A href="#Sjobs">Scheduler jobs</A></LI>
prompt <LI><A href="#Nobjects">Number of objects</A></LI>
prompt <LI><A href="#NInvalid">Number of invalid objects</A></LI>
prompt <LI><A href="#Invalid">Invalid Objects</A></LI>
prompt <LI><a href="#schemas"> Number of Schemas</A></LI>
prompt <LI><a href="#Schema_Names">Schema Names</a></LI>
prompt <LI><a href="#Big_Schemas">Big Schemas</A></LI>
prompt <LI><a href="#Big_Tables"</A>Big Tables </LI>
prompt <LI><a href="#Big_Indexes"</A>Big Indexes </LI>
prompt <LI><a href="#Part_Tables">Partitioned Tables</A></LI>
prompt <LI><a href="#Partitions">Number of partitions</A></LI>
prompt <LI><a href="#RAW_LOB">RAW_LOB tables</A></LI>
prompt <LI><a href="#MVIEWS"></A>Materialized Views</LI>
prompt <LI><A href="#Create_User">Create User Statements</A></LI>
prompt <LI><A href="#Create_Pubsyn">Create Public Synonyms</A></LI>
prompt <hr>
prompt
SET MARKUP HTML ON ENTMAP OFF
prompt <style>table {font-size: 9pt;}</style>
set markup html on spool on
set feedback off
set pages 300
set lines 120
Prompt <b>Instance, Startup time and nodename en platform</b>
select INSTANCE_NAME, STARTUP_TIME, HOST_NAME, DBMS_UTILITY.PORT_STRING "PLATFORM" from v$instance
/
prompt <a name="Version information"></a>
prompt <A href="#Index"><b>Version information</b></A>
select * from v$version
/
prompt <a name="Logmode"></a>
prompt <A href="#Index"><b>Database and archivelogmode</b></A>
select NAME, LOG_MODE from v$database
/
prompt <a name="Storage"></a>
prompt <A href="#Index"><b>Storage usage</b></A>
select round((sum(bytes)/1024/1024/1024),2) || ' GB' "Sum datafiles" from dba_data_files
/
select round(sum(bytes)/1024/1024/1024,2) || ' GB' "Sum tempfiles" from dba_temp_files
/
select round(avg(a.num_switches)*avg(b.bytes)/1024/1024/1024,2) || ' GB' "Avg redo per day"
from (select to_char(first_time, 'dd-mm-yy') dag, count(*) num_switches from v$loghist group by to_char(first_time, 'dd-mm-yy')) a, v$log b
/
select name, value/1024/1024/1024 || ' GB' "FRA size" from v$parameter where name = 'db_recovery_file_dest_size'
/
prompt <a name="Memory"></a>
prompt <A href="#Index"><b>Memory usage</b></A>
select name, round(value/1024/1024) || ' MB' "SIZE" from v$sga
/
select name, round(value/1024/1024) || ' MB' "SIZE" from v$pgastat where name like '%PGA%' and unit='bytes' and value <> 0
/
prompt <a name="Tablespaces"></a>
prompt <A href="#Index"><b>Tablespace</b></A>
col tablespace_name format a40
select /*+ RULE */ a.tablespace_name tablespace,
round(a.bytes_alloc / 1024 / 1024, 2) total_mb,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) free_mb,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) mb_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free
from ( select /*+ RULE */ f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',
decode(sign(f.maxbytes-f.bytes),-1,f.bytes,f.maxbytes),'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
( select /*+ RULE */ f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
union
select /*+ RULE */ tablespace_name,
round(sum(bytes_used + bytes_free) / 1048576, 2),
round(sum(bytes_free) / 1048576,2),
round(sum(bytes_used) / 1048576,2),
round((sum(bytes_free) / sum(bytes_used + bytes_free)) * 100,2) Pct_Free
from sys.v_$TEMP_SPACE_HEADER
group by tablespace_name
ORDER BY 1
/
prompt <a name="Datafiles"></a>
prompt <A href="#Index"><b>Datafiles</b></A>
col tablespace_name format a40
col file_name format a50
select tablespace_name, FILE_NAME, round(bytes/1024/1024) MB from dba_data_files order by 1,2
/
prompt <a name="Controlfiles"></a>
prompt <A href="#Index"><b>Controlfiles</b></A>
col name format a60
select name from v$controlfile
/
prompt <a name="Redolog"></a>
prompt <A href="#Index"><b>Redolog files</b></A>
col member format a60
select * from v$logfile order by 1
/
prompt <b>Redolog sizes</b>
select group#, round(bytes/1024/1024) MB from v$log
/
prompt <a name="Archivelog"></a>
prompt <A href="#Index"><b>Archivelog</b></A>
select trunc(first_time) datum, round(sum(blocks)/1024) MB
from v$archived_log
where dest_id=1
and first_time > sysdate -30
group by trunc(first_time)
order by 1
/
prompt <a name="Parameters"></a>
prompt <A href="#Index"><b>Parameters</b></A>
col value format a50
col name format a40
select name, value,ISDEFAULT from v$parameter order by 1
/
prompt <a name="Nondefaultparameters"></a>
prompt <A href="#Index"><b>Non default parameters</b></A>
col value format a50
col name format a40
select name
, value
from v$parameter
where ISDEFAULT='FALSE' order by 1
/
prompt <a name="Nlsparameters"></a>
prompt <A href="#Index"><b>NLs parameters</b></A>
col parameter format a40
col VALUE format a50
select * from nls_database_parameters order by 1
/
prompt <a name="Options"></a>
prompt <A href="#Index"><b>Database options</b></A>
select * from v$option order by 1;
prompt <a name="Properties"></a>
prompt <A href="#Index"><b>Database properties</b></A>
select * from database_properties order by 1
/
prompt <a name="Registry"></a>
prompt <A href="#Index"><b>Registry</b></A>
select comp_name, version, status from dba_registry order by 1
/
prompt <a name="Schemas"></a>
prompt <A href="#Index"><b>Schemas and sizes</b></A>
select owner, round(sum(bytes)/1024/1024) MB from dba_segments group by owner order by 1
/
prompt <b>Owner, tablespace_name</b>
select owner, tablespace_name from dba_segments
group by owner, tablespace_name order by owner,tablespace_name
/
prompt <b>Owner, last_analyzed</b>
col last_analyzed format a30
select owner, max(last_analyzed) last_analyzed
from dba_tab_columns
group by owner order by owner
/
prompt <a name="Database links"></a>
prompt <A href="#Index"><b>Database links</b></A>
select * from dba_db_links order by owner
/
prompt <a name="Directories"></a>
prompt <A href="#Index"><b>Database directories</b></A>
select * from dba_directories order by 1
/
prompt <a name="Jobs"></a>
prompt <A href="#Index"><b>DBA Jobs</b></A>
select schema_user,what, last_date, next_date, broken from dba_jobs order by 1
/
prompt <a name="SJobs"></a>
prompt <A href="#Index"><b>Scheduler Jobs (10 only)</b></A>
select owner, job_name, program_name, job_action, comments, state from dba_scheduler_jobs order by 1
/
prompt <a name="Nobjects"></a>
prompt <A href="Index"><b>Number of objects</b></A>
select owner, count(*) from dba_objects
where owner not in ('CTXSYS','DMSYS','EXFSYS','MDSYS','OLAPSYS','ORDSYS','OWF_MGR','SYS','SYSTEM','WMSYS')
group by owner order by owner
/
prompt <a name="NInvalid"></a>
prompt <A href="#Index"><b>Number of invalid objects</b></A>
select owner, count(*) from dba_objects where status!='VALID'
and owner not in ('CTXSYS','DMSYS','EXFSYS','MDSYS','OLAPSYS','ORDSYS','OWF_MGR','SYS','SYSTEM','WMSYS')
group by owner order by 1
/
prompt <a name="Invalid"></a>
prompt <A href="#Index"><b>Invalid Objects</b></A>
select owner, object_name, object_type from dba_objects where status!='VALID'
and owner not in ('CTXSYS','DMSYS','EXFSYS','MDSYS','OLAPSYS','ORDSYS','OWF_MGR','SYS','SYSTEM','WMSYS')
order by 1,3,2
/
prompt <a name="Schemas"></a>
prompt <a href="#index"><b>Schemas</b></A>
select count(distinct (owner)) from dba_segments where owner not in ('APPQOSSYS','CACHEADM','CTXSYS','DBSNMP','DEMO','EXFSYS','FLOWS_FILES','HR','HR1','IX','MDSYS','OBE','OE','OE1','OLAPSYS','ORDDATA','ORDSYS','OUTLN','OWBSYS','PHPDEMO','PLS','PM','SH','SYS','SYSMAN','SYSTEM','TIMESTEN','TTHR','WMSYS','XDB','XDBEXT','XDBPM','XFILES')
/
prompt <a name="Schema_Names"></a>
prompt <a href="#index"><b>Schema Names</b></A>
select distinct owner from dba_segments where owner not in ('APPQOSSYS','CACHEADM','CTXSYS','DBSNMP','DEMO','EXFSYS','FLOWS_FILES','HR','HR1','IX','MDSYS','OBE','OE','OE1','OLAPSYS','ORDDATA','ORDSYS','OUTLN','OWBSYS','PHPDEMO','PLS','PM','SH','SYS','SYSMAN','SYSTEM','TIMESTEN','TTHR','WMSYS','XDB','XDBEXT','XDBPM','XFILES') order by owner
/
prompt <a name="Big_Schemas"></a>
prompt <a href="#index"><b>Big Schemas</b></A>
select owner SCHEMA, ROUND (sum(bytes)/(1024*1024*1024)) SIZE_GB from dba_segments
where owner not in ('APPQOSSYS','CACHEADM','CTXSYS','DBSNMP','DEMO','EXFSYS','FLOWS_FILES','HR','HR1','IX','MDSYS','OBE','OE','OE1','OLAPSYS','ORDDATA','ORDSYS','OUTLN','OWBSYS','PHPDEMO','PLS','PM','SH','SYS','SYSMAN','SYSTEM','TIMESTEN','TTHR','WMSYS','XDB','XDBEXT','XDBPM','XFILES')
having sum(bytes) > 2*1024*1024*1024
group by owner order by 2,1
/
prompt <a name="Big_Tables"></a>
prompt <a href="#index"><b>Big Tables</b></A>
col segment_name format a30
select owner, segment_name, ROUND (sum(bytes)/(1024*1024*1024)) SIZE_GB from dba_segments
where segment_type = 'TABLE' having sum(bytes) > 2*1024*1024*1024
and owner not in ('APPQOSSYS','CACHEADM','CTXSYS','DBSNMP','DEMO','EXFSYS','FLOWS_FILES','HR','HR1','IX','MDSYS','OBE','OE','OE1','OLAPSYS','ORDDATA','ORDSYS','OUTLN','OWBSYS','PHPDEMO','PLS','PM','SH','SYS','SYSMAN','SYSTEM','TIMESTEN','TTHR','WMSYS','XDB','XDBEXT','XDBPM','XFILES')
group by owner, segment_name order by sum(bytes), owner, segment_name
/
prompt <a name="Big_Indexes"></a>
prompt <a href="#index"<b>Big Indexes</b></A>
select owner, segment_name, ROUND (sum(bytes)/(1024*1024*1024)) SIZE_GB from dba_segments
where segment_type = 'INDEX' having sum(bytes) > 2*1024*1024*1024
and owner not in ('APPQOSSYS','CACHEADM','CTXSYS','DBSNMP','DEMO','EXFSYS','FLOWS_FILES','HR','HR1','IX','MDSYS','OBE','OE','OE1','OLAPSYS','ORDDATA','ORDSYS','OUTLN','OWBSYS','PHPDEMO','PLS','PM','SH','SYS','SYSMAN','SYSTEM','TIMESTEN','TTHR','WMSYS','XDB','XDBEXT','XDBPM','XFILES')
group by owner, segment_name order by sum(bytes), owner, segment_name
/
prompt <a name="Part_Tables"></a>
prompt <a href="#index"><b>Partitioned Tables</b></A>
select table_owner, table_name, count(partition_name)
from dba_tab_partitions
where table_owner not in ('APPQOSSYS','CACHEADM','CTXSYS','DBSNMP','DEMO','EXFSYS','FLOWS_FILES','HR','HR1','IX','MDSYS','OBE','OE','OE1','OLAPSYS','ORDDATA','ORDSYS','OUTLN','OWBSYS','PHPDEMO','PLS','PM','SH','SYS','SYSMAN','SYSTEM','TIMESTEN','TTHR','WMSYS','XDB','XDBEXT','XDBPM','XFILES')
group by table_owner, table_name order by table_owner, table_name
/
prompt <a name="Partitions"></a>
prompt <a href="#index"><b>Number of Partitions</b></A>
select table_owner, table_name, count(partition_name)
from dba_tab_partitions
where table_owner not in ('APPQOSSYS','CACHEADM','CTXSYS','DBSNMP','DEMO','EXFSYS','FLOWS_FILES','HR','HR1','IX','MDSYS','OBE','OE','OE1','OLAPSYS','ORDDATA','ORDSYS','OUTLN','OWBSYS','PHPDEMO','PLS','PM','SH','SYS','SYSMAN','SYSTEM','TIMESTEN','TTHR','WMSYS','XDB','XDBEXT','XDBPM','XFILES')
group by table_owner, table_name order by table_owner, table_name
/
prompt <a name="RAW_LOB"></a>
prompt <a href="#index"><b>RAW_LOB_Tables</b></A>
col data_type format a20
select owner, table_name, data_type, count(column_name)
from dba_tab_columns where (data_type = 'LONG RAW' or data_type = 'LONG' or data_type like '%LOB%')
and owner not in ('APPQOSSYS','CACHEADM','CTXSYS','DBSNMP','DEMO','EXFSYS','FLOWS_FILES','HR','HR1','IX','MDSYS','OBE','OE','OE1','OLAPSYS','ORDDATA','ORDSYS','OUTLN','OWBSYS','PHPDEMO','PLS','PM','SH','SYS','SYSMAN','SYSTEM','TIMESTEN','TTHR','WMSYS','XDB','XDBEXT','XDBPM','XFILES') and owner not like 'APEX%'
group by owner, table_name, data_type
order by owner, table_name, data_type
/
prompt <a name="MVIEWS"></a>
prompt <a href="#index"><b>Materialized Views</b></A>
select owner, count(mview_name)
from dba_mviews
where owner not in ('APPQOSSYS','CACHEADM','CTXSYS','DBSNMP','DEMO','EXFSYS','FLOWS_FILES','HR','HR1','IX','MDSYS','OBE','OE','OE1','OLAPSYS','ORDDATA','ORDSYS','OUTLN','OWBSYS','PHPDEMO','PLS','PM','SH','SYS','SYSMAN','SYSTEM','TIMESTEN','TTHR','WMSYS','XDB','XDBEXT','XDBPM','XFILES')
group by owner order by owner
/
prompt <a name="Create_User"></a>
prompt <A href="#Index"><b>Create User Statements></b></A>
set head off
set pages 0
set long 9999999
select dbms_metadata.get_ddl('USER', username) || ';' usercreate
from dba_users;
prompt <a name="Create_Pubsyn"></a>
prompt <A href="#Index"><b>Create Public Synonyms</b></A>
set feedback off
set trimspool on
set pagesize 1000
set linesize 1000
set heading off
select 'create public synonym ' || table_name || ' for ' || table_owner || '.' || table_name || ';' from dba_synonyms where owner='PUBLIC'
and table_owner not in ('CTXSYS','DMSYS','EXFSYS','MDSYS','OLAPSYS','ORDSYS','OWF_MGR','SYS','SYSTEM','WMSYS');
spool off
set markup html off
/
Admin param hidden.sql
- shows value of all hidden parameters
SET PAGESIZE 60 SET LINESIZE 300 COLUMN ksppinm FORMAT A50 COLUMN ksppstvl FORMAT A50 SELECT ksppinm, ksppstvl FROM x$ksppi a, x$ksppsv b WHERE a.indx=b.indx AND substr(ksppinm,1,1) = '_' ORDER BY ksppinm /
Admin check registry.sql
SET LINESIZE 400
COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10
SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS patch_time,
action,
status,
description,
version,
patch_id,
bundle_series
FROM sys.dba_registry_sqlpatch
ORDER by patch_time;
Admin stats stale
* Script that Checks for Schemas Containing Stale Statistics (Doc ID 560336.1)
-- - - - - - - - - - - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - - - - - - - - - - - -
-- NAME: CHECK_STALE_STATS.SQL
-- Execute as SYS as sysdba
-- ----------------------------------------------------------------------------------------------------------------
-- AUTHOR:
-- Raja Ganesh - Oracle Support Services - DataServer Group
-- Copyright 2008, Oracle Corporation
-- -----------------------------------------------------------------------------------------------------------------
-- PURPOSE:
-- This script is an automated way to deal with stale statistics
-- operations that are required to be done as part of manual
-- upgrade OR when reported by DBUA.
--
-- This script will work in both Windows and Unix platforms from database
-- version 9.2 or higher.
-- ------------------------------------------------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only. It is NOT
-- supported by Oracle World Wide Technical Support.
-- The script has been tested and appears to work as intended.
-- You should always run new scripts on a test instance initially.
-- -------------------------------------------------------------------------------------------------------------------
-- VERSION HISTORY
-- V1 sdixon : added 12.1.0' to supported versions
--
SET FEEDBACK OFF
SET LINESIZE 250
SET SERVEROUTPUT ON
DECLARE
-- Variables declared
P_OTAB DBMS_STATS.OBJECTTAB;
MCOUNT NUMBER := 0;
P_VERSION VARCHAR2(10);
-- Cursor defined
CURSOR c1
IS
SELECT distinct schema
FROM dba_registry
ORDER by 1;
-- Beginning of the anonymous block
BEGIN
-- Verifying version from v$instance
SELECT version INTO p_version FROM v$instance;
DBMS_OUTPUT.PUT_LINE(chr(13));
-- Defining Loop 1 for listing schema which have stale stats
FOR x in c1
LOOP
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>x.schema,OPTIONS=>'LIST STALE',OBJLIST=>p_otab);
-- Defining Loop 2 to find number of objects containing stale stats
FOR i in 1 .. p_otab.count
LOOP
IF p_otab(i).objname NOT LIKE 'SYS_%'
AND p_otab(i).objname NOT IN ('CLU$','COL_USAGE$','FET$','INDPART$',
'MON_MODS$','TABPART$','HISTGRM$',
'MON_MODS_ALL$',
'HIST_HEAD$','IN $','TAB$',
'WRI$_OPTSTAT_OPR','PUIU$DATA',
'XDB$NLOCKS_CHILD_NAME_IDX',
'XDB$NLOCKS_PARENT_OID_IDX',
'XDB$NLOCKS_RAWTOKEN_IDX', 'XDB$SCHEMA_URL',
'XDBHI_IDX', 'XDB_PK_H_LINK')
THEN
-- Incrementing count for each object found with statle stats
mcount := mcount + 1;
END IF;
-- End of Loop 2
END LOOP;
-- Displays no stale statistics, if coun is 0
IF mcount!=0
THEN
-- Displays Schema with stale stats if count is greater than 0
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- '|| x.schema || ' schema contains stale statistics use the following to gather the statistics '||'--');
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
-- Displays Command to be executed if schema with stale statistics is found depending on the version.
IF SUBSTR(p_version,1,5) in ('8.1.7','9.0.1','9.2.0')
THEN
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_SCHEMA_STATS('''||x.schema||''',OPTIONS=>'''||'GATHER STALE'||''', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => '''||'FOR ALL COLUMNS SIZE AUTO'||''', CASCADE => TRUE);');
ELSIF SUBSTR(p_version,1,6) in ('10.1.0','10.2.0','11.1.0','11.2.0','12.1.0')
THEN
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('''||x.schema||''',OPTIONS=>'''||'GATHER STALE'||''', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => '''||'FOR ALL COLUMNS SIZE AUTO'||''', CASCADE => TRUE);');
ELSE
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE('Version is '||p_version);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('-- There are no stale statistics in '|| x.schema || ' schema.');
DBMS_OUTPUT.PUT_LINE(chr(13));
END IF;
-- Reset count to 0.
mcount := 0;
-- End of Loop 1
END LOOP;
END;
/
SET FEEDBACK ON
-- - - - - - - - - - - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - - - - - - - - - - - - - -
Admin db maintenance jobs.sql
- shows dba maintenance jobs and run history. Checks if maintenance window is large enough.
set linesize 300 col client_name format a31 col job_status format 15 col job_start_time format a25 col job_duration format a15 col window_duration format a40 column job_status format a30 SELECT client_name, status FROM dba_autotask_operation; prompt select client_name, job_status, to_char(job_start_time,'DD-MON-YYYY HH24:MI:SS') job_start_time, job_duration, window_duration from dba_autotask_job_history order by job_start_time;
ASM info1.sql
displays current ASM diskgroup usage
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
Brec show archive scn.sql
prompt *** location of archive log files *** select value from v$parameter where name = 'log_archive_dest_1'; prompt *** log sequence number containing the SCN *** select sequence# from v$archived_log where &scn between FIRST_CHANGE# and NEXT_CHANGE#;
!#python highlight
SQL> @brec_show_archive_scn.sql
*** location of archive log files ***
VALUE
---------------------------------------------------------------------------------------------------------------------------
LOCATION=/u02/app/oracle/oradata/PROD/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD_DB1
*** log sequence number containing the SCN ***
Enter value for scn: 11914029569600
old 1: select sequence# from v$archived_log where &scn between FIRST_CHANGE# and NEXT_CHANGE#
new 1: select sequence# from v$archived_log where 11914029569600 between FIRST_CHANGE# and NEXT_CHANGE#
SEQUENCE#
----------
106035
106035
Dev show table locks.sql
set linesize 200 col machine format a30 col object_name format a30 col osuser format a20 select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id and c.object_name = '&table_name';
Admin user grants.sql
col table_name format a30 col granted_role format a30 col privilege format a30 set linesize 200 prompt prompt . . . granted_roles prompt select * from DBA_ROLE_PRIVS where GRANTEE='&&username'; prompt prompt . . . grants to tables prompt select * from DBA_TAB_PRIVS where GRANTEE = '&&username'; prompt prompt . . . system grants prompt select * from DBA_SYS_PRIVS where GRANTEE = '&&username';
Dev find child.sql
col PARENT_TABLE format a30
col CHILD_TABLE format a30
col CHILD_CONSTRAINT_NAME format a30
SELECT p.table_name PARENT_TABLE, c.table_name CHILD_TABLE, c.constraint_name, c.status STATUS
FROM dba_constraints p, dba_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND p.table_name = UPPER('&PARENT_TABLE_NAME')
and p.owner='&PARENT_TABLE_SCHEMA';
show index columns
col index_name format a30
col table_name format a20
col ind_cols format a100
set linesize 200
SELECT -- t.owner
-- , t.table_name
-- , ROUND ( t.blocks * 8 / 1024 / 1024 ) AS gb -- assuming each block is default 8kb
-- , t.num_rows
i.index_name
, LISTAGG ( ic.column_name || ' , ' ) WITHIN GROUP ( ORDER BY ic.column_position ) AS ind_cols
, i.index_type
, i.uniqueness
, t.partitioned AS table_partitioned
, i.partitioned AS index_partitioned
FROM dba_tables t
LEFT OUTER JOIN dba_indexes i
ON i.table_owner = t.owner
AND i.table_name = t.table_name
LEFT OUTER JOIN dba_ind_columns ic
ON ic.index_owner = i.owner
AND ic.index_name = i.index_name
-- WHERE t.blocks > 1024 * 1024 / 8 -- 1gb, assuming each block is default 8kb
WHERE t.TABLE_NAME = '&table_name'
GROUP BY t.owner
, t.table_name
, t.blocks
, t.num_rows
, i.index_name
, i.index_type
, i.uniqueness
, t.partitioned
, i.partitioned
ORDER BY t.blocks DESC , t.owner , t.table_name , i.index_name
Find Primary Key
col table_name format a30 col column_name format a30 SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = '&TABLE_NAME' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.table_name, cols.position;
Perf active session cpu.sql
Show CPU Usage for Active Sessions
SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300 COLUMN username FORMAT A30 COLUMN sid FORMAT 999,999,999 COLUMN serial# FORMAT 999,999,999 COLUMN "cpu usage (seconds)" FORMAT 999,999,999.0000 SELECT s.username, t.sid, s.serial#, SUM(VALUE/100) as "cpu usage (seconds)" FROM v$session s, v$sesstat t, v$statname n WHERE t.STATISTIC# = n.STATISTIC# AND NAME like '%CPU used by this session%' AND t.SID = s.SID AND s.status='ACTIVE' AND s.username is not null GROUP BY username,t.sid,s.serial# /
Show db links.sql
col owner format a20 col db_link format a30 col username format a20 col host format a30 set linesize 200 set pagesize 50 select owner, db_link, username, host from dba_db_links order by 1,2;
Perf buffer pool.sql
this query dispplays the buffer pool information and objects that are currentlly cached
prompt this query dispplays the buffer pool information and objects that are currentlly cached prompt hit <ENTER> to continue pause col object format a30 col object_type format a25 col owner format a20 set linesize 200 select name BufferPool, physical_reads, db_block_gets, consistent_gets, 100*(1-physical_reads/(db_block_gets+consistent_gets)) HitRatio from sys.v$buffer_pool_statistics; SELECT BUFF_POOL.NAME POOL, D.OWNER, O.NAME OBJECT, D.OBJECT_TYPE, SUM(CT) BLOCKS FROM (SELECT SET_DS, OBJ, COUNT(*) CT FROM X$BH GROUP BY SET_DS, OBJ) BH, OBJ$ O,X$KCBWDS KCBW,V$BUFFER_POOL BUFF_POOL, DBA_OBJECTS D WHERE O.DATAOBJ# = BH.OBJ AND D.OBJECT_ID = O.DATAOBJ# AND O.OWNER# > 0 AND BH.SET_DS = KCBW.ADDR AND KCBW.SET_ID BETWEEN BUFF_POOL.LO_SETID AND BUFF_POOL.HI_SETID AND BUFF_POOL.BUFFERS != 0 AND D.OWNER <> 'SYSTEM' GROUP BY BUFF_POOL.NAME, D.OWNER, O.NAME, D.OBJECT_TYPE, O.SUBNAME ORDER BY BUFF_POOL.NAME, D.OWNER, O.NAME, D.OBJECT_TYPE, O.SUBNAME;
Perf log switches.sql
set lines 120; set pages 999; SELECT to_char(first_time,'YYYY-MM-DD') day, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00", to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01", to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02", to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03", to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04", to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05", to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06", to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07", to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0", to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09", to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10", to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11", to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12", to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13", to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14", to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15", to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16", to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17", to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18", to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19", to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20", to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21", to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22", to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23" from v$log_history GROUP by to_char(first_time,'YYYY-MM-DD')
Perf redo volume.sql
select trunc(completion_time) rundate ,count(*) logswitch ,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" from v$archived_log group by trunc(completion_time) order by 1;
Perf longops.sql
COLUMN sid FORMAT 999
COLUMN opname format a40
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
COLUMN target format a30
set linesize 200
SELECT s.sid,
s.serial#,
s.machine,
s.sql_id,
opname,
target,
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#;
Perf topsql running
Script:
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'
-- the where clause totalwork-sofar > 0 shows only queries that are still running
select * from
(
select
opname,
start_time,
target,
sofar,
totalwork,
units,
elapsed_seconds,
message
from
v$session_longops
where totalwork-sofar > 0
order by start_time asc
)
where rownum <=10;