oracle备库数据文件resize,数据文件resize的问题

死脑筋 发表于 2012-4-9 14:04

这是我执行的所有的语句：

select sum(bytes)/1024/1024 from dba_segments where tablespace_name='PAY ...

先创建tbs_test表空间用来存放移动的表和索引。

移动：

表：

select DISTINCT 'alter table ' || owner || '.' || segment_name ||

' move tablespace &tablespace_name;'

from dba_extents

where segment_type = 'TABLE'

and file_id = &id and owner = '&NAME'

索引：

select DISTINCT 'alter index ' || owner || '.' || segment_name ||

' rebuild tablespace &tablespace_name;'

from dba_extents

where segment_type = 'INDEX'

and file_id = &id AND OWNER='&NAME'

分区表：

select DISTINCT 'alter table ' || owner || '.' || segment_name ||

' move partition ' || partition_name ||

' tablespace &tablespace_name;'

from dba_extents

where segment_type = 'TABLE PARTITION'

and file_id = &id and owner = '&NAME'

分区索引：

select DISTINCT 'alter index ' || owner || '.' || segment_name ||

' rebuild partition ' || partition_name ||

' tablespace &tablespace_name;'

from dba_extents

where segment_type = 'INDEX PARTITION'

and file_id = &id AND OWNER='&NAME'

清理回收站：

purge recyclebin；

resize：

1.推荐使用toad，计算最小值

2.select a.file#,

a.name,

a.bytes / 1024 / 1024 CurrentMB,

ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,

(a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,

'alter database datafile ''' || a.name || ''' resize ' ||

ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCMD

from v$datafile a,

(select file_id, max(block_id + blocks - 1) HWM

from dba_extents

where file_id in (select b.file#

From v$tablespace a, v$datafile b

where a.ts# = b.ts#

and a.name = '&TBS_NAME')

group by file_id) b

where a.file# = b.file_id(+)

and (a.bytes - HWM * block_size) > 0

order by 5

移动回来：

表：

select DISTINCT 'alter table ' || owner || '.' || segment_name ||

' move tablespace &tablespace_name;'

from dba_extents

where segment_type = 'TABLE'

and file_id = &id  --id用tbs_test的

and owner = '&owner'

索引：

select DISTINCT 'alter index ' || owner || '.' || segment_name ||

' rebuild tablespace &tablespace_name;'

from dba_extents

where segment_type = 'INDEX'

and file_id = &id

and owner = '&owner'

分区表：

select DISTINCT 'alter table ' || owner || '.' || segment_name ||

' move partition ' || partition_name ||

' tablespace &tablespace_name;'

from dba_extents

where segment_type = 'TABLE PARTITION'

and file_id = &id

and owner = '&owner'

分区索引：

select DISTINCT 'alter index ' || owner || '.' || segment_name ||

' rebuild partition ' || partition_name ||

' tablespace &tablespace_name;'

from dba_extents

where segment_type = 'INDEX PARTITION'

and file_id = &id

and owner = '&owner'

索引重建：

select 'alter index ' || owner || '.' || index_name ||

' rebuild tablespace &tbs_name;'

from dba_indexes

where owner = '&name'

and tablespace_name is not null;

分区索引重建：

select 'alter index ' || index_owner || '.' || index_name ||

' rebuild partition ' || partition_name || ' tablespace &tbs_name ;'

from dba_ind_partitions

where index_owner = '&name';