Diff for "désert/Oracle/Scripts"

Admin active sessions.sql

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

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

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

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

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

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;