[오라클 튜닝] instance 튜닝3

obj_stat.sql
 => 현재 데이터베이스 내에 생성되어 있는 모든 객체의 종류와 개수를 분석하여 출력

col ta format 999,999 heading 'Tables'
col ind format 999,999 heading 'Indexs'
col sy format 999,999 heading 'Synonyms'
col se format 999,999 heading 'Sequences'
col ve format 999,999 heading 'Views'
col clu format 999,999 heading 'Clusters'
col dbl format 999,999 heading 'Database|Links'
col pkg format 999,999 heading 'Packages'
col pkb format 999,999 heading 'Package|Bodies'
col pro format 999,999 heading 'Procedures'
set verify off
compute sum of ta on report
compute sum of ow on report
compute sum of sy on report
compute sum of se on report
compute sum of ind on report
compute sum of ve on report
compute sum of clu on report
compute sum of dbl on report
compute sum of pkg on report
compute sum of pkb on report
compute sum of pro on report
break on report
spool objstat.lst
set heading on

select owner ow,
 sum(decode(object_type,'TABLE',1,0)) ta,
 sum(decode(object_type,'INDEX',1,0)) ind,
 sum(decode(object_type,'SYNONYM',1,0)) sy,
 sum(decode(object_type,'SEQUENCE',1,0)) se,
 sum(decode(object_type,'VIEW',1,0)) ve,
 sum(decode(object_type,'CLUSTER',1,0)) clu,
 sum(decode(object_type,'DATABASE LINK',1,0)) dbl,
 sum(decode(object_type,'PACKAGE',1,0)) pkg,
 sum(decode(object_type,'PACKAGE BODY',1,0)) pkb,
 sum(decode(object_type,'PROCEDURE',1,0)) pro
from dba_objects
group by owner
/

prompt
spool off

 

[오라클 튜닝] instance 튜닝3

 

* db_space.sql
 => 현재 데이터 파일 내에 사용된 공간과 남아 있는 공간이 얼마나 되는지 확인

set pagesize 66
set echo off
set feedback off
set verify off
ttitle 'Disk Usage'

spool db_space.lst
create or replace view tbspace (
tablespace_name, asize, used )
as
select tablespace_name, sum(bytes), 0
from dba_data_files
group by tablespace_name
union
select tablespace_name, 0, sum(bytes)
from dba_extents
group by tablespace_name
/

col col0 format a20 heading 'Tablespace'
col col1 format 999,999 heading 'Size(KB)'
col col2 format 999,999 heading 'Used(KB)'
col col3 format 999,999 heading 'Free(KB)'
select tablespace_name col0,
 sum(asize) / (1024*1024) col1,
 sum(used) / (1024*1024) col2,
 (sum(asize)-sum(used)) / (1024*1024) col3
from tbspace
group by tablespace_name
/
drop view tbspace
/
spool off