Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Ugly PL/SQL Code

Ugly PL/SQL Code

From: Charles <cdavis10717_at_comcast.net>
Date: 5 Feb 2004 10:50:10 -0800
Message-ID: <83dbb3cd.0402051050.249941e4@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

; Received on Thu Feb 05 2004 - 12:50:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US