This discussion is archived
0 Replies Latest reply: Feb 27, 2012 8:42 PM by 880923 RSS

Query Modification

880923 Newbie
Currently Being Moderated
Dear all,

I require your help to modify the below query. When you check the output am finding that tablespace name column having repeatition of certain tablespace names . In that case of repeated tablespaces I want the one with whose autoextensible column value is 'Yes'. Kindly assist me .

select df.TABLESPACE_NAME,
round(((df.BYTES - fs.BYTES) / df.BYTES) * 100) usage_pct,
round(decode(df.MAXBYTES, 34359721984, 0, (df.BYTES - fs.BYTES) / df.MAXBYTES * 100)) max_pct,
df.AUTOEXTENSIBLE
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES,
AUTOEXTENSIBLE,
decode(AUTOEXTENSIBLE, 'YES', sum(MAXBYTES), sum(BYTES)) MAXBYTES
from dba_data_files
group by TABLESPACE_NAME,
AUTOEXTENSIBLE
)
df,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_free_space
group by TABLESPACE_NAME
)
fs
where df.TABLESPACE_NAME=fs.TABLESPACE_NAME
order by df.TABLESPACE_NAME asc
/

Current output:

TABLESPACE_NAME USAGE_PCT MAX_PCT AUT
------------------------------ ---------- ---------- ---
CORE_DATABASE_DATA 95 0 YES
CORE_DATABASE_DATA 96 96 NO
CORE_DATABASE_INDEX 9 9 NO
DRSYS 24 0 YES
INDX 0 0 YES
NCO_DATA1 26 26 NO
NCO_DATA2 54 54 NO
NCO_DATA3 93 0 YES
NCO_GENERAL 5 0 YES
NCO_GENERAL 5 5 NO
NCO_IDX1 16 16 NO

TABLESPACE_NAME USAGE_PCT MAX_PCT AUT
------------------------------ ---------- ---------- ---
NCO_IDX2 13 13 NO
NCO_IDX3 95 0 YES
STATS 83 2 YES
SYSTEM 100 0 YES
TOOLS 1 0 YES
UNDOTBS1 0 0 YES
USERS 1 0 YES
V2_DATA 100 0 YES
V2_DATA 100 100 NO
XDB 94 0 YES

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points