Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 关于system表空间使用率问题
发新帖
makaveli

39

积分

0

好友

5

主题

关于system表空间使用率问题

1#
发表于 2011-12-7 17:25:22 | 查看: 12958| 回复: 16
ml你好：  我发现在在一个aix上的oracle 10.2.0.3的库上，system表空间大小已经达到7g多，但是实际查询dba_segments视图一共才占用300多m，resize datafile也降不下来，查了下hwm也是在7g多的位置上，system表空间也没有非系统用户的对象，以前这个表是自动扩展的，但是通过查询dba_data_files发现bytes和user_bytes都大于maxsize的值，现在已经改成非自动扩展，请问这个情况该如何降低hwm来回收空间? 谢谢！
  1. select * from dba_data_files where tablespace_name='SYSTEM';

  3.           FILE_NAME        FILE_ID        TABLESPACE_NAME        BYTES        BLOCKS        STATUS        RELATIVE_FNO        AUTOEXTENSIBLE        MAXBYTES        MAXBLOCKS        INCREMENT_BY        USER_BYTES        USER_BLOCKS        ONLINE_STATUS
  4. 1        /oradata/orcl/system01.dbf        1        SYSTEM        7864320000        960000        AVAILABLE        1        NO        0        0        0        7864254464        959992        SYSTEM

  6. select owner,count(*) from dba_SEGMENTs where tablespace_name='SYSTEM' group by owner

  8.            OWNER        COUNT(*)
  9. 1        SYSTEM        246
  10. 2        SYS        951
  11. 3        OUTLN        8

  13. select round(sum(bytes)/1024/1024/1024,2) from dba_segments where tablespace_name='SYSTEM';

  15.            ROUND(SUM(BYTES)/1024/1024/102
  16. 1        0.33

  18.            表空间名        表空间大小(M)        已使用空间(M)        使用百分比        空闲空间(M)
  19. 9        SYSTEM        7500        339           4.52        7161

  21. select max(block_id)*8/1024 "m size" from dba_extents where tablespace_name='SYSTEM'

  23. 7362.0703125
复制代码
2#
发表于 2011-12-8 00:11:40
action plan:

运行以下SQL语句:
  1. REM tablespace report

  3. set linesize 200

  5. select a.tablespace_name,
  6.        round(a.bytes_alloc / 1024 / 1024) megs_alloc,
  7.        round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
  8.        round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
  9.        round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
  10.        100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
  11.        round(maxbytes / 1048576) Max
  12.   from (select f.tablespace_name,
  13.                sum(f.bytes) bytes_alloc,
  14.                sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
  15.           from dba_data_files f
  16.          group by tablespace_name) a,
  17.        (select f.tablespace_name, sum(f.bytes) bytes_free
  18.           from dba_free_space f
  19.          group by tablespace_name) b
  20. where a.tablespace_name = b.tablespace_name(+)
  21. union all
  22. select h.tablespace_name,
  23.        round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
  24.        round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  25.              1048576) megs_free,
  26.        round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
  27.        round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  28.              sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
  29.        100 -
  30.        round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  31.              sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
  32.        round(sum(f.maxbytes) / 1048576) max
  33.   from sys.v_$TEMP_SPACE_HEADER h,
  34.        sys.v_$Temp_extent_pool  p,
  35.        dba_temp_files           f
  36. where p.file_id(+) = h.file_id
  37.    and p.tablespace_name(+) = h.tablespace_name
  38.    and f.file_id = h.file_id
  39.    and f.tablespace_name = h.tablespace_name
  40. group by h.tablespace_name
  41. ORDER BY 1
  42. /

  44.     SELECT d.tablespace_name "Name",
  45.                 TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
  46.                 TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
  47.                 TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
  48.                 TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",
  49.                 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
  50.            FROM sys.dba_tablespaces d,
  51.                 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
  52.                 (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
  53.           WHERE d.tablespace_name = a.tablespace_name(+)
  54.             AND d.tablespace_name = t.tablespace_name(+)
  55.             AND d.extent_management like 'LOCAL'
  56.             AND d.contents like 'TEMPORARY'
  57. /

  59. ttitle -
  60.    center  'Database Freespace Summary'  skip 2

  62. comp sum of nfrags totsiz avasiz on report
  63. break on report

  65. set pages 999
  66. col tsname  format     a16 justify c heading 'Tablespace'
  67. col nfrags  format 999,990 justify c heading 'Free|Frags'
  68. col mxfrag  format 999,999 justify c heading 'Largest|Frag (MB)'
  69. col totsiz  format 999,999 justify c heading 'Total|(MB)'
  70. col avasiz  format 999,999 justify c heading 'Available|(MB)'
  71. col pctusd  format     990 justify c heading 'Pct|Used'

  73. select total.TABLESPACE_NAME tsname,
  74.        D nfrags,
  75.        C/1024/1024 mxfrag,
  76.        A/1024/1024 totsiz,
  77.        B/1024/1024 avasiz,
  78.        (1-nvl(B,0)/A)*100 pctusd
  79. from
  80.     (select sum(bytes) A,
  81.             tablespace_name
  82.             from dba_data_files
  83.             group by tablespace_name) TOTAL,
  84.     (select sum(bytes) B,
  85.             max(bytes) C,
  86.             count(bytes) D,
  87.             tablespace_name
  88.             from dba_free_space
  89.             group by tablespace_name) FREE
  90. where
  91.       total.TABLESPACE_NAME=free.TABLESPACE_NAME(+)
  92. /

  94. SELECT t.tablespace_name,
  95.        CASE
  96.          WHEN t.contents = 'TEMPORARY' AND t.extent_management = 'LOCAL' THEN
  97.           u.bytes
  98.          ELSE
  99.           df.user_bytes - NVL(fs.bytes, 0)
  100.        END / 1024 / 1024 used_mb,
  101.        CASE
  102.          WHEN t.contents = 'TEMPORARY' AND t.extent_management = 'LOCAL' THEN
  103.           df.user_bytes - NVL(u.bytes, 0)
  104.          ELSE
  105.           NVL(fs.bytes, 0)
  106.        END / 1024 / 1024 free_mb,
  107.        fs.min_fragment / 1024 / 1024 min_fragment_mb,
  108.        fs.max_fragment / 1024 / 1024 max_fragment_mb,
  109.        (fs.bytes / 1024 / 1024) / fs.fragments avg_fragment_mb,
  110.        fs.fragments,
  111.        t.status,
  112.        t.contents,
  113.        t.logging,
  114.        t.extent_management,
  115.        t.allocation_type,
  116.        t.force_logging,
  117.        t.segment_space_management,
  118.        t.def_tab_compression,
  119.        t.retention,
  120.        t.bigfile
  121.   FROM dba_tablespaces t,
  122.        (SELECT tablespace_name,
  123.                SUM(bytes) bytes,
  124.                MIN(min_fragment) min_fragment,
  125.                MAX(max_fragment) max_fragment,
  126.                SUM(fragments) fragments
  127.           FROM (SELECT tablespace_name,
  128.                        SUM(bytes) bytes,
  129.                        MIN(bytes) min_fragment,
  130.                        MAX(bytes) max_fragment,
  131.                        COUNT(*) fragments
  132.                   FROM dba_free_space
  133.                  GROUP BY tablespace_name
  134.                 UNION ALL
  135.                 SELECT tablespace_name,
  136.                        SUM(bytes) bytes,
  137.                        MIN(bytes) min_fragment,
  138.                        MAX(bytes) max_fragment,
  139.                        COUNT(*) fragments
  140.                   FROM dba_undo_extents
  141.                  WHERE status = 'EXPIRED'
  142.                  GROUP BY tablespace_name)
  143.          GROUP BY tablespace_name) fs,
  144.        (SELECT tablespace_name, SUM(user_bytes) user_bytes
  145.           FROM dba_data_files
  146.          GROUP BY tablespace_name
  147.         UNION ALL
  148.         SELECT tablespace_name, SUM(user_bytes) user_bytes
  149.           FROM dba_temp_files
  150.          GROUP BY tablespace_name) df,
  151.        (SELECT tablespace_name, SUM(bytes_used) bytes
  152.           FROM gv$temp_extent_pool
  153.          GROUP BY tablespace_name) u
  154. WHERE t.tablespace_name = df.tablespace_name(+)
  155.    AND t.tablespace_name = fs.tablespace_name(+)
  156.    AND t.tablespace_name = u.tablespace_name(+)
  157. /
复制代码
将结果上传 ， 以code代码模式展示

回复 只看该作者 道具 举报

3#
发表于 2011-12-8 10:52:50

  2. SQL> REM tablespace report
  3. SQL> set linesize 200
  4. SQL> select a.tablespace_name,
  5.   2         round(a.bytes_alloc / 1024 / 1024) megs_alloc,
  6.   3         round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
  7.   4         round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
  8.   5         round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
  9.   6         100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
  10.   7         round(maxbytes / 1048576) Max
  11.   8    from (select f.tablespace_name,
  12.   9                 sum(f.bytes) bytes_alloc,
  13. 10                 sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
  14. 11            from dba_data_files f
  15. 12           group by tablespace_name) a,
  16. 13         (select f.tablespace_name, sum(f.bytes) bytes_free
  17. 14            from dba_free_space f
  18. 15           group by tablespace_name) b
  19. 16  where a.tablespace_name = b.tablespace_name(+)
  20. 17  union all
  21. 18  select h.tablespace_name,
  22. 19         round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
  23. 20         round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  24. 21               1048576) megs_free,
  25. 22         round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
  26. 23         round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  27. 24               sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
  28. 25         100 -
  29. 26         round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  30. 27               sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
  31. 28         round(sum(f.maxbytes) / 1048576) max
  32. 29    from sys.v_$TEMP_SPACE_HEADER h,
  33. 30         sys.v_$Temp_extent_pool  p,
  34. 31         dba_temp_files           f
  35. 32  where p.file_id(+) = h.file_id
  36. 33     and p.tablespace_name(+) = h.tablespace_name
  37. 34     and f.file_id = h.file_id
  38. 35     and f.tablespace_name = h.tablespace_name
  39. 36  group by h.tablespace_name
  40. 37  ORDER BY 1
  41. 38  /
  42. TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX
  43. ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
  44. anno_122                              100          8         92          8         92      32768
  45. IM2                                 31828      13704      18124         43         57     362948
  46. IM2_INDEX                           65696       5315      60381          8         92     196608
  47. IM2_TEMP                             3096       3096          0        100          0          0
  48. IM2_TS2                               100        100          0        100          0      32768
  49. IM3WEB                                115          1        114          1         99      32768
  50. JDHXP                                 100         81         19         81         19      32768
  51. P2SYS                                 500        499          1        100          0      32768
  52. P2USER                                500        446         55         89         11      32768
  53. P3USER                                500        497          3         99          1      32768
  54. SYSAUX                                844         92        752         11         89      32768
  55. SYSTEM                               7500       7161        340         95          5       7500
  56. TEMP                                  733        733          0        100          0      32768
  57. TS_AD                                 200         44        156         22         78        200
  58. TS_EDOC                                20         20          0        100          0      32768
  59. UNDOTBS1                             9820       9617        203         98          2      32768
  60. USERS                                 500        489         11         98          2      32768
  61. 17 rows selected
  62. SQL>     SELECT d.tablespace_name "Name",
  63.   2                  TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
  64.   3                  TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
  65.   4                  TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
  66.   5                  TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",
  67.   6                  TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
  68.   7             FROM sys.dba_tablespaces d,
  69.   8                  (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
  70.   9                  (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
  71. 10            WHERE d.tablespace_name = a.tablespace_name(+)
  72. 11              AND d.tablespace_name = t.tablespace_name(+)
  73. 12              AND d.extent_management like 'LOCAL'
  74. 13              AND d.contents like 'TEMPORARY'
  75. 14  /
  76. Name                           Size (M)        HWM (M)       HWM %   Using (M)     Using %
  77. ------------------------------ --------------- ------------- ------- ------------- -------
  78. TEMP                                   733.000       194.000   26.47          .000    0.00
  79. IM2_TEMP                             3,096.000      2778.000   89.73          .000    0.00

复制代码

回复 只看该作者 道具 举报

4#
发表于 2011-12-8 10:53:17
  1. SQL> ttitle -
  2. SQL> center 'Database Freespace Summary' skip 2
  3. SQL> comp sum of nfrags totsiz avasiz on report
  4. SQL> break on report
  5. SQL> set pages 999
  6. SQL> col tsname format a16 justify c heading 'Tablespace'
  7. SQL> col nfrags format 999,990 justify c heading 'Free|Frags'
  8. SQL> col mxfrag format 999,999 justify c heading 'Largest|Frag (MB)'
  9. SQL> col totsiz format 999,999 justify c heading 'Total|(MB)'
  10. SQL> col avasiz format 999,999 justify c heading 'Available|(MB)'
  11. SQL> col pctusd format 990 justify c heading 'Pct|Used'
  12. SQL> select total.TABLESPACE_NAME tsname,
  13. 2 D nfrags,
  14. 3 C/1024/1024 mxfrag,
  15. 4 A/1024/1024 totsiz,
  16. 5 B/1024/1024 avasiz,
  17. 6 (1-nvl(B,0)/A)*100 pctusd
  18. 7 from
  19. 8 (select sum(bytes) A,
  20. 9 tablespace_name
  21. 10 from dba_data_files
  22. 11 group by tablespace_name) TOTAL,
  23. 12 (select sum(bytes) B,
  24. 13 max(bytes) C,
  25. 14 count(bytes) D,
  26. 15 tablespace_name
  27. 16 from dba_free_space
  28. 17 group by tablespace_name) FREE
  29. 18 where
  30. 19 total.TABLESPACE_NAME=free.TABLESPACE_NAME(+)
  31. 20 /

  33. TSNAME NFRAGS MXFRAG TOTSIZ AVASIZ PCT
  34. ---------------- ---------- ---------- ---------- ---------- ---
  35. SYSTEM 5 3309.5625 7500 7160.5 4.5
  36. 266
  37. 666
  38. 666
  39. 666
  40. 7

  42. IM2_INDEX 224 277 65696 5314.5625 91.
  43. 910
  44. 371
  45. 255
  46. 479
  47. 8

  49. USERS 4 488.4375 500 488.625 2.2
  50. 75

  52. anno_122 4 8.0625 100 8.25 91.
  53. 75

  55. TS_AD 18 41.9375 200 43.5625 78.
  56. 218
  57. 75

  59. P2USER 1 445.5 500 445.5 10.
  60. 9

  62. IM2_TS2 1 99.9375 100 99.9375 0.0
  63. 625

  65. TS_EDOC 1 19.9375 20 19.9375 0.3
  66. 125

  68. IM2 171 868.9375 31828 13703.9375 56.
  69. 943
  70. 768
  71. 065
  72. 854

  74. JDHXP 2 80.9375 100 81.0625 18.
  75. 937
  76. 5

  78. IM3WEB 2 0.9375 115 1.3125 98.
  79. 858
  80. 695
  81. 652
  82. 173
  83. 9

  85. SYSAUX 50 63.3125 844 91.8125 89.
  86. 121
  87. 741
  88. 706
  89. 161
  90. 1

  92. P3USER 1 497.125 500 497.125 0.5
  93. 75

  95. UNDOTBS1 60 3968 9820 9617.1875 2.0
  96. 653
  97. 004
  98. 073
  99. 319
  100. 8

  102. P2SYS 1 499.25 500 499.25 0.1
  103. 5


  106. 15 rows selected

  108. SQL> SELECT t.tablespace_name,
  109. 2 CASE
  110. 3 WHEN t.contents = 'TEMPORARY' AND t.extent_management = 'LOCAL' THEN
  111. 4 u.bytes
  112. 5 ELSE
  113. 6 df.user_bytes - NVL(fs.bytes, 0)
  114. 7 END / 1024 / 1024 used_mb,
  115. 8 CASE
  116. 9 WHEN t.contents = 'TEMPORARY' AND t.extent_management = 'LOCAL' THEN
  117. 10 df.user_bytes - NVL(u.bytes, 0)
  118. 11 ELSE
  119. 12 NVL(fs.bytes, 0)
  120. 13 END / 1024 / 1024 free_mb,
  121. 14 fs.min_fragment / 1024 / 1024 min_fragment_mb,
  122. 15 fs.max_fragment / 1024 / 1024 max_fragment_mb,
  123. 16 (fs.bytes / 1024 / 1024) / fs.fragments avg_fragment_mb,
  124. 17 fs.fragments,
  125. 18 t.status,
  126. 19 t.contents,
  127. 20 t.logging,
  128. 21 t.extent_management,
  129. 22 t.allocation_type,
  130. 23 t.force_logging,
  131. 24 t.segment_space_management,
  132. 25 t.def_tab_compression,
  133. 26 t.retention,
  134. 27 t.bigfile
  135. 28 FROM dba_tablespaces t,
  136. 29 (SELECT tablespace_name,
  137. 30 SUM(bytes) bytes,
  138. 31 MIN(min_fragment) min_fragment,
  139. 32 MAX(max_fragment) max_fragment,
  140. 33 SUM(fragments) fragments
  141. 34 FROM (SELECT tablespace_name,
  142. 35 SUM(bytes) bytes,
  143. 36 MIN(bytes) min_fragment,
  144. 37 MAX(bytes) max_fragment,
  145. 38 COUNT(*) fragments
  146. 39 FROM dba_free_space
  147. 40 GROUP BY tablespace_name
  148. 41 UNION ALL
  149. 42 SELECT tablespace_name,
  150. 43 SUM(bytes) bytes,
  151. 44 MIN(bytes) min_fragment,
  152. 45 MAX(bytes) max_fragment,
  153. 46 COUNT(*) fragments
  154. 47 FROM dba_undo_extents
  155. 48 WHERE status = 'EXPIRED'
  156. 49 GROUP BY tablespace_name)
  157. 50 GROUP BY tablespace_name) fs,
  158. 51 (SELECT tablespace_name, SUM(user_bytes) user_bytes
  159. 52 FROM dba_data_files
  160. 53 GROUP BY tablespace_name
  161. 54 UNION ALL
  162. 55 SELECT tablespace_name, SUM(user_bytes) user_bytes
  163. 56 FROM dba_temp_files
  164. 57 GROUP BY tablespace_name) df,
  165. 58 (SELECT tablespace_name, SUM(bytes_used) bytes
  166. 59 FROM gv$temp_extent_pool
  167. 60 GROUP BY tablespace_name) u
  168. 61 WHERE t.tablespace_name = df.tablespace_name(+)
  169. 62 AND t.tablespace_name = fs.tablespace_name(+)
  170. 63 AND t.tablespace_name = u.tablespace_name(+)
  171. 64 /

  173. TABLESPACE_NAME USED_MB FREE_MB MIN_FRAGMENT_MB MAX_FRAGMENT_MB AVG_FRAGMENT_MB FRAGMENTS STATUS CONTENTS LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE FORCE_LOGGING SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION RETENTION BIGFILE
  174. ------------------------------ ---------- ---------- --------------- --------------- --------------- ---------- --------- --------- --------- ----------------- --------------- ------------- ------------------------ ------------------- ----------- -------
  175. SYSTEM 339.4375 7160.5 136.9375 3309.5625 1432.1 5 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
  176. IM2_INDEX 60381.0625 5314.5625 0.0625 277 23.725725446428 224 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  177. USERS 11.3125 488.625 0.0625 488.4375 122.15625 4 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  178. anno_122 91.6875 8.25 0.0625 8.0625 2.0625 4 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  179. TS_AD 156.375 43.5625 0.0625 41.9375 2.4201388888888 18 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  180. P2USER 54.4375 445.5 445.5 445.5 445.5 1 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  181. IM2_TS2 0 99.9375 99.9375 99.9375 99.9375 1 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  182. TS_EDOC 0 19.9375 19.9375 19.9375 19.9375 1 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  183. IM2 18123.3125 13703.9375 0.0625 868.9375 80.139985380117 171 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  184. JDHXP 18.875 81.0625 0.125 80.9375 40.53125 2 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  185. IM3WEB 113.625 1.3125 0.375 0.9375 0.65625 2 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  186. SYSAUX 752.125 91.8125 0.0625 63.3125 1.83625 50 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  187. P3USER 2.8125 497.125 497.125 497.125 497.125 1 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  188. UNDOTBS1 20.125 9799.8125 0.0625 3968 36.980424528301 265 ONLINE UNDO LOGGING LOCAL SYSTEM NO MANUAL DISABLED NOGUARANTEE NO
  189. P2SYS 0.6875 499.25 499.25 499.25 499.25 1 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
  190. TEMP 0 732 ONLINE TEMPORARY NOLOGGING LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO
  191. IM2_TEMP 0 3094 ONLINE TEMPORARY NOLOGGING LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO
复制代码

回复 只看该作者 道具 举报

5#
发表于 2011-12-8 19:52:19
你好，

就输出结果来看system表空间确实有大量空闲空间

SYSTEM                               7500       7161        340         95          5       7500


一般这些空闲空间是可以回收的  但是请注意 resize的大小 过小可能导致 ORA-03297: file contains used data beyond requested RESIZE value 错误



如:
  1. SQL> alter database datafile '/s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf' autoextend on next 200M maxsize unlimited;

  3. Database altered.

  5. SQL> create table tl(t1 int) tablespace system;

  7. Table created.

  9. SQL> alter table tl allocate extent ( size 2000M);

  11. Table altered.

  13. SQL> REM tablespace report
  14. SQL>
  15. SQL> set linesize 200
  16. SQL>
  17. SQL> select a.tablespace_name,
  18.   2         round(a.bytes_alloc / 1024 / 1024) megs_alloc,
  19.   3         round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
  20.   4         round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
  21.   5         round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
  22.   6         100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
  23.   7         round(maxbytes / 1048576) Max
  24.   8    from (select f.tablespace_name,
  25.   9                 sum(f.bytes) bytes_alloc,
  26. 10                 sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
  27. 11            from dba_data_files f
  28. 12           group by tablespace_name) a,
  29. 13         (select f.tablespace_name, sum(f.bytes) bytes_free
  30. 14            from dba_free_space f
  31. 15           group by tablespace_name) b
  32. 16  where a.tablespace_name = b.tablespace_name(+)
  33. 17  union all
  34. 18  select h.tablespace_name,
  35. 19         round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
  36. 20         round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  37. 21               1048576) megs_free,
  38. 22         round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
  39. 23         round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  40. 24               sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
  41. 25         100 -
  42. 26         round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  43. 27               sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
  44. 28         round(sum(f.maxbytes) / 1048576) max
  45. 29    from sys.v_$TEMP_SPACE_HEADER h,
  46. 30         sys.v_$Temp_extent_pool  p,
  47. 31         dba_temp_files           f
  48. 32  where p.file_id(+) = h.file_id
  49. 33     and p.tablespace_name(+) = h.tablespace_name
  50. 34     and f.file_id = h.file_id
  51. 35     and f.tablespace_name = h.tablespace_name
  52. 36  group by h.tablespace_name
  53. 37  ORDER BY 1
  54. 38  /

  56. TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX
  57. ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
  58. EXAMPLE                               171          3        168          2         98      32768
  59. SYSAUX                                310         44        266         14         86      32768
  60. SYSTEM                               5700        190       5510          3         97      32768
  61. TEMP                                 2968       2968          0        100          0      32768
  62. UNDOTBS1                             1165        825        340         71         29      32768
  63. USERS                                8985         36       8950          0        100      32768

  65. 6 rows selected.

  67. SQL> drop table tl;

  69. Table dropped.

  71. SQL> REM tablespace report
  72. SQL>
  73. SQL> set linesize 200
  74. SQL>
  75. SQL> select a.tablespace_name,
  76.   2         round(a.bytes_alloc / 1024 / 1024) megs_alloc,
  77.   3         round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
  78.   4         round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
  79.   5         round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
  80.   6         100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
  81.   7         round(maxbytes / 1048576) Max
  82.   8    from (select f.tablespace_name,
  83.   9                 sum(f.bytes) bytes_alloc,
  84. 10                 sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
  85. 11            from dba_data_files f
  86. 12           group by tablespace_name) a,
  87. 13         (select f.tablespace_name, sum(f.bytes) bytes_free
  88. 14            from dba_free_space f
  89. 15           group by tablespace_name) b
  90. 16  where a.tablespace_name = b.tablespace_name(+)
  91. 17  union all
  92. 18  select h.tablespace_name,
  93. 19         round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
  94. 20         round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  95. 21               1048576) megs_free,
  96. 22         round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
  97. 23         round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  98. 24               sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
  99. 25         100 -
  100. 26         round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  101. 27               sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
  102. 28         round(sum(f.maxbytes) / 1048576) max
  103. 29    from sys.v_$TEMP_SPACE_HEADER h,
  104. 30         sys.v_$Temp_extent_pool  p,
  105. 31         dba_temp_files           f
  106. 32  where p.file_id(+) = h.file_id
  107. 33     and p.tablespace_name(+) = h.tablespace_name
  108. 34     and f.file_id = h.file_id
  109. 35     and f.tablespace_name = h.tablespace_name
  110. 36  group by h.tablespace_name
  111. 37  ORDER BY 1
  112. 38  /

  114. TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX
  115. ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
  116. EXAMPLE                               171          3        168          2         98      32768
  117. SYSAUX                                310         44        266         14         86      32768
  118. SYSTEM                               5700       2242       3458         39         61      32768
  119. TEMP                                 2968       2968          0        100          0      32768
  120. UNDOTBS1                             1165        825        340         71         29      32768
  121. USERS                                8985         36       8950          0        100      32768

  123. 6 rows selected.

  125. SQL> alter database datafile '/s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf' resize 5000M;

  127. Database altered.

  129. SQL> REM tablespace report
  130. SQL>
  131. SQL> set linesize 200
  132. SQL>
  133. SQL> select a.tablespace_name,
  134.   2         round(a.bytes_alloc / 1024 / 1024) megs_alloc,
  135.   3         round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
  136.   4         round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
  137.   5         round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
  138.   6         100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
  139.   7         round(maxbytes / 1048576) Max
  140.   8    from (select f.tablespace_name,
  141.   9                 sum(f.bytes) bytes_alloc,
  142. 10                 sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
  143. 11            from dba_data_files f
  144. 12           group by tablespace_name) a,
  145. 13         (select f.tablespace_name, sum(f.bytes) bytes_free
  146. 14            from dba_free_space f
  147. 15           group by tablespace_name) b
  148. 16  where a.tablespace_name = b.tablespace_name(+)
  149. 17  union all
  150. 18  select h.tablespace_name,
  151. 19         round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
  152. 20         round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  153. 21               1048576) megs_free,
  154. 22         round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
  155. 23         round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  156. 24               sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
  157. 25         100 -
  158. 26         round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  159. 27               sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
  160. 28         round(sum(f.maxbytes) / 1048576) max
  161. 29    from sys.v_$TEMP_SPACE_HEADER h,
  162. 30         sys.v_$Temp_extent_pool  p,
  163. 31         dba_temp_files           f
  164. 32  where p.file_id(+) = h.file_id
  165. 33     and p.tablespace_name(+) = h.tablespace_name
  166. 34     and f.file_id = h.file_id
  167. 35     and f.tablespace_name = h.tablespace_name
  168. 36  group by h.tablespace_name
  169. 37  ORDER BY 1
  170. 38  /

  172. TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX
  173. ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
  174. EXAMPLE                               171          3        168          2         98      32768
  175. SYSAUX                                310         44        266         14         86      32768
  176. SYSTEM                               5000       1542       3458         31         69      32768
  177. TEMP                                 2968       2968          0        100          0      32768
  178. UNDOTBS1                             1165        825        340         71         29      32768
  179. USERS                                8985         36       8950          0        100      32768

  181. 6 rows selected.

  183. SQL>  alter database datafile '/s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf' resize 3460;
  184. alter database datafile '/s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf' resize 3460
  185. *
  186. ERROR at line 1:
  187. ORA-03214: File Size specified is smaller than minimum required


  190. SQL>  alter database datafile '/s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf' resize 3460M;
  191. alter database datafile '/s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf' resize 3460M
  192. *
  193. ERROR at line 1:
  194. ORA-03297: file contains used data beyond requested RESIZE value


  197. SQL> alter database datafile '/s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf' resize 3470M;

  199. Database altered.

  201. SQL> REM tablespace report
  202. SQL>
  203. SQL> set linesize 200
  204. SQL>
  205. SQL> select a.tablespace_name,
  206.   2         round(a.bytes_alloc / 1024 / 1024) megs_alloc,
  207.   3         round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
  208.   4         round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
  209.   5         round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
  210.   6         100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
  211.   7         round(maxbytes / 1048576) Max
  212.   8    from (select f.tablespace_name,
  213.   9                 sum(f.bytes) bytes_alloc,
  214. 10                 sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
  215. 11            from dba_data_files f
  216. 12           group by tablespace_name) a,
  217. 13         (select f.tablespace_name, sum(f.bytes) bytes_free
  218. 14            from dba_free_space f
  219. 15           group by tablespace_name) b
  220. 16  where a.tablespace_name = b.tablespace_name(+)
  221. 17  union all
  222. 18  select h.tablespace_name,
  223. 19         round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
  224. 20         round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  225. 21               1048576) megs_free,
  226. 22         round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
  227. 23         round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  228. 24               sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
  229. 25         100 -
  230. 26         round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
  231. 27               sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
  232. 28         round(sum(f.maxbytes) / 1048576) max
  233. 29    from sys.v_$TEMP_SPACE_HEADER h,
  234. 30         sys.v_$Temp_extent_pool  p,
  235. 31         dba_temp_files           f
  236. 32  where p.file_id(+) = h.file_id
  237. 33     and p.tablespace_name(+) = h.tablespace_name
  238. 34     and f.file_id = h.file_id
  239. 35     and f.tablespace_name = h.tablespace_name
  240. 36  group by h.tablespace_name
  241. 37  ORDER BY 1
  242. 38  /

  244. TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX
  245. ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
  246. EXAMPLE                               171          3        168          2         98      32768
  247. SYSAUX                                310         44        266         14         86      32768
  248. SYSTEM                               3470         12       3458          0        100      32768
  249. TEMP                                 2968       2968          0        100          0      32768
  250. UNDOTBS1                             1165        825        340         71         29      32768
  251. USERS                                8985         36       8950          0        100      32768

  253. 6 rows selected.
复制代码

回复 只看该作者 道具 举报

6#
发表于 2011-12-8 20:34:23
您所说如果设置的值过小会导致ORA-03297: file contains used data beyond requested RESIZE value

导致这个错误的原因是因为resize的大小 小于hwm水位线的值就会报这个错误吗？

回复 只看该作者 道具 举报

7#
发表于 2011-12-8 23:54:33
实际上datafile 没有高水位high water mark一说 ：there’s no HWM for datafiles, it’s just a virtual term to describe the last block containing data in the data file, which is the minimum size allowed for sizing down the data file.

你可以尝试换一个大小去resize ， 把操作过程贴出来

回复 只看该作者 道具 举报

8#
发表于 2011-12-12 16:16:26
  1. SQL> ALTER DATABASE DATAFILE '/oradata/brjl/system01.dbf' RESIZE 7000M;
  2. ALTER DATABASE DATAFILE '/oradata/brjl/system01.dbf' RESIZE 7000M
  3. *
  4. ERROR at line 1:
  5. ORA-03297: file contains used data beyond requested RESIZE value


  8. SQL> ALTER DATABASE DATAFILE '/oradata/brjl/system01.dbf' RESIZE 7200m;
  9. ALTER DATABASE DATAFILE '/oradata/brjl/system01.dbf' RESIZE 7200m
  10. *
  11. ERROR at line 1:
  12. ORA-03297: file contains used data beyond requested RESIZE value
复制代码

回复 只看该作者 道具 举报

9#
发表于 2011-12-12 18:51:17
  1. set linesize 140 pagesize 1400
  2. col segment_name for a40
  3. select segment_name,segment_type,block_id from dba_extents where file_id=1 order by block_id desc ;
复制代码
查一下

回复 只看该作者 道具 举报

10#
发表于 2011-12-12 21:27:17

回复 9# 的帖子

我第一个帖子里查过max的block_id是7362，我知道现在是只能resize到7362附近，但是实际只使用了300多m，我是想能大幅度缩小system表空间的大小，但是我又不太敢随便shrink或者做别的操作，现在有办法回缩表空间大小吗还？

select max(block_id)*8/1024 "m size" from dba_extents where tablespace_name='SYSTEM'

7362.0703125

回复 只看该作者 道具 举报

11#
发表于 2011-12-12 21:33:06

回复 10# 的帖子

查一下 该Extent属于哪个对象？  因该不是system表空间原有的段

[ 本帖最后由 maclean 于 2011-12-12 21:39 编辑 ]

回复 只看该作者 道具 举报

12#
发表于 2011-12-13 11:26:17
  1. SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCK_ID from dba_extents where tablespace_name='SYSTEM' ORDER BY BLOCK_ID DESC;

  3. SEGMENT_NAME SEGMENT_TYPE BLOCK_ID
  4. -------------------------------------------------------------------------------- ------------------ ----------
  5. C_OBJ#_INTCOL# CLUSTER 942345
  6. HIST_HEAD$ TABLE 942217
  7. C_OBJ#_INTCOL# CLUSTER 868361
  8. I_H_OBJ#_COL# INDEX 868233
  9. SUMPARTLOG$ TABLE 84281
  10. I_OBJ#_INTCOL# INDEX 84273
  11. I_OBJ1 INDEX 84265
  12. C_COBJ# CLUSTER 84257
  13. C_TS# CLUSTER 84249
  14. TABPART$ TABLE 84241
  15. I_MON_MODS$_OBJ INDEX 84233
  16. C_OBJ#_INTCOL# CLUSTER 84105
  17. C_OBJ#_INTCOL# CLUSTER 83977
  18. SOURCE$ TABLE 42505
  19. IDL_UB2$ TABLE 42377
  20. C_FILE#_BLOCK# CLUSTER 42249
  21. FGA_LOG$ TABLE 42121
  22. FGA_LOG$ TABLE 41993
  23. FGA_LOG$ TABLE 41865
复制代码

回复 只看该作者 道具 举报

13#
发表于 2011-12-13 21:56:24
How to Resize a Datafile


Applies to:  Oracle Server - Enterprise Edition - Version: 7.3.0.0 and later   [Release: 7.3.0 and later ]
Information in this document applies to any platform.
PurposeThis note will discuss resizing of Oracle datafiles (larger or smaller)
Scope and ApplicationThe intended audience for this document is for experienced DBA's  
How to Resize a DatafileOracle file sizing is vital part of managing Oracle databases

In older versions (Oracle 7.1 and lower) the only methods available to resize tablespace storage were

    * Drop and recreate the tablespace with different sized datafiles (decrease / shrink)
    * Add one or more datafiles to the tablespace (increase)

Beginning in Oracle 7.2 Oracle introduced the ALTER DATABASE DATAFILE .... RESIZE command.

This option allows you to change the physical size of a datafile from what was specified during its creation.

  Attempting to use the RESIZE command on versions prior to 7.2 will receive the following error:

ORA-00923: FROM keyword not found where expected

  =============================
1. Increase Datafile Size
=============================To increase the size of a datafile, you would use the command:

  ALTER DATABASE DATAFILE '<full path and name of the file>' RESIZE [K|M|G];

where the size specified is larger than the existing file size.

The current size of a datafile may be found by querying V$DATAFILE.BYTES or DBA_DATA_FILES.BYTES

Examples:

  column file_name format a45

select file_name, bytes from dba_data_files where file_name like '%test%';

FILE_NAME                                     BYTES
--------------------------------------------- ----------
/u01/app/oracle/oradata/v10205/test.dbf          1048576


ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/v10205/test.dbf' RESIZE 10567680;

    In this example the size before resize was 1048576 bytes and after the resize 10567680 bytes

  SELECT FILE#, STATUS, ENABLED, CHECKPOINT_CHANGE#, BYTES, CREATE_BYTES, NAME FROM V$DATAFILE;

FILE#  STATUS  ENABLED    CHECKPOINT BYTES      CREATE_BYT NAME
------ ------- ---------- ---------- ---------- ---------- ----------------
5      ONLINE  READ WRITE 7450       2097152    102400     /databases/oracle/test.dbf


   In this example the file was created with a size of 100K (CREATE_BYTES) and is currently 2 MB in size ... this shows that the file was resized.

  =============================
2. Decrease Datafile Size
=============================
To decrease the size of a datafile, you would use the command:

  ALTER DATABASE DATAFILE '<full path and name of the file>' RESIZE [K|M|G];

where the size specified is smaller than the existing file size.

The current size of a datafile may be found by querying V$DATAFILE.BYTES or DBA_DATA_FILES.BYTES (see section 1 for examples)

Reducing the size of a datafile is more complicated than increasing the size of a datafile as space cannot be deallocated from a datafile that is currently being used by database objects.

To remove space from a datafile, you have to have contiguous free space at the END of the datafile.  

The view DBA_FREE_SPACE is used to display the free space (space not being used) in a datafile.

For example:

  SELECT * FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME='TEST'
ORDER BY BLOCK_ID;

TABLESPACE_NAME    FILE_ID    BLOCK_ID   BYTES      BLOCKS
------------------ ---------- ---------- ---------- ----------
TEST                        5          2     102400         50
TEST                        5         55      96256         47
TEST                        5        102    1890304        923

There are two large extents at the high end of the datafile
  
  • BLOCK_ID=55 contains 47 blocks
  • BLOCK_ID=102 contains 923 blocks
This means there are 1986560 unused bytes at the end of our datafile, almost 2 MB.

We want to leave some room for growth in our datafile, and depending on how the objects in that datafile allocate new extents, we could remove up to 1.89 MB of disk space from the datafile without damaging any objects in the tablespace.

If you have a large extent in the middle of a datafile, and some object taking up room at the end of the datafile, you can use the query FINDEXT.SQL below to find this object. If you export this object, then drop it, you should then free up contiguous space at the end of your datafile so you will be able to resize it smaller.

Make sure you leave enough room in the datafile for importing the object back into the tablespace.

  =============================
3. Cautions and Warnings and Notes
=============================
A) For safety reasons, you should take a backup of your database whenever you change its structure, which includes altering the size of datafiles.

B) If you try to resize a datafile to a size smaller than is needed to contain all the database objects in that datafile, the following error is returned:

ORA-03297: file contains blocks of data beyond requested RESIZE value

Or

ORA-03297: file contains used data beyond requested RESIZE value

The resize operation will fail at this point.

C) If you try to resize a datafile larger than can be created, you will also get an error.

For instance, if trying to create a file of 2 GB, without having 2 GB of available disk space the following errors will be returned:

ORA-01237: cannot extend datafile
ORA-01110: data file : ''
ORA-19502: write error on file "", blockno (blocksize=)
ORA-27072: File I/O error

If you check V$DATAFILE, you will see that the file size does not change unless the operation is successful.

D) MANUAL versus AUTOMATIC extension:

Be careful giving datafiles the AUTOEXTEND attribute, and make sure to specify the NEXT and MAXSIZE parameters.

If using dictionary managed tablespaces (DMT)
  
  • set appropriate values for the default storage parameters on tablespace level and the MAXEXTENTS parameter
  • avoid UNLIMITED sizes and extents to prevent objects with a very high number of extents to be created which causes not only a huge number of records in the dictionary tables, but dropping them can tade long time with SMON consuming a lot of CPU resources
If using multiple database writers (db_writers > 1) in RDBMS versions below version 7.3.4.1, might be encounterd. This appears as ORA-7374 errors when accessing the datafile after it has been resized.  The workaround is to shutdown and restart the database after resizing a datafile (a convenient time to take a backup). This will cause the new datafile size information to be refreshed to all the DBWR slave processes.

E) Please be aware that beside the command 'ALTER DATABASE DATAFILE ... RESIZE ... ' there are still alternatives like the export and import utility (including DataPump) to perform a resize of a datafile. Using export/import will lead also result in a reorganization of the objects in the tablespace related to the datafile.
  =============================
4. SCRIPTS
=============================
  FINDEXT.SQL  
  -- FINDEXT.SQL

-- This script lists all the extents contained in that datafile,
-- the block_id where the extent starts,
-- and how many blocks the extent contains.
-- It also shows the owner, segment name, and segment type.

-- Input: FILE_ID from DBA_DATA_FILES or FILE# from V$DATAFILE

SET ECHO OFF
SET PAGESIZ 25

column file_name format a50
select file_name, file_id from dba_data_files order by 2;

ttitle -
center 'Segment Extent Summary' skip 2

col ownr format a8 heading 'Owner' justify c
col type format a8 heading 'Type' justify c trunc
col name format a30 heading 'Segment Name' justify c
col exid format 990 heading 'Extent#' justify c
col fiid format 9990 heading 'File#' justify c
col blid format 99990 heading 'Block#' justify c
col blks format 999,990 heading 'Blocks' justify c

select owner ownr, segment_name name, segment_type type, extent_id exid, file_id fiid, block_id blid, blocks blks
from dba_extents
where file_id = &file_id
order by block_id
/

Sample Output:

  SQL> @findext.sql

FILE_NAME FILE_ID
-------------------------------------------------- ----------
/u01/app/oracle/oradata/v10205/system01.dbf 1
/u01/app/oracle/oradata/v10205/undotbs01.dbf 2
/u01/app/oracle/oradata/v10205/sysaux01.dbf 3
/u01/app/oracle/oradata/v10205/users01.dbf 4

Enter value for file_id: 4
old 3: where file_id = &file_id
new 3: where file_id = 4

Segment Extent Summary

Owner    Segment Name                   Type     Extent# File# Block# Blocks
-------- ------------------------------ -------- ------- ----- ------ --------
USER     EMP                            TABLE          0     4      2        5
USER     TAB3                           TABLE          0     4    108        5
USER     TEST                           TABLE          0     4    348        5
USER     PK_EMP                         INDEX          0     4    483        5
USER     EMP                            TABLE          1     4    433        5
USER     EMP                            TABLE          2     4    438       10
USER     PK_EMP                         INDEX          1     4    488       10
  
  SHRINK_DATAFILE.SQL

  -- SHRINK_DATAFILE.SQL

-- This script lists the object names and types that must be moved in order to resize a datafile to a specified smaller size

-- Input: FILE_ID from DBA_DATA_FILES or FILE# from V$DATAFILE
-- Size in bytes that the datafile will be resized to

SET SERVEROUTPUT ON

DECLARE
     V_FILE_ID NUMBER;
     V_BLOCK_SIZE NUMBER;
     V_RESIZE_SIZE NUMBER;
BEGIN
     V_FILE_ID := &FILE_ID;
     V_RESIZE_SIZE := &RESIZE_FILE_TO;

     SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID;

     DBMS_OUTPUT.PUT_LINE('.');
     DBMS_OUTPUT.PUT_LINE('.');
     DBMS_OUTPUT.PUT_LINE('.');
     DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '||V_RESIZE_SIZE||' BYTES');
     DBMS_OUTPUT.PUT_LINE('===================================================================');
     DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS');
     DBMS_OUTPUT.PUT_LINE('===================================================================');

     for my_record in (
          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
          FROM DBA_EXTENTS
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE
          AND FILE_ID = V_FILE_ID
          AND SEGMENT_TYPE NOT LIKE '%PARTITION%'
          ORDER BY 1) LOOP
               DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
     END LOOP;

     DBMS_OUTPUT.PUT_LINE('===================================================================');
     DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS');
     DBMS_OUTPUT.PUT_LINE('===================================================================');

     for my_record in (
          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - PARTITION = '||PARTITION_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
          FROM DBA_EXTENTS
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE
          AND FILE_ID = V_FILE_ID
          AND SEGMENT_TYPE LIKE '%PARTITION%'
          ORDER BY 1) LOOP
               DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
     END LOOP;

END;
/


Sample Output:

  SQL > @SHRINK_DATAFILE.SQL

Enter value for file_id: 2
old 6: V_FILE_ID := &FILE_ID;
new 6: V_FILE_ID := 2;
Enter value for resize_file_to: 300000000
old 7: V_RESIZE_SIZE := &RESIZE_FILE_TO;
new 7: V_RESIZE_SIZE := 300000000;
.
.
.
OBJECTS IN FILE 2 THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO 300000000 BYTES
===================================================================
NON-PARTITIONED OBJECTS
===================================================================
SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST - OBJECT TYPE = INDEX
...

===================================================================
PARTITIONED OBJECTS
===================================================================
SYS.WRH$_ACTIVE_SESSION_HISTORY - PARTITION = WRH$_ACTIVE_512113771_430 - OBJECT TYPE = TABLE PARTITION
...
    =============================
5. CASE STUDY
=============================sqlplus / as sysdba

    -- Setup for the testcase
alter system set recyclebin=on;

create tablespace test datafile '/u01/app/oracle/oradata/v10205/test.dbf' size 100m extent management local uniform size 1m;

select bytes from v$datafile where file# = 5;

    BYTES
----------
104857600


!ls -alt /u01/app/oracle/oradata/v10205/test.dbf

    -rw-r----- 1 oracle dba 104865792 Apr 20 07:49 /u01/app/oracle/oradata/v10205/test.dbf

create user test identified by test;
grant dba to test;
alter user test default tablespace test;
connect test/test;
    -- Fill up the TEST Tablespace
BEGIN
   for i in 1..19 LOOP
      execute immediate 'create table ' || 'TEST'||i ||' as select * from dba_objects';
   end loop;
end;
/

select bytes-ebytes from (select sum(bytes) ebytes from dba_extents where file_id=5), dba_data_files where file_id=5;

    BYTES-EBYTES
------------
5242880

select sum(bytes) from dba_free_space where file_id=5;

    SUM(BYTES)
----------
4194304
    -- Examine the extent storage for the TEST datafile with FINDEXT.SQL
@FINDEXT

                                      Segment Extent Summary

FILE_NAME                                             FILE_ID
-------------------------------------------------- ----------
/u01/app/oracle/oradata/v10205/system01.dbf                 1
/u01/app/oracle/oradata/v10205/undotbs01.dbf                2
/u01/app/oracle/oradata/v10205/sysaux01.dbf                 3
/u01/app/oracle/oradata/v10205/users01.dbf                  4
/u01/app/oracle/oradata/v10205/test.dbf                     5

Enter value for file_id: 5
old   3: where file_id = &file_id
new   3: where file_id = 5

                             Segment Extent Summary

Owner            Segment Name            Type   Extent# File# Block#  Blocks
-------- ------------------------------ -------- ------- ----- ------ --------
TEST     TEST1                          TABLE          0     5      9      128
TEST     TEST1                          TABLE          1     5    137      128
TEST     TEST1                          TABLE          2     5    265      128
TEST     TEST1                          TABLE          3     5    393      128
TEST     TEST1                          TABLE          4     5    521      128
TEST     TEST2                          TABLE          0     5    649      128
...
TEST     TEST18                         TABLE          3     5  11273      128
TEST     TEST18                         TABLE          4     5  11401      128
TEST     TEST19                         TABLE          0     5  11529      128
TEST     TEST19                         TABLE          1     5  11657      128
TEST     TEST19                         TABLE          2     5  11785      128
TEST     TEST19                         TABLE          3     5  11913      128
TEST     TEST19                         TABLE          4     5  12041      128   

-- DROP TABLES TEST2 TO TEST17 AND TEST19

BEGIN
   for i in 2..17 LOOP
      execute immediate 'DROP table ' || 'TEST'||i;
   end loop;
   execute immediate 'DROP TABLE TEST19';
end;
/
    -- Examine the storage for the TEST datafile with FINDEXT.SQL
@FINDEXT

                                       Segment Extent Summary

FILE_NAME                                             FILE_ID
-------------------------------------------------- ----------
/u01/app/oracle/oradata/v10205/system01.dbf                 1
/u01/app/oracle/oradata/v10205/undotbs01.dbf                2
/u01/app/oracle/oradata/v10205/sysaux01.dbf                 3
/u01/app/oracle/oradata/v10205/users01.dbf                  4
/u01/app/oracle/oradata/v10205/test.dbf                     5

Enter value for file_id: 5
old   3: where file_id = &file_id
new   3: where file_id = 5

                             Segment Extent Summary

Owner            Segment Name            Type   Extent# File# Block#  Blocks
-------- ------------------------------ -------- ------- ----- ------ --------
TEST     TEST1                          TABLE          0     5      9      128
TEST     TEST1                          TABLE          1     5    137      128
TEST     TEST1                          TABLE          2     5    265      128
TEST     TEST1                          TABLE          3     5    393      128
TEST     TEST1                          TABLE          4     5    521      128
TEST     TEST18                         TABLE          0     5  10889      128
TEST     TEST18                         TABLE          1     5  11017      128
TEST     TEST18                         TABLE          2     5  11145      128
TEST     TEST18                         TABLE          3     5  11273      128
TEST     TEST18                         TABLE          4     5  11401      128   

-- NOTE THAT THERE IS NOW A LARGE HOLE IN THE MIDDLE OF THE TEST TABLESPACE DATAFILE
    -- Examine the Recylebin Contents for the Test Tablespace
SELECT ORIGINAL_NAME FROM DBA_RECYCLEBIN WHERE TS_NAME = 'TEST';

    ORIGINAL_NAME
--------------------------------
TEST2
TEST3
TEST4
TEST5
TEST6
TEST7
TEST8
TEST9
TEST10
TEST11
TEST12
TEST13
TEST14
TEST15
TEST16
TEST17
TEST19

    -- Determine the the amount of data remaining in active extents in the TEST tablespace datafile
SELECT SUM(BYTES) FROM DBA_EXTENTS WHERE FILE_ID = 5;

SUM(BYTES)
----------
10485760

-- Remember .. when resizing datafiles smaller .. extra space should be allowed for future growth as well as space for the datafile headers
--      this test was run with an overhead of 10 (8K) blocks
    -- Determine what objects must be moved in order for the resize to succeed
@SHRINK_DATAFILE.SQL

    Enter value for file_id: 5
old 6: V_FILE_ID := &FILE_ID;
new 6: V_FILE_ID := 5;
Enter value for resize_file_to: 10567680
old 7: V_RESIZE_SIZE := &RESIZE_FILE_TO;
new 7: V_RESIZE_SIZE := 10567680;
.
.
.
Objects in File 5 that must move in order to resize the file to 10567680 BYTES
=================================================
TEST.TEST18 - OBJECT TYPE = TABLE

-- Show that the resize will not succeed with the objects in their present position

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/v10205/test.dbf' RESIZE 10567680;

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/v10205/test.dbf' RESIZE 10567680
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

-- Move the TEST18 table to another tablespace so that the resize can succeed

ALTER TABLE TEST18 MOVE TABLESPACE USERS;

-- Attempt to resize the table again

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/v10205/test.dbf' RESIZE 10567680;

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/v10205/test.dbf' RESIZE 10567680
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

-- The reason that this resize failed is there are still objects in the recycle bin ... they need to removed before the resize can succeed

purge tablespace test;

-- Tablespace purged.

-- Attempt the resize again

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/v10205/test.dbf' RESIZE 10567680;

-- Database altered.

select bytes from v$datafile where file# = 5;

    BYTES
----------
10567680

!ls -alt /u01/app/oracle/oradata/v10205/test.dbf

    -rw-r----- 1 oracle dba 10575872 Apr 20 06:19 /u01/app/oracle/oradata/v10205/test.dbf



-- Return the TEST18 table to the TEST tablespace

ALTER TABLE TEST18 MOVE TABLESPACE TEST;


-- CLEAN UP

CONNECT / AS SYSDBA
drop user test cascade;
drop tablespace test including contents and datafiles cascade constraints;

回复 只看该作者 道具 举报

14#
发表于 2011-12-13 21:58:02
To decrease the size of a datafile, you would use the command:

  ALTER DATABASE DATAFILE '<full path and name of the file>' RESIZE [K|M|G];

where the size specified is smaller than the existing file size.

The current size of a datafile may be found by querying V$DATAFILE.BYTES or DBA_DATA_FILES.BYTES (see section 1 for examples)

Reducing the size of a datafile is more complicated than increasing the size of a datafile as space cannot be deallocated from a datafile that is currently being used by database objects.

To remove space from a datafile, you have to have contiguous free space at the END of the datafile.  

The view DBA_FREE_SPACE is used to display the free space (space not being used) in a datafile.

For example:

  SELECT * FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME='TEST'
ORDER BY BLOCK_ID;

TABLESPACE_NAME    FILE_ID    BLOCK_ID   BYTES      BLOCKS
------------------ ---------- ---------- ---------- ----------
TEST                        5          2     102400         50
TEST                        5         55      96256         47
TEST                        5        102    1890304        923

There are two large extents at the high end of the datafile
  

    BLOCK_ID=55 contains 47 blocks
    BLOCK_ID=102 contains 923 blocks

This means there are 1986560 unused bytes at the end of our datafile, almost 2 MB.

We want to leave some room for growth in our datafile, and depending on how the objects in that datafile allocate new extents, we could remove up to 1.89 MB of disk space from the datafile without damaging any objects in the tablespace.

If you have a large extent in the middle of a datafile, and some object taking up room at the end of the datafile, you can use the query FINDEXT.SQL below to find this object. If you export this object, then drop it, you should then free up contiguous space at the end of your datafile so you will be able to resize it smaller.

Make sure you leave enough room in the datafile for importing the object back into the tablespace.

回复 只看该作者 道具 举报

15#
发表于 2011-12-13 22:01:12
因为system 数据文件的 尾部 仍有被使用的Extent 这导致resize 操作无法成功；

而这些Extent 是属于一些 bootstrap 重要的自举对象的，所以我们无法轻易去移动他们：

SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCK_ID from dba_extents where tablespace_name='SYSTEM' ORDER BY BLOCK_ID DESC;

SEGMENT_NAME SEGMENT_TYPE BLOCK_ID
-------------------------------------------------------------------------------- ------------------ ----------
C_OBJ#_INTCOL# CLUSTER 942345
HIST_HEAD$ TABLE 942217
C_OBJ#_INTCOL# CLUSTER 868361
I_H_OBJ#_COL# INDEX 868233
SUMPARTLOG$ TABLE 84281
I_OBJ#_INTCOL# INDEX 84273
I_OBJ1 INDEX 84265
C_COBJ# CLUSTER 84257
C_TS# CLUSTER 84249
TABPART$ TABLE 84241


究其原因是 之前 有大的数据段存放在system表空间上，之后这些大的数据段被清理掉，导致出现 该数据文件头和尾仍被占用，而 中间出现大的Free Extents，从用户角度看就是有大量的空闲空间，但是却无法回收，resize 失败，出现ORA-03297: file contains used data beyond requested RESIZE value错误

回复 只看该作者 道具 举报

16#
发表于 2011-12-13 22:03:42
  1. How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark

  3. Applies to:
  4. Oracle Server - Enterprise Edition - Version: 8.1.5.0 and later   [Release: 8.1.5 and later ]
  5. Information in this document applies to any platform.
  6. Goal
  7. In a number of cases it is desired to shrink an oversized datafile. The challenge is then up to find to which size the datafile can be shrunk? Using the trial and error approach will likely give:

  9. SQL> alter database datafile '/oradata/v112/users01.dbf' resize 117248K;
  10. alter database datafile '/oradata/v112/users01.dbf' resize 117248K
  11. *
  12. ERROR at line 1:
  13. ORA-03297: file contains used data beyond requested RESIZE value

  15. The error is due to fact that there are allocated extents between the size to resize/shrink to and the end-of-file (might be space management blocks, so not necessary belonging to a segment).
  16. Solution

  18. Two options do exist to find the highwater mark of a datafile:

  20. - checking backwards for free space at end of file (proven to be the fastest)

  22. - check highest allocated extent in datafile (it might be that space management blocks are above last allocated extend)

  24. 1) checking backwards for free space at end of file

  26. Due to added functionality (different blocksizes for tablespaces) the script has been split into:

  28. - Oracle version 9 and higher

  30. - Oracle version 8 and lower
  31. REM Script is meant for Oracle version 9 and higher
  32. REM -----------------------------------------------

  34. set serveroutput on
  35. exec dbms_output.enable(1000000);

  37. declare

  39. cursor c_dbfile is
  40. select f.tablespace_name,f.file_name,f.file_id,f.blocks,t.block_size
  41. ,decode(t.allocation_type,'UNIFORM',t.initial_extent/t.block_size,0) uni_extent
  42. ,decode(t.allocation_type,'UNIFORM',(128+(t.initial_extent/t.block_size)),128) file_min_size
  43. from dba_data_files f,
  44. dba_tablespaces t
  45. where f.tablespace_name = t.tablespace_name
  46. and t.status = 'ONLINE'
  47. order by f.tablespace_name,f.file_id;

  49. cursor c_freespace(v_file_id in number) is
  50. select block_id, block_id+blocks max_block
  51. from dba_free_space
  52. where file_id = v_file_id
  53. order by block_id desc;

  55. /* variables to check settings/values */
  56. dummy number;
  57. checkval varchar2(10);
  58. block_correction number;

  60. /* running variable to show (possible) end-of-file */
  61. file_min_block number;

  63. /* variables to check if recycle_bin is on and if extent as checked is in ... */
  64. recycle_bin boolean:=false;
  65. extent_in_recycle_bin boolean;

  67. /* exception handler needed for non-existing tables note:344940.1 */
  68. sqlstr varchar2(100);
  69. table_does_not_exist exception;
  70. pragma exception_init(table_does_not_exist,-942);

  72. /* variable to spot space wastage in datafile of uniform tablespace */
  73. space_wastage number;

  75. begin

  77. /* recyclebin is present in Oracle 10.2 and higher and might contain extent as checked */
  78. begin
  79. select value into checkval from v$parameter where name = 'recyclebin';
  80. if checkval = 'on'
  81. then
  82. recycle_bin := true;
  83. end if;
  84. exception
  85. when no_data_found
  86. then
  87. recycle_bin := false;
  88. end;

  90. /* main loop */
  91. for c_file in c_dbfile
  92. loop
  93. /* initialization of loop variables */
  94. dummy :=0;
  95. extent_in_recycle_bin := false;
  96. file_min_block := c_file.blocks;

  98. begin

  100. space_wastage:=0; /* reset for every file check */

  102. <<check_free>>

  104. for c_free in c_freespace(c_file.file_id)
  105. loop
  106. /* if blocks is an uneven value there is a need to correct
  107. with -1 to compare with end-of-file which is even */
  108. block_correction := (0-mod(c_free.max_block,2));
  109. if file_min_block = c_free.max_block+block_correction
  110. then

  112. /* free extent is at end so file can be resized */
  113. file_min_block := c_free.block_id;

  115. /* Uniform sized tablespace check if space at end of file
  116. is less then uniform extent size */
  117. elsif (c_file.uni_extent !=0) and ((c_file.blocks - c_free.max_block) < c_file.uni_extent)
  118. then

  120. /* uniform tablespace which has a wastage of space in datafile
  121. due to fact that space at end of file is smaller than uniform extent size */

  123. space_wastage:=c_file.blocks - c_free.max_block;
  124. file_min_block := c_free.block_id;

  126. else
  127. /* no more free extent at end of file, file cannot be further resized */
  128. exit check_free;
  129. end if;
  130. end loop;
  131. end;

  133. /* check if file can be resized, minimal size of file 128 {+ initial_extent} blocks */
  134. if (file_min_block = c_file.blocks) or (c_file.blocks <= c_file.file_min_size)
  135. then

  137. dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);
  138. dbms_output.put_line('cannot be resized no free extents found');
  139. dbms_output.put_line('.');

  141. else

  143. /* file needs minimal no of blocks which does vary over versions,
  144. using safe value of 128 {+ initial_extent} */
  145. if file_min_block < c_file.file_min_size
  146. then
  147. file_min_block := c_file.file_min_size;
  148. end if;


  151. dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);
  152. dbms_output.put_line('current size: '||(c_file.blocks*c_file.block_size)/1024||'K'||' can be resized to: '||round((file_min_block*c_file.block_size)/1024)||'K (reduction of: '||round(((c_file.blocks-file_min_block)/c_file.blocks)*100,2)||' %)');


  155. /* below is only true if recyclebin is on */
  156. if recycle_bin
  157. then
  158. begin
  159. sqlstr:='select distinct 1 from recyclebin$ where file#='||c_file.file_id;
  160. execute immediate sqlstr into dummy;

  162. if dummy > 0
  163. then

  165. dbms_output.put_line('Extents found in recyclebin for above file/tablespace');
  166. dbms_output.put_line('Implying that purge of recyclebin might be needed in order to resize');
  167. dbms_output.put_line('SQL> purge tablespace '||c_file.tablespace_name||';');
  168. end if;
  169. exception
  170. when no_data_found
  171. then null;
  172. when table_does_not_exist
  173. then null;
  174. end;
  175. end if;
  176. dbms_output.put_line('SQL> alter database datafile '''||c_file.file_name||''' resize '||round((file_min_block*c_file.block_size)/1024)||'K;');

  178. if space_wastage!=0
  179. then
  180. dbms_output.put_line('Datafile belongs to uniform sized tablespace and is not optimally sized.');
  181. dbms_output.put_line('Size of datafile is not a multiple of NN*uniform_extent_size + overhead');
  182. dbms_output.put_line('Space that cannot be used (space wastage): '||round((space_wastage*c_file.block_size)/1024)||'K');
  183. dbms_output.put_line('For optimal usage of space in file either resize OR increase to: '||round(((c_file.blocks+(c_file.uni_extent-space_wastage))*c_file.block_size)/1024)||'K');
  184. end if;

  186. dbms_output.put_line('.');

  188. end if;

  190. end loop;

  192. end;
  193. /

  195. *) The absolute minimum size of a datafile varies per version/tablespace type.
  196.      In above case 128*8K (=1Mb) is used, but this might be too low.

  198. Example output for Oracle version 9 and higher:
  199. Tablespace: TEST Datafile: +DG1/test01.dbf
  200. current size: 11264K can be resized to: 4096K (reduction of: 63.64 %)
  201. Extents found in recyclebin for above file/tablespace
  202. Implying that purge of recyclebin might be needed in order to resize
  203. SQL> purge tablespace TEST;
  204. SQL> alter database datafile '+DG1/test01.dbf' resize 4096K;
  205. Datafile belongs to uniform sized tablespace and is not optimally sized.
  206. Size of datafile is not a multiple of NN*uniform_extent_size + overhead
  207. Space that cannot be used (space wastage): 1024K
  208. For optimal usage of space in file either resize OR increase to: 13312K
  209. .
  210. Tablespace: TEST Datafile: +DG2/test02.dbf
  211. current size: 10240K can be resized to: 4096K (reduction of: 60 %)
  212. SQL> alter database datafile '+DG2/test02.dbf' resize 4096K;
  213. .
  214. Tablespace: UNDOTBS1 Datafile: /oradata/v1122/undotbs01.dbf
  215. current size: 660480K can be resized to: 103424K (reduction of: 84.34 %)
  216. SQL> alter database datafile '/oradata/v1122/undotbs01.dbf' resize 103424K;
  217. .
  218. Tablespace: USERS Datafile: /oradata/v1122/users01.dbf
  219. cannot be resized no free extents found


  222. REM Script is meant for Oracle version 8 and lower
  223. REM ----------------------------------------------

  225. set serveroutput on
  226. exec dbms_output.enable(1000000);

  228. declare

  230. cursor c_dbfile is
  231. select f.tablespace_name,f.file_name,f.file_id,f.blocks
  232. from dba_data_files f,
  233. dba_tablespaces t
  234. where f.tablespace_name = t.tablespace_name
  235. and t.status = 'ONLINE'
  236. order by f.tablespace_name,f.file_id;

  238. cursor c_freespace(v_file_id in number) is
  239. select block_id, block_id+blocks max_block
  240. from dba_free_space
  241. where file_id = v_file_id
  242. order by block_id desc;

  244. /* variables to check settings/values */
  245. block_correction number;
  246. block_size number;

  248. /* running variable to show (possible) end-of-file */
  249. file_min_block number;

  251. begin

  253. select value into block_size from v$parameter where name='db_block_size';

  255. /* main loop */
  256. for c_file in c_dbfile
  257. loop
  258. /* initialization of loop variables */
  259. file_min_block := c_file.blocks;

  261. begin

  263. <<check_free

  265. for c_free in c_freespace(c_file.file_id)
  266. loop
  267. /* if blocks is an uneven value there is a need to correct with -1 to compare with end-of-file which is even */
  268. block_correction := (0-mod(c_free.max_block,2));
  269. if file_min_block = c_free.max_block+block_correction
  270. then

  272. /* free extent is at end so file can be resized */
  273. file_min_block := c_free.block_id;

  275. else
  276. /* no more free extent at end of file, file cannot be further resized */
  277. exit check_free;
  278. end if;
  279. end loop;
  280. end;

  282. /* check if file can be resized, minimal size of file 16 blocks */
  283. if (file_min_block = c_file.blocks) or (c_file.blocks <= 16)
  284. then

  286. dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);
  287. dbms_output.put_line('cannot be resized no free extents found');
  288. dbms_output.put_line('.');

  290. else

  292. /* file needs minimal no of blocks which does vary over versions */
  293. if file_min_block < 16
  294. then
  295. file_min_block := 16;
  296. end if;

  298. dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);
  299. dbms_output.put_line('current size: '||(c_file.blocks*block_size)/1024||'K'||' can be resized to: '||round((file_min_block*block_size)/1024)||'K (reduction of: '||round(((c_file.blocks-file_min_block)/c_file.blocks)*100,2)||' %)');
  300. dbms_output.put_line('SQL> alter database datafile '''||c_file.file_name||''' resize '||round((file_min_block*block_size)/1024)||'K;');
  301. dbms_output.put_line('.');

  303. end if;

  305. end loop;

  307. end;
  308. /

  310. Example output for Oracle version 8 and lower:
  311. Tablespace: SYSTEM Datafile:
  312. /refresh/64bit/app/oracle/oradata/REF817U6/system01.dbf
  313. cannot be resized no free extents found
  314. .
  315. Tablespace: TEMP Datafile: /refresh/64bit/app/oracle/oradata/REF817U6/temp01.dbf
  316. current size: 69632K can be resized to: 128K (reduction of: 99.82 %)
  317. SQL> alter database datafile
  318. '/refresh/64bit/app/oracle/oradata/REF817U6/temp01.dbf' resize 128K;


  321. 2. Check highest allocated extent in datafile (likely slow when having many extents)
  322. column file_name format a50;
  323. column tablespace_name format a15;
  324. column highwater format 9999999999;
  325. set pagesize 9999

  327. select a.tablespace_name
  328. ,a.file_name
  329. ,(b.maximum+c.blocks-1)*d.db_block_size highwater
  330. from dba_data_files a
  331. ,(select file_id,max(block_id) maximum
  332. from dba_extents
  333. group by file_id) b
  334. ,dba_extents c
  335. ,(select value db_block_size
  336. from v$parameter
  337. where name='db_block_size') d
  338. where a.file_id = b.file_id
  339. and c.file_id = b.file_id
  340. and c.block_id = b.maximum
  341. order by a.tablespace_name,a.file_name
  342. /

  344. Output from option Number 2:
  345. TABLESPACE_NAME FILE_NAME HIGHWATER
  346. --------------- -------------------------------------------------- -----------
  347. BIGPARTYWEEK1 /ots1/app/oracle/product/8.1.7/dbs/bw1.dbf 49152
  348. IFS_LOB_N /ots3/oradata/v817/oradata/v817/ifs_lob_n.dbf 157294592
  349. IFS_MAIN /ots3/oradata/v817/oradata/v817/ifs_main.dbf 23027712
  350. OEM_REPOSITORY /ots1/oradata/v817/oradata/v817/oem_repository.dbf 56492032
  351. RBS /ots1/oradata/v817/oradata/v817/rbs01.dbf 230170624
复制代码

回复 只看该作者 道具 举报

17#
发表于 2011-12-27 16:09:09
是这样的，目前还没有找到解决办法，我的也是一样，我的system还占用了60G呢，真正还不到1G，痛苦死了，希望12g在这方面能改进，到时只要一命令就可将后面的整到前面，然后再收缩就可以了，目前写在最后无法自动往前整，而我们又不太放心手工整这些会不会有问题，所以引发了这个问题。

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-15 01:45 , Processed in 0.060758 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569