Link to home
Create AccountLog in
Avatar of TMoperations
TMoperations

asked on

Hot to defrag Oracle datafiles volumes on Windows 2003 NTFS partition.

Hello,
I am looking for a way to defrag an Oracle datafile volume reside on a clustered disk drive (HP MSA500G2) online.
I tried the regular Windows defrag that caused seviere problems on the Oracle database.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
first you should verify that no users are connected to the datbase
then from sql plus run this script
conn / as sysdba

spool c:\reclaim.txt

set linesize 500 pagesize 0 feedback off trimspool on
SELECT '/* '||to_char(CEIL((f.blocks-e.hwm)*(f.bytes/f.blocks)/1024/1024),99999999)||' M */ ' || 'alter database datafile '''||file_name||''' resize '||CEIL(e.hwm*(f.bytes/f.blocks)/1024/1024)||'M;' SQL
FROM
DBA_DATA_FILES f,
SYS.TS$ t,
(SELECT ktfbuefno relative_fno,ktfbuesegtsn ts#,
MAX(ktfbuebno+ktfbueblks) hwm FROM sys.x$ktfbue GROUP BY ktfbuefno,ktfbuesegtsn) e
WHERE
f.relative_fno=e.relative_fno and t.name=f.tablespace_name and t.ts#=e.ts#
and f.blocks-e.hwm > 1000
ORDER BY f.blocks-e.hwm DESC
/

spool off

this script will make a spool file includes some scripts run them to reclaim space in your datafiles
first you should verify that no users are connected to the datbase
then from sql plus run this script
conn / as sysdba

spool c:\reclaim.txt

set linesize 500 pagesize 0 feedback off trimspool on
SELECT '/* '||to_char(CEIL((f.blocks-e.hwm)*(f.bytes/f.blocks)/1024/1024),99999999)||' M */ ' || 'alter database datafile '''||file_name||''' resize '||CEIL(e.hwm*(f.bytes/f.blocks)/1024/1024)||'M;' SQL
FROM
DBA_DATA_FILES f,
SYS.TS$ t,
(SELECT ktfbuefno relative_fno,ktfbuesegtsn ts#,
MAX(ktfbuebno+ktfbueblks) hwm FROM sys.x$ktfbue GROUP BY ktfbuefno,ktfbuesegtsn) e
WHERE
f.relative_fno=e.relative_fno and t.name=f.tablespace_name and t.ts#=e.ts#
and f.blocks-e.hwm > 1000
ORDER BY f.blocks-e.hwm DESC
/

spool off

this script will make a spool file includes some scripts run them to reclaim space in your datafiles