This seems to work for me:
select a.tablespace_name, a.file_name, nvl(b.used,'No')
from dba_data_files a,
(select distinct a.file_id "FILE_ID", 'Yes' "USED" from dba_data_files
a, dba_extents b where a.file_id = b.file_id) b
where a.file_id = b.file_id (+)
order by 1,2
When I specify a specific set of tablespaces on my system I get :
select a.tablespace_name, a.file_name, nvl(b.used,'No')
from dba_data_files a,
(select distinct a.file_id "FILE_ID", 'Yes' "USED" from dba_data_files
a, dba_extents b where a.file_id = b.file_id) b
where a.file_id = b.file_id (+)
and a.tablespace_name in ('XPRPI','XPRPT')
order by 1,2
XPRPI /u07/oradata/DEMO/xprpi02.dbf No
XPRPI /u08/oradata/DEMO/xprpi01.dbf Yes
XPRPT /u02/oradata/DEMO/xprpi02.dbf Yes
XPRPT /u04/oradata/DEMO/xprpt03.dbf Yes
XPRPT /u06/oradata/DEMO/xprpt02.dbf Yes
XPRPT /u07/oradata/DEMO/xprpt01.dbf Yes
This is consistant since I just added the first file.
And, yes, there are utilities to look at the datafile. Oracle's Enterprise
Manager's Performance pack had a tool that will do that
-----Original Message-----
Sent: Wednesday, July 11, 2001 6:45 PM
To: Multiple recipients of list ORACLE-L
If my tablespace became so big that it had 3 datafiles
and became so fragmented, then I export and import
the whole databse, how can I tell if the REORG only
used up one or two datafiles.
OR is there a utility to see contents of a datafile?
Joe
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Leyden, Joseph
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kevin Lange
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
