当前位置: 首页 > BASIC > 正文

关于resize datafile理解的错误

      resize数据文件的时候,常常会报ORA-03297错误,原来理解通过在相同表空间move一下对象,就会自动使用最小的未使用的空间,其实并不是这样的,在MOS文件中遇到ORA-03297错误也是直接给出的DROP对象,也不是在相同表空间中MOVE对象。下面通过测试来说明:

      下面的测试没有实际意义,因为生产环境基本上不会做RESIZE操作。

1,数据库版本

orcl1123.htz.pw > select * from v$version where rownum=1;

 

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

2,查询表空间中对象的大小

这里可以看到对象总的大小为271974400

orcl1123.htz.pw > select tablespace_name,sum(bytes) from dba_segments where tablespace_name=’USERS’ group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)

—————————— ———-

USERS                           271974400

然后表表空间中总分配的空间远远大于271974400

orcl1123.htz.pw > select tablespace_name,sum(bytes) total,sum(user_bytes) used from dba_data_files where tablespace_name=’USERS’ group by tablespace_name;

TABLESPACE_NAME         TOTAL                      USED

—————- ———— ————————-

USERS             30703616000               30701518848

3 查询需要MOVE的对象

查看表空间中的数据文件系统

orcl1123.htz.pw > select file_id,file_name from dba_data_files where tablespace_name=’USERS’;

   FILE_ID FILE_NAME

———- ——————————————————————————–

         4 /oracle/app/oracle/oradata/orcl1123/users01.dbf

         5 /oracle/app/oracle/oradata/orcl1123/users02.dbf

原来以为表空间中使用数据文件是均匀的使用,但是后面的测试并不是这样的,下面这样的SQL是之前以后均匀的使用数据文件而写的

orcl1123.htz.pw > select 271974400/2+128 from dual;

271974400/2+128

—————

      135987328

由于查询dba_extents需要消耗大量的时间,特别是数据库越大的时候,所以这里我们通过一次性查询出来,并生成一个表来实现只查询一次dba_extents

orcl1123.htz.pw > select *

  2    from (select owner,

  3                 segment_name,

  4                 a.segment_type,

  5                 max(block_id + blocks) * 8192 max_size

  6            from dba_extents a

  7           where tablespace_name = ‘USERS’

  8           group by owner, segment_name, a.segment_type)

  9   where max_size > 135987328;

 

OWNER                SEGMENT_NAME                        SEGMENT_TYPE      MAX_SIZE

——————– ———————————– ————— ———-

HTZ                  SYS_LOB0000075782C00003$$           LOBSEGMENT      8798797824

HTZ                  HTZ4                                TABLE           8798601216

HTZ                  SYS_IL0000075782C00003$$            LOBINDEX        8797552640

HTZ                  HTZ3                                TABLE           8796504064

HTZ                  TEST                                TABLE           8801746944

HTZ                  SYS_IL0000075778C00002$$            LOBINDEX        8796700672

HTZ                  SYS_LOB0000075778C00002$$           LOBSEGMENT      8796635136

HTZ                  TEST_BFILE                          TABLE           8797421568

 

 

orcl1123.htz.pw > create table system.resize_datafile as select *

    from (select owner,

                 segment_name,

                 a.segment_type,

                 max(block_id + blocks) * 8192 max_size

            from dba_extents a

           where tablespace_name = ‘USERS’

           group by owner, segment_name, a.segment_type)

   where max_size > 135987328;

 

Table created.

4 MOVE对象

orcl1123.htz.pw > select ‘alter table ‘ || owner || ‘.’ || segment_name || ‘ move;’

         from system.resize_datafile a

        where a.segment_type = ‘TABLE’;

 

‘ALTERTABLE’||OWNER||’.’||SEGMENT_NAME||’MOVE;’

————————————————

alter table HTZ.HTZ4 move;

alter table HTZ.HTZ3 move;

alter table HTZ.TEST move;

alter table HTZ.HTZ_LONG move;

alter table HTZ.TEST_BFILE move;

 

 

 

select ‘alter table ‘ || a.owner || ‘.’ || b.table_name || ‘ move lob(‘ ||

       b.column_name || ‘) store as ‘ ||

       decode(b.securefile, ‘NO’, ‘BASICFILE’, ‘YES’, ‘securefile’) ||

       ‘ (tablespace ‘ || b.tablespace_name || ‘);’ ddl

  from system.resize_datafile a, dba_lobs b

 where a.segment_type = ‘LOBSEGMENT’

   AND a.owner = b.owner

   and a.segment_name = b.segment_name;

  

alter table HTZ.TEST move lob(PHOTO) store as securefile (tablespace USERS);

alter table HTZ.HTZ_LONG move lob(SQL_FULLTEXT) store as BASICFILE (tablespace USERS);

alter table HTZ.HTZ4 move lob(SQL_FULLTEXT) store as securefile (tablespace USERS);

多次MOVE后,查看当前表空间使用情况

orcl1123.htz.pw > select max(block_id) from dba_extents where file_id=4;

MAX(BLOCK_ID)

————-

      1074144

orcl1123.htz.pw > select max(block_id) from dba_extents where file_id=5;

MAX(BLOCK_ID)

————-

        16896 

这里看到两个数据文件中分配给对象的最大块的BLOCK_ID相差太多了,也说明数据文件中并不是平均使用数据文件的

    

orcl1123.htz.pw > select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=5;

MAX(BLOCK_ID+BLOCKS)*8192+128*8192

———————————-

                         147849216

orcl1123.htz.pw > select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=4;

MAX(BLOCK_ID+BLOCKS)*8192+128*8192

———————————-

                        8800501760

orcl1123.htz.pw > select tablespace_name,sum(bytes) from dba_segments where tablespace_name=’USERS’ group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)

—————————— ———-

USERS                           270073856

这里看到表空间分配的大小还是远远大于表空间中对象使用的大小

5 查询对表块的分布情况

orcl1123.htz.pw > select owner,segment_name,file_id,block_id+blocks from dba_extents where tablespace_name=’USERS’ order by file_id,4;

 

OWNER      SEGMENT_NAME                      FILE_ID BLOCK_ID+BLOCKS

———- —————————— ———- —————

SCOTT      DEPT                                    4             136

SCOTT      PK_DEPT                                 4             144

SCOTT      EMP                                     4             152

………………..

HTZ        HTZ_4                                   4             376

HTZ        TEST                                    4             384

HTZ        HTZ4                                    4         1016064

…………………….

HTZ        SYS_IL0000075818C00002$$                4         1016152

HTZ        SYS_LOB0000075782C00003$$               5            5120

HTZ        HTZ3                                    5           18048

HTZ        HTZ3                                    5           18176

HTZ        HTZ3                                    5           18304

HTZ        SYS_LOB0000075782C00003$$               5           19328

……………….

通过上面信息得到,中间其它有很多块并没有使用,并且相隔还很大

6 dump数据文件位图块

File Space Bitmap Block:

BitMap Control:

RelFno: 4, BeginBlock: 128, Flag: 0, First: 23, Free: 63456

FFFFFFFF00000000 0000000000000000 0000000000000000 0000000000000000

0000000000000000 0000000000000000 0000000000000000 0000000000000000

0000000000000000 0000000000000000 0000000000000000 0000000000000000

0000000000000000 0000000000000000 0000000000000000 0000000000000000

0000000000000000 0000000000000000 0000000000000000 0000000000000000

………………….

File Space Bitmap Block:

BitMap Control: 

RelFno: 4, BeginBlock: 508032, Flag: 0, First: 0, Free: 63488

0000000000000000 0000000000000000 0000000000000000 0000000000000000

0000000000000000 0000000000000000 0000000000000000 0000000000000000

0000000000000000 0000000000000000 0000000000000000 0000000000000000

0000000000000000 0000000000000000 0000000000000000 0000000000000000

………………………..

File Space Bitmap Block:

BitMap Control: 

RelFno: 4, BeginBlock: 1015936, Flag: 0, First: 46, Free: 59945

FFFFFFFFFF3FFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

通过上面的信息可以得到每一个位图块都对应数据文件中一段连续的空间,在分配空间的时候,并不是依次使用位图块,而是同时在多个位图块中使用位图块,估计是为了减少块的挣用,提高并发性。

7 MOVE到其它表空间

其实通过MOS文档可以发现,ORACLE官方并没有说通过MOVE方式,而是通过DROP方式来实现,下面我们通过MOVE到其它的表空间来使用。

alter table HTZ.HTZ5 move tablespace sysaux;

alter table HTZ.HTZ3 move tablespace sysaux;

alter table HTZ.TEST move tablespace sysaux;

alter table HTZ.NL1 move tablespace sysaux;

alter table HTZ.HTZ4 move tablespace sysaux;

alter table HTZ.HTZ_LONG1 move tablespace sysaux;

alter table HTZ.NL2 move tablespace sysaux;

alter table HTZ.TEST move lob(PHOTO) store as securefile (tablespace SYSAUX);

alter table HTZ.HTZ_LONG1 move lob(SQL_FULLTEXT) store as BASICFILE (tablespace SYSAUX);

alter table HTZ.HTZ4 move lob(SQL_FULLTEXT) store as securefile (tablespace SYSAUX);

8 RESIZE数据文件

查询数据分配给对象的最大块的位置

orcl1123.htz.pw > select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=5;

MAX(BLOCK_ID+BLOCKS)*8192+128*8192

———————————-

                           8126464

orcl1123.htz.pw > select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=4;

MAX(BLOCK_ID+BLOCKS)*8192+128*8192

———————————-

                           4128768

这里看到已经除下面了。

orcl1123.htz.pw > alter database datafile 5 resize 8126464;

Database altered.

orcl1123.htz.pw > alter database datafile 4 resize 4128768;

Database altered.

已经resize成功

9 将对象MOVE回来

alter table HTZ.HTZ5 move tablespace USERS;

alter table HTZ.HTZ3 move tablespace USERS;

alter table HTZ.TEST move tablespace USERS;

alter table HTZ.NL1 move tablespace USERS;

alter table HTZ.HTZ4 move tablespace USERS;

alter table HTZ.HTZ_LONG1 move tablespace USERS;

alter table HTZ.NL2 move tablespace USERS;

alter table HTZ.TEST move lob(PHOTO) store as securefile (tablespace USERS);

alter table HTZ.HTZ_LONG1 move lob(SQL_FULLTEXT) store as BASICFILE (tablespace USERS);

alter table HTZ.HTZ4 move lob(SQL_FULLTEXT) store as securefile (tablespace USERS);

查看当前数据文件分配的大小

orcl1123.htz.pw > select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=4;

MAX(BLOCK_ID+BLOCKS)*8192+128*8192

———————————-

                         139460608

orcl1123.htz.pw > select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=5;

MAX(BLOCK_ID+BLOCKS)*8192+128*8192

———————————-

                         147849216

 

orcl1123.htz.pw > !ls -l /oracle/app/oracle/oradata/orcl1123/users02.dbf

-rw-r—–  1 oracle oinstall 153034752 Nov 29 23:02 /oracle/app/oracle/oradata/orcl1123/users02.dbf

 

orcl1123.htz.pw > !ls -l /oracle/app/oracle/oradata/orcl1123/users01.dbf

-rw-r—–  1 oracle oinstall 141762560 Nov 29 23:02 /oracle/app/oracle/oradata/orcl1123/users01.dbf

本文固定链接: http://www.htz.pw/2014/11/29/%e5%85%b3%e4%ba%8eresize-datafile%e7%90%86%e8%a7%a3%e7%9a%84%e9%94%99%e8%af%af.html | 认真就输

该日志由 huangtingzhong 于2014年11月29日发表在 BASIC 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: 关于resize datafile理解的错误 | 认真就输
关键字: