Re: To Resize a Datafile to a Smaller Size

Dino,

The following script is one of Howard's from a while back.

SELECT tablespace_name, file_id, owner, segment_name, block_id start_block, blocks num_blocks, block_id + blocks -1 end_block, bytes/1024 kb, '' free
FROM sys.DBA_EXTENTS
WHERE tablespace_name ='TABLESPACE_NAME_GOES_HERE' UNION
SELECT tablespace_name, file_id, '' owner, '' segment_name, block_id start_block, blocks num_blocks, block_id + blocks -1 end_block, bytes/1024 kb, 'Free' free
FROM sys.DBA_FREE_SPACE
WHERE tablespace_name = 'TABLESPACE_NAME_GOES_HERE' ORDER BY 1,2,5 DESC; The following is one I made up to see what is the minimum size I can resize a file don't to :

• Downsize all datafiles to the current HWM
• Automatically adjusts for DB_BLOCK_SIZE
  --
  SELECT 'alter database datafile '''||file_name||''' resize '||last_free_block*value/1024||'k;' "Script" FROM ( SELECT fs.tablespace_name, df.file_name, MAX(fs.block_id) last_free_block FROM sys.DBA_FREE_SPACE fs, sys.DBA_DATA_FILES df WHERE fs.tablespace_name IN ('FOLIOCOM','FOLIOCRD','FOLIOLSE') AND fs.file_id = df.file_id GROUP BY fs.tablespace_name, df.file_name ), v$parameter WHERE name = 'db_block_size';

It produces a script that you can spool off to a file and then run - after checking and/or adjusting the numbers.

Regards,
Norman.

Norman Dunbar		EMail:	NDunbar_at_LynxFinancialSystems.co.uk
Database/Unix administrator	Phone:	0113 289 6265
Lynx Financial Systems Ltd.	Fax:	0113 201 7265
			URL:	http://www.LynxFinancialSystems.com
------------------------------------------------------------------------

-----Original Message-----
From: Dino Hsu [mailto:dino1_nospam_at_ms1.hinet.net] Posted At: Wednesday, August 01, 2001 7:40 AM Posted To: server
Conversation: To Resize a Datafile to a Smaller Size Subject: Re: To Resize a Datafile to a Smaller Size

Dear all,

A related question is:
How to find the schema objects hosted by a tablespace?

Dino Received on Wed Aug 01 2001 - 02:14:45 CDT