Reorganizing the DB.. the tricky way

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Mon, 7 Apr 2003 11:46:02 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA70385A929@lnewton.leeds.lfs.co.uk>

Hi Rick,

>> Perhaps there is a way to locate/identify the segments located at the
>> upper end of the file, I just don't know how.

SELECT		tablespace_name, 
		owner, 
		segment_name, 
		block_id as start_block,
		blocks as num_blocks, 
		block_id + blocks -1 as end_block, 
		bytes/1024 as size_kb, 
		NULL as free
FROM 		dba_extents
WHERE 	file_id = &file_id
UNION
SELECT 	tablespace_name, 
		NULL, 
		NULL, 
		block_id as start_block, 
		blocks as num_blocks, 
		block_id + blocks -1 as end_block, 
		bytes/1024 as size_kb, 
		'Free' as free
FROM 		dba_free_space
WHERE 	file_id = &&file_id
ORDER BY 	start_block DESC;

Cheers,
Norman.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

I cannot accept (much) credit for this script, I have a funny feeling it is one of Howard's originally. I used to use it when I was trying to reorg a DMT so that I could identify the extents at the top end of a datafile and shift them prior to a resize downwards of that datafile. Sounds like this is what you need !

Cheers,
Norm.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

Received on Mon Apr 07 2003 - 05:46:02 CDT