收缩表空间数据文件大小的两个案例

收缩表空间两个案例：使用users表空间测试。undo表空间也可以这样操作，系统表空间未测试。

一、空闲空间可以正常收回。

1.查出表空间总大小及空闲空间大小

BYS@ bys001>select  df.tablespace_name,df.sum_df_m as space_m,fs.sum_fs_m free_space,to_char(trunc((df.sum_df_m-fs.sum_fs_m)/df.sum_df_m,2)*100) as "used_%" from  (select tablespace_name,sum(bytes/1024/1024) as sum_df_m from dba_data_files group by tablespace_name)  df,(select tablespace_name,sum(bytes/1024/1024) as sum_fs_m from dba_free_space group by tablespace_name)  fs where df.tablespace_name=fs.tablespace_name and df.tablespace_name='USERS';

TABLESPACE_NAME                   SPACE_M FREE_SPACE used_%
------------------------------ ---------- ---------- ----------------------------------------
USERS                              1047.5    906.375 13
BYS@ bys001>select file#,name from v$datafile where name like '%users%';
     FILE# NAME
---------- ---------------------------------------------
         4 /u01/app/oracle/oradata/bys001/users01.dbf

2.收缩表空间大小时--这里是自己计算了下1047.5-906.375=141.125

此时如果设置收缩为140M，会出错。
BYS@ bys001>alter database datafile '/u01/app/oracle/oradata/bys001/users01.dbf' resize 140M;
alter database datafile '/u01/app/oracle/oradata/bys001/users01.dbf' resize 140M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
收缩为150M，可以执行。
BYS@ bys001>alter database datafile '/u01/app/oracle/oradata/bys001/users01.dbf' resize 150M;
Database altered.
BYS@ bys001>select df.tablespace_name,df.sum_df_m as space_m,fs.sum_fs_m free_space,to_char(trunc((df.sum_df_m-fs.sum_fs_m)/df.sum_df_m,2)*100) as "used_%" from (select tablespace_name,sum(bytes/1024/1024) as sum_df_m from dba_data_files group by tablespace_name) df,(select tablespace_name,sum(bytes/1024/1024) as sum_fs_m from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name;
TABLESPACE_NAME                   SPACE_M FREE_SPACE used_%
------------------------------ ---------- ---------- ----------------------------------------
SYSAUX                                670    49.4375 92
UNDOTBS1                              440     418.75 4
USERS                                 150     9.4375 93
CATALOG1                               50     43.375 13
SYSTEM                                710          4 99
EXAMPLE                               100      21.25 78
#########################################################################################

二、空闲空间有较大空闲无法收回时的解决思路。

出现这种情况原因是数据文件的使用不连续，比如整个表空间大小100M，现在空闲90M。
如果想要收缩为11M，但是在80M的地方还有数据，所以收缩为11M会出错，收缩为81M则可以实现。

实验如下：

1.先删除一个占空间较大的表（未演示），然后查询：

BYS@ bys001>select df.tablespace_name,df.sum_df_m as space_m,fs.sum_fs_m free_space,to_char(trunc((df.sum_df_m-fs.sum_fs_m)/df.sum_df_m,2)*100) as "used_%" from (select tablespace_name,sum(bytes/1024/1024) as sum_df_m from dba_data_files group by tablespace_name) df,(select tablespace_name,sum(bytes/1024/1024) as sum_fs_m from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name and df.tablespace_name='USERS';
TABLESPACE_NAME                   SPACE_M FREE_SPACE used_%
------------------------------ ---------- ---------- ----------------------------------------
USERS                                 150    145.375
BYS@ bys001>select 150-145.375 from dual;
150-145.375
-----------
      4.625

2.表空间只使用了4.6M，此时将表空间收缩为5M和6M来测试，均未实现。

BYS@ bys001>select file#,name from v$datafile where name like '%users%';
     FILE# NAME
---------- ---------------------------------------------
         4 /u01/app/oracle/oradata/bys001/users01.dbf
BYS@ bys001>alter database datafile '/u01/app/oracle/oradata/bys001/users01.dbf' resize 5M;
alter database datafile '/u01/app/oracle/oradata/bys001/users01.dbf' resize 5M
*
ERROR at line 1:

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

BYS@ bys001>alter database datafile '/u01/app/oracle/oradata/bys001/users01.dbf' resize 6M;
alter database datafile '/u01/app/oracle/oradata/bys001/users01.dbf' resize 6M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

3.此时可以查询出表空间数据文件的最大block号，根据最大block号和block大小算出数据存储的最大位置--这个称呼不太严谨。

用以下方法查出top_blocks数据块的位置
BYS@ bys001>select max(block_id) from dba_extents where file_id=4;
MAX(BLOCK_ID)
-------------
         2376
BYS@ bys001>show parameter db_block_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
算出top_blocks位于18.5625M处。
db_block_size 是8192byte,也就是8KB。这里也可以是2376*8192/1024/1024来算，算出的单位是M。
BYS@ bys001>select 2376*8/1024 M from dual;
         M
----------
   18.5625

4.此时执行收缩表空间命令，设置为19M是可以的（这里resize的尺寸需要是整数）。

BYS@ bys001>alter database datafile '/u01/app/oracle/oradata/bys001/users01.dbf' resize 19M;
Database altered.
从操作系统上查询：
[oracle@oel-01 bys001]$ du -sh users01.dbf
20M     users01.dbf

5.此时虽然表空间收缩为19M，但是实际使用的只有不到5M，还可以继续收缩。方法如下：

根据上面查出的file#和最大block号来查出是哪些段对象
BYS@ bys001>select distinct owner,segment_name,segment_type from dba_extents where file_id = &file_id and (block_id + &top_blocks) >(select max(block_id) from dba_extents where file_id=&file_id);
Enter value for file_id: 4
Enter value for top_blocks: 2376
Enter value for file_id: 4
old   1: select distinct owner,segment_name,segment_type from dba_extents where file_id = &file_id and (block_id + &top_blocks) >(select max(block_id) from dba_extents where file_id=&file_id)
new   1: select distinct owner,segment_name,segment_type from dba_extents where file_id = 4 and (block_id + 2376) >(select max(block_id) from dba_extents where file_id=4)
OWNER                          SEGMENT_NAME    SEGMENT_TYPE
------------------------------ --------------- ---------------
SCOTT                          DEPT2           TABLE
BYS                            TEST            TABLE
然后使用alter table test move命令
此命令可以对非分区表重新设置数据的存放位置、可以对分区表的一个分区移动到新的段中，甚至能够转移到不同的表空间中
BYS@ bys001> alter table test move tablespace users;
Table altered.
BYS@ bys001> alter table scott.dept2 move tablespace users;  
Table altered.
BYS@ bys001>alter database datafile '/u01/app/oracle/oradata/bys001/users01.dbf' resize 6M;
Database altered.
BYS@ bys001>select df.tablespace_name,df.sum_df_m as space_m,fs.sum_fs_m free_space,to_char(trunc((df.sum_df_m-fs.sum_fs_m)/df.sum_df_m,2)*100) as "used_%" from (select tablespace_name,sum(bytes/1024/1024) as sum_df_m from dba_data_files group by tablespace_name) df,(select tablespace_name,sum(bytes/1024/1024) as sum_fs_m from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name  and df.tablespace_name='USERS';
TABLESPACE_NAME                   SPACE_M FREE_SPACE used_%
------------------------------ ---------- ---------- ----------------------------------------
USERS                                   6     4.625  77