RE: How to find out the segments (whether it is table or index) at the end of each datafile in a tablespace.
- From: Herring Dave - dherri <Dave.Herring@xxxxxxxxxx>
- To: "anuragdba@xxxxxxxxx" <anuragdba@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 27 Nov 2011 04:29:37 +0000
Anurag,
Fortunately for you the answer doesn't depend on which version of Oracle you're
running, which I assume is why you didn't include it in your post.
DBA_EXTENTS is your friend in this situation. You can a very simple cursor to
run against this view to list out segments whose extents are holding blocks
with the highest BLOCK_ID value per datafile. I ran into a few situations
where I wanted to see a little more detail, including size of pockets of free
space, so I made that simple query rather complex, so if it helps I've listed
it below. Just call it in sqlplus passing the TABLESPACE_NAME and FILE_ID of
the datafile you're interested in and this will produce an extent map, with the
HWM listed first descending to the "start" of the datafile.
The code is a bit more complex as I've rolled up consecutive extents into
chunks, including listing "free space" the same way, so that you get a better
idea of the impact of moving segments, plus I found it easier to read. One
more thing - the code assumes a 16 KB block size. I was too lazy to join to
DBA_TABLESPACES to get the block size.
SET ECHO off FEEDBACK off HEADING on NUMWIDTH 10
DEFINE TABLESPACE='&1';
DEFINE FILE_ID=&2
DECLARE
v_prior_file_name VARCHAR2(513);
v_prior_owner VARCHAR2(30);
v_prior_segment VARCHAR2(30);
v_segment_kb NUMBER := 0;
v_start_kb NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE(RPAD('.', 60, ' ') || ' Start Pos Length');
DBMS_OUTPUT.PUT_LINE(RPAD('Owner and Segment Name', 60) || ' (KB)
(KB)');
DBMS_OUTPUT.PUT_LINE(RPAD('-', 60, '-') || ' ----------- ---------');
FOR c_extents IN (SELECT e.*, d.file_name
FROM dba_data_files d
, (SELECT owner, segment_name, file_id, (block_id *
16) start_kb
, ((block_id + blocks - 1) * 16) end_kb
FROM dba_extents
WHERE tablespace_name = '&TABLESPACE'
AND file_id = &FILE_ID
UNION ALL
SELECT '*****Free', 'Space*****', file_id,
(block_id * 16) start_kb
, ((block_id + blocks - 1) * 16) end_kb
FROM dba_free_space
WHERE tablespace_name = '&TABLESPACE'
AND file_id = &FILE_ID) e
WHERE d.file_id = e.file_id
ORDER BY start_kb DESC)
LOOP
/*
* If the segment is the same as the previous, continue adding it's space.
*/
IF c_extents.owner = v_prior_owner AND c_extents.segment_name =
v_prior_segment
THEN
v_segment_kb := v_segment_kb + (c_extents.end_kb - c_extents.start_kb);
ELSE
/*
* If v_prior_owner IS NULL, then this is the first row read.
*/
IF v_prior_owner IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(RPAD(v_prior_owner || '.' ||v_prior_segment,
60) || ' ' ||
TO_CHAR(v_start_kb, '9999999999') || ' ' ||
TO_CHAR(v_segment_kb, '99999999'));
END IF;
v_start_kb := c_extents.start_kb;
v_prior_file_name := c_extents.file_name;
v_prior_owner := c_extents.owner;
v_prior_segment := c_extents.segment_name;
v_segment_kb := (c_extents.end_kb - c_extents.start_kb);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(RPAD(v_prior_owner || '.' ||v_prior_segment, 60) || ' '
||
TO_CHAR(v_start_kb, '9999999999') || ' ' ||
TO_CHAR(v_segment_kb, '99999999'));
END;
/
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring@xxxxxxxxxx
TEL 630.944.4762
MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM
The information contained in this communication is confidential, is intended
only for the use of the recipient named above, and may be legally privileged.
If the reader of this message is not the intended recipient, you are hereby
notified that any dissemination, distribution or copying of this communication
is strictly prohibited. If you have received this communication in error,
please resend this communication to the sender and delete the original message
or any copy of it from your computer system. Thank you.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Anurag Verma
Sent: Saturday, November 26, 2011 9:49 PM
To: ORACLE-L
Subject: How to find out the segments (whether it is table or index) at the end
of each datafile in a tablespace.
how to find out the segments (whether it is table or index) at the end of
each datafile in a tablespace.
I am trying to reduce the sizes of several datafiles, but getting the below
error message.
alter database datafile '/u01/oradata/testdb/users01.dbf' resize 12415139840
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
If I can find out the segments, I can move them to another tablespace.
I know, in OEM, we can use tablespace map and find out different segments.
Unfortunately, for this database I have only sqlplus to check the data
dictionary.
No Toad or Grid/database control :(
--
Anurag Verma,
Database Administrator
ERCOT(Electric Reliability Council of Texas),
Texas 76574
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
