730 lines
24 KiB
MySQL
730 lines
24 KiB
MySQL
|
/* run this script as a dba user, passing the logfile as parameter
|
||
|
|
This script has been created to assist in the migrations of the 9i databases at OLV
|
||
|
|
|
||
|
|
@get_db_overview.sql <logfile>
|
||
|
|
|
||
|
|
this will generate a logfile in the current directory
|
||
|
|
|
||
|
|
*/
|
||
|
|
|
||
|
|
|
||
|
|
set pagesize 9999
|
||
|
|
set linesize 150
|
||
|
|
set serveroutput on
|
||
|
|
set trimspool on
|
||
|
|
set echo off
|
||
|
|
set feedback 1
|
||
|
|
|
||
|
|
spool &1
|
||
|
|
|
||
|
|
--------------------------------------------- DB ----------------------------------------------------------------------
|
||
|
|
|
||
|
|
column platform_name format a40
|
||
|
|
column name format a15
|
||
|
|
column db_unique_name format a20
|
||
|
|
|
||
|
|
select dbid, name, created, log_mode
|
||
|
|
from v$database;
|
||
|
|
|
||
|
|
--------------------------------------------- DB PARAMETERS ------------------------------------------------------------
|
||
|
|
prompt
|
||
|
|
prompt PARAMETERS
|
||
|
|
prompt __________
|
||
|
|
|
||
|
|
|
||
|
|
set linesize 180
|
||
|
|
set pagesize 9999
|
||
|
|
|
||
|
|
|
||
|
|
COLUMN display_value FORMAT a15 word_wrapped
|
||
|
|
COLUMN value FORMAT a75 word_wrapped
|
||
|
|
COLUMN name FORMAT a35
|
||
|
|
column type format 999
|
||
|
|
|
||
|
|
select x.inst_id inst_id,ksppinm name,ksppity type,
|
||
|
|
ksppstvl value, ksppstdf isdefault
|
||
|
|
from x$ksppi x, x$ksppcv y
|
||
|
|
where (x.indx = y.indx)
|
||
|
|
and ( ksppstdf = 'FALSE'
|
||
|
|
or translate(ksppinm,'_','#') like '##%'
|
||
|
|
-- or translate(ksppinm,'_','#') like '#%'
|
||
|
|
)
|
||
|
|
order by x.inst_id, ksppinm;
|
||
|
|
|
||
|
|
--------------------------------------------- DB PROPERTIES ------------------------------------------------------------
|
||
|
|
prompt
|
||
|
|
prompt DB PROPERTIES
|
||
|
|
prompt _____________
|
||
|
|
|
||
|
|
column property_value format a40
|
||
|
|
|
||
|
|
select property_name, property_value
|
||
|
|
from database_properties
|
||
|
|
order by property_name;
|
||
|
|
|
||
|
|
--------------------------------------------- INSTALLED OPTIONS --------------------------------------------------------
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt INSTALLED OPTIONS
|
||
|
|
prompt _________________
|
||
|
|
|
||
|
|
|
||
|
|
column comp_name format a50
|
||
|
|
|
||
|
|
select comp_name, version, status
|
||
|
|
from dba_registry
|
||
|
|
order by comp_name;
|
||
|
|
|
||
|
|
--------------------------------------------- DB SIZES ------------------------------------------------------------------
|
||
|
|
prompt
|
||
|
|
prompt DB - Sizes
|
||
|
|
prompt __________
|
||
|
|
|
||
|
|
column name format a25 heading "tablespace name"
|
||
|
|
column space_mb format 99g999g990D99 heading "curr df mbytes"
|
||
|
|
column maxspace_mb format 99g999g990D99 heading "max df mbytes"
|
||
|
|
column used format 99g999g990D99 heading "used mbytes"
|
||
|
|
column df_free format 99g999g990D99 heading "curr df free mbytes"
|
||
|
|
column maxdf_free format 99g999g990D99 heading "max df free mbytes"
|
||
|
|
column pct_free format 990D99 heading "% free"
|
||
|
|
column pct_maxfile_free format 990D99 heading "% maxfile free"
|
||
|
|
|
||
|
|
break on report
|
||
|
|
|
||
|
|
compute sum of space_mb on report
|
||
|
|
compute sum of maxspace_mb on report
|
||
|
|
compute sum of df_free on report
|
||
|
|
compute sum of maxdf_free on report
|
||
|
|
compute sum of used on report
|
||
|
|
|
||
|
|
|
||
|
|
select df.tablespace_name name, df.space space_mb, df.maxspace maxspace_mb, (df.space - nvl(fs.freespace,0)) used,
|
||
|
|
nvl(fs.freespace,0) df_free, (nvl(fs.freespace,0) + df.maxspace - df.space) maxdf_free,
|
||
|
|
100 * (nvl(fs.freespace,0) / df.space) pct_free,
|
||
|
|
100 * ((nvl(fs.freespace,0) + df.maxspace - df.space) / df.maxspace) pct_maxfile_free
|
||
|
|
from ( select tablespace_name, sum(bytes)/1024/1024 space, sum(greatest(maxbytes,bytes))/1024/1024 maxspace
|
||
|
|
from dba_data_files
|
||
|
|
group by tablespace_name
|
||
|
|
union all
|
||
|
|
select tablespace_name, sum(bytes)/1024/1024 space, sum(greatest(maxbytes,bytes))/1024/1024 maxspace
|
||
|
|
from dba_temp_files
|
||
|
|
group by tablespace_name
|
||
|
|
) df,
|
||
|
|
( select tablespace_name, sum(bytes)/1024/1024 freespace
|
||
|
|
from dba_free_space
|
||
|
|
group by tablespace_name
|
||
|
|
) fs
|
||
|
|
where df.tablespace_name = fs.tablespace_name(+)
|
||
|
|
order by name;
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
|
||
|
|
|
||
|
|
--------------------------------------------- TABLESPACE INFO --------------------------------------------------------------
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt tablespace info
|
||
|
|
prompt _______________
|
||
|
|
|
||
|
|
column max_mb format 9G999G990D99
|
||
|
|
column curr_mb format 9G999G990D99
|
||
|
|
column free_mb format 9G999G990D99
|
||
|
|
column pct_free format 900D99 heading "%FREE"
|
||
|
|
column NE format 9G999G999D99
|
||
|
|
column SSM format a6
|
||
|
|
column AT format a8
|
||
|
|
column tablespace_name format a20
|
||
|
|
column EM format a10
|
||
|
|
column contents format a15
|
||
|
|
column block_size format 99999 heading bsize
|
||
|
|
|
||
|
|
select A.tablespace_name, block_size, A.contents, extent_management EM, allocation_type AT,
|
||
|
|
segment_space_management ssm, decode(allocation_type, 'UNIFORM',next_extent/1024,'') NE,
|
||
|
|
B.max_mb, B.curr_mb,
|
||
|
|
(B.max_mb - B.curr_mb) + nvl(c.free_mb,0) free_mb,
|
||
|
|
((100/B.max_mb)*(B.max_mb - B.curr_mb + nvl(c.free_mb,0))) pct_free
|
||
|
|
from dba_tablespaces A,
|
||
|
|
( select tablespace_name, sum(bytes)/1024/1024 curr_mb,
|
||
|
|
sum(greatest(bytes, maxbytes))/1024/1024 max_mb
|
||
|
|
from dba_data_files
|
||
|
|
group by tablespace_name
|
||
|
|
union all
|
||
|
|
select tablespace_name, sum(bytes)/1024/1024 curr_mb,
|
||
|
|
sum(greatest(bytes, maxbytes))/1024/1024 max_mb
|
||
|
|
from dba_temp_files
|
||
|
|
group by tablespace_name
|
||
|
|
) B,
|
||
|
|
( select tablespace_name, sum(bytes)/1024/1024 free_mb
|
||
|
|
from dba_free_space
|
||
|
|
group by tablespace_name
|
||
|
|
) C
|
||
|
|
where A.tablespace_name = B.tablespace_name
|
||
|
|
and A.tablespace_name = C.tablespace_name(+)
|
||
|
|
order by tablespace_name;
|
||
|
|
|
||
|
|
--------------------------------------------- DF DETAILS ------------------------------------------------------------------
|
||
|
|
|
||
|
|
column curr_mb format 9G999G990D99
|
||
|
|
column max_mb format 9G9999990D99
|
||
|
|
column incr_mb format 9G999G990D99
|
||
|
|
column file_name format a75
|
||
|
|
--column file_name format a60
|
||
|
|
column tablespace_name format a20
|
||
|
|
break on tablespace_name skip 1
|
||
|
|
set linesize 160
|
||
|
|
set pagesize 999
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt datafiles info
|
||
|
|
prompt ______________
|
||
|
|
|
||
|
|
select A.tablespace_name, file_id, file_name, bytes/1024/1024 curr_mb, autoextensible,
|
||
|
|
maxbytes/1024/1024 max_mb, (increment_by * block_size)/1024/1024 incr_mb
|
||
|
|
from ( select tablespace_name, file_id, file_name, bytes, autoextensible, maxbytes,
|
||
|
|
increment_by
|
||
|
|
from dba_data_files
|
||
|
|
union all
|
||
|
|
select tablespace_name, file_id, file_name, bytes, autoextensible, maxbytes,
|
||
|
|
increment_by
|
||
|
|
from dba_temp_files
|
||
|
|
) A, dba_tablespaces B
|
||
|
|
where A.tablespace_name = B.tablespace_name
|
||
|
|
order by A.tablespace_name, file_name;
|
||
|
|
|
||
|
|
clear breaks;
|
||
|
|
|
||
|
|
--------------------------------------------- OWNER / TBS MATRIX ------------------------------------------------------------------
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt OWNER / TBS MATRIX
|
||
|
|
prompt __________________
|
||
|
|
|
||
|
|
column mb format 9G999G999D99
|
||
|
|
|
||
|
|
break on owner skip 1
|
||
|
|
compute sum of mb on owner
|
||
|
|
|
||
|
|
select owner, tablespace_name, sum(bytes)/1024/1024 mb, count(*) counted
|
||
|
|
from dba_segments
|
||
|
|
group by owner, tablespace_name
|
||
|
|
order by owner, tablespace_name;
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
|
||
|
|
--------------------------------------------- OBJECTS ------------------------------------------------------------------
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt OBJECTS
|
||
|
|
prompt __________________
|
||
|
|
|
||
|
|
break on owner skip 1 on object_type
|
||
|
|
compute sum of counted on owner
|
||
|
|
column counted format 9G999G999G999
|
||
|
|
|
||
|
|
select owner, object_type, status, count(*) counted
|
||
|
|
from dba_objects
|
||
|
|
where owner not in
|
||
|
|
( 'DBSNMP','ORACLE_OCM','OUTLN','PUBLIC','SYS','SYSMAN','SYSTEM', 'TSMSYS','WMSYS','APPQOSSYS',
|
||
|
|
'CTXSYS','EXFSYS','MDSYS','ORDDATA','ORDPLUGINS','ORDSYS','SI_INFORMTN_SCHEMA','XDB'
|
||
|
|
)
|
||
|
|
group by owner, object_type, status
|
||
|
|
order by owner, object_type, status;
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
|
||
|
|
--------------------------------------------- ONLINE REDO INFO ----------------------------------------------------------------
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt ONLINE REDO INFO
|
||
|
|
prompt ________________
|
||
|
|
|
||
|
|
column member format a65
|
||
|
|
column type format a10
|
||
|
|
column status format a15
|
||
|
|
column arch format a4
|
||
|
|
column mb format 9G999G999
|
||
|
|
|
||
|
|
break on type on thread# nodup skip 1 on type nodup on GROUP# nodup
|
||
|
|
|
||
|
|
select type, A.thread#, A.group#, B.member, A.bytes/1024/1024 mb,A.status, arch
|
||
|
|
from ( select group#, thread#, bytes, status, archived arch
|
||
|
|
from v$log
|
||
|
|
union all
|
||
|
|
select group#, thread#, bytes, status, archived arch
|
||
|
|
from v$standby_log
|
||
|
|
) A, v$logfile B
|
||
|
|
where A.group# = B.group#
|
||
|
|
order by type, A.thread#, A.group#, B.member;
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
|
||
|
|
|
||
|
|
--------------------------------------------- REDO SIZES ------------------------------------------------------------------
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt REDO STATISTICS
|
||
|
|
prompt _______________
|
||
|
|
|
||
|
|
column day_arch# format 999G999
|
||
|
|
column graph format a15
|
||
|
|
column dayname format a12
|
||
|
|
column day format a12
|
||
|
|
|
||
|
|
column start_day format a22
|
||
|
|
column end_day format a22
|
||
|
|
column days_between format 99
|
||
|
|
column avg_archived_per_day format a13 heading avg_gen
|
||
|
|
|
||
|
|
select to_char(min(dag), 'DD/MM/YYYY HH24:MI:SS') start_day, to_char(max(dag) + 1 - 1/(24*60*60), 'DD/MM/YYYY HH24:MI:SS') end_day,
|
||
|
|
(max(dag) - min(dag) + 1) days_between,
|
||
|
|
to_char(avg(gen_archived_size),'9G999G999D99') avg_archived_per_day
|
||
|
|
from ( select trunc(completion_time) dag, sum(blocks * block_size)/1024/1024 gen_archived_size
|
||
|
|
from v$archived_log
|
||
|
|
where standby_dest = 'NO'
|
||
|
|
and months_between(trunc(sysdate), trunc(completion_time)) <= 1
|
||
|
|
and completion_time < trunc(sysdate)
|
||
|
|
group by trunc(completion_time)
|
||
|
|
);
|
||
|
|
|
||
|
|
/*
|
||
|
|
archived redo over the (max) last 10 days
|
||
|
|
*/
|
||
|
|
column day_arch_size format 99G999D99
|
||
|
|
column day_arch# format 999G999
|
||
|
|
column graph format a15
|
||
|
|
column dayname format a12
|
||
|
|
column day format a12
|
||
|
|
|
||
|
|
select to_char(day, 'DD/MM/YYYY') day, to_char(day,'DAY') dayname, day_arch_size, day_arch#, graph
|
||
|
|
from ( select trunc(completion_time) day, sum(blocks * block_size)/1024/1024 day_arch_size, count(*) day_arch#,
|
||
|
|
rpad('*',floor(count(*)/10),'*') graph
|
||
|
|
from v$archived_log
|
||
|
|
where standby_dest = 'NO'
|
||
|
|
and completion_time >= trunc(sysdate) - 10
|
||
|
|
group by trunc(completion_time)
|
||
|
|
order by day
|
||
|
|
);
|
||
|
|
|
||
|
|
/*
|
||
|
|
archived redo per hour over the (max) last 2 days
|
||
|
|
*/
|
||
|
|
column hour_arch_size format 99G999D99
|
||
|
|
column hour_arch# format 9G999
|
||
|
|
column graph format a15
|
||
|
|
column dayname format a12
|
||
|
|
column dayhour format a18
|
||
|
|
break on dayname skip 1
|
||
|
|
|
||
|
|
select to_char(dayhour,'DAY') dayname, to_char(dayhour, 'DD/MM/YYYY HH24:MI') dayhour, hour_arch_size, hour_arch#, graph
|
||
|
|
from ( select trunc(completion_time, 'HH') dayhour, sum(blocks * block_size)/1024/1024 hour_arch_size, count(*) hour_arch#,
|
||
|
|
rpad('*',floor(count(*)/4),'*') graph
|
||
|
|
from v$archived_log
|
||
|
|
where standby_dest = 'NO'
|
||
|
|
and completion_time >= trunc(sysdate) - 2
|
||
|
|
group by trunc(completion_time, 'HH')
|
||
|
|
order by dayhour
|
||
|
|
);
|
||
|
|
|
||
|
|
clear breaks;
|
||
|
|
|
||
|
|
--------------------------------------------- USERS ------------------------------------------------------------------
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt USERS
|
||
|
|
prompt _____
|
||
|
|
|
||
|
|
|
||
|
|
column username format a20
|
||
|
|
column password format a20
|
||
|
|
column account_status format a20
|
||
|
|
column default_tablespace format a20
|
||
|
|
column temporary_tablespace format a20
|
||
|
|
column profile format a15
|
||
|
|
|
||
|
|
select username, password, account_status, lock_date, expiry_date, default_tablespace, temporary_tablespace, created, profile
|
||
|
|
from dba_users
|
||
|
|
order by username;
|
||
|
|
|
||
|
|
--------------------------------------------- TS QUOTA ------------------------------------------------------------------
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt TS QUOTA
|
||
|
|
prompt __________
|
||
|
|
prompt(0 = unlimited)
|
||
|
|
|
||
|
|
column mb format 9G999G999D99
|
||
|
|
|
||
|
|
select username, tablespace_name, (decode (max_bytes, -1,0, max_bytes))/1024/1024 mb
|
||
|
|
from dba_ts_quotas
|
||
|
|
order by username, tablespace_name;
|
||
|
|
|
||
|
|
--------------------------------------------- PRIVILEGES ------------------------------------------------------------------
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt SYS PRIVILEGES
|
||
|
|
prompt ______________
|
||
|
|
|
||
|
|
column grantee format a30
|
||
|
|
break on grantee skip 1
|
||
|
|
|
||
|
|
select grantee, privilege, admin_option
|
||
|
|
from dba_sys_privs
|
||
|
|
where grantee not in
|
||
|
|
( 'SYS', 'SYSTEM', 'TSMSYS', 'WMSYS','SYSMAN', 'OUTLN', 'DBSNMP', 'PERFSTAT', 'UPTIME',
|
||
|
|
'ANALYZETHIS', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE', 'DBA', 'DELETE_CATALOG_ROLE',
|
||
|
|
'EXECUTE_CATALOG_ROLE', 'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS', 'HS_ADMIN_ROLE',
|
||
|
|
'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'OEM_MONITOR', 'OUTLN', 'PANDORA', 'PERFSTAT',
|
||
|
|
'PUBLIC', 'SELECT_CATALOG_ROLE', 'SYS', 'SYSTEM', 'WMSYS', 'WM_ADMIN_ROLE', 'TIVOLI_ROLE',
|
||
|
|
'CONNECT', 'JAVADEBUGPRIV', 'RECOVERY_CATALOG_OWNER', 'RESOURCE', 'TIVOLI', 'JAVASYSPRIV',
|
||
|
|
'GLOBAL_AQ_USER_ROLE', 'JAVAUSERPRIV', 'JAVAIDPRIV', 'EJBCLIENT', 'JAVA_ADMIN', 'JAVA_DEPLOY',
|
||
|
|
'MGMT_USER','MGMT_VIEW','OEM_ADVISOR','ORACLE_OCM','SCHEDULER_ADMIN','DIP','APPQOSSYS', 'ANONYMOUS',
|
||
|
|
'CTXSYS','EXFSYS','MDSYS','ORDDATA','ORDPLUGINS','ORDSYS','SI_INFORMTN_SCHEMA','XDB','DATAPUMP_EXP_FULL_DATABASE',
|
||
|
|
'DATAPUMP_IMP_FULL_DATABASE','XDBADMIN','XDB_SET_INVOKER','XDB_WEBSERVICES','XDB_WEBSERVICES_OVER_HTTP','XDB_WEBSERVICES_WITH_PUBLIC'
|
||
|
|
)
|
||
|
|
order by grantee, privilege;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt ROLE PRIVILEGES
|
||
|
|
prompt _______________
|
||
|
|
|
||
|
|
column grantee format a30
|
||
|
|
break on grantee skip 1
|
||
|
|
|
||
|
|
select grantee, granted_role, admin_option, default_role
|
||
|
|
from dba_role_privs
|
||
|
|
where grantee not in
|
||
|
|
( 'SYS', 'SYSTEM', 'TSMSYS', 'WMSYS','SYSMAN', 'OUTLN', 'DBSNMP', 'PERFSTAT', 'UPTIME',
|
||
|
|
'ANALYZETHIS', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE', 'DBA', 'DELETE_CATALOG_ROLE',
|
||
|
|
'EXECUTE_CATALOG_ROLE', 'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS', 'HS_ADMIN_ROLE',
|
||
|
|
'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'OEM_MONITOR', 'OUTLN', 'PANDORA', 'PERFSTAT',
|
||
|
|
'PUBLIC', 'SELECT_CATALOG_ROLE', 'SYS', 'SYSTEM', 'WMSYS', 'WM_ADMIN_ROLE', 'TIVOLI_ROLE',
|
||
|
|
'CONNECT', 'JAVADEBUGPRIV', 'RECOVERY_CATALOG_OWNER', 'RESOURCE', 'TIVOLI', 'JAVASYSPRIV',
|
||
|
|
'GLOBAL_AQ_USER_ROLE', 'JAVAUSERPRIV', 'JAVAIDPRIV', 'EJBCLIENT', 'JAVA_ADMIN', 'JAVA_DEPLOY',
|
||
|
|
'MGMT_USER','MGMT_VIEW','OEM_ADVISOR','ORACLE_OCM','SCHEDULER_ADMIN','DIP', 'CTXSYS', 'DATAPUMP_EXP_FULL_DATABASE',
|
||
|
|
'DATAPUMP_IMP_FULL_DATABASE','EXFSYS','MDSYS','ORDSYS','XDB','XDBADMIN','XDB_SET_INVOKER','XDB_WEBSERVICES','XDB_WEBSERVICES_OVER_HTTP','XDB_WEBSERVICES_WITH_PUBLIC'
|
||
|
|
)
|
||
|
|
order by grantee, granted_role;
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt OBJECT PRIVILEGES
|
||
|
|
prompt _________________
|
||
|
|
|
||
|
|
column grantee format a30
|
||
|
|
column privilege format a30
|
||
|
|
|
||
|
|
break on grantee skip 1
|
||
|
|
|
||
|
|
select grantee, owner, table_name, privilege, grantable, hierarchy
|
||
|
|
from dba_tab_privs
|
||
|
|
where grantee not in
|
||
|
|
( 'SYS', 'SYSTEM', 'TSMSYS', 'WMSYS','SYSMAN', 'OUTLN', 'DBSNMP', 'PERFSTAT', 'UPTIME',
|
||
|
|
'ANALYZETHIS', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE', 'DBA', 'DELETE_CATALOG_ROLE',
|
||
|
|
'EXECUTE_CATALOG_ROLE', 'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS', 'HS_ADMIN_ROLE',
|
||
|
|
'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'OEM_MONITOR', 'OUTLN', 'PANDORA', 'PERFSTAT',
|
||
|
|
'PUBLIC', 'SELECT_CATALOG_ROLE', 'SYS', 'SYSTEM', 'WMSYS', 'WM_ADMIN_ROLE', 'TIVOLI_ROLE',
|
||
|
|
'CONNECT', 'JAVADEBUGPRIV', 'RECOVERY_CATALOG_OWNER', 'RESOURCE', 'TIVOLI', 'JAVASYSPRIV',
|
||
|
|
'GLOBAL_AQ_USER_ROLE', 'JAVAUSERPRIV', 'JAVAIDPRIV', 'EJBCLIENT', 'JAVA_ADMIN', 'JAVA_DEPLOY',
|
||
|
|
'MGMT_USER','MGMT_VIEW','OEM_ADVISOR','ORACLE_OCM','SCHEDULER_ADMIN','DIP','APPQOSSYS', 'ANONYMOUS',
|
||
|
|
'CTXSYS','EXFSYS','MDSYS','ORDDATA','ORDPLUGINS','ORDSYS','SI_INFORMTN_SCHEMA','XDB','DATAPUMP_EXP_FULL_DATABASE',
|
||
|
|
'DATAPUMP_IMP_FULL_DATABASE','ADM_PARALLEL_EXECUTE_TASK','CTXAPP','DBFS_ROLE','HS_ADMIN_EXECUTE_ROLE','HS_ADMIN_SELECT_ROLE',
|
||
|
|
'ORDADMIN','XDBADMIN','XDB_SET_INVOKER','XDB_WEBSERVICES','XDB_WEBSERVICES_OVER_HTTP','XDB_WEBSERVICES_WITH_PUBLIC'
|
||
|
|
)
|
||
|
|
order by grantee, owner, table_name, privilege;
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt NON STANDARD ROLES
|
||
|
|
prompt __________________
|
||
|
|
|
||
|
|
column grantee format a30
|
||
|
|
column privilege format a30
|
||
|
|
|
||
|
|
break on grantee skip 1
|
||
|
|
|
||
|
|
select role, password_required
|
||
|
|
from dba_roles
|
||
|
|
where role not in
|
||
|
|
( 'SYS', 'SYSTEM', 'TSMSYS', 'WMSYS','SYSMAN', 'OUTLN', 'DBSNMP', 'PERFSTAT', 'UPTIME',
|
||
|
|
'ANALYZETHIS', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE', 'DBA', 'DELETE_CATALOG_ROLE',
|
||
|
|
'EXECUTE_CATALOG_ROLE', 'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS', 'HS_ADMIN_ROLE',
|
||
|
|
'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'OEM_MONITOR', 'OUTLN', 'PANDORA', 'PERFSTAT',
|
||
|
|
'PUBLIC', 'SELECT_CATALOG_ROLE', 'SYS', 'SYSTEM', 'WMSYS', 'WM_ADMIN_ROLE', 'TIVOLI_ROLE',
|
||
|
|
'CONNECT', 'JAVADEBUGPRIV', 'RECOVERY_CATALOG_OWNER', 'RESOURCE', 'TIVOLI', 'JAVASYSPRIV',
|
||
|
|
'GLOBAL_AQ_USER_ROLE', 'JAVAUSERPRIV', 'JAVAIDPRIV', 'EJBCLIENT', 'JAVA_ADMIN', 'JAVA_DEPLOY',
|
||
|
|
'MGMT_USER','MGMT_VIEW','OEM_ADVISOR','ORACLE_OCM','SCHEDULER_ADMIN','DIP','APPQOSSYS', 'ANONYMOUS',
|
||
|
|
'CTXSYS','EXFSYS','MDSYS','ORDDATA','ORDPLUGINS','ORDSYS','SI_INFORMTN_SCHEMA','XDB','DATAPUMP_EXP_FULL_DATABASE',
|
||
|
|
'DATAPUMP_IMP_FULL_DATABASE','ADM_PARALLEL_EXECUTE_TASK','CTXAPP','DBFS_ROLE','HS_ADMIN_EXECUTE_ROLE','HS_ADMIN_SELECT_ROLE',
|
||
|
|
'ORDADMIN','XDBADMIN','XDB_SET_INVOKER','XDB_WEBSERVICES','XDB_WEBSERVICES_OVER_HTTP','XDB_WEBSERVICES_WITH_PUBLIC',
|
||
|
|
'AUTHENTICATEDUSER','JMXSERVER'
|
||
|
|
)
|
||
|
|
order by role;
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
|
||
|
|
--------------------------------------------- SYNONYMS ------------------------------------------------------------------
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt SYNONYMS
|
||
|
|
prompt _________
|
||
|
|
|
||
|
|
break on owner skip 1
|
||
|
|
|
||
|
|
select owner, synonym_name, table_owner, table_name
|
||
|
|
from dba_synonyms
|
||
|
|
where owner not in
|
||
|
|
( 'SYS', 'SYSTEM', 'TSMSYS', 'WMSYS','SYSMAN', 'OUTLN', 'DBSNMP', 'PERFSTAT', 'UPTIME',
|
||
|
|
'ANALYZETHIS', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE', 'DBA', 'DELETE_CATALOG_ROLE',
|
||
|
|
'EXECUTE_CATALOG_ROLE', 'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS', 'HS_ADMIN_ROLE',
|
||
|
|
'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'OEM_MONITOR', 'OUTLN', 'PANDORA', 'PERFSTAT',
|
||
|
|
'PUBLIC', 'SELECT_CATALOG_ROLE', 'SYS', 'SYSTEM', 'WMSYS', 'WM_ADMIN_ROLE', 'TIVOLI_ROLE',
|
||
|
|
'CONNECT', 'JAVADEBUGPRIV', 'RECOVERY_CATALOG_OWNER', 'RESOURCE', 'TIVOLI', 'JAVASYSPRIV',
|
||
|
|
'GLOBAL_AQ_USER_ROLE', 'JAVAUSERPRIV', 'JAVAIDPRIV', 'EJBCLIENT', 'JAVA_ADMIN', 'JAVA_DEPLOY',
|
||
|
|
'MGMT_USER','MGMT_VIEW','OEM_ADVISOR','ORACLE_OCM','SCHEDULER_ADMIN','DIP','APPQOSSYS', 'ANONYMOUS',
|
||
|
|
'CTXSYS','EXFSYS','MDSYS','ORDDATA','ORDPLUGINS','ORDSYS','SI_INFORMTN_SCHEMA','XDB','DATAPUMP_EXP_FULL_DATABASE',
|
||
|
|
'DATAPUMP_IMP_FULL_DATABASE','ADM_PARALLEL_EXECUTE_TASK','CTXAPP','DBFS_ROLE','HS_ADMIN_EXECUTE_ROLE','HS_ADMIN_SELECT_ROLE',
|
||
|
|
'ORDADMIN','XDBADMIN','XDB_SET_INVOKER','XDB_WEBSERVICES','XDB_WEBSERVICES_OVER_HTTP','XDB_WEBSERVICES_WITH_PUBLIC',
|
||
|
|
'AUTHENTICATEDUSER','JMXSERVER'
|
||
|
|
)
|
||
|
|
order by owner, synonym_name;
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt NON DEFAULT PUBLIC SYNONYMS
|
||
|
|
prompt ____________________________
|
||
|
|
|
||
|
|
break on table_owner skip 1
|
||
|
|
|
||
|
|
select table_owner, table_name, synonym_name
|
||
|
|
from dba_synonyms
|
||
|
|
where owner = 'PUBLIC'
|
||
|
|
and table_owner not in
|
||
|
|
( 'SYS', 'SYSTEM', 'TSMSYS', 'WMSYS','SYSMAN', 'OUTLN', 'DBSNMP', 'PERFSTAT', 'UPTIME',
|
||
|
|
'ANALYZETHIS', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE', 'DBA', 'DELETE_CATALOG_ROLE',
|
||
|
|
'EXECUTE_CATALOG_ROLE', 'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS', 'HS_ADMIN_ROLE',
|
||
|
|
'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'OEM_MONITOR', 'OUTLN', 'PANDORA', 'PERFSTAT',
|
||
|
|
'PUBLIC', 'SELECT_CATALOG_ROLE', 'SYS', 'SYSTEM', 'WMSYS', 'WM_ADMIN_ROLE', 'TIVOLI_ROLE',
|
||
|
|
'CONNECT', 'JAVADEBUGPRIV', 'RECOVERY_CATALOG_OWNER', 'RESOURCE', 'TIVOLI', 'JAVASYSPRIV',
|
||
|
|
'GLOBAL_AQ_USER_ROLE', 'JAVAUSERPRIV', 'JAVAIDPRIV', 'EJBCLIENT', 'JAVA_ADMIN', 'JAVA_DEPLOY',
|
||
|
|
'MGMT_USER','MGMT_VIEW','OEM_ADVISOR','ORACLE_OCM','SCHEDULER_ADMIN','DIP','APPQOSSYS', 'ANONYMOUS',
|
||
|
|
'CTXSYS','EXFSYS','MDSYS','ORDDATA','ORDPLUGINS','ORDSYS','SI_INFORMTN_SCHEMA','XDB','DATAPUMP_EXP_FULL_DATABASE',
|
||
|
|
'DATAPUMP_IMP_FULL_DATABASE','ADM_PARALLEL_EXECUTE_TASK','CTXAPP','DBFS_ROLE','HS_ADMIN_EXECUTE_ROLE','HS_ADMIN_SELECT_ROLE',
|
||
|
|
'ORDADMIN','XDBADMIN','XDB_SET_INVOKER','XDB_WEBSERVICES','XDB_WEBSERVICES_OVER_HTTP','XDB_WEBSERVICES_WITH_PUBLIC',
|
||
|
|
'AUTHENTICATEDUSER','JMXSERVER'
|
||
|
|
)
|
||
|
|
order by table_owner, table_name;
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
|
||
|
|
--------------------------------------------- JOBS ------------------------------------------------------------------
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt JOBS
|
||
|
|
prompt ____
|
||
|
|
|
||
|
|
column what format a60 word_wrapped
|
||
|
|
column interval format a30 word_wrapped
|
||
|
|
|
||
|
|
break on job skip 1
|
||
|
|
|
||
|
|
select job, what, interval, failures, broken, schema_user
|
||
|
|
from dba_jobs;
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
|
||
|
|
--------------------------------------------- SCHEDULER ------------------------------------------------------------------
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt SCHEDULER
|
||
|
|
prompt _________
|
||
|
|
|
||
|
|
prompt JOBS
|
||
|
|
prompt -----
|
||
|
|
|
||
|
|
set linesize 200
|
||
|
|
|
||
|
|
column interval format a30 word_wrapped
|
||
|
|
column what format a50 word_wrapped
|
||
|
|
column failures format 999
|
||
|
|
column broken format a1
|
||
|
|
column schema_user format a20
|
||
|
|
column last_date format a10
|
||
|
|
column last_sec format a10
|
||
|
|
|
||
|
|
select
|
||
|
|
job, schema_user, what, last_date, last_sec, interval, broken, failures
|
||
|
|
from
|
||
|
|
dba_jobs
|
||
|
|
order by
|
||
|
|
job
|
||
|
|
;
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt
|
||
|
|
prompt DEFINED SCHEDULER JOBS
|
||
|
|
prompt -----------------------
|
||
|
|
|
||
|
|
set linesize 150
|
||
|
|
column owner format a15
|
||
|
|
column state format a10
|
||
|
|
column failure_count format 999 heading FC
|
||
|
|
column run_count format 99999 heading RC
|
||
|
|
column job_name format a28
|
||
|
|
column next_run_date format a35
|
||
|
|
column last_start_date format a35
|
||
|
|
|
||
|
|
select
|
||
|
|
owner, job_name, state, run_count, failure_count,
|
||
|
|
to_char(last_start_date, 'DD/MM/YYYY HH24:MI:SS TZR') last_start_date,
|
||
|
|
to_char(next_run_date, 'DD/MM/YYYY HH24:MI:SS TZR') next_run_date
|
||
|
|
from
|
||
|
|
dba_scheduler_jobs
|
||
|
|
order by
|
||
|
|
owner, job_name
|
||
|
|
;
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt
|
||
|
|
prompt LAST 10 RUNS PER SCHEDULER JOB
|
||
|
|
prompt -------------------------------
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
set linesize 150
|
||
|
|
column log_date format a20
|
||
|
|
column req_start_date format a35
|
||
|
|
column actual_start_date format a35
|
||
|
|
column run_duration format a14
|
||
|
|
column status format a10
|
||
|
|
column owner format a15
|
||
|
|
column job_name format a28
|
||
|
|
break on owner skip 1 on job_name skip 1
|
||
|
|
|
||
|
|
select
|
||
|
|
owner, job_name, -- to_char(log_date, 'DD/MM/YYYY HH24:MI:SS') log_date,
|
||
|
|
to_char(req_start_date, 'DD/MM/YYYY HH24:MI:SS TZR') req_start_date,
|
||
|
|
to_char(actual_start_date, 'DD/MM/YYYY HH24:MI:SS TZR') actual_start_date,
|
||
|
|
run_duration, status
|
||
|
|
from
|
||
|
|
( select
|
||
|
|
owner, job_name, log_date, req_start_date, actual_start_date,
|
||
|
|
run_duration, status,
|
||
|
|
row_number () over
|
||
|
|
( partition by owner, job_name
|
||
|
|
order by log_date desc
|
||
|
|
) rn
|
||
|
|
from
|
||
|
|
dba_scheduler_job_run_details
|
||
|
|
where
|
||
|
|
job_name not like 'ORA$AT_%' -- filter out autotasks
|
||
|
|
) jrd
|
||
|
|
where
|
||
|
|
rn <= 10
|
||
|
|
order by
|
||
|
|
owner, job_name, jrd.log_date desc
|
||
|
|
;
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt
|
||
|
|
prompt 10 MOST RECENT JOB RUNS
|
||
|
|
prompt ------------------------
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
set linesize 150
|
||
|
|
column start_date format a20
|
||
|
|
column run_duration format a14
|
||
|
|
column status format a10
|
||
|
|
column owner format a15
|
||
|
|
column job_name format a30
|
||
|
|
|
||
|
|
select
|
||
|
|
to_char(actual_start_date, 'DD/MM/YYYY HH24:MI:SS') start_date,
|
||
|
|
owner, job_name, run_duration, status
|
||
|
|
from
|
||
|
|
( select
|
||
|
|
actual_start_date, owner, job_name, run_duration, status
|
||
|
|
from
|
||
|
|
dba_scheduler_job_run_details
|
||
|
|
where
|
||
|
|
job_name not like 'ORA$AT_%' -- filter out autotasks
|
||
|
|
order by
|
||
|
|
actual_start_date desc
|
||
|
|
) jrd
|
||
|
|
where
|
||
|
|
rownum <= 10
|
||
|
|
;
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt
|
||
|
|
prompt DEFINED AUTOTASKS
|
||
|
|
prompt ------------------
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
column client_name format a35
|
||
|
|
|
||
|
|
select
|
||
|
|
client_name, status
|
||
|
|
from
|
||
|
|
dba_autotask_operation
|
||
|
|
order by
|
||
|
|
client_name
|
||
|
|
;
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt
|
||
|
|
prompt AUTOTASK WINDOWS
|
||
|
|
prompt -----------------
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
set linesize 150
|
||
|
|
column window_next_time format a45
|
||
|
|
|
||
|
|
select
|
||
|
|
*
|
||
|
|
from
|
||
|
|
dba_autotask_window_clients
|
||
|
|
;
|
||
|
|
|
||
|
|
prompt
|
||
|
|
prompt
|
||
|
|
prompt LAST 10 RUNS PER AUTOTASK
|
||
|
|
prompt --------------------------
|
||
|
|
|
||
|
|
column client_name format a35
|
||
|
|
column job_duration format a14
|
||
|
|
column job_start_time format a45
|
||
|
|
column job_status format a10
|
||
|
|
|
||
|
|
break on client_name skip 1
|
||
|
|
|
||
|
|
select
|
||
|
|
client_name, job_start_time, job_duration, job_status, job_error
|
||
|
|
from
|
||
|
|
( select
|
||
|
|
client_name, job_status, job_start_time, job_duration, job_error,
|
||
|
|
row_number () over
|
||
|
|
( partition by client_name
|
||
|
|
order by job_start_time desc
|
||
|
|
) rn
|
||
|
|
from
|
||
|
|
dba_autotask_job_history
|
||
|
|
)
|
||
|
|
where
|
||
|
|
rn <= 10
|
||
|
|
order by
|
||
|
|
client_name, job_start_time desc
|
||
|
|
;
|
||
|
|
|
||
|
|
clear breaks
|
||
|
|
|
||
|
|
|
||
|
|
--------------------------------------------- THE END ------------------------------------------------------------------
|
||
|
|
spool off
|
||
|
|
|