Shrinking Oracle Data files

Recently, some netizens have mentioned the problem of shrinking Oracle data files, which is a common issue that DBAs often encounter. Usually we need to shrink the corresponding data files to reduce the pressure from disk space and improve the overall performance of the database. But this is not applicable in all cases, especially in production environments. Because the production environment data cleaning is relatively small, so the space waste is also smaller, and once the contraction will re-automatically expand the data file, wasting system resources. For the UAT,DEV environment, multi-DB, disk space pressure is very high, it is necessary to shrink a bit. Tightening your belts and days is a common thing, haha. In summary, shrinking the data file will allow disk space to be freed and speed up data migration, Rman backup, and so on. This article shares Tom Master's shrink script and gives links to undo, temp table space, and table-segment contractions.

Several cases of shrinkage:
Shrink table segment (shrink space)
Shrinking temporary table spaces
Shrink Undo Table Space

1. Presentation of shrinking data files

[SQL]View PlainCopyprint?

2. [Email protected]:~/dba_scripts/custom/sql> sql
6. Sql*plus:release 10.2.0.3.0-production on Wed Oct-15:05:18
10. Copyright (c) 1982, 2006, Oracle.  All rights Reserved.
14. Connected to :
16. Oracle Database 10g Release 10.2.0.3.0-64bit Production
20. Goe[email protected]> @shrink_data_files;
24. VALUE
26. --------------------
28. 8192
32. Smallest
34. Size current Poss.
36. file_name Poss. Size Savings
38. -------------------------------------------------- -------- -------- --------
40. /u02/database/usbotst/oradata/sysusbotst.dbf 605 650
42. /u02/Database/usbotst/oradata/usbotst_archive_idx. 725 1,871 1,146
44. Dbf
48. /u02/database/usbotst/oradata/usbotst_his_idx.dbf 1
50. /u02/database/usbotst/oradata/usbotst_ipo_idx.dbf 7 3
52. /u02/database/usbotst/oradata/usbotst_account_tbl. 6,293 6,293 0
54. Dbf
58. /u02/database/usbotst/oradata/usbotst_rpt_tbl.dbf 373 352
60. /u02/database/usbotst/oradata/usbotst_audit_tbl.db 938 966
62. F
66. /u02/database/usbotst/oradata/tbs_rman01.dbf
68. /u02/database/usbotst/undo/undotbsusbotst.dbf 358 7,350 6,992
70. /u02/database/usbotst/oradata/usbotst_archive_tbl. 760 1,950 1,190
72. Dbf
76. /u02/database/usbotst/oradata/usbotst_rpt_idx.dbf 359 349
78. /u02/database/usbotst/oradata/usbotst_vou_tbl.dbf 4 145 141
80. /u02/database/usbotst/oradata/usbotst_stock_l_tbl. 4
82. Dbf
86. /u02/database/usbotst/oradata/usbotst_ca_idx.dbf 1
88. /u02/database/usbotst/oradata/usbotst_his_tbl.dbf 1 959 958
90. /u02/database/usbotst/oradata/usbotst_vou_idx.dbf 2
92. /u02/database/usbotst/oradata/sysauxusbotst.dbf 697 103
94. /u02/database/usbotst/oradata/spot_data.dbf Bayi
96. /u02/DATABASE/USBOTST/ORADATA/USBOTST_TX_TBL.DBF 103
98. /u02/DATABASE/USBOTST/ORADATA/USBOTST_TX_HIS_TBL.D 878 790
100. Bf
104. /u02/database/usbotst/oradata/usbotst_ca_tbl.dbf 1
106. /u02/database/usbotst/oradata/usbotst_imp_exp_tbl. 108
108. Dbf
112. .........................................................................
116. --------  
118. Sum 29,686 -the total space that can be freed
122. The rows selected.
126. Database altered.
130. Database altered.
134. Database altered.
138. Database altered.
142. Alter database datafile '/u02/database/usbotst/oradata/usbotst_ipo_idx.dbf '
144. *
146. ERROR at line 1:
148. Ora-03297:file contains used data beyond requested RESIZE value
150. ---> Author:leshami--->blog:http://blog.csdn.net/leshami
152. ...........................................
154. -There may be cases where individual files cannot be shrunk, suggesting that the minimum size is exceeded.

2. Shrink Script

[SQL]View PlainCopyprint?

2. --This script can be used for Oracle 10g,11g
4. [Email protected]:~/dba_scripts/custom/sql> more Shrink_data_files.sql
6. Set verify off
8. Col Value Format A20
10. Column file_name format A50 word_wrapped
12. Column smallest format 999,990 heading "smallest| Size|poss. "
14. Column currsize format 999,990 heading "current| Size "
16. Column savings format 999,990 heading "poss.| Savings "
18. Break on the report
20. Compute sum of savings on report
24. Column Value New_val blksize
26. Select value from v$parameter where name = ' db_block_size '
28. /
32. Select file_name,
34. Ceil ((NVL (hwm,1) *&&blksize)/1024/1024) smallest,
36. Ceil (blocks*&&blksize/1024/1024) currsize,
38. Ceil (blocks*&&blksize/1024/1024)-
40. Ceil ((NVL (hwm,1) *&&blksize)/1024/1024) savings
42. From Dba_data_files A,
44. ( select file_id, max (block_id+blocks-1) HWM
46. From dba_extents
48. GROUP by file_id) b
50. where a.file_id = b.file_id (+)
52. /
56. Column cmd format A75 word_wrapped
60. Set heading off feedback off termout off
62. Spool/tmp/tmp_shrink_data_files.sql
64. Select ' ALTER DATABASE datafile '| | file_name| |  "' resize ' | |
66. Ceil ((NVL (hwm,1) *&&blksize)/1024/1024) | | ' m; ' cmd
68. From Dba_data_files A,
70. ( select file_id, max (block_id+blocks-1) HWM
72. From dba_extents
74. GROUP by file_id) b
76. where a.file_id = b.file_id (+)
78. and Ceil (blocks*&&blksize/1024/1024)-
80. Ceil ((NVL (hwm,1) *&&blksize)/1024/1024) > 0
82. /
84. Spool off;
86. Set heading on feedback in termout on
88. @/tmp/tmp_shrink_data_files.sql

Ext.: http://blog.csdn.net/leshami/article/details/13628463

Shrinking Oracle Data files