자작 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을 알 수 있어 편하다! (나만?)