oracle resize datafile,Script: 查看datafile 的HWM,估算resize 最小size

最新推荐文章于 2024-04-03 20:07:45 发布

是易不是一

最新推荐文章于 2024-04-03 20:07:45 发布

阅读量355

点赞数

文章标签： oracle resize datafile

数据文件如果有2g，先create一个大表占了1G,后来建了个几M小表，再把大表drop 掉加purge选项，向把数据文件resize到100M，往往会失败，在磁盘紧张的情况下，想知道数据文件最小值是多少？就要计算datafile HWM,意义和segment的HWM是一样的。

测试一下,分享收集的script

sys@ANBOB>create tablespace tt datafile '/u01/app/oracle/oradata/anbob/tt01.dbf' size 81k;

Tablespace created.

sys@ANBOB>select bytes/1024 from dba_data_files where file_name='/u01/app/oracle/oradata/anbob/tt01.dbf';

BYTES/1024

----------

db version:10201

block size :8192

File header: min 1 block

Bitmap: min 64k

Segment header: min 1 block

Segment data block: min 1 block: 8k

Total: 88k

sys@ANBOB>alter database datafile '/u01/app/oracle/oradata/anbob/tt01.dbf' resize 10m;

Database altered.

sys@ANBOB>select bytes/1024 from dba_data_files where file_name='/u01/app/oracle/oradata/anbob/tt01.dbf'

2 ;

BYTES/1024

----------

10240

sys@ANBOB>create table test tablespace tt as select * from dba_objects ;

Table created.

sys@ANBOB>select bytes/1024 from dba_segments where segment_name='TEST' AND owner='SYS';

BYTES/1024

----------

7168

sys@ANBOB>insert into test select * from test;

insert into test select * from test

ERROR at line 1:

ORA-01653: unable to extend table SYS.TEST by 128 in tablespace TT

sys@ANBOB>select bytes/1024 from dba_segments where segment_name='TEST' AND owner='SYS';

BYTES/1024

----------

9216

sys@ANBOB>select max(block_id+blocks-1)*8192/1024 from dba_extents where segment_name='TEST' AND owner='SYS';

MAX(BLOCK_ID+BLOCKS-1)*8192/1024

--------------------------------

9240

sys@ANBOB>create table test1(id int) tablespace tt;

Table created.

sys@ANBOB>insert into test1 values(1);

1 row created.

sys@ANBOB>commit;

sys@ANBOB>select bytes/1024 from dba_segments where segment_name='TEST1' AND owner='SYS';

BYTES/1024

----------

sys@ANBOB>drop table test purge;

Table dropped.

sys@ANBOB>alter database datafile '/u01/app/oracle/oradata/anbob/tt01.dbf' resize 9m;

alter database datafile '/u01/app/oracle/oradata/anbob/tt01.dbf' resize 9m

ERROR at line 1:

ORA-03297: file contains used data beyond requested RESIZE value

sys@ANBOB>col tablespace_name for a18

sys@ANBOB>col file_name for a60

sys@ANBOB>SELECT a.tablespace_name,

file_name,

c.VALUE / 1024 "Blk. size(Kb)",

CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "smallest(Mb) - HWM",

CEIL (blocks * c.VALUE / 1024 / 1024) "currsize(Mb)",

CEIL (blocks * c.VALUE / 1024 / 1024) - CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "savings(Mb)"

FROM dba_data_files a,

(SELECT file_id, MAX (block_id + blocks - 1) hwm

FROM dba_extents

GROUP BY file_id) b,

(SELECT VALUE

FROM v$parameter

WHERE NAME = 'db_block_size') c

WHERE a.file_id = b.file_id(+)

and a.status !='INVALID';

TABLESPACE_NAME FILE_NAME Blk. size(Kb) smallest(Mb) - HWM currsize(Mb) savings(Mb)

------------------ ------------------------------------------------------------ ------------- ------------------ ------------ -----------

TT /u01/app/oracle/oradata/anbob/tt01.dbf 8 10 10 0

MGMT_ECM_DEPOT_TS /u01/app/oracle/oradata/anbob/mgmt_ecm_depot1.dbf 8 10 10 0

SYSAUX /u01/app/oracle/oradata/anbob/sysaux01.dbf 8 303 310 7

TBSAPP /u01/app/oracle/oradata/anbob/tbsapp1.dbf 8 1 20 19

EXAMPLE /u01/app/oracle/oradata/anbob/example01.dbf 8 69 69 0

USERS /u01/app/oracle/oradata/anbob/users01.dbf 8 19 19 0

UNDOTBS1 /u01/app/oracle/oradata/anbob/undotbs01.dbf 8 104 120 16

MGMT_TABLESPACE /u01/app/oracle/oradata/anbob/mgmt.dbf 8 438 440 2

SYSTEM /u01/app/oracle/oradata/anbob/system01.dbf 8 539 540 1

sys@ANBOB>drop table test1 purge;

Table dropped.

sys@ANBOB> SELECT a.tablespace_name,

2 file_name,

3 c.VALUE / 1024 "Blk. size(Kb)",

4 CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "smallest(Mb) - HWM",

5 CEIL (blocks * c.VALUE / 1024 / 1024) "currsize(Mb)",

6 CEIL (blocks * c.VALUE / 1024 / 1024) - CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "savings(Mb)"

7 FROM dba_data_files a,

8 (SELECT file_id, MAX (block_id + blocks - 1) hwm

9 FROM dba_extents

10 GROUP BY file_id) b,

11 (SELECT VALUE

12 FROM v$parameter

13 WHERE NAME = 'db_block_size') c

14 WHERE a.file_id = b.file_id(+)

15 and a.status !='INVALID';

TABLESPACE_NAME FILE_NAME Blk. size(Kb) smallest(Mb) - HWM currsize(Mb) savings(Mb)

------------------ ------------------------------------------------------------ ------------- ------------------ ------------ -----------

TT /u01/app/oracle/oradata/anbob/tt01.dbf 8 1 10 9

MGMT_ECM_DEPOT_TS /u01/app/oracle/oradata/anbob/mgmt_ecm_depot1.dbf 8 10 10 0

SYSAUX /u01/app/oracle/oradata/anbob/sysaux01.dbf 8 303 310 7

TBSAPP /u01/app/oracle/oradata/anbob/tbsapp1.dbf 8 1 20 19

EXAMPLE /u01/app/oracle/oradata/anbob/example01.dbf 8 69 69 0

USERS /u01/app/oracle/oradata/anbob/users01.dbf 8 19 19 0

UNDOTBS1 /u01/app/oracle/oradata/anbob/undotbs01.dbf 8 104 120 16

MGMT_TABLESPACE /u01/app/oracle/oradata/anbob/mgmt.dbf 8 438 440 2

SYSTEM /u01/app/oracle/oradata/anbob/system01.dbf 8 539 540 1

9 rows selected.

NOTE:

任何查询DBA_EXTENTS的代价都是非常昂贵的,在本地管理的表空间里是用x$ktfbue stucture 表现已用的extents情况,在v$lock 视图中能看到在查询dba_extents时会加TT,TO lock type,(TT:Serializes DDL operations on tablespaces;TO:Serializes DDL operations on tablespaces)，会对tablespace上的每个seg$ 进行递归查询