首页 » ORACLE » Script: 查看datafile 的HWM,估算resize 最小size

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

数据文件如果有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
----------
        88


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
----------
        64

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$ 进行递归查询

相关
http://www.anbob.com/?p=1259

打赏

对不起,这篇文章暂时关闭评论。