ORACLE Alter database datafile resize extents

http://space.itpub.net/8750110/viewspace-167602[@more@]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

)

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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/450962/viewspace-1027044/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/450962/viewspace-1027044/