RE: view contents of datafile

From: Kevin Lange <kgel_at_ppoone.com>
Date: Wed, 11 Jul 2001 17:04:03 -0700
Message-ID: <F001.003482C0.20010711171528@fatcity.com>

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: LeydenJ_at_MTA.NET

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: ListGuru_at_fatcity.com (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: kgel_at_ppoone.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------