95 - [Oracle 19C] Monitoring Tablespace Space Usage - example

 

 

DBMS_SERVER_ALERT.SET_THRESHOLD example

show user;
show con_name;

/*
USER is "PDB10_ADMIN"
CON_NAME 
------------------------------
PDB10
*/

 

 

 

-- no auto extend option.
CREATE TABLESPACE tbsalert
DATAFILE '/u01/app/oracle/oradata/ORCL/PDB10/tbsalert.dbf'
SIZE 50M  LOGGING EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
/*
TABLESPACE TBSALERT created.
*/



SELECT DBMS_METADATA.GET_DDL('TABLESPACE','TBSALERT') FROM dual;
/*
DBMS_METADATA.GET_DDL('TABLESPACE','TBSALERT')                                  
--------------------------------------------------------------------------------

  CREATE TABLESPACE "TBSALERT" DATAFILE 
  '/u01/app/oracle/oradata/ORCL/PDB10/tbsalert.dbf' SIZE 52428800
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT 
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
*/



select file_name, file_id, tablespace_name, bytes, blocks, status from dba_data_files
where TABLESPACE_NAME='TBSALERT'; --52428800
/*
FILE_NAME                                             FILE_ID TABLESPACE_NAME      BYTES     BLOCKS STATUS   
-------------------------------------------------- ---------- --------------- ---------- ---------- ---------
/u01/app/oracle/oradata/ORCL/PDB10/tbsalert.dbf            56 TBSALERT          52428800       6400 AVAILABLE
*/

 

 

 

# 테이블 스페이스의 여유 공간 확인
select * from dba_free_space
where TABLESPACE_NAME='TBSALERT';--51380224
/*
TABLESPACE_NAME    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
--------------- ---------- ---------- ---------- ---------- ------------
TBSALERT                56        128   51380224       6272           56
*/


# 테이블 스페이스의 여유 공간과 전체 할당 공간
SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 
fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df ,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';
/*
TABLESPACE                           FREE      BYTES   PCT_FREE
------------------------------ ---------- ---------- ----------
TBSALERT                         51380224   52428800         98
*/


# 알람 설정
begin
  DBMS_SERVER_ALERT.SET_THRESHOLD( 
  metrics_id => dbms_server_alert.tablespace_pct_full,    
  warning_operator =>DBMS_SERVER_ALERT.OPERATOR_GE, 
  warning_value => '55', 
  critical_operator =>DBMS_SERVER_ALERT.OPERATOR_GE, 
  critical_value => '70', 
  observation_period => 1,
  consecutive_occurrences => 1, 
  instance_name => 'orcl', 
  object_type =>DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE, 
  object_name => 'TBSALERT');
end;
/*
PL/SQL procedure successfully completed.
*/


# 설정한 알람 확인
SELECT warning_value, critical_value
FROM dba_thresholds 
WHERE object_name='TBSALERT';
/*
WARNING_VALUE   CRITICAL_VALUE 
--------------- ---------------
55              70             
*/

 

 

 

# 테이블 스페이스의 알람 확인, 현재는 입력한 데이터가 없어서 없음.
SELECT * FROM dba_outstanding_alerts
 WHERE object_name='TBSALERT';
/*
no rows selected
*/


create table test100 ( emp_id number, name varchar2(100) )
 tablespace TBSALERT;
/*
Table TEST100 created.
*/


# 데이터 입력
 begin
 for i in 1..1000000
 loop
 insert into test100 values ( i, 'any dummy name' );
 end loop;
 commit;
 end;
/*
PL/SQL procedure successfully completed.
*/


# 테이블 스페이스 여유 공간 확인
SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 
fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df ,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';
/*
TABLESPACE                           FREE      BYTES   PCT_FREE
------------------------------ ---------- ---------- ----------
TBSALERT                         22020096   52428800         42
*/


# 알람 메시지 조회
--Wait a few minutes. 10-15
SELECT reason, message_type , message_level
FROM dba_outstanding_alerts
WHERE object_name='TBSALERT';
/*
REASON                                             MESSAGE_TYPE MESSAGE_LEVEL
-------------------------------------------------- ------------ -------------
Tablespace [TBSALERT@PDB10] is [58 percent] full   Warning                  5
*/


# 데이터 입력
 begin
 for i in 1000001..1500000
 loop
 insert into test100 values ( i, 'any dummy name' );
 end loop;
 commit;
 end;
/*
PL/SQL procedure successfully completed.
*/


# 테이블 스페이스 여유 공간 확인
SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 
fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df ,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';
/*
TABLESPACE                           FREE      BYTES   PCT_FREE
------------------------------ ---------- ---------- ----------
TBSALERT                          7340032   52428800         14
*/


# 알람 메시지 조회, 메시지 레벨이 1등급으로 변경된 것을 볼 수 있다.
--Wait a few minutes. 10-15
SELECT reason, message_type , message_level
FROM dba_outstanding_alerts
WHERE object_name='TBSALERT';
/*
REASON                                             MESSAGE_TYPE MESSAGE_LEVEL
-------------------------------------------------- ------------ -------------
Tablespace [TBSALERT@PDB10] is [86 percent] full   Warning                  1
*/