小格格's Blog

2010/01/22

[筆記] oracle 如何detail datafile usage size and resize datefile

Filed under: 身為一個DBA,會這些東西也是很合理的 — justin2k @ 05:48:48

 

某同事發的mail….筆記起來..

 

 

如何detail datafile usage size and resize datefile

DEAR Sirs

上週有人跟我要關於如何查ORACLE DATA file的high water maker並將DATAFILE給予RESIZE

請參閱
該語法目前使用於INT資料ARCHIEVE後,RESIZE SPACE還給OS SPACE

SELECT      ‘ALTER DATABASE DATAFILE “‘
         || (SELECT file_name
               FROM dba_data_files
              WHERE file_id = a.file_id)
         || “‘ RESIZE ‘
         || CEIL (MAX ((a.block_id + a.blocks) * b.VALUE) / (1024 * 1024))
         || ‘M;’
    FROM dba_extents a, v$parameter b
   WHERE a.tablespace_name IN (
            SELECT tablespace_name
              FROM dba_tablespaces
             WHERE tablespace_name NOT IN
                             (‘DBA’, ‘DBA_IDX’, ‘XDB’, ‘USERS’, ‘TOOLS’, ‘INDX’, ‘DRSYS’, ‘SYSTEM’, ‘UNDOTBS1’, ‘TEMP’))
     AND b.NAME = ‘db_block_size’
GROUP BY a.file_id

 

=============================================================================

跑完之後會產生alter語法,size就是可以alter下去的最小size , 執行需要一點時間

第一次跑沒注意,以為直接會幫我alter下去….當下馬上跑去扁那個同事…

 

Technorati 的標籤:,,

發表迴響 »

仍無迴響。

RSS feed for comments on this post. TrackBack URI

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

在 WordPress.com 建立免費網站或網誌.

%d 位部落客按了讚: