1. 首页
  2. IT资讯

ORACLE Alter database datafile resize extents

http://space.itpub.net/8750110/viewspace-167602[@more@]LMT TABLESPACE,有时候虽然里面的数据不是很多,但是RESIZE DATAFILE的时候会失败,因为一些OBJECTEXTENTS已经扩展到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 MOVEALTER 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://www.cxybcw.com/194662.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code