| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Empty BLocks (Holes) in the Datafile
On May 1, 11:26 am, DA Morgan <damor..._at_psoug.org> wrote:
> bnsa..._at_gmail.com 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..._at_x.washington.edu
> (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%
HWM TOTAL_BLOCKS total_Mb SHRINKAGE_POSSIBLE shrinkage_possible_mb
893739 896000 7000 2262 17.671875
14:56:34 sys_at_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) a6* where block_id = max_block_id
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_at_VRASDB> /
SEGMENT_NAME Type MAX(BLOCK_ID) BLOCKSActual_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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue May 01 2007 - 14:28:51 CDT
 |
 |