RE: view contents of datafile
Joe, I am reading between the lines here, so bear with me. I think you will be disappointed with the results. Oracle (8.0.x at least) will assign extents in a round robin fashion across all online datafiles in a tablespace. All three files will have some extents in them. The only solution (well, there may be others) is to include a step to drop and recreate the tablespace after the export. And, assuming you export with compress=y, you can create empty tables in the rebuilt tablespace, and know before you import rows if the tablespace is large enough. Mike Hand Polaroid Corp. -Original Message- Sent: Wednesday, July 11, 2001 7: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: Hand, Michael T 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).
RE: view contents of datafile
I change it a little bit.
select a.tablespace_name, a.file_name, decode(b.file_id, NULL, 'No', 'Yes')
from dba_data_files a,
(select distinct a.file_id 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
Alex Hillman
-Original Message-
Sent: Wednesday, July 11, 2001 9:15 PM
To: Multiple recipients of list ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hillman, Alex
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).
RE: view contents of datafile
You can also use DBControl - a later version of the first product to have a Tablespace Map (DBGeneral Tablespace Mananager). -Original Message- Akula Sent: Thursday, July 12, 2001 02:06 To: Multiple recipients of list ORACLE-L hi joe, You can use Tablespace Map in OEM 2.2 (may be 2.1) and also you can reorganise them. or you can use TOAD ( tablespace Map). naren -Original Message- Sent: Thursday, 12 July 2001 11:45 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: Narender Akula 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: Mark Leith 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).
RE: view contents of datafile
hi joe, You can use Tablespace Map in OEM 2.2 (may be 2.1) and also you can reorganise them. or you can use TOAD ( tablespace Map). naren -Original Message- Sent: Thursday, 12 July 2001 11:45 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: Narender Akula 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).
RE: view contents of datafile
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).
view contents of datafile
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).
