Hello guys ,
Today I write a SQL statement checking the used percent for all of tablespaces (including TEMP tablespace), it has shown the following error when I run it on my SQL*Plus command line interface.
set linesize 200
set pagesize 200
col ts_name for a25
col auto for a4
col total_gb for 999,999,999.99
col free_gb for 999,999,999.99
col used(%) for 999.99
with a as (select tablespace_name, autoextensible, sum(bytes)/1024/1024/1024 total from dba_data_files group by tablespace_name, autoextensible),
b as (select tablespace_name, sum(bytes)/1024/1024/1024 free from dba_free_space group by tablespace_name),
c as (select tablespace_name, autoextensible, sum(bytes)/1024/1024/1024 total from dba_temp_files group by tablespace_name, autoextensible),
d as (select tablespace_name, sum(bytes_cached)/1024/1024/1024 used from v$temp_extent_pool group by tablespace_name)
select a.tablespace_name ts_name, a.autoextensible auto, a.total as "TOTAL_GB", b.free as "FREE_GB", round((1-b.free/a.total)*100, 2) as "USED(%)"
from a, b
where a.tablespace_name = b.tablespace_name
order by round((1-b.free/a.total)*100, 2) desc
union all
select c.tablespace_name ts_name, c.autoextensible auto, c.total as "TOTAL_GB", c.total - d.used as "FREE_GB", round((d.used/c.total*100), 2) as "USED(%)"
from c, d
where c.tablespace_name = d.tablespace_name
order by round((b.used/a.total*100), 2) desc;
union all
*
ERROR at line 9:
ORA-00933: SQL command not properly ended
If I just check USER tablespaces or TEMP tablespaces and it's both Okay.
set linesize 200
set pagesize 200
col ts_name for a25
col auto for a4
col total_gb for 999,999,999.99
col free_gb for 999,999,999.99
col used(%) for 999.99
with a as (select tablespace_name, autoextensible, sum(bytes)/1024/1024/1024 total from dba_data_files group by tablespace_name, autoextensible),
b as (select tablespace_name, sum(bytes)/1024/1024/1024 free from dba_free_space group by tablespace_name)
select a.tablespace_name ts_name, a.autoextensible auto, a.total as "TOTAL_GB", b.free as "FREE_GB", round((1-b.free/a.total)*100, 2) as "USED(%)"
from a, b
where a.tablespace_name = b.tablespace_name
order by round((1-b.free/a.total)*100, 2) desc;
TS_NAME AUTO TOTAL_GB FREE_GB USED(%)
------------------------- ---- --------------- --------------- -------
SZD_RESOURCE_V2 YES 76.75 1.99 97.41
SZD_BASE_V2 YES 106.89 17.34 83.78
SYSAUX YES 7.87 3.29 58.24
SZD_CAL_V2 YES 8.00 4.54 43.29
UNDOTBS2 YES 4.47 2.90 35.01
USERS YES .00 .00 26.25
NCET_ZTXX NO 1.95 1.65 15.75
SZD_STUDIO_V2 YES 16.00 13.75 14.07
TEST NO .01 .01 10.00
SZD_BBS_V2 YES 16.00 14.43 9.84
SZD_T_V2 YES 16.00 14.51 9.32
SYSTEM YES 24.46 22.78 6.88
SZD_PERFORMANCE_V2 YES 16.00 15.07 5.82
UNDOTBS1 YES 7.74 7.34 5.19
SZD_SNS_V2 YES 16.00 15.55 2.80
SZD_MANAGER_V2 YES 16.00 15.56 2.72
SZD_FAQ_V2 YES 16.00 15.58 2.60
SZD_BAR_V2 YES 16.00 15.92 .51
SZD_RESOURCE_V3 NO 2.44 2.43 .44
SZD_CENTER_V2 YES 16.00 15.98 .11
SZD_MEETING_V2 YES 16.00 15.99 .07
SZD_SMS_V2 YES 16.00 16.00 .01
22 rows selected.
set linesize 200
set pagesize 200
col ts_name for a25
col auto for a4
col total_gb for 999,999,999.99
col free_gb for 999,999,999.99
col used(%) for 999.99
with a as (select tablespace_name, autoextensible, sum(bytes)/1024/1024/1024 total from dba_temp_files group by tablespace_name, autoextensible),
b as (select tablespace_name, sum(bytes_cached)/1024/1024/1024 used from v$temp_extent_pool group by tablespace_name)
select a.tablespace_name ts_name, a.autoextensible auto, a.total as "TOTAL_GB", a.total - b.used as "FREE_GB", round((b.used/a.total*100), 2) as "USED(%)"
from a, b
where a.tablespace_name = b.tablespace_name
order by round((b.used/a.total*100), 2) desc;
TS_NAME AUTO TOTAL_GB FREE_GB USED(%)
------------------------- ---- --------------- --------------- -------
TEMP YES 32.00 14.05 56.08
Why? Please assisting or guiding me to trouble-shooting for my first SQL statement. Thanks in advance.
Best Regards
Quanwen Zhao