실행한 쿼리는 다음과 같아요.
set linesize 165
set pagesize 200
set newpage 0
set verify off
clear column
column tablespace_name format a22 heading "tablespace name"
column "All" format 999,999.99 heading "Total(GB)"
column "used" format 999,999.99 heading "used(GB)"
column "percent" format 999.99 heading "per(%)"
column "free" format 999.99 heading "free(GB)"
prompt TABLESPACE USAGE
select ddf.tablespace_name,
ddf.bytes/1024/1024/1024 "ALL",
(ddf.bytes - dfs.bytes)/1024/1024/1024 "USED",
dfs.bytes/1024/1024/1024 "free",
round(((ddf.bytes - dfs.bytes) / ddf.bytes) * 100, 2) "percent"
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) ddf,
(select /*+ use_hash */
f.tablespace_name,
sum(decode(sign(nvl(s.bebytes,0)- f.bytes), -1, f.bytes, 0)) bytes,
max(decode(sign(nvl(s.bebytes,0)- f.bytes), -1, f.bytes, 0)) bfbytes,
max(nvl(s.bebytes,0)) bebytes
from (select tablespace_name,
bytes
from dba_free_space) f,
(select tablespace_name,
max(decode(next_extent, null, initial_extent, next_extent)) bebytes
from dba_segments
group by tablespace_name) s
where f.tablespace_name=s.tablespace_name(+)
group by f.tablespace_name) dfs
where ddf.tablespace_name = dfs.tablespace_name(+)
order by ((ddf.bytes - dfs.bytes)/ddf.bytes) desc
/
accept tbs_name prompt 'input tbs_name:' DEFAULT '% '
prompt datafile info
select tablespace_name, file_name, bytes/1024/1024/1024 GB, autoextensible
from dba_data_files
where tablespace_name like '&tbs_name'
order by file_name
/
set verify on
그런데....
(1) 리눅스에서 실행할 때..발생하는 오류상태
SQL> @tbss
TABLESPACE USAGE
SP2-0734: unknown command beginning "group by t..." - rest of line ignored.
SP2-0734: unknown command beginning "where f.ta..." - rest of line ignored.
SP2-0734: unknown command beginning "group by f..." - rest of line ignored.
SP2-0734: unknown command beginning "where ddf...." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "order by (..." - rest of line ignored.
from dba_segments
*
ERROR at line 11:
ORA-00907: missing right parenthesis
input tbs_name:
(2)토-드에서 실행할때...
 
https://i.postimg.cc/nh01QqB3/1.png <---- toad 실행시 tbs입력하라는 창에서 % 부분없애고 대문자로 테이블스페이스이름입력
https://i.postimg.cc/mrky3hSB/2.png
|