자작 SQL이니 감안하여 주시길 바란다.

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.

SQLPlus 프롬프트에서 !df -k 부분을 긁어넣기 하면 파일시스템 usage을 알 수 있어 편하다! (나만?)