Re: Ugly PL/SQL Code

Charles wrote:

> I am clearly no PL/SQL expert, but can anymore make this select less
> complex.
>
> I wanted to get a count of the files for each tablespace within the
> file systems of my server.
>
> The first 21 characters of all my Oracle database file names are the
> file systems names they live on.
>
> It was a one-time thing for me, I got it working, but it could have
> been easier.
>
> What could be done do this to make it less complex? Thanks.
>
> Charles
>
> select
> TS,
> FS,
> count(*)
> from
> (select
> TS,
> substr(DF,1,21) AS FS,
> 1
> from
> (select
> *
> from
> (select
> a.name TS,
> b.name DF
> from
> v$tablespace a,
> v$datafile b
> where
> a.ts# = b.ts#
> order by
> 1, 2)
> order by
> TS,
> DF)
> )
> group by
> TS,
> FS
> order by
> TS,
> FS
> ;

And what is wrong with:

SELECT tablespace_name, count(*)
FROM dba_data_files
GROUP BY tablespace_name;

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)