Re: Empty BLocks (Holes) in the Datafile
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: bnsarma@xxxxxxxxx, DA Morgan <damorgan@xxxxxxxxx>
- Date: Tue, 1 May 2007 13:29:02 -0700 (PDT)
grid control will show an extent map image for a tablespace.
DB Console->admin->tablespaces
pull-down menu ->Show tablespace Contents
Expand Extent Map:
shows you a pretty picture like below with mouseover data on EVERY block.. so
you can see the segment name (if used), block id, file name, and size. right
from there you can run segment advisor, etc.
so much for sql. i like pictures.
bnsarma@xxxxxxxxx wrote:
On May 1, 11:26 am, DA Morgan wrote:
> bnsa...@xxxxxxxxx wrote:
> > Greetings
>
> > Oracle 10gRel2, on HP-UX
>
> > The Db (550GB) is migrated from 8.1.7.4 to 10gRel2, a year back.
> > During Migration we have CONVERTED Dictionary TBS to LMTS using
> > "dbms_space_admin.tablespace_migrate_to_local"
>
> > We have Deleted around 190 B of Tables and its Indexes that Belongs
> > to 4 different LMTS Tablespaces spread across in total 61 datafiles
> > (for all the 4 TBS)
>
> > For Some reason We are not able to see the space We deleted in these
> > Datafiles,
> > I have PURGED the Recycle Bin, no Luck.
>
> > My Goal is to reclaim the space.
>
> > Please Note We are not using ASSM, so I am guessing SHRINK is out of
> > the question
>
> > I am thinking, since these are converted LMTS, there may be still big
> > holes in the datafiles thats holding the space.
>
> > How can I see these holes (number of Blocks), I tried dba_extents
> > Lead Block - Blocks they look
> > contiguous, Unless I made a mistake in the SQ L.
>
> > Appreciate if any of you can throw more light and share a SQL to
> > see where these holes are located in the datafile, so that If they
> > are at the end (almost) I can move a few segmetns to release the space
> > to resize the datafiles
>
> > Regards & thanks
> > BN
>
> You give no indication of what you are looking at.
>
> Why write "Unless I made a mistake in the SQL" and not post is so we can
> see?
> --
> Daniel A. Morgan
> University of Washington
> damor...@xxxxxxxxxxxxxxxx
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
Greetings Daniel
Here is the info,
Candidate Datafile:
Tablespace
Name Size(M)
Used(M) Used%
-------------------------
------------------------------------------------------------ -------
------- -----
AS_INDEX_ES /u56/oradata/vrasdb/
AS_INDEX_ES_01.dbf 6983 1250 18.00
HWM TOTAL_BLOCKS total_Mb SHRINKAGE_POSSIBLE shrinkage_possible_mb
893739 896000 7000 2262 17.671875
14:56:34 sys@VRASDB> select min(BLOCK_ID), max(BLOCK_ID) from
dba_extents where FILE_ID=18;
MIN(BLOCK_ID) MAX(BLOCK_ID)
------------- -------------
2 893755
Last Segment of the Datafile:
1 select owner, segment_name, segment_type
2 from
3 (select owner, segment_name, segment_type, file_id,
relative_fno, block_id, max(block_id) over
4 (partition by file_id, relative_fno) max_block_id
5 from dba_extents where file_id=18) a
6* where block_id = max_block_id
13:39:14 sys@VRASDB> /
OWNER SEGMENT_NAME Type
---------- ---------------------------- ----------
ASDBA1 XPKSPECIALDELPHICODEDETAILS INDEX
Not sure if I move/rebuild this index into other TBS, how much space I
can get, so I wanted to get a map of the Blocks for this datafile to
see the Block distribution and if other BIG holes exist in the
datafile some where else in the datailfe
Came up with the following Query, Doesn't make sense, (I have not
added header Block)
For the same Segent the blocks look contigious, dont see the holes,
If the blocks are contigious, why am I seeing multiple blocks for each
segment?
The output is not complete, there
1 select SEGMENT_NAME,SEGMENT_TYPE,max(BLOCK_ID),BLOCKS,(BLOCKS
+max(BLOCK_ID)) "Actual_Next",
2 lead(max(BLOCK_ID)) over(order by BLOCK_ID) Real_Next
3 from dba_extents where FILE_ID=18
4* group by
SEGMENT_NAME,block_id,SEGMENT_TYPE,TABLESPACE_NAME,BYTES,BLOCKS
14:47:16 sys@VRASDB> /
SEGMENT_NAME Type MAX(BLOCK_ID) BLOCKS
Actual_Next REAL_NEXT
------------------------------ ---------- ------------- ----------
----------- ----------
XPKROUTINGTYPECODE INDEX 2
130 132 132
XPKBATCHRESTART INDEX 132
130 262 262
XPKAPPLICATIONIDENTIFIER INDEX 262
130 392 392
XPKCENTERROUTINGCRITERIA INDEX 392
130 522 522
XFK1CENTERSTATE INDEX 522
130 652 652
....
5335 rows selected.
Regards
BN
--
//www.freelists.org/webpage/oracle-l
---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check outnew cars at Yahoo! Autos.
Other related posts:
