column Headroom format 999999999999.0
column Adjust_SCN format 999999999999.0
column ALL_SCN format 999999999999.0
column "SCN Headroom Health" format a21
select tim "Date_Time",
scn,
round((chk16kscn-scn)/24/3600/16/1024,1) "Headroom",
round((chk16kscn-scn)/1024/1024/1024,1) "Adjust_SCN",
round(chk16kscn/1024/1024/1024,1) "ALL_SCN",
case when round((chk16kscn-scn)/24/3600/16/1024,1) > 62 then ‘SCN Headroom Is Good.‘
when round((chk16kscn-scn)/24/3600/16/1024,1) < 10 then ‘<div align="left"><font color="red">SCN Headroom Is Bad.</font></div>‘
end "SCN Headroom Health"
from (
select tim, scn,
((((to_number(to_char(tim,‘YYYY‘))-1988)*12*31*24*60*60)
+ ((to_number(to_char(tim,‘MM‘))-1)*31*24*60*60)
+ (((to_number(to_char(tim,‘DD‘))-1))*24*60*60)
+ (to_number(to_char(tim,‘HH24‘))*60*60)
+ (to_number(to_char(tim,‘MI‘))*60)
+ (to_number(to_char(tim,‘SS‘)))) * (16*1024)) chk16kscn
from (select sysdate tim,dbms_flashback.get_system_change_number scn
from v$instance
where version like ‘10.%‘
or version like ‘11.1%‘
or version like ‘11.2.0.1%‘
)
)
union all
select tim "Date_Time",
scn,
round((chk32kscn-scn)/24/3600/32/1024,1) "Headroom",
round((chk32kscn-scn)/1024/1024/1024,1) "Adjust_SCN",
round(chk32kscn/1024/1024/1024,1) "ALL_SCN",
case when round((chk32kscn-scn)/24/3600/32/1024,1) > 62 then ‘SCN Headroom Is Good.‘
when round((chk32kscn-scn)/24/3600/32/1024,1) < 10 then ‘<div align="left"><font color="red">SCN Headroom Is Bad.</font></div>‘
end "SCN Headroom Health"
from (
select tim, scn,
((((to_number(to_char(tim,‘YYYY‘))-1988)*12*31*24*60*60)
+ ((to_number(to_char(tim,‘MM‘))-1)*31*24*60*60)
+ (((to_number(to_char(tim,‘DD‘))-1))*24*60*60)
+ (to_number(to_char(tim,‘HH24‘))*60*60)
+ (to_number(to_char(tim,‘MI‘))*60)
+ (to_number(to_char(tim,‘SS‘)))) * (32*1024)) chk32kscn
from (select sysdate tim,dbms_flashback.get_system_change_number scn
from v$instance
where version like ‘11.2.0.2%‘
or version like ‘11.2.0.3%‘
or version like ‘11.2.0.4%‘
or version like ‘12.%‘
)
);
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#overview">Back to Database Information</a>
prompt <a href="#top">Back to Top</a>
--prompt <br />
-- +----------------------------------------------------------------------------+
-- | High Water Mark Statistics |
-- +----------------------------------------------------------------------------+
prompt <a name="103"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>High Water Mark Statistics</b></font>
clear columns breaks computes
column name format a50 heading ‘Statistic Name‘ print entmap off
column version format a10 heading ‘Version‘ print entmap off
column highwater format 9,999,999,999,999,999 heading ‘Highwater‘ print entmap off
column last_value format 9,999,999,999,999,999 heading ‘Last Value‘ print entmap off
column description format a150 heading ‘Description‘ print entmap off
select name, version, highwater, last_value, description
from dba_high_water_mark_statistics;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#overview">Back to Database Information</a>
prompt <a href="#top">Back to Top</a>
prompt <br />
-- ==============================================================================
-- Storage
-- ==============================================================================
prompt <a name="storage"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Storage Statistics</b></font>
prompt <ul>-
<li><a href=‘#21‘>Tablespace Statistics</a></li>-
<li><a href=‘#22‘>Datafiles and Tempfiles Statistics</a></li>-
<li><a href=‘#23‘>Total Segments Size</a></li>-
<li><a href=‘#24‘>Top 10 Tables</a></li>-
<li><a href=‘#25‘>Top 10 Indexes</a></li>-
</ul>
prompt <a href="#top">Back to Top</a>
-- +----------------------------------------------------------------------------+
-- | Tablespace |
-- +----------------------------------------------------------------------------+
prompt <a name="21"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Tablespace Statistics</b></font>
clear columns breaks computes
column tablespace_name format "a&tablespace_name" heading ‘Tablespace‘ print entmap off
column type format a9 heading ‘Type‘ print entmap off
column status heading ‘Status‘ print entmap off
column bigfile format a7 heading ‘Bigfile‘ print entmap off
column total_mb format 999,999,990.00 heading ‘Total_MB‘ print entmap off
column free_mb format 999,999,990.00 heading ‘Free_MB‘ print entmap off
column used_mb format 999,999,990.00 heading ‘Used_MB‘ print entmap off
column "used%" heading ‘Used %‘ print entmap off
column extent_management format a10 heading ‘Extent Management‘ print entmap off
column segment_space_management format a13 heading ‘Segment Space Management‘ print entmap off
column compute format a7 heading ‘Compute‘ print entmap off
break on report
compute count label ‘Count:‘ of type on report
compute sum label ‘Total:‘ of total_mb used_mb free_mb on report
select a.tablespace_name,
a.type,
space total_mb,
nvl (free_space, 0) free_mb,
space - nvl (free_space, 0) used_mb,
case
when trunc ( (1 - nvl (free_space, 0) / space) * 100) >= 90
then ‘<div align="right"><font color="red">‘ || to_char (round ( (1 - nvl (free_space, 0) / space) * 100, 2), ‘990.99‘) || ‘</font></div>‘
else ‘<div align="right">‘ || to_char (round ( (1 - nvl (free_space, 0) / space) * 100, 2), ‘990.99‘) || ‘</div>‘
end "used%",
a.bigfile,
decode(a.status,‘ONLINE‘,a.status,‘<div align="left"><font color="red">‘ || a.status || ‘</font></div>‘) status,
a.extent_management,
a.segment_space_management
from ( select d.tablespace_name,
t.contents type,
t.status,
t.bigfile,
t.extent_management,
t.segment_space_management,
round (sum (bytes) / 1024 / 1024, 2) space
from dba_data_files d, dba_tablespaces t
where d.tablespace_name = t.tablespace_name
group by d.tablespace_name,
t.contents,
t.status,
t.bigfile,
t.extent_management,
t.segment_space_management) a,
( select tablespace_name,
round (sum (bytes) / 1024 / 1024, 2) free_space
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select a.tablespace_name,
a.type,
space total_mb,
nvl (free_space, 0) free_mb,
nvl (used_space, 0) as used_mb,
case
when trunc ( (nvl (used_space, 0) / space) * 100) >= 90
then ‘<div align="right"><font color="red">‘ || to_char (round ( (nvl (used_space, 0) / space) * 100, 2), ‘990.99‘) || ‘</font></div>‘
else ‘<div align="right">‘ || to_char (round ( (nvl (used_space, 0) / space) * 100, 2), ‘990.99‘) || ‘</div>‘
end "used%",
a.bigfile,
decode(a.status,‘ONLINE‘,a.status,‘<div align="left"><font color="red">‘ || a.status || ‘</font></div>‘) status,
a.extent_management,
a.segment_space_management
from ( select d.tablespace_name,
t.contents type,
t.status,
t.bigfile,
t.extent_management,
t.segment_space_management,
round (sum (bytes) / 1024 / 1024, 2) space
from dba_temp_files d, dba_tablespaces t
where d.tablespace_name = t.tablespace_name
group by d.tablespace_name,
t.contents,
t.status,
t.bigfile,
t.extent_management,
t.segment_space_management) a,
( select tablespace_name,
round (sum (bytes_used) / 1024 / 1024, 2) used_space,
round (sum (bytes_free) / 1024 / 1024, 2) free_space
from v$temp_space_header
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
order by 6 desc;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#storage">Back to Strorage Statistics</a>
prompt <a href="#top">Back to Top</a>
--prompt <br />
-- +----------------------------------------------------------------------------+
-- | Datafiles And Tempfiles |
-- +----------------------------------------------------------------------------+
prompt <a name="22"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Datafiles And Tempfiles</b></font>
clear columns breaks computes
column tablespace_name format "a&tablespace_name" heading ‘Tablespace‘ print entmap
column file_name format "a&file_name" heading ‘File_Name‘ print entmap
column total_mb format 999,999,990.00 heading ‘Total_MB‘ print entmap
column max_mb format 999,999,990.00 heading ‘Max_MB‘ print entmap
column autoextensible format a15 heading ‘Autoextensible‘ print entmap
column status format a15 heading ‘Status‘ print entmap
break on tablespace_name on report
compute count label ‘Count:‘ of file_name on report
compute sum label ‘Total:‘ of total_mb max_mb on report
select tablespace_name,
file_name,
bytes / 1024 / 1024 total_mb,
maxbytes / 1024 / 1024 max_mb,
autoextensible,
decode(online_status,‘ONLINE‘,online_status,‘SYSTEM‘,online_status,‘<div align="left"><font color="red">‘ || online_status || ‘</font></div>‘) status
from dba_data_files
union all
select a.tablespace_name,
a.file_name,
a.bytes / 1024 / 1024 total_mb,
a.maxbytes / 1024 / 1024 max_mb,
a.autoextensible,
decode(b.status,‘ONLINE‘,b.status,‘<div align="left"><font color="red">‘ || b.status || ‘</font></div>‘) status
from dba_temp_files a, v$tempfile b
where a.file_id = b.file#;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#storage">Back to Strorage Statistics</a>
prompt <a href="#top">Back to Top</a>
--prompt <br />
-- +----------------------------------------------------------------------------+
-- | Segment Statistics |
-- +----------------------------------------------------------------------------+
prompt <a name="23"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Total Segments Size</b></font>
set markup html table ‘class="t_200px"‘
clear columns breaks computes
column total_segments_mb format 999,999,990.99 heading ‘Total Segments Size(MB)‘ print entmap off
select ‘<div align="center">‘ || round (sum (bytes) / 1024 / 1024, 2) || ‘</div>‘ total_segments_mb
from dba_segments;
set markup html table ‘border_collapse: collapse;border="0";cellpadding="0";cellspacing="1"‘
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#storage">Back to Strorage Statistics</a>
prompt <a href="#top">Back to Top</a>
prompt <a name="24"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Top 10 Tables</b></font>
clear columns breaks computes
column owner format a50 heading ‘Owner‘ print entmap off
column table_name format a50 heading ‘Table_Name‘ print entmap off
column partitioned format a15 heading ‘Partition‘ print entmap off
column size_mb format 999,999,990.99 heading ‘Size_MB‘ print entmap off
select *
from ( select t.owner,
t.table_name,
t.partitioned,
round (sum (s.bytes) / 1024 / 1024, 2) size_mb
from dba_tables t, dba_segments s
where t.owner = s.owner
and s.segment_name = t.table_name
and s.segment_type like ‘TABLE%‘
group by t.owner, t.table_name, t.partitioned
order by size_mb desc)
where rownum <= 10;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#storage">Back to Strorage Statistics</a>
prompt <a href="#top">Back to Top</a>
prompt <a name="25"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Top 10 Indexes</b></font>
column owner format a50 heading ‘Owner‘ print entmap off
column table_name format a50 heading ‘Table_Name‘ print entmap off
column index_name format a50 heading ‘Index_Name‘ print entmap off
select *
from ( select i.owner,
i.table_name,
i.index_name,
i.partitioned,
sum (s.bytes) / 1024 / 1024 size_mb
from dba_indexes i, dba_segments s
where s.owner = i.owner
and s.segment_name = i.index_name
and s.segment_type like ‘INDEX%‘
group by i.owner,
i.table_name,
i.index_name,
i.partitioned
order by size_mb desc)
where rownum <= 10;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#storage">Back to Strorage Statistics</a>
prompt <a href="#top">Back to Top</a>
-- ==============================================================================
-- Undo Segments
-- ==============================================================================
prompt <a name="undo"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Undo Segments Information</b></font>
prompt <ul>-
<li><a href=‘#31‘>Undo Parameters</a></li>-
<li><a href=‘#32‘>Undo Segments Statistics</a></li>-
</ul>
prompt <a href="#top">Back to Top</a>
-- +----------------------------------------------------------------------------+
-- | Undo Parameters |
-- +----------------------------------------------------------------------------+
prompt <a name="31"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Undo Parameters</b></font>
clear columns breaks computes
column instance format a15 heading ‘Instance‘ print entmap off
column name format a15 heading ‘Name‘ print entmap off
column value format a10 heading ‘Value‘ print entmap off
break on instance
select i.instance_name instance, p.name, p.value
from gv$parameter p, gv$instance i
where name like ‘%undo%‘
and p.inst_id = i.inst_id
order by 1;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#undo">Back to Undo Segments Information</a>
prompt <a href="#top">Back to Top</a>
--prompt <br />
-- +----------------------------------------------------------------------------+
-- | Undo Segments |
-- +----------------------------------------------------------------------------+
prompt <a name="32"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Undo Segments Statistics</b></font>
clear columns breaks computes
column instance_name format a10 heading ‘Instance‘ entmap off
column tablespace_name format a9 heading ‘Tablspace‘ entmap off
column roll_name format a20 heading ‘Roll Name‘ entmap off
column initial_extent format 999,999,999 heading ‘Init Extent‘ entmap off
column next_extent format 999,999,999 heading ‘Next Extent‘ entmap off
column min_extents format 999,999,999 heading ‘Min Extents‘ entmap off
column max_extents format 999,999,999 heading ‘Max Extents‘ entmap off
column status format a8 heading ‘Status‘ entmap off
column wraps format 999,999,999 heading ‘Wraps‘ entmap off
column shrinks format 999,999,999 heading ‘Shrinks‘ entmap off
column optsize format 999,999,999,999 heading ‘Optsize‘ entmap off
column bytes format 999,999,999,999 heading ‘Bytes‘ entmap off
column extents format 999,999,999 heading ‘Extents‘ entmap off
clear computes breaks
break on report on instance_name on tablespace
compute sum label ‘Total:‘ of bytes extents shrinks wraps on report
select instance_name,
a.tablespace_name,
a.owner || ‘.‘ || a.segment_name roll_name,
a.initial_extent,
a.next_extent,
a.min_extents,
a.max_extents,
a.status,
b.bytes,
b.extents,
d.shrinks,
d.wraps,
d.optsize
from dba_rollback_segs a,
dba_segments b,
v$rollname c,
v$rollstat d,
gv$parameter p,
gv$instance i
where a.segment_name = b.segment_name
and a.segment_name = c.name(+)
and c.usn = d.usn(+)
and p.name(+) = ‘undo_tablespace‘
and p.value(+) = a.tablespace_name
and p.inst_id = i.inst_id(+)
order by a.tablespace_name, a.segment_name;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#undo">Back to Undo Segments Information</a>
prompt <a href="#top">Back to Top</a>
prompt <br />
-- ==============================================================================
-- Memory Statistics
-- ==============================================================================
prompt <a name="memory"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Memory Statistics</b></font>
prompt <ul>-
<li><a href=‘#41‘>Memory Allocate Size</a></li>-
<li><a href=‘#42‘>Show SGA</a></li>-
<li><a href=‘#43‘>SGA Info</a></li>-
<li><a href=‘#44‘>PGA Info</a></li>-
</ul>
prompt <a href="#top">Back to Top</a>
-- +----------------------------------------------------------------------------+
-- | Memory Allocate Size |
-- +----------------------------------------------------------------------------+
prompt <a name="41"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Memory Allocate Size</b></font>
clear columns breaks computes
break on instance_name
column instance_name format 9999999 heading ‘Instance‘ print entmap off
column name format a20 heading ‘Memory Stats‘ print entmap off
column value format 999,990.99 heading ‘Size_MB‘ print entmap off
select i.instance_name, name, round (value / 1024 / 1024, 2) value
from gv$parameter p, gv$instance i
where p.inst_id = i.inst_id
and name in (‘memory_max_target‘,
‘memory_target‘,
‘sga_max_size‘,
‘sga_target‘,
‘pga_aggregate_target‘)
order by 1;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#memory">Back to Memory Statistics</a>
prompt <a href="#top">Back to Top</a>
--prompt <br />
-- +----------------------------------------------------------------------------+
-- | SGA Show |
-- +----------------------------------------------------------------------------+
prompt <a name="42"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Show SGA</b></font>
clear columns breaks computes
column instance_name format a13 heading ‘Instance‘ print entmap off
column name format a30 heading ‘Name‘ print entmap off
column value format 999,990.99 heading ‘Size_MB‘ print entmap off
break on report on instance_name
compute sum label ‘Total SGA:‘ of value on instance_name
select i.instance_name,
s.name,
round(s.value/1024/1024,2) value
from gv$sga s, gv$instance i
where s.inst_id = i.inst_id
order by i.instance_name, s.value desc;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#memory">Back to Memory Statistics</a>
prompt <a href="#top">Back to Top</a>
--prompt <br />
-- +----------------------------------------------------------------------------+
-- | SGA Info |
-- +----------------------------------------------------------------------------+
prompt <a name="43"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>SGA Info</b></font>
clear columns breaks computes
break on report on instance_name
column instance_name format a13 heading ‘Instance‘ print entmap off
column name format a50 heading ‘Pool Name‘ print entmap off
column bytes format 999,990.99 heading ‘Size_MB‘ print entmap off
column resizeable format a10 heading ‘Resizeable‘ print entmap off
select instance_name,
name,
round (bytes / 1024 / 1024, 2) bytes,
resizeable
from gv$sgainfo s, gv$instance i
where s.inst_id = i.inst_id
order by 1, 3 desc;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#memory">Back to Memory Statistics</a>
prompt <a href="#top">Back to Top</a>
--prompt <br />
-- +----------------------------------------------------------------------------+
-- | PGA Info |
-- +----------------------------------------------------------------------------+
prompt <a name="44"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>PGA Info</b></font>
clear columns breaks computes
column instance_name format a13 heading ‘Instance‘ print entmap off
column name format a50 heading ‘Name‘ print entmap off
column total_mb format 999,999,990.99 heading ‘Total_MB‘ print entmap off
column unit
format a7 heading ‘Unit‘ print entmap off
break on report on instance_name
select i.instance_name,
p.name,
round (p.value / 1024 / 1024, 2) total_mb,
p.unit
from gv$pgastat p, gv$instance i
where p.inst_id = i.inst_id
order by 1, 3 desc,4;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#memory">Back to Memory Statistics</a>
prompt <a href="#top">Back to Top</a>
prompt <br />
-- ==============================================================================
-- Security
-- ==============================================================================
prompt <a name="secutiry"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Security Information</b></font>
prompt <ul>-
<li><a href=‘#51‘>User Summary</a></li>-
<li><a href=‘#52‘>User Profiles</a></li>-
<li><a href=‘#53‘>Role</a></li>-
<li><a href=‘#54‘>Database Links</a></li>-
</ul>
prompt <a href="#top">Back to Top</a>
-- +----------------------------------------------------------------------------+
-- | User Info |
-- +----------------------------------------------------------------------------+
prompt <a name="51"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>User Summary</b></font>-
<br>
-- User Information
prompt <font size="2pt"><b>User Information</b></font>
clear columns breaks computes
column username format a30 heading ‘Username‘ print entmap off
column default_tablespace format a18 heading ‘Default Tablespace‘ print entmap off
column temporary_tablespace format a15 heading ‘Temp Tablespace‘ print entmap off
column created format a19 heading ‘Created‘ print entmap off
column expiry_date format a19 heading ‘Expire Date‘ print entmap off
column lock_date format a19 heading ‘Lock Date‘ print entmap off
column profile format a20 heading ‘Profile‘ print entmap off
column sysdba format a6 heading ‘SYSDBA‘ print entmap off
column sysoper format a7 heading ‘SYSOPER‘ print entmap off
column sysasm format a6 heading ‘SYSASM‘ print entmap off
column account_status format a16 heading ‘Account Status‘ print entmap off
select distinct a.username,
a.default_tablespace,
a.temporary_tablespace,
a.profile,
a.account_status,
to_char (a.created, ‘YYYY-MM-DD HH24:MI:SS‘) created,
to_char (a.expiry_date, ‘YYYY-MM-DD HH24:MI:SS‘) expiry_date,
to_char (a.lock_date, ‘YYYY-MM-DD HH24:MI:SS‘) lock_date
from dba_users a
order by a.account_status desc;
-- User With SYSDBA Privilege
prompt <font size="2pt"><b>User With SYSDBA Privilege</b></font>
select * from v$pwfile_users;
-- User With DBA Privilege
prompt <font size="2pt"><b>User With DBA Privilege</b></font>
clear columns breaks computes
column grantee for a50 heading ‘User‘ print entmap off
column granted_role for a4 heading ‘Role‘ print entmap off
column admin_option for a12 heading ‘Admin Option‘ print entmap off
column default_role for a12 heading ‘Default Role‘ print entmap off
select grantee,
granted_role,
admin_option,
default_role
from dba_role_privs
where granted_role = ‘DBA‘;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#secutiry">Back to Security Information</a>
prompt <a href="#top">Back to Top</a>
--prompt <br />
-- +----------------------------------------------------------------------------+
-- | User Profiles |
-- +----------------------------------------------------------------------------+
prompt <a name="52"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>User Profiles</b></font>
clear columns breaks computes
column profile format a20 heading ‘Profile‘ print entmap off
column resource_name format a50 heading ‘Resource Name‘ print entmap off
column resource_type format a30 heading ‘Resource Type‘ print entmap off
column limit format a30 heading ‘Limit‘ print entmap off
break on profile
select profile,
resource_name,
resource_type,
limit
from dba_profiles
order by profile;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#secutiry">Back to Security Information</a>
prompt <a href="#top">Back to Top</a>
-- +----------------------------------------------------------------------------+
-- | Role |
-- +----------------------------------------------------------------------------+
prompt <a name="53"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Roles</b></font>
clear columns breaks computes
column role for a50 heading ‘Role name‘ print entmap off
column grantee for a50 heading ‘Grantee‘ print entmap off
column admin_option for a15 heading ‘Admin Option‘ print entmap off
column default_role for a15 heading ‘Default Role‘ print entmap off
break on role
select b.role,
a.grantee,
a.admin_option,
a.default_role
from dba_role_privs a, dba_roles b
where granted_role(+) = b.role
order by b.role, a.grantee;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#secutiry">Back to Security Information</a>
prompt <a href="#top">Back to Top</a>
--prompt <br />
-- +----------------------------------------------------------------------------+
-- | DB Links |
-- +----------------------------------------------------------------------------+
prompt <a name="54"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Database Links</b></font>
clear columns breaks computes
column owner for a30 heading ‘Owner‘ print entmap off
column db_link for a30 heading ‘DB Link‘ print entmap off
column username for a30 heading ‘Username‘ print entmap off
column host for a30 heading ‘Host‘ print entmap off
column created for a19 heading ‘Created‘ print entmap off
break on owner
select owner,
db_link,
username,
host,
to_char (created, ‘YYYY-MM-DD HH24:MI:SS‘) created
from dba_db_links
order by owner, db_link;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#secutiry">Back to Security Information</a>
prompt <a href="#top">Back to Top</a>
prompt <br />
-- ==============================================================================
-- Objects
-- ==============================================================================
prompt <a name="object"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Objects Information</b></font>
prompt <ul>-
<li><a href=‘#61‘>Invalid Objects</a></li>-
<li><a href=‘#62‘>Disabled Constraints</a></li>-
<li><a href=‘#63‘>Disabled Triggers</a></li>-
<li><a href=‘#64‘>Objects In System Tablespace</a></li>-
<li><a href=‘#65‘>Directory Information</a></li>-
<li><a href=‘#66‘>Recyclebin</a></li>-
</ul>
prompt <a href="#top">Back to Top</a>
-- +----------------------------------------------------------------------------+
-- | Invalid Objects |
-- +----------------------------------------------------------------------------+
prompt <a name="61"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Invalid Objects</b></font>-
<br>
-- Invalid Objects Count
prompt <font size="2pt"><b>Invalid Objects Count</b></font>
clear columns breaks computes
column owner for a30 heading ‘Owner‘ print entmap off
column object_name for a50 heading ‘Name‘ print entmap off
column object_type for a50 heading ‘Type‘ print entmap off
column count for 999,999 heading ‘Count‘ print entmap off
column status for a7 heading ‘Status‘ print entmap off
select owner,
object_type,
status,
count (object_name) as "count"
from dba_objects
where status = ‘INVALID‘
group by owner, object_type, status
order by count (object_name) desc;
-- Invalid Objects Detail
prompt <font size="2pt"><b>Invalid Objects Detail</b></font>
break on owner on object_type
select owner,
object_type,
object_name,
status
from dba_objects
where status = ‘INVALID‘
order by 1,2,3;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#object">Back to Objects Information</a>
prompt <a href="#top">Back to Top</a>
-- +----------------------------------------------------------------------------+
-- | Disabled Constraints |
-- +----------------------------------------------------------------------------+
prompt <a name="62"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Disabled Constraints</b></font>
clear columns breaks computes
column owner format a20 heading ‘Owner‘ print entmap off;
column constraint_name format a30 heading ‘Constraint Name‘ print entmap off;
column constraint_type format a15 heading ‘Constraint Type‘ print entmap off;
column table_name format a30 heading ‘Table Name‘ print entmap off;
break on owner on table_name
select owner,
table_name,
constraint_name,
decode (constraint_type,
‘C‘, ‘check constraint on a table‘,
‘P‘, ‘primary key‘,
‘U‘, ‘unique key‘,
‘R‘, ‘referential integrity‘,
‘V‘, ‘with check option, on a view‘,
‘O‘, ‘with read only, on a view‘,
null)
constraint_type
from dba_constraints
where status = ‘DISABLED‘
order by 1,2;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#object">Back to Objects Information</a>
prompt <a href="#top">Back to Top</a>
-- +----------------------------------------------------------------------------+
-- | Disabled Triggers |
-- +----------------------------------------------------------------------------+
-- Check if there have disabled triggers
---- Recompile the disabled triggers
prompt <a name="63"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Disabled Triggers</b></font>
clear columns breaks computes
column owner format a20 heading ‘Owner‘ print entmap off
column trigger_name format a30 heading ‘Trigger Name‘ print entmap off
column trigger_type format a20 heading ‘Trigger Type‘ print entmap off
break on owner
select owner, trigger_name, trigger_type
from dba_triggers
where status = ‘DISABLED‘;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#object">Back to Objects Information</a>
prompt <a href="#top">Back to Top</a>
-- +----------------------------------------------------------------------------+
-- | Object In System Tablespace |
-- +----------------------------------------------------------------------------+
prompt <a name="64"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Objects In System Tablespace</b></font>
-- Check tables and indexes in system tablespace that not belong to sys or system
clear columns breaks computes
column object_type format a15 heading ‘Object Type‘ print entmap off
column owner format a20 heading ‘Owner‘ print entmap off
column tablespace format a30 heading ‘Tablespace‘ print entmap off
column object_name format a30 heading ‘Object Name‘ print entmap off
break on object_type on owner on tablespace
select object_type,
owner,
tablespace_name tablespace,
object_name
from (select ‘Table‘ object_type,
owner,
tablespace_name,
table_name object_name
from dba_tables
union
select ‘Index‘ object_type,
owner,
tablespace_name,
index_name object_name
from dba_indexes)
where tablespace_name = ‘SYSTEM‘
and owner not in
(‘ANONYMOUS‘,
‘BI‘,
‘CTXSYS‘,
‘DBSNMP‘,
‘DIP‘,
‘DMSYS‘,
‘EXFSYS‘,
‘HR‘,
‘IX‘,
‘LBACSYS‘,
‘MDDATA‘,
‘MDSYS‘,
‘MGMT_VIEW‘,
‘OE‘,
‘OLAPSYS‘,
‘ORDPLUGINS‘,
‘ORDSYS‘,
‘OUTLN‘,
‘PM‘,
‘SCOTT‘,
‘SH‘,
‘SI_INFORMTN_SCHEMA‘,
‘SYS‘,
‘SYSMAN‘,
‘SYSTEM‘,
‘WMSYS‘,
‘WKPROXY‘,
‘WK_TEST‘,
‘WKSYS‘,
‘XDB‘,
‘APEX_030200‘,
‘APEX_PUBLIC_USER‘,
‘APPQOSSYS‘,
‘DVSYS‘,
‘FLOWS_FILES‘,
‘IX‘,
‘LBACSYS‘,
‘ORACLE_OCM‘,
‘OWBSYS‘,
‘OWBSYS_AUDIT‘,
‘SPATIAL_CSW_ADMIN_USR‘,
‘SPATIAL_WFS_ADMIN_USR‘);
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#object">Back to Objects Information</a>
prompt <a href="#top">Back to Top</a>
-- +----------------------------------------------------------------------------+
-- | Directory |
-- +----------------------------------------------------------------------------+
prompt <a name="65"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Directory Information</b></font>-
<br>
-- Directory List
prompt <font size="2pt"><b>Directory List</b></font>
clear columns breaks computes
column owner format a30 heading ‘Owner‘ print entmap off
column directory_name format a30 heading ‘Directory Name‘ print entmap off
column directory_path format a100 heading ‘Directory Path‘ print entmap off
break on report on owner
select owner,
directory_name,
directory_path
from dba_directories
order by owner,directory_name;
-- Directory Privilege
prompt <font size="2pt"><b>Directory Privilege</b></font>
clear columns breaks computes
column table_name format a30 heading ‘Directory Name‘ print entmap off
column grantee format a30 heading ‘Grantee‘ print entmap off
column privilege format a9 heading ‘Privilege‘ print entmap off
column grantable format a9 heading ‘Grantable‘ print entmap off
break on report on table_name on grantee
select table_name,
grantee,
privilege,
grantable
from dba_tab_privs
where privilege in (‘READ‘, ‘WRITE‘)
order by table_name, grantee, privilege;
prompt <hr align="left" size="1" color="Gray" width="20%" />-
<a href="#object">Back to Objects Information</a>
prompt <a href="#top">Back to Top</a>
--prompt <br />
-- +----------------------------------------------------------------------------+
-- | Recyclebin |
-- +----------------------------------------------------------------------------+
prompt <a name="66"></a>
prompt <font size="+1" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Recyclebin</b></font>
clear columns breaks computes
column owner heading ‘Owner‘ print entmap off
column type format a50 heading ‘Object Type‘ print entmap off
column count format 999999 heading ‘Count‘ print entmap off
break on report on owner
compute sum label ‘Total:‘ of count on report
select owner,type,count(*) count
from dba_recyclebin
group by owner,type;
/*
column owner heading ‘Owner‘ print entmap off
column original_name heading ‘Original Name‘ print entmap off
column type format a50 heading ‘Object Type‘ print entmap off
column object_name format a50 heading ‘Object Name‘ print entmap off
column ts_name format a50 heading ‘Tablespace‘ print entmap off
column operation format a9 heading ‘Operation‘ print entmap off
column createtime format a19 heading ‘Create Time‘ print entmap off
column droptime format a19 heading ‘Drop Time‘ print entmap off
column can_undrop format a10 heading ‘Can Undrop‘ print entmap off
column can_purge format a9 heading ‘Can Purge‘ print entmap off
column size_mb format 999,990.99 heading ‘Size_MB‘ print entmap off
break on report on owner
compute count label ‘Count:‘ of original_name on report
select owner,
original_name,
type,
object_name,
ts_name,
operation,
createtime,
droptime,
