收縮datafile for oracle -- 轉

ningzi82發表於2010-10-19
Oracle
LMT TABLESPACE，有時候雖然裡面的資料不是很多，但是RESIZE DATAFILE的時候會失敗，因為一些OBJECT的EXTENTS已經擴充套件到DATAFILE的邊緣（最大的地方）。下面的SQL可以讓我們找到前5個最邊緣的OBJECT
select *
from (
select owner, segment_name,
segment_type, block_id
from dba_extents
where file_id =
( select file_id
from dba_data_files
where file_name = :FILE ) --用你的DATAFILE代替
order by block_id desc
)
[@more@]
where rownum <= 5
結果是行如下面的五行
OWNER
--------------------------------------------------------
SEGMENT_NAME
--------------------------------------------------------
SEGMENT_TYPE BLOCK_ID
------------------------------------ ----------
PERFSTAT
STATS$PARAMETER
TABLE 30345
講得到的這些OBJECT透過ALTER TABLE MOVE或ALTER INDEX REBUILD轉移到其他DATAFILE中去，依次類推，直到滿足你要RESIZE的空間為止。
下面的SQL可以得到ALTER DATABASE DATAFILE RESIZE字句，自動判斷可以RESIZE到什麼地方。
column value new_val blksize
select value
from v$parameter
where name = 'db_block_size'
/
select 'alter database datafile ''' ||
file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )
|| 'm;' cmd
from dba_data_files a,
( select file_id,
max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and
ceil(blocks*&&blksize/1024/1024)-
ceil((nvl(hwm,1)*
&&blksize)/1024/1024 ) > 0
/
3: ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )
3: ceil( (nvl(hwm,1)*8192)/1024/1024 )
12: ceil(blocks*&&blksize/1024/1024)-
12: ceil(blocks*8192/1024/1024)-
14: &&blksize)/1024/1024 ) > 0
14: 8192)/1024/1024 ) > 0
CMD
--------------------------------------------------------------------------------
alter database datafile 'E:ORACLEORADATASIDDBSYSTEM01.DBF' resize 413m;
alter database datafile 'E:ORACLEORADATASIDDBUNDOTBS01.DBF' resize 75m;
alter database datafile 'E:ORACLEORADATASIDDBCWMLITE01.DBF' resize 10m;
alter database datafile 'E:ORACLEORADATASIDDBDRSYS01.DBF' resize 10m;
alter database datafile 'E:ORACLEORADATASIDDBODM01.DBF' resize 10m;
alter database datafile 'E:ORACLEORADATASIDDBTOOLS01.DBF' resize 7m;
alter database datafile 'E:ORACLEORADATASIDDBUSERS01.DBF' resize 239m;
alter database datafile 'E:ORACLEORADATASIDDBXDB01.DBF' resize 38m;
alter database datafile 'E:ORACLEORADATASIDDBDATA01.DBF' resize 720m;
alter database datafile 'E:ORACLEORADATASIDDBWWM.DBF' resize 1m;
alter database datafile 'D:ORACLEORADATASIDDBDATA02.DBF' resize 1m;
alter database datafile 'D:ORACLEORADATASIDDBDATA04.DBF' resize 1m;
alter database datafile 'D:ORACLEORADATASIDDBDATA03.DBF' resize 1m;
alter database datafile 'E:ORACLEORADATASIDDBINDX01.DBF' resize 1m;
測試下
SQL> alter database datafile 'E:ORACLEORADATASIDDBINDX01.DBF' resize 1m;
執行成功
或者換個寫法直接用一下命令整體察看並得到結果
SELECT
a.file_id,
a.file_name
file_name,
CEIL( ( NVL( hwm,1 ) * blksize ) / 1024 / 1024 ) smallest,
CEIL( blocks * blksize / 1024 / 1024 ) currsize,
CEIL( blocks * blksize / 1024 / 1024 ) -
CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) savings,
'alter database datafile ''' || file_name || ''' resize ' ||
CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) || 'm;' cmd
FROM
DBA_DATA_FILES a,
(
SELECT file_id, MAX( block_id + blocks - 1 ) hwm
FROM DBA_EXTENTS
GROUP BY file_id
) b,
(
SELECT TO_NUMBER( value ) blksize
FROM V$PARAMETER
WHERE name = 'db_block_size'
)
WHERE
a.file_id = b.file_id(+)
AND
CEIL( blocks * blksize / 1024 / 1024 ) - CEIL( ( NVL( hwm, 1 ) * blksize ) / 1024 / 1024 ) > 0
ORDER BY 5 desc
/
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
SMALLEST CURRSIZE SAVINGS
---------- ---------- ----------
CMD
--------------------------------------------------------------------------------
11
E:ORACLEORADATASIDDBDATA01.DBF
721 3000 2279
alter database datafile 'E:ORACLEORADATASIDCDBDATA01.DBF' resize721m;
轉自：http://hi.baidu.com/cwh_blog/blog/item/0df74c11e1fa38f7c2ce7915.html
http://blog.csdn.net/47522341/archive/2009/02/06/3865946.aspx

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/789833/viewspace-1039967/，如需轉載，請註明出處，否則將追究法律責任。

相關文章