행위

"DATABASE 조회 scripts"의 두 판 사이의 차이

DB CAFE

(→‎언두 테이블스페이스 사용량 모니터링)
(→‎언두 테이블스페이스 사용량 모니터링)
671번째 줄: 671번째 줄:
 
=== 언두 테이블스페이스 사용량 모니터링 ===
 
=== 언두 테이블스페이스 사용량 모니터링 ===
 
<source lang=sql>
 
<source lang=sql>
−
sselect a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
+
select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
−
from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
+
  from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
−
from dba_data_files a, dba_tablespaces b
+
          from dba_data_files a, dba_tablespaces b
−
where a.tablespace_name = b.tablespace_name
+
        where a.tablespace_name = b.tablespace_name
−
and b.contents = 'UNDO'
+
          and b.contents = 'UNDO'
−
group by b.tablespace_name) a,
+
        group by b.tablespace_name) a
−
(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
+
      , (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
−
from DBA_UNDO_EXTENTS c
+
            from DBA_UNDO_EXTENTS c
−
where status <> 'EXPIRED'
+
          where status <> 'EXPIRED'
−
group by c.tablespace_name) b
+
          group by c.tablespace_name) b
−
where a.tablespace_name = b.tablespace_name;
+
where a.tablespace_name = b.tablespace_name;
 +
 
 +
</source>
 +
=== 템프 테이블스페이스 사용량 모니터링 ===
 +
<source lang=sql>
 +
 
 +
select a.tablespace_name tablespace,
 +
d.TEMP_TOTAL_MB,
 +
sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
 +
d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
 +
from v$sort_segment a,
 +
(
 +
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
 +
from v$tablespace b, v$tempfile c
 +
where b.ts#= c.ts#
 +
group by b.name, c.block_size
 +
) d
 +
where a.tablespace_name = d.name
 +
group by a.tablespace_name, d.TEMP_TOTAL_MB;
  
 
</source>
 
</source>

2024년 7월 11일 (목) 22:36 판

thumb_up 추천메뉴 바로가기

1 DATABASE 조회 scripts[편집]

1.1 리두로그 멤버 조회[편집]

col member for a56
set pagesize 299
set lines 299
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) "Size (MB)"
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/

1.2 전체 테이블스페이스 DDL 추출[편집]

set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_tablespace.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;
spool off

1.3 유저에게 부여된 모든 권한 - DDL 추출[편집]

set feedback off pages 0 long 900000 lines 20000 pagesize 20000 serveroutput on
accept USERNAME prompt "Enter username :"

--This line add a semicolon at the end of each statement
execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

-- This will generate the DDL for the user and add his objects,system and role grants
SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',grantee)as script from DBA_SYS_PRIVS where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',grantee)as script from DBA_ROLE_PRIVS where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',grantee)as script from DBA_TAB_PRIVS where grantee='&username' and rownum=1;

1.4 DB (데이터/템프/로그/남은공간) SIZE 조회[편집]

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
/

1.5 시간별 생성되는 아키이브 로그 조회[편집]

set lines 299
SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
/

1.6 현재 트랜잭션 상태 조회[편집]

col name format a10
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'

select s.sid,username,t.start_time, r.name, t.used_ublk "USED BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/

1.7 계정 잠금 사유 조회[편집]

-- Return code 1017 ( INVALID LOGIN ATTEMPT)
-- Return code 28000 ( ACCOUNT LOCKED)

set pagesize 1299
set lines 299
col username for a15
col userhost for a13
col timestamp for a39
col terminal for a23
SELECT username,userhost,terminal,timestamp,returncode
FROM dba_audit_session
WHERE username='&USER_NAME' and returncode in (1017,28000);

1.8 테이블에서 중복된 ROW 찾기[편집]

--- Reference metalink id - 332494.1
-- Save as duplicate.sql and run as @duplicate.sql

REM This is an example SQL*Plus Script to detect duplicate rows from
REM a table.
REM
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
accept t prompt 'Table: '
prompt
select 'Table '||upper('&&t') from dual;
describe &&t
prompt
prompt Enter name(s) of column(s) which should be unique. If more than
prompt one column is specified, you MUST separate with commas.
prompt
accept c prompt 'Column(s): '
prompt
select &&c from &&t
where rowid not in (select min(rowid) from &&t group by &&c)
/

1.9 데이터파일 RESIZE 스크립트 생성기[편집]

generate resize datafile script without ORA-03297 error
 

select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a        ,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id=  b.file_id
and   c.file_id  = b.file_id
and   c.block_id = b.maximum
order by a.tablespace_name,a.file_name);

1.10 DB 한달 증가량 조회[편집]

select to_char(creation_time, 'MM-RRRR') "Month"
     , sum(bytes)/1024/1024/1024 "Growth in GB
  from sys.v_$datafile
 where to_char(creation_time,'RRRR')='&YEAR_IN_YYYY_FORMAT'
 group by to_char(creation_time, 'MM-RRRR')
 order by to_char(creation_time, 'MM-RRRR');

1.11 DB UPTIME 조회[편집]

select to_char(startup_time, 'DD-MM-YYYY HH24:MI:SS'),floor(sysdate-startup_time) DAYS from v$Instance;

1.12 현재 scn 정보 조회[편집]

-- Get current scn value:

select current_scn from v$database;

-- Get scn value at particular time:

select timestamp_to_scn('19-JAN-08:22:00:10') from dual;

-- Get timestamp from scn:

select scn_to_timestamp(224292)from dual;


1.13 스키마(유저)의 모든 테이블 row건수[편집]

select table_name,
to_number(extractvalue(dbms_xmlgen.getXMLtype('select /*+ PARALLEL(8) */ count(*) cnt from "&&SCHEMA_NAME".'||table_name),'/ROWSET/ROW/CNT'))
rows_in_table from dba_TABLES
where owner='&&SCHEMA_NAME';


1.14 인덱스 사용량 모니터링[편집]

---Index monitoring is required, to find whether indexes are really in use or not. Unused can be dropped to avoid overhead.

-- First enable monitoring usage for the indexes. (운영중인 db에서는 사용금지. 라이브러리 캐시 락 발생됨.)

alter index siebel.S_ASSET_TEST monitoring usage;

--Below query to find the index usage:

select * from v$object_usage;

1.15 SQL 쿼리 HTML로 출력하기[편집]

We can spool output of an sql query to html format:

set pages 5000
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>EMPLOYEE REPORT</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFC6} --> -
</STYLE>" -
BODY "TEXT='#FF00Ff'" -
TABLE "WIDTH='90%' BORDER='5'"
spool report.html
Select * from scott.emp;
spool off
exit

1.16 오라클 패치 정보 조회[편집]

--- From 12c onward

set lines 2000
select patch_id,status,description from dba_registry_sqlpatch;

--- For 11g and below:

set lines 2000
select * from dba_registry_history;

1.17 Data Pump jobs 정리[편집]

-- Find the orphaned Data Pump jobs:

SELECT owner_name, job_name, rtrim(operation) "OPERATION",
rtrim(job_mode) "JOB_MODE", state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%' and state='NOT RUNNING'
ORDER BY 1,2;

-- Drop the tables

SELECT 'drop table ' || owner_name || '.' || job_name || ';'
FROM dba_datapump_jobs WHERE state='NOT RUNNING' and job_name NOT LIKE 'BIN$%'

1.18 설치된 RDBMS 컴포넌트[편집]

col comp_id for a10
col comp_name for a56
col version for a12
col status for a10
set pagesize 200
set lines 200
set long 999
select comp_id,comp_name,version,status from dba_registry;


1.19 AWR retention 기간 조회/변경[편집]

-- View current AWR retention period

select retention from dba_hist_wr_control;

-- Modify retention period to 7 days and interval to 30 min

select dbms_workload_repository.modify_snapshot_settings (interval => 30, retention => 10080);

NOTE - 7 DAYS = 7*24*3600= 10080 minutes

1.20 언두 리텐션 최적 사이즈 찾기[편집]

SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value, 1, 25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) /
(1024 * 1024) "NEEDED UNDO SIZE [MByte]"
FROM (SELECT SUM(a.bytes) undo_size
FROM gv$datafile a, gv$tablespace b, dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#) d,
gv$parameter e,
gv$parameter f,
(SELECT MAX(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec
FROM v$undostat) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';


1.21 오래된 AWR 스냅샷 Purge[편집]

-- Find the AWR snapshot details.

select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id

-- Purge snapshot between snapid 612 to 700

execute dbms_workload_repository.drop_snapshot_range(low_snap_id =>612 , high_snap_id =>700);

-- Verify again

select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id

1.22 AWR window_size 조사/변경[편집]

-- Check the current moving window baseline size:

select BASELINE_TYPE,MOVING_WINDOW_SIZE from dba_hist_baseline;

-- Modify window_size to (7 days):

execute dbms_workload_repository.modify_baseline_window_size(window_size=> 7);

1.23 열린 DBLINK 정보 조회[편집]

set pagesize 200
set lines 200
col db_link for a19
set long 999

SELECT db_link,
owner_id,
logged_on,
heterogeneous,
open_cursors,
in_transaction,
update_sent
FROM gv$dblink
ORDER BY db_link;

1.24 현재 사용중인 리루로그량  %[편집]

SELECT le.leseq "Current log sequence No"
     , 100*cp.cpodr_bno/le.lesiz "Percent Full"
     , cp.cpodr_bno "Current Block No"
     , le.lesiz "Size of Log in Blocks"
  FROM x$kcccp cp, x$kccle le
 WHERE le.leseq =CP.cpodr_seq
   AND bitand(le.leflg,24) = 8

1.25 FK 컬럼에 인덱스가 없는 테이블 조회[편집]

select * from (
select c.table_name, co.column_name, co.position column_position
from user_constraints c, user_cons_columns co
where c.constraint_name = co.constraint_name
and c.constraint_type = 'R'
minus
select ui.table_name, uic.column_name, uic.column_position
from user_indexes ui, user_ind_columns uic
where ui.index_name = uic.index_name
)
order by table_name, column_position;

select
a.constraint_name cons_name
,a.table_name tab_name
,b.column_name cons_column
,nvl(c.column_name,'***No Index***') ind_column
from user_constraints a
join
user_cons_columns b on a.constraint_name = b.constraint_name
left outer join
user_ind_columns c on b.column_name = c.column_name
and b.table_name = c.table_name
where constraint_type = 'R'
order by 2,1;

1.26 DB서버 cpu 메모리 현황[편집]

set pagesize 200
set lines 200
col name for a21
col stat_name for a25
col value for a13
col comments for a56

select STAT_NAME
     , to_char(VALUE) as VALUE 
     , COMMENTS 
  from v$osstat 
 where stat_name IN ('NUM_CPUS','NUM_CPU_CORES','NUM_CPU_SOCKETS')
 union
select STAT_NAME,VALUE/1024/1024/1024 || ' GB' 
     , COMMENTS 
  from v$osstat 
 where stat_name IN ('PHYSICAL_MEMORY_BYTES');

1.27 DB incarnation 정보 조회[편집]

assignment * incarnation
  1. 데이터베이스를 resetlogs 옵션으로 open 할때 incarnation이 생성됨(reset)
  2. 일반 recover 완료 후에는 resetlogs 없이도 정상 open 할 수도 있지만 Point In Time Recovery 나 using backup controlfile로 복구한 후에는 resetlogs 옵션으로 데이터베이스를 open 해야 하므로 데이터베이스의 새로운 incarnation이 생성됨
  3. 데이터베이스는 서로 다른 두 redo stream의 SCN이 동일하지만 서로 다른 시간에 발생한 경우 혼란을 피하기 위해 새로운 incarnation을 필요로함
  4. 데이터베이스에 잘못된 redo를 적용하면 데이터베이스가 손상을 초래함.
  5. 단일 데이터베이스에 여러 개의 incarnation이 존재함에 따라 rman이 현재 incarnation 경로에 없는 백업을 처리하는 방식이 결정됨
  6. 일반적으로 current incarnation의 데이터베이스를 사용하는 것이 좋음
  7. 일부 경우에 데이터베이스를 이전 incarnation로 재설정하는 것이 가장 좋은 방법일수도 있음


set heading off
set feedback off
select 'Incarnation Destination Configuration' from dual;
select '*************************************' from dual;
set heading on
set feedback on

select INCARNATION# INC#
     , RESETLOGS_CHANGE# RS_CHANGE#
     , RESETLOGS_TIME
     , PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#
     , STATUS
     , FLASHBACK_DATABASE_ALLOWED FB_OK 
  from v$database_incarnation;


1.28 DB 타임존 조회[편집]

SELECT version FROM v$timezone_file;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
  FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;

1.29 전체 DB 사이즈(사용량/남은량/총량) 조회[편집]

set feedback off
set pagesize 70;
set linesize 2000
set head on
COLUMN Tablespace format a25 heading 'Tablespace Name'
COLUMN autoextensible format a11 heading 'AutoExtend'
COLUMN files_in_tablespace format 999 heading 'Files'
COLUMN total_tablespace_space format 99999999 heading 'TotalSpace'
COLUMN total_used_space format 99999999 heading 'UsedSpace'
COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'
COLUMN total_used_pct format 9999 heading '%Used'
COLUMN total_free_pct format 9999 heading '%Free'
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto'
COLUM total_auto_used_pct format 999.99 heading 'Max%Used'
COLUMN total_auto_free_pct format 999.99 heading 'Max%Free'


WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES/1024/1024) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES)/1024/1024 total_tbs_free_bytes,
MAX (BYTES)/1024/1024 max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT c.instance_name,a.tablespace_name Tablespace,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
files.tbs_files files_in_tablespace,
files.total_tbs_bytes total_tablespace_space,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
) total_used_space,
fragments.total_tbs_free_bytes total_tablespace_free_space,
round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
)) total_used_pct,
round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
)) total_free_pct
FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
order by total_free_pct;

1.30 테이블스페이스 사용량 모니터링[편집]

set feedback off
set pagesize 70;
set linesize 2000
set head on
COLUMN Tablespace format a25 heading 'Tablespace Name'
COLUMN autoextensible format a11 heading 'AutoExtend'
COLUMN files_in_tablespace format 999 heading 'Files'
COLUMN total_tablespace_space format 99999999 heading 'TotalSpace'
COLUMN total_used_space format 99999999 heading 'UsedSpace'
COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'
COLUMN total_used_pct format 9999 heading '%Used'
COLUMN total_free_pct format 9999 heading '%Free'
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto'
COLUM total_auto_used_pct format 999.99 heading 'Max%Used'
COLUMN total_auto_free_pct format 999.99 heading 'Max%Free'
WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES/1024/1024) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES)/1024/1024 total_tbs_free_bytes,
MAX (BYTES)/1024/1024 max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT c.instance_name,a.tablespace_name Tablespace,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
files.tbs_files files_in_tablespace,
files.total_tbs_bytes total_tablespace_space,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
) total_used_space,
fragments.total_tbs_free_bytes total_tablespace_free_space,
round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
)) total_used_pct,
round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
)) total_free_pct
FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
order by total_free_pct;

1.31 언두 테이블스페이스 사용량 모니터링[편집]

select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
  from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
          from dba_data_files a, dba_tablespaces b
         where a.tablespace_name = b.tablespace_name
           and b.contents = 'UNDO'
         group by b.tablespace_name) a
       , (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
            from DBA_UNDO_EXTENTS c
           where status <> 'EXPIRED'
           group by c.tablespace_name) b
 where a.tablespace_name = b.tablespace_name;

1.32 템프 테이블스페이스 사용량 모니터링[편집]

select a.tablespace_name tablespace,
d.TEMP_TOTAL_MB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
from v$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
from v$tablespace b, v$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_MB;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=DATABASE_조회_scripts&oldid=12172"