当前位置：Gxlcms > 数据库问题 > Oracle设置和删除不可用列

Oracle设置和删除不可用列

时间：2021-07-01 10:21:17 帮助过：45人阅读

* FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT --------------------------- --------------------------- ----- HR ADMIN_EMP 2

–count列代表不可用列数量

6、使用案例？

  1. <code class=" hljs oxygene">SCOTT@orcl> <span class="hljs-keyword">create</span> table tmp_all_objects
  2.   <span class="hljs-number">2</span>  <span class="hljs-keyword">AS</span>
  3.   <span class="hljs-number">3</span>  <span class="hljs-keyword">SELECT</span> object_id, object_name
  4.   <span class="hljs-number">4</span>  <span class="hljs-keyword">from</span> dba_objects
  5.   <span class="hljs-number">5</span>  ;
  6. 表已创建。</code>
  1. <code class=" hljs lasso">SYS@orcl<span class="hljs-subst">></span> exec show_space(<span class="hljs-string">‘TMP_ALL_OBJECTS‘</span>,<span class="hljs-string">‘SCOTT‘</span>);
  2. Unformatted Blocks  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  3. FS1 Blocks (<span class="hljs-number">0</span><span class="hljs-subst">-</span><span class="hljs-number">25</span>)   <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  4. FS2 Blocks (<span class="hljs-number">25</span><span class="hljs-subst">-</span><span class="hljs-number">50</span>)  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  5. FS3 Blocks (<span class="hljs-number">50</span><span class="hljs-subst">-</span><span class="hljs-number">75</span>)  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  6. FS4 Blocks (<span class="hljs-number">75</span><span class="hljs-subst">-</span><span class="hljs-number">100</span>) <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  7. <span class="hljs-literal">Full</span> Blocks         <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>             <span class="hljs-number">352</span>
  8. Total Blocks <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>             <span class="hljs-number">384</span>
  9. Total <span class="hljs-built_in">Bytes</span>  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>       <span class="hljs-number">3</span>,<span class="hljs-number">145</span>,<span class="hljs-number">728</span>
  10. Total MBytes <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>               <span class="hljs-number">3</span>
  11. Unused Blocks<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>              <span class="hljs-number">18</span>
  12. Unused <span class="hljs-built_in">Bytes</span> <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>         <span class="hljs-number">147</span>,<span class="hljs-number">456</span>
  13. Last Used Ext FileId<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">4</span>
  14. Last Used Ext BlockId<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">.</span>          <span class="hljs-number">14</span>,<span class="hljs-number">592</span>
  15. Last Used Block<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">.</span>             <span class="hljs-number">110</span>
  16. PL/SQL 过程已成功完成。
  17. SYS@orcl<span class="hljs-subst">></span> DESC DBA_UNUSED_COL_TABS
  18.  名称                                     是否为空<span class="hljs-subst">?</span> 类型
  19.  <span class="hljs-subst">----------------------------------------</span> <span class="hljs-subst">--------</span> <span class="hljs-subst">---------------------------</span>
  20.  OWNER                                    <span class="hljs-literal">NOT</span> <span class="hljs-built_in">NULL</span> VARCHAR2(<span class="hljs-number">30</span>)
  21.  TABLE_NAME                               <span class="hljs-literal">NOT</span> <span class="hljs-built_in">NULL</span> VARCHAR2(<span class="hljs-number">30</span>)
  22.  COUNT                                             NUMBER
  23. SYS@orcl<span class="hljs-subst">></span> <span class="hljs-keyword">SELECT</span> <span class="hljs-subst">*</span> FROM DBA_UNUSED_COL_TABS;
  24. 未选定行
  25. SCOTT@orcl<span class="hljs-subst">></span> ALTER TABLE TMP_ALL_OBJECTS <span class="hljs-built_in">SET</span> UNUSED(OBJECT_NAME);
  26. 表已更改。
  27. SYS@orcl<span class="hljs-subst">></span> <span class="hljs-keyword">SELECT</span> <span class="hljs-subst">*</span> FROM DBA_UNUSED_COL_TABS;
  28. OWNER                          TABLE_NAME                          COUNT
  29. <span class="hljs-subst">------------------------------</span> <span class="hljs-subst">------------------------------</span> <span class="hljs-subst">----------</span>
  30. SCOTT                          TMP_ALL_OBJECTS                         <span class="hljs-number">1</span>
  31. SYS@orcl<span class="hljs-subst">></span> exec show_space(<span class="hljs-string">‘TMP_ALL_OBJECTS‘</span>,<span class="hljs-string">‘SCOTT‘</span>);
  32. Unformatted Blocks  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  33. FS1 Blocks (<span class="hljs-number">0</span><span class="hljs-subst">-</span><span class="hljs-number">25</span>)   <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  34. FS2 Blocks (<span class="hljs-number">25</span><span class="hljs-subst">-</span><span class="hljs-number">50</span>)  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  35. FS3 Blocks (<span class="hljs-number">50</span><span class="hljs-subst">-</span><span class="hljs-number">75</span>)  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  36. FS4 Blocks (<span class="hljs-number">75</span><span class="hljs-subst">-</span><span class="hljs-number">100</span>) <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  37. <span class="hljs-literal">Full</span> Blocks         <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>             <span class="hljs-number">352</span>
  38. Total Blocks <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>             <span class="hljs-number">384</span>
  39. Total <span class="hljs-built_in">Bytes</span>  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>       <span class="hljs-number">3</span>,<span class="hljs-number">145</span>,<span class="hljs-number">728</span>
  40. Total MBytes <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>               <span class="hljs-number">3</span>
  41. Unused Blocks<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>              <span class="hljs-number">18</span>
  42. Unused <span class="hljs-built_in">Bytes</span> <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>         <span class="hljs-number">147</span>,<span class="hljs-number">456</span>
  43. Last Used Ext FileId<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">4</span>
  44. Last Used Ext BlockId<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">.</span>          <span class="hljs-number">14</span>,<span class="hljs-number">592</span>
  45. Last Used Block<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">.</span>             <span class="hljs-number">110</span>
  46. PL/SQL 过程已成功完成。
  47. <span class="hljs-subst">--</span>删除不可用列
  48. SCOTT@orcl<span class="hljs-subst">></span> ALTER TABLE TMP_ALL_OBJECTS DROP UNUSED COLUMNS CHECKPOINT <span class="hljs-number">250</span>;
  49. 表已更改。
  50. SYS@orcl<span class="hljs-subst">></span> <span class="hljs-keyword">SELECT</span> <span class="hljs-subst">*</span> FROM DBA_UNUSED_COL_TABS;
  51. 未选定行
  52. SYS@orcl<span class="hljs-subst">></span> exec show_space(<span class="hljs-string">‘TMP_ALL_OBJECTS‘</span>,<span class="hljs-string">‘SCOTT‘</span>);
  53. Unformatted Blocks  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  54. FS1 Blocks (<span class="hljs-number">0</span><span class="hljs-subst">-</span><span class="hljs-number">25</span>)   <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  55. FS2 Blocks (<span class="hljs-number">25</span><span class="hljs-subst">-</span><span class="hljs-number">50</span>)  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">1</span>
  56. FS3 Blocks (<span class="hljs-number">50</span><span class="hljs-subst">-</span><span class="hljs-number">75</span>)  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>             <span class="hljs-number">350</span>
  57. FS4 Blocks (<span class="hljs-number">75</span><span class="hljs-subst">-</span><span class="hljs-number">100</span>) <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">1</span>
  58. <span class="hljs-literal">Full</span> Blocks         <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  59. Total Blocks <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>             <span class="hljs-number">384</span>
  60. Total <span class="hljs-built_in">Bytes</span>  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>       <span class="hljs-number">3</span>,<span class="hljs-number">145</span>,<span class="hljs-number">728</span>
  61. Total MBytes <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>               <span class="hljs-number">3</span>
  62. Unused Blocks<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>              <span class="hljs-number">18</span>
  63. Unused <span class="hljs-built_in">Bytes</span> <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>         <span class="hljs-number">147</span>,<span class="hljs-number">456</span>
  64. Last Used Ext FileId<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">4</span>
  65. Last Used Ext BlockId<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">.</span>          <span class="hljs-number">14</span>,<span class="hljs-number">592</span>
  66. Last Used Block<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">.</span>             <span class="hljs-number">110</span>
  67. PL/SQL 过程已成功完成。
  68. <span class="hljs-subst">--</span>move操作，减少碎片
  69. SCOTT@orcl<span class="hljs-subst">></span> ALTER TABLE TMP_ALL_OBJECTS MOVE;
  70. 表已更改。
  71. SYS@orcl<span class="hljs-subst">></span> exec show_space(<span class="hljs-string">‘TMP_ALL_OBJECTS‘</span>,<span class="hljs-string">‘SCOTT‘</span>);
  72. Unformatted Blocks  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  73. FS1 Blocks (<span class="hljs-number">0</span><span class="hljs-subst">-</span><span class="hljs-number">25</span>)   <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  74. FS2 Blocks (<span class="hljs-number">25</span><span class="hljs-subst">-</span><span class="hljs-number">50</span>)  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  75. FS3 Blocks (<span class="hljs-number">50</span><span class="hljs-subst">-</span><span class="hljs-number">75</span>)  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  76. FS4 Blocks (<span class="hljs-number">75</span><span class="hljs-subst">-</span><span class="hljs-number">100</span>) <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">0</span>
  77. <span class="hljs-literal">Full</span> Blocks         <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>             <span class="hljs-number">113</span>
  78. Total Blocks <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>             <span class="hljs-number">128</span>
  79. Total <span class="hljs-built_in">Bytes</span>  <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>       <span class="hljs-number">1</span>,<span class="hljs-number">048</span>,<span class="hljs-number">576</span>
  80. Total MBytes <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>               <span class="hljs-number">1</span>
  81. Unused Blocks<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>               <span class="hljs-number">5</span>
  82. Unused <span class="hljs-built_in">Bytes</span> <span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>          <span class="hljs-number">40</span>,<span class="hljs-number">960</span>
  83. Last Used Ext FileId<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">..</span>               <span class="hljs-number">4</span>
  84. Last Used Ext BlockId<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">.</span>          <span class="hljs-number">14</span>,<span class="hljs-number">808</span>
  85. Last Used Block<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-attribute">...</span><span class="hljs-built_in">.</span>               <span class="hljs-number">3</span>
  86. PL/SQL 过程已成功完成。
  87. <span class="hljs-subst">--</span>可以看到总块数下降
  88. <span class="hljs-subst">--</span>删除测试表
  89. SCOTT@orcl<span class="hljs-subst">></span> drop table TMP_ALL_OBJECTS;
  90. 表已删除。</code>

7、关于不可用列的恢复（以下摘自网络）
刚才有个人问我如何修复被设置为UNUSED的字段，我考虑了一下，以下的方法可以恢复（以下步骤执行前要做好备份），没有经验的DBA不要轻易尝试。

1、创建实验表TTTA

  1. <code class=" hljs vbnet">SQL> CREATE TABLE TTTA ( A <span class="hljs-built_in">INTEGER</span>,B <span class="hljs-built_in">INTEGER</span>,C VARCHAR2(<span class="hljs-number">10</span>),D <span class="hljs-built_in">INTEGER</span>);
  2. 表已创建。
  3. SQL> INSERT <span class="hljs-keyword">INTO</span> TTTA VALUES (<span class="hljs-number">1</span>,<span class="hljs-number">2</span>,<span class="hljs-comment">‘3‘,4);</span>
  4. 已创建 <span class="hljs-number">1</span> 行。
  5. SQL> INSERT <span class="hljs-keyword">INTO</span> TTTA VALUES (<span class="hljs-number">2</span>,<span class="hljs-number">3</span>,<span class="hljs-comment">‘4‘,5);</span>
  6. 已创建 <span class="hljs-number">1</span> 行。
  7. SQL> COMMIT;
  8. 提交完成。
  9. ALTER TABLE TTTA <span class="hljs-keyword">SET</span> UNUSED COLUMN C;
  10. </code>

2、以下进行恢复

  1. <code class=" hljs vbnet">SQL> <span class="hljs-keyword">SELECT</span> OBJ<span class="hljs-preprocessor"># FROM OBJ$ WHERE NAME=‘TTTA‘;</span>
  2.       OBJ<span class="hljs-preprocessor">#</span>
  3. ----------
  4.      <span class="hljs-number">32067</span>
  5. <span class="hljs-keyword">SELECT</span> COL<span class="hljs-preprocessor">#,INTCOL#,NAME FROM COL$ WHERE OBJ#=32067;</span>
  6.       COL<span class="hljs-preprocessor">#    INTCOL# NAME</span>
  7. ---------- ---------- ------------------------------
  8.          <span class="hljs-number">1</span>          <span class="hljs-number">1</span> A
  9.          <span class="hljs-number">2</span>          <span class="hljs-number">2</span> B
  10.          <span class="hljs-number">0</span>          <span class="hljs-number">3</span> SYS_C00003_08031720:<span class="hljs-number">09</span>:<span class="hljs-number">55</span>$   被UNUSED的字段
  11.          <span class="hljs-number">3</span>          <span class="hljs-number">4</span> D
  12. SQL> <span class="hljs-keyword">SELECT</span> COLS <span class="hljs-keyword">FROM</span> TAB$ <span class="hljs-keyword">WHERE</span> OBJ<span class="hljs-preprocessor">#=32067;</span>
  13.       COLS
  14. ----------
  15.          <span class="hljs-number">3</span>      ------字段数变为<span class="hljs-number">3</span>了
  16. SQL> UPDATE COL$ <span class="hljs-keyword">SET</span> COL<span class="hljs-preprocessor">#=INTCOL# WHERE OBJ#=32067;</span>
  17. 已更新<span class="hljs-number">4</span>行。
  18. SQL> UPDATE TAB$ <span class="hljs-keyword">SET</span> COLS=COLS+<span class="hljs-number">1</span> <span class="hljs-keyword">WHERE</span> OBJ<span class="hljs-preprocessor">#=32067;</span>
  19. 已更新 <span class="hljs-number">1</span> 行。
  20. UPDATE COL$ <span class="hljs-keyword">SET</span> NAME=<span class="hljs-comment">‘C‘ WHERE OBJ#=32067 AND COL#=3;</span>
  21. UPDATE COL$ <span class="hljs-keyword">SET</span> <span class="hljs-keyword">PROPERTY</span>=<span class="hljs-number">0</span> <span class="hljs-keyword">WHERE</span> OBJ<span class="hljs-preprocessor">#=32067;</span>
  22. SQL> COMMIT;</code>

3、重启数据库

  1. <code class=" hljs brainfuck"><span class="hljs-comment">SQL</span>> <span class="hljs-comment">SELECT</span> <span class="hljs-comment">*</span> <span class="hljs-comment">FROM</span> <span class="hljs-comment">SCOTT</span><span class="hljs-string">.</span><span class="hljs-comment">TTTA;</span>
  2.          <span class="hljs-comment">A</span>          <span class="hljs-comment">B</span> <span class="hljs-comment">C</span>                   <span class="hljs-comment">D</span>
  3. <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-literal">-</span>
  4.          <span class="hljs-comment">1</span>          <span class="hljs-comment">2</span> <span class="hljs-comment">3</span>                   <span class="hljs-comment">4</span>
  5.          <span class="hljs-comment">2</span>          <span class="hljs-comment">3</span> <span class="hljs-comment">4</span>                   <span class="hljs-comment">5</span></code>

恢复完成

版权声明：本文为博主原创文章，未经博主允许不得转载。

Oracle设置和删除不可用列

标签：oracle   不可用列   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！