[转载]减少临时表空间使用空间的几种方法
<p>信息来源: 邪恶八进制信息安全团队</p><p> 1、 第一种方式，直接resize tempfile , 虽然简单，但是很容易无效<br /><br /></p><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="3"><div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">sys</span><span style="COLOR: #008000">@mescp</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> tablespace_name,</span><span style="COLOR: #ff00ff">file_name</span><span style="COLOR: #000000"> ,
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">file_id</span><span style="COLOR: #000000">,bytes</span><span style="COLOR: #808080">/</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1024</span><span style="COLOR: #808080">/</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1024</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">as</span><span style="COLOR: #000000"> "size(M)"
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">3</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> dba_temp_files
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">4</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />TABLESPACE_NAME </span><span style="COLOR: #ff00ff">FILE_NAME</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">FILE_ID</span><span style="COLOR: #000000"> size(M)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">------------------ ---------------------------------- ---------- ----------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">TEMP</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">orabin</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">oradata</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">mescp</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">temp01.dbf </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1024</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">TEMP</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">orabin</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">oradata</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">mescp</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">tmp1_01.dbf </span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">4096</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"> rows selected.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />sys</span><span style="COLOR: #008000">@mescp</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">alter</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">database</span><span style="COLOR: #000000"> tempfile </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"> resize 16M;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">alter</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">database</span><span style="COLOR: #000000"> tempfile </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"> resize 16M
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />ERROR at line </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">:
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />ORA</span><span style="COLOR: #808080">-</span><span style="FONT-WEIGHT: bold; COLOR: #800000">03297</span><span style="COLOR: #000000">: </span><span style="COLOR: #0000ff">file</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">contains</span><span style="COLOR: #000000"> used data beyond requested RESIZE value
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080"> 失败了</span></div>
</div></pre></div><p /><div id="4"><br /> 2、第二种，建立另一个临时表空间，并把它设为default temporary tablespace,再删除原来的temporary tablespace以及tempfile。 <br /><br /> 详情见：<a href="http://www.eygle.com/archives/2006/...tablespace.html" target="_blank"><font face="Verdana">[url]http://www.eygle.com/archives/2006/...tablespace.html[/url]</font></a><br /><br /><font face="Verdana"> --需要切换，复杂了点……<br /><br /> 3、我提供的方式<br /></font></div><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="5">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">sys</span><span style="COLOR: #008000">@mescp</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">alter</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">database</span><span style="COLOR: #000000"> tempfile </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">drop</span><span style="COLOR: #000000"> ;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">Database</span><span style="COLOR: #000000"> altered.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />sys</span><span style="COLOR: #008000">@mescp</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">alter</span><span style="COLOR: #000000"> tablespace </span><span style="COLOR: #0000ff">temp</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">add</span><span style="COLOR: #000000"> tempfile
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">/orabin/oradata/mescp/temp01.dbf</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">3</span><span style="COLOR: #000000"> size 16M reuse autoextend </span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">next</span><span style="COLOR: #000000"> 16M maxsize 4096M;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />Tablespace altered.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />sys</span><span style="COLOR: #008000">@mescp</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">alter</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">database</span><span style="COLOR: #000000"> tempfile </span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">drop</span><span style="COLOR: #000000"> ;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">Database</span><span style="COLOR: #000000"> altered.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />sys</span><span style="COLOR: #008000">@mescp</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">alter</span><span style="COLOR: #000000"> tablespace </span><span style="COLOR: #0000ff">temp</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">add</span><span style="COLOR: #000000"> tempfile
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">/orabin/oradata/mescp/tmp1_01.dbf</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">3</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />sys</span><span style="COLOR: #008000">@mescp</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">3</span><span style="COLOR: #000000"> size 16M reuse autoextend </span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">next</span><span style="COLOR: #000000"> 16M maxsize 4096M;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />sys</span><span style="COLOR: #008000">@mescp</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />Tablespace altered.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />sys</span><span style="COLOR: #008000">@mescp</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> tablespace_name,</span><span style="COLOR: #ff00ff">file_name</span><span style="COLOR: #000000"> ,
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">file_id</span><span style="COLOR: #000000">,bytes</span><span style="COLOR: #808080">/</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1024</span><span style="COLOR: #808080">/</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1024</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">as</span><span style="COLOR: #000000"> "size(M)"
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">3</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> dba_temp_files
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">4</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />TABLESPACE_NAME </span><span style="COLOR: #ff00ff">FILE_NAME</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">FILE_ID</span><span style="COLOR: #000000"> size(M)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">------------------ ---------------------------------- ---------- ----------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">TEMP</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">orabin</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">oradata</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">mescp</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">temp01.dbf </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">16</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">TEMP</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">orabin</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">oradata</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">mescp</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">tmp1_01.dbf </span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">16</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"> rows selected.</span></div>
</div></pre></div><p /><div id="6"><br /><br /> --无需重新建立temporary tablespace,无需修改database的default temporary tablespace</div><p /><div id="7"><div id="8"></div></div>
页:
[1]