Re: Ugly PL/SQL Code

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 6 Feb 2004 05:51:26 -0800
Message-ID: <2687bb95.0402060551.6ee8c4f6@posting.google.com>

cdavis10717_at_comcast.net (Charles) wrote in message news:<83dbb3cd.0402051050.249941e4_at_posting.google.com>...
> 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
> ;

Here are the first 5 lines of your query on one of my test systems using the first 9 characters of the file name as the FS:

SQL> @test2

TS                             FS          COUNT(*)
------------------------------ --------- ----------
DATASPC                        /dev/rut1          6
DATASPC                        /dev/rut2          6
IDXSPC                         /dev/rut3          5
IDXSPC                         /dev/rut4          4
RBS1                           /dev/rut1          1


Here are the first 6 lines from my version:
FS        TABLESPACE_NAME                        FN
--------- ------------------------------ ----------
/dev/rut1 DATASPC                                 6
/dev/rut2 DATASPC                                 6
/dev/rut3 IDXSPC                                  5
/dev/rut4 IDXSPC                                  4
/dev/rut1 RBS1                                    1
/dev/rut2 RBS1                                    1

As you can see they match. Here is my shorter version of your query: SQL> l
  1 select substr(file_name,1,9) as FS,

  2         tablespace_name,
  3         count(file_name) as FN
  4  from sys.dba_data_files

  5 group by substr(file_name,1,9), tablespace_name   6* order by tablespace_name, substr(file_name,1,9)

HTH -- Mark D Powell -- Received on Fri Feb 06 2004 - 07:51:26 CST