Query

Startup time

select to_char(startup_time, ‘HH24:MI DD-MON-YY’) Startup_time
from v$instance
/

Berapa Besar Database Oracle Kita

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
/

Distribusi dari object dan data

Skema mana yang paling banyak menghabiskan storage kita

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
/

Sepuluh object paling besar di 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
/

Apakah java telah di install di database

return 9000..

select count(*)
from all_objects
where object_type like ‘%JAVA%’
and owner = ‘SYS’
/

Display informasi character set

select * from nls_database_parameters
/

Informasi semua fitur yang digunakan

select name
, detected_usages
from dba_feature_usage_statistics
where detected_usages > 0
/