Differences between revisions 4 and 5
⇤ ← Revision 4 as of 2020-04-08 02:19:57 →
Size: 20682
Editor: 192
Comment:
← Revision 5 as of 2020-04-08 02:21:53 → ⇥
Size: 25447
Editor: 192
Comment:
Deletions are marked like this. Additions are marked like this.
Line 568: Line 568:

== 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 - - - - - - - - - - - - - - - - - - - - - - - - - -
}}}

Contents

  1. Admin active sessions.sql
  2. Admin all sessions.sql
  3. Admin check dblinks.sql
  4. Dbinfo.sql
  5. Admin param hidden.sql
  6. Admin check registry.sql
  7. Admin stats stale

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 - - - - - - - - - - - - - - - - - - - - - - - - - -

désert/Oracle/Scripts (last edited 2020-09-04 07:14:25 by merlyn)