[자작] 특정 Tablespace의 데이터 파일 및 파일시스템 위치 조회 SQL

Tablespace 데이터파일이 위치한 파일시스템 확인하기

자작 SQL이니 감안하여 봐주시길 바란다. 특정 tablespace에 속한 datafile 목록을 확인하고, 해당 datafile들이 올라가 있는 OS 파일시스템에 대해 df -k 명령을 바로 실행할 수 있도록 명령 문자열을 만들어 주는 스크립트다.

SQL*Plus에서 실행하는 것을 전제로 했으며, DBA_DATA_FILES를 조회하므로 해당 뷰를 조회할 수 있는 권한이 필요하다. 아래 스크립트는 Unix/Linux 계열 경로처럼 /u01, /u02 형태의 파일시스템 경로를 사용하는 환경을 기준으로 작성되어 있다.

accept tablespacename prompt '[http://sarc.io] Enter Tablespace name : '

set serveroutput on
set pagesize 100
set linesize 150

col file_name for a50
col bytes/1024/1024 for 9999999999999

select file_name
     , bytes/1024/1024
  from dba_data_files
 where tablespace_name='&tablespacename';

declare
cursor a_tabs is
select distinct substr(file_name,instr(file_name,'/',instr(file_name,'/'))
         , instr(file_name,'/',instr(file_name,'/')+1)-instr(file_name,'/')) file_name
  from dba_data_files
 where tablespace_name='&tablespacename';

cursor b_tabs is
select distinct substr(file_name,instr(file_name,'/',instr(file_name,'/'))
         , instr(file_name,'/',instr(file_name,'/')+1)-instr(file_name,'/')) file_name
from dba_data_files;

begin
  dbms_output.put_line(CHR(10)||'+----------------------------------------------------------------------');
  dbms_output.put_line('| Datafile OS filesystem');
  dbms_output.put_line('+----------------------------------------------------------------------');
  dbms_output.put_line(CHR(10)||'(1) OS filesystem for this Tablespace');
  dbms_output.put('!df -k');

  for r_tabs in a_tabs
  loop
    dbms_output.put(' '||r_tabs.file_name);
  end loop;

  dbms_output.put_line(CHR(10)||CHR(10)||'(2) OS filesystem for all Datafiles');
  dbms_output.put('!df -k');
  for r_tabs in b_tabs
  loop
    dbms_output.put(' '||r_tabs.file_name);
  end loop;
  dbms_output.put_line(CHR(10));
end;
/

실행 결과

결과는 다음과 같다.

SQL> @tablespace
[http://sarc.io] Enter Tablespace name : ARTICLE_DATA
old   4:  WHERE tablespace_name='&tablespacename'
new   4:  WHERE tablespace_name='ARTICLE_DATA'

FILE_NAME                                          BYTES/1024/1024
-------------------------------------------------- ---------------
/u03/oracledb/datafile/articledata_04.dbf                    10000
/u11/oracledb/datafile/articledata_03.dbf                    10000
/u09/oracledb/datafile/articledata_02.dbf                    10000
/u10/oracledb/datafile/articledata_01.dbf                    10000
/u11/oracledb/datafile/articledata_05.dbf                    10000
/u10/oracledb/datafile/articledata_06.dbf                    10000
/u02/oracledb/datafile/articledata_07.dbf                    10000
/u13/oracledb/datafile/articledata_08.dbf                    10000
/u14/oracledb/datafile/articledata_09.dbf                    10000

9 rows selected.

old   6:  WHERE tablespace_name='&tablespacename';
new   6:  WHERE tablespace_name='ARTICLE_DATA';

+----------------------------------------------------------------------
| Datafile OS filesystem
+----------------------------------------------------------------------

(1) OS filesystem for this Tablespace
!df -k /u02 /u13 /u03 /u11 /u14 /u09 /u10

(2) OS filesystem for all Datafiles
!df -k /u04 /u02 /u13 /u11 /u03 /u06 /u14 /u09 /u08 /u01 /u05 /u07 /u10



PL/SQL procedure successfully completed.

사용 방법

출력된 !df -k 부분을 SQL*Plus 프롬프트에 그대로 붙여 넣으면 해당 파일시스템의 사용량을 확인할 수 있어 편하다. 예를 들어 위 결과에서는 특정 tablespace에 대해서는 다음 명령만 복사해 실행하면 된다.

!df -k /u02 /u13 /u03 /u11 /u14 /u09 /u10

장애 대응이나 용량 증설 전 점검처럼 “이 tablespace의 datafile들이 실제로 어느 파일시스템을 쓰고 있는가”를 빠르게 확인해야 할 때 유용하다. 다만 ASM, OMF, Windows 경로 등 파일명이 일반적인 Unix/Linux 절대 경로 형식이 아닌 환경에서는 경로를 잘라내는 로직이 맞지 않을 수 있으므로 환경에 맞게 수정해서 사용하는 것이 좋다.

SQL*Plus 프롬프트에서 !df -k 부분을 긁어 넣으면 파일시스템 사용량을 알 수 있어 편하다! (나만?)