- Administration
- Structure
- Performance
- Backup
- Miscellaneous
- SQL*Net
|
|
Administration - Status
Startup time
select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from v$instance
/
|
How large is the database
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
|
Distribution of objects and data
Which schemas are taking up all of the space
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
|
Show the ten largest objects in the database
col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/
|
Is java installed in the database?
This will return 9000'ish if it is...
select count(*)
from all_objects
where object_type like '%JAVA%'
and owner = 'SYS'
/
|
Display character set information
select * from nls_database_parameters
/
|
Show all used features
select name
, detected_usages
from dba_feature_usage_statistics
where detected_usages > 0
/
|
|
