Oracle 表空間管理（tablespace managment）

發(fā)布時(shí)間：2020-08-09 21:16:16 來源：ITPUB博客閱讀：137 作者：Davis_itpub 欄目：關(guān)系型數(shù)據(jù)庫

一. 官網(wǎng)說明 <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;"> Logical Storage Structures  其中有關(guān)ASSM 的內(nèi)容如下： </div> <div style="white-space:normal;widows:auto;font-family:Verdana, Arial, Helvetica, sans-serif;font-size:13px;line-height:1.8;background-color:#F5F5F5;"> <h4 style="font-size:16px;background-color:inherit;"> Logical Space Management </h4> Oracle Database must use logical space management to track and allocate the extents in a tablespace. When a database object requires an extent, the database must have a method of finding and providing it. Similarly, when an object no longer requires an extent, the database must have a method of making the free extent available. Oracle Database manages space within a tablespace based on the type that you create. You can create either of the following types of tablespaces: ·         Locally managed tablespaces (default) The database uses bitmaps in the tablespaces themselves to manage extents. Thus, locally managed tablespaces have a part of the tablespace set aside for a bitmap. Within a tablespace, the database can manage segments with automatic segment space management (ASSM) or manual segment space management (MSSM). ·         Dictionary-managed tablespaces The database uses the <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/glossary.htm#CHDJJJGD" style="background-color:inherit;cursor:pointer;color:navy;">data dictionary</a> to manage extents (see <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/datadict.htm#i1012" style="background-color:inherit;cursor:pointer;color:navy;">"Overview of the Data Dictionary"</a>). <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/logical.htm#BABDJBGD" style="background-color:inherit;cursor:pointer;color:navy;">Figure 12-3</a> shows the alternatives for logical space management in a tablespace. Figure 12-3 Logical Space Management <img src="/attachment/201510/13/30208428_1444744363SAZY.gif" width="451" height="181" alt="" /> <img src="file://D:/%E6%9C%89%E9%81%93%E4%BA%91%E7%AC%94%E8%AE%B0/%E6%95%B0%E6%8D%AE%E7%9B%AE%E5%BD%95/zr2095@163.com/057aa1fe78c244fe87181267ee85c38f/04934118i922.gif" data-media-type="image" data-attr-org-src-id="33B54179DE834BEEA5EEE4A0C3ED479C" data-attr-org-img-file="file:///D:/%E6%9C%89%E9%81%93%E4%BA%91%E7%AC%94%E8%AE%B0/%E6%95%B0%E6%8D%AE%E7%9B%AE%E5%BD%95/zr2095@163.com/057aa1fe78c244fe87181267ee85c38f/04934118i922.gif" style="background-color:inherit;cursor:default;display:inline-block;margin-top:8px;max-width:800px;" /> <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/img_text/cncpt268.htm" style="background-color:inherit;cursor:pointer;color:navy;">Description of "Figure 12-3 Logical Space Management"</a> <h5 style="background-color:inherit;font-size:14px;"> Locally Managed Tablespaces </h5> A locally managed tablespace maintains a bitmap in the data file header to track free and used space in the data file body. Each bit corresponds to a group of blocks. When space is allocated or freed, Oracle Database changes the bitmap values to reflect the new status of the blocks. The following graphic is a conceptual representation of bitmap-managed storage. A <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">1</code> in the header refers to used space, whereas a <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">0</code> refers to free space. <img src="/attachment/201510/13/30208428_1444744504B8TM.gif" width="633" height="332" alt="" /> <img src="file://D:/%E6%9C%89%E9%81%93%E4%BA%91%E7%AC%94%E8%AE%B0/%E6%95%B0%E6%8D%AE%E7%9B%AE%E5%BD%95/zr2095@163.com/8ee0499171524680ad495f8edfa56674/049341258bpp.gif" data-media-type="image" data-attr-org-src-id="D0BFE5097E49426DB272DC1F2FE07435" data-attr-org-img-file="file:///D:/%E6%9C%89%E9%81%93%E4%BA%91%E7%AC%94%E8%AE%B0/%E6%95%B0%E6%8D%AE%E7%9B%AE%E5%BD%95/zr2095@163.com/8ee0499171524680ad495f8edfa56674/049341258bpp.gif" style="background-color:inherit;cursor:default;display:inline-block;margin-top:8px;max-width:800px;" /> A locally managed tablespace has the following advantages: ·         Avoids using the data dictionary to manage extents Recursive operations can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a data dictionary table or undo segment. ·         Tracks adjacent free space automatically In this way, the database eliminates the need to coalesce free extents. ·         Determines the size of locally managed extents automatically Alternatively, all extents can have the same size in a locally managed tablespace and override object storage options. Note: Oracle strongly recommends the use of locally managed tablespaces with Automatic Segment Space Management. Segment space management is an attribute inherited from the tablespace that contains the segment. Within a locally managed tablespace, the database can manage segments automatically or manually. For example, segments in tablespace <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">users</code> can be managed automatically while segments in tablespace <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">tools</code> are managed manually. Automatic Segment Space Management The ASSM method uses bitmaps to manage space. Bitmaps provide the following advantages: ·         Simplified administration ASSM avoids the need to manually determine correct settings for many storage parameters. Only one crucial SQL parameter controls space allocation: <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">PCTFREE</code>. This parameter specifies the percentage of space to be reserved in a block for future updates (see <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/logical.htm#BABBAJHC" style="background-color:inherit;cursor:pointer;color:navy;">"Percentage of Free Space in Data Blocks"</a>). ASSM 管理，只需要一個(gè)參數(shù)：PCTFREE ·         Increased concurrency Multiple <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/glossary.htm#CHDJEBHA" style="background-color:inherit;cursor:pointer;color:navy;">transactions</a> can search separate lists of free data blocks, thereby reducing contention and waits. For many standard workloads, application performance with ASSM is better than the performance of a well-tuned application that uses MSSM. ·         Dynamic affinity of space to instances in an Oracle Real Application Clusters (Oracle RAC) environment ASSM is more efficient and is the default for permanent, locally managed tablespaces. Note: This chapter assumes the use of ASSM in all of its discussions of logical storage space.   Manual Segment Space Management The legacy MSSM method uses a linked list called a free list to manage free space in the segment. For a database object that has free space, a free list keeps track of blocks under the <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/glossary.htm#CHDIJHII" style="background-color:inherit;cursor:pointer;color:navy;">high water mark</a> (HWM), which is the dividing line between segment space that is used and not yet used. As blocks are used, the database puts blocks on or removes blocks from the free list as needed. In addition to <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">PCTFREE</code>, MSSM requires you to control space allocation with SQL parameters such as <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">PCTUSED</code>, <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">FREELISTS</code>, and <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">FREELIST GROUPS</code>.<code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">PCTUSED</code> sets the percentage of free space that must exist in a currently used block for the database to put it on the free list. For example, if you set<code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">PCTUSED</code> to <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">40</code> in a <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">CREATE TABLE</code> statement, then you cannot insert rows into a block in the segment until less than 40% of the block space is used. As an illustration, suppose you insert a row into a table. The database checks a free list of the table for the first available block. If the row cannot fit in the block, and if the used space in the block is greater than or equal to <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">PCTUSED</code>, then the database takes the block off the list and searches for another block. If you delete rows from the block, then the database checks whether used space in the block is now less than <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">PCTUSED</code>. If so, then the database places the block at the beginning of the free list. An object may have multiple free lists. In this way, multiple sessions performing DML on a table can use different lists, which can reduce contention. Each database session uses only one free list for the duration of its session. As shown in <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/logical.htm#BABJABGH" style="background-color:inherit;cursor:pointer;color:navy;">Figure 12-4</a>, you can also create an object with one or more free list groups, which are collections of free lists. Each group has a master free list that manages the individual process free lists in the group. Space overhead for free lists, especially for free list groups, can be significant. Figure 12-4 Free List Groups <img src="/attachment/201510/13/30208428_1444744426N32P.gif" width="323" height="275" alt="" /> <img src="file://D:/%E6%9C%89%E9%81%93%E4%BA%91%E7%AC%94%E8%AE%B0/%E6%95%B0%E6%8D%AE%E7%9B%AE%E5%BD%95/zr2095@163.com/5534672ca11440319f1ff7c414e940dd/04934130k9ex.gif" data-media-type="image" data-attr-org-src-id="C05DADD0BEBD4277841C477F77662550" data-attr-org-img-file="file:///D:/%E6%9C%89%E9%81%93%E4%BA%91%E7%AC%94%E8%AE%B0/%E6%95%B0%E6%8D%AE%E7%9B%AE%E5%BD%95/zr2095@163.com/5534672ca11440319f1ff7c414e940dd/04934130k9ex.gif" style="background-color:inherit;cursor:default;display:inline-block;margin-top:8px;max-width:800px;" /> <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/img_text/cncpt320.htm" style="background-color:inherit;cursor:pointer;color:navy;">Description of "Figure 12-4 Free List Groups"</a> Managing segment space manually can be complex. You must adjust <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">PCTFREE</code> and <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">PCTUSED</code> to reduce row migration (see <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/logical.htm#BABEEAAE" style="background-color:inherit;cursor:pointer;color:navy;">"Chained and Migrated Rows"</a>) and avoid wasting space. For example, if every used block in a segment is half full, and if <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">PCTUSED</code> is 40, then the database does not permit inserts into any of these blocks. Because of the difficulty of fine-tuning space allocation parameters, Oracle strongly recommends ASSM. In ASSM, <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">PCTFREE</code> determines whether a new row can be inserted into a block, but it does not use free lists and ignores <code style="font-family:Monaco, Consolas, Courier, 'Lucida Console', monospace;background-color:inherit;">PCTUSED</code>. -- ASSM 會(huì)忽略PCTUSED 參數(shù) Dictionary-Managed Tablespaces A dictionary-managed tablespace uses the data dictionary to manage its extents. Oracle Database updates tables in the data dictionary whenever an extent is allocated or freed for reuse. For example, when a table needs an extent, the database queries the data dictionary tables, and searches for free extents. If the database finds space, then it modifies one data dictionary table and inserts a row into another. In this way, the database manages space by modifying and moving data. The SQL that the database executes in the background to obtain space for database objects is recursive SQL. Frequent use of recursive SQL can have a negative impact on performance because updates to the data dictionary must be serialized. Locally managed tablespaces, which are the default, avoid this performance problem. </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;"> 二.  ASSM 說明 </div>     在Orale 9i以前，表的剩余空間的管理與分配都是由鏈接列表freelist來完成的，因?yàn)?lt;/span>freelist存在串行的問題因此容易引起往往容易引起段頭的爭(zhēng)用與空間的浪費(fèi)（其實(shí)這一點(diǎn)并不明顯），最主要的還是因?yàn)樾枰?lt;/span>DBA 花費(fèi)大量的精力去管理這些爭(zhēng)用并監(jiān)控表的空間利用。       自動(dòng)段空間管理（ASSM），它首次出現(xiàn)在Oracle 920里。有了ASSM，鏈接列表freelist被位圖所取代，它是一個(gè)二進(jìn)制的數(shù)組，能夠迅速有效地管理存儲(chǔ)擴(kuò)展和剩余區(qū)塊（free block），因此能夠改善分段存儲(chǔ)本質(zhì)，ASSM表空間上創(chuàng)建的段還有另外一個(gè)稱呼叫Bitmap Managed Segments（BMB 段）。   讓我們看看位圖freelist是如何實(shí)現(xiàn)的。從使用區(qū)段空間管理自動(dòng)參數(shù)創(chuàng)建tablespace開始：   create tablespace demo   datafile '/ora01/oem/demo01.dbf '   size 5m   EXTENT MANAGEMENT LOCAL -- Turn on LMT   SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM;       一旦你定義好了tablespace，那么表和索引就能夠使用各種方法很容易地被移動(dòng)到新的tablespace里，帶有ASSM的本地管理tablespace會(huì)略掉任何為PCTUSED、NEXT和FREELISTS所指定的值。       當(dāng)表格或者索引被分配到這個(gè)tablespace以后，用于獨(dú)立對(duì)象的PCTUSED的值會(huì)被忽略，而Oracle9i會(huì)使用位圖數(shù)組來自動(dòng)地管理tablespace里表格和索引的freelist。對(duì)于在LMT的tablespace內(nèi)部創(chuàng)建的表格和索引而言，這個(gè)NEXT擴(kuò)展子句是過時(shí)的，因?yàn)橛杀镜毓芾淼?lt;/span>tablespace會(huì)管理它們。但是，INITIAL參數(shù)仍然是需要的，因?yàn)?lt;/span>Oracle不可能提前知道初始表格加載的大小。對(duì)于ASSM而言，INITIAL最小的值是三個(gè)塊。       新的管理機(jī)制用位圖來跟蹤或管理每個(gè)分配到對(duì)象的塊，每個(gè)塊有多少剩余空間根據(jù)位圖的狀態(tài)來確定，如>75%,50%-75%,25%-50%和<25%，也就是說位圖其實(shí)采用了四個(gè)狀態(tài)位來代替以前的pctused，什么時(shí)候該利用該數(shù)據(jù)塊則由設(shè)定的pctfree來確定。       使用ASSM的一個(gè)巨大優(yōu)勢(shì)是，位圖freelist肯定能夠減輕緩沖區(qū)忙等待（buffer busy wait）的負(fù)擔(dān)，這個(gè)問題在Oracle9i以前的版本里曾是一個(gè)嚴(yán)重的問題 。      在沒有多個(gè)freelist的時(shí)候，每個(gè)Oracle表格和索引在表格的頭部都曾有一個(gè)數(shù)據(jù)塊，用來管理對(duì)象所使用的剩余區(qū)塊，并為任何SQL插入聲明所創(chuàng)建的新數(shù)據(jù)行提供數(shù)據(jù)塊。當(dāng)數(shù)據(jù)緩沖內(nèi)的數(shù)據(jù)塊由于被另一個(gè)DML事務(wù)處理鎖定而無法使用的時(shí)候，緩沖區(qū)忙等待就會(huì)發(fā)生。當(dāng)你需要將多個(gè)任務(wù)插入到同一個(gè)表格里的時(shí)候，這些任務(wù)就被強(qiáng)制等待，而同時(shí)Oracle會(huì)在同時(shí)分派剩余的區(qū)塊，一次一個(gè)。       有了ASSM之后，Oracle宣稱顯著地提高了DML并發(fā)操作的性能，因?yàn)?lt;/span>（同一個(gè)）位圖的不同部分可以被同時(shí)使用，這樣就消除了尋找剩余空間的串行化。根據(jù)Oracle的測(cè)試結(jié)果，使用位圖freelist會(huì)消除所有分段頭部（對(duì)資源）的爭(zhēng)奪，還能獲得超快的并發(fā)插入操作      盡管ASSM顯示出了令人激動(dòng)的特性并能夠簡(jiǎn)化Oracle DBA的工作，但是Oracle9i的位圖分段管理還是有一些局限性的：               1. 一旦DBA被分配之后，它就無法控制tablespace內(nèi)部的獨(dú)立表格和索引的存儲(chǔ)行為。               2. 大型對(duì)象不能夠使用ASSM，而且必須為包含有LOB數(shù)據(jù)類型的表格創(chuàng)建分離的tablespace。               3. 你不能夠使用ASSM創(chuàng)建臨時(shí)的tablespace。這是由排序時(shí)臨時(shí)分段的短暫特性所決定的。               4. 只有本地管理的tablespace才能夠使用位圖分段管理。               5· 使用超高容量的DML（例如INSERT、UPDATE和DELETE等）的時(shí)候可能會(huì)出現(xiàn)性能上的問題。 <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;"> </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;"> 三、表空間的更改 </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;"> </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">     有時(shí)候我們創(chuàng)建了我們想要的表空間，但是計(jì)劃是趕不上變化的，比如你的數(shù)據(jù)數(shù)據(jù)越來越多，原來的表空間容納不下了，怎么辦？或者你想把你的表空間變?yōu)橹蛔x，或者你想讓你的有空間自己擴(kuò)展大小，變手工為自動(dòng)，等等，這些我們都可以利用ORACLE 的ALTER TABLESPACE 來完成。 </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">     最先看本地管理表空間(locally managed tablespace) </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;"> </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">    關(guān)于表空間，不能更改的內(nèi)容： </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">         1.       不能把永久表空間變?yōu)榕R時(shí)表空間； </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">         2.       不能改變表空間的segment管理方式。 </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">    可以更改的內(nèi)容： </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">         1.   增加數(shù)據(jù)文件，DATAFILE </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">         2.   改變表空間的可用性，online與offline </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">             a) ALTER TABLESPACE lmtemp TEMPFILE OFFLINE; </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">             b) ALTER TABLESPACE lmtemp TEMPFILE ONLINE; </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">         3.       改變表空間的屬性,read/write only </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">         4.       更改數(shù)據(jù)文件的名字，啟動(dòng)或禁止數(shù)據(jù)文件的自動(dòng)擴(kuò)展功能。 </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">   </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">     大表空間(bigfile tablespace) </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">         1. resize：ALTER TABLESPACE XXX RESIZE 80G </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">         2. autoextend：  ALTER TABLESPACE XXX QUTOEXEND ON NEXT 20G </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;"> </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">     本地臨時(shí)表空間 </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">     1.增加臨時(shí)文件 </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">       ALTER TABLESPACE lmtemp </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">         ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE; </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">     2.改變可用性，不可以讓一個(gè)臨時(shí)表空間離線，但可以讓臨時(shí)文件離線 </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">      ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE; </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">      ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE; </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">     3.改變臨時(shí)文件的大小 </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">        ALTER DATABASE TEMPFILE  '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M; </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">     4.干掉臨時(shí)數(shù)據(jù)文件 </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">        ALTER DATABASE TEMPFILE  '/u02/oracle/data/lmtemp02.dbf' DROP  INCLUDING DATAFILES; </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">     5.壓縮臨時(shí)表空間 </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">       先說說操作 </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">       ALTER TABLESPACE lmtemp1 SHRINK SPACE KEEP 20M; </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">       ALTER TABLESPACE lmtemp2 SHRINK TEMPFILE   '/u02/oracle/data/lmtemp02.dbf'; </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">       KEEP 保持指定大小，如果沒有KEEP語句，則盡可能的減小。還是指定一個(gè)好。       </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">       為什么要執(zhí)行這個(gè)操作？因?yàn)閿?shù)據(jù)庫在執(zhí)行大的排序操作時(shí)，會(huì)占用很大的臨時(shí)表空間，它會(huì)讓臨時(shí)表空間增大的，然而它也不很完全負(fù)責(zé)任，當(dāng)它執(zhí)行完命令后，他不會(huì)把增加的那部分空間歸還給操作系統(tǒng)，它只把它標(biāo)記為FREE可以重用。這樣就會(huì)造成磁盤空間亂費(fèi)，很可能以后那部分FREE空間一直FREE。 </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">   </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;"> 上面有針對(duì)性的說了一些各種類型表空間的更改方法，下面說一些共性的東西吧： </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">    1.改名字用RENAME TO </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">       ALTER TABLESPACE user RENAME TO newuser; </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">    2.刪除表空間 </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">       DROP TABLESPACE users INCLUDING CONTENTS; </div> <div style="font-family:微軟雅黑;font-size:14px;line-height:21px;white-space:normal;widows:auto;background-color:#FFFFFF;">       DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES; </div>

向AI問一下細(xì)節(jié)

Oracle 表空間管理（tablespace managment）

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽