MOS(Doc ID 1029252.6)最佳实践

column file_name format a50; 
column tablespace_name format a15; 
column highwater format 9999999999; 
select a.tablespace_name 
,a.file_name 
,(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 "highwater(M)"
from dba_data_files a 
,(select file_id,max(block_id) maximum 
from dba_extents 
group by file_id) b 
,dba_extents c 
,(select value db_block_size 
from v$parameter 
where name='db_block_size') d 
where a.file_id = b.file_id 
and c.file_id = b.file_id 
and c.block_id = b.maximum 
order by a.tablespace_name,a.file_name 
/ 

TABLESPACE_NAME FILE_NAME                                          highwater(M)
--------------- -------------------------------------------------- ------------
SYSAUX          /oracle/oradata/orcl/sysaux01.dbf                    645.992188
SYSTEM          /oracle/oradata/orcl/system01.dbf                    643.117188
UNDOTBS1        /oracle/oradata/orcl/undotbs01.dbf                   199.054688
USERS           /oracle/oradata/orcl/users01.dbf                     7141.99219


SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "已扩展空闲空间(M)",
    (SELECT free_space_mb+free_allocate_mb FROM dba_tablespace_free a where a.tablespace_name= f.tablespace_name) "总剩余空间",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 3 desc;

表空间名                       表空间大小(M) 已使用空间(M) 使用比   已扩展空闲空间(M) 总剩余空间  最大块(M)
------------------------------ ------------- ------------- -------- ----------------- ---------- ----------
USERS                                   7168       3674.06   51.26%           3493.94    3493.94       3370
SYSTEM                                   700        643.12   91.87%             56.88 32124.8644      56.88
SYSAUX                                   680        621.81   91.44%             58.19 32146.1744         34
UNDOTBS1                                 200         91.69   45.85%            108.31     108.31      11.06
RMAN                                      50             1    2.00%                49         49         49


sys@ORCL>alter database datafile '/oracle/oradata/orcl/users01.dbf' resize 7141M;
alter database datafile '/oracle/oradata/orcl/users01.dbf' resize 7141M
*
第 1 行出现错误:
ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据


select * from (
select segment_name,file_id,block_id,blocks,bytes/1024/1024 M from dba_extents where tablespace_name='USERS' order by 3 desc) where rownum <4

SEGMENT_NAME                           FILE_ID   BLOCK_ID     BLOCKS          M
----------------------------------- ---------- ---------- ---------- ----------
T_USER_ACTIVE_LOG_BAK                        4     913152       1024          8
T_USER_ACTIVE_LOG_BAK                        4     912128       1024          8
T_USER_ACTIVE_LOG_BAK                        4     911104       1024          8
可以看到最后一个区的起始block_id是913152,这个区有1024个块

sys@ORCL>SELECT tablespace_name,file_id,block_id,blocks,ROUND(BYTES / (1024 * 1024), 2) BYTES FROM DBA_FREE_SPACE
    WHERE TABLESPACE_NAME='USERS'
    ORDER BY BLOCK_ID;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID     BLOCKS      BYTES -空闲的区大小
------------------------------ ---------- ---------- ---------- ----------
USERS                                   4      76672     431360       3370
USERS                                   4     533632        128          1
USERS                                   4     533888        128          1
USERS                                   4     537216        896          7
USERS                                   4     550784        768          6
USERS                                   4     551808       3456         27
USERS                                   4     576360         24        .19
USERS                                   4     584344         80        .63
USERS                                   4     584440          8        .06
USERS                                   4     584472         16        .13
USERS                                   4     584496         80        .63
USERS                                   4     592528         24        .19
USERS                                   4     628432         24        .19
USERS                                   4     675616        992       7.75
USERS                                   4     681216        512          4
USERS                                   4     681752        104        .81
USERS                                   4     690928         16        .13
USERS                                   4     691736        104        .81
USERS                                   4     706904         40        .31
USERS                                   4     707096        104        .81
USERS                                   4     724816         48        .38
USERS                                   4     725528        104        .81
USERS                                   4     742936        104        .81
USERS                                   4     762088         24        .19
USERS                                   4     762392        104        .81
USERS                                   4     779344         48        .38
USERS                                   4     779648        128          1
USERS                                   4     779800       1000       7.81
USERS                                   4     800280       1000       7.81
USERS                                   4     801304       1000       7.81
USERS                                   4     821296        464       3.63
USERS                                   4     821784       1000       7.81
USERS                                   4     828152          8        .06
USERS                                   4     914176       3328         26

913152 + 1024 正好等于 914176

4号文件最后一个区起始block_id = 914176 是一块空闲空间 ,大小26M
与我们之前查询得到的
USERS           /oracle/oradata/orcl/users01.dbf                     7141.99219
刚好吻合 7168 - 26 = 7142 M。所以该表空间现在只能resize到 7142 M

sys@ORCL>alter database datafile '/oracle/oradata/orcl/users01.dbf' resize 7142M
  2  ;

Database altered.

也就是说resize只能收缩datafile尾部的空闲区,而中间的无法直接resize回收,比如这个
USERS                                   4      76672     431360       3370 近 3G的空间无法回收

If you have a large extent in the middle of a datafile, and some object taking up room at the end of the datafile, you can use the query FINDEXT.SQL below to find this object. If you export this object, then drop it, you should then free up contiguous space at the end of your datafile so you will be able to resize it smaller.


这个脚本就是查出某个数据文件的所有区,每个区的起始block_id,包含多少个块,还会显示区的owner,segment name, and segment type.
-- FINDEXT.SQL

-- This script lists all the extents contained in that datafile,
-- the block_id where the extent starts,
-- and how many blocks the extent contains.
-- It also shows the owner, segment name, and segment type.

-- Input: FILE_ID from DBA_DATA_FILES or FILE# from V$DATAFILE

SET ECHO OFF
SET PAGESIZ 25

column file_name format a50
select file_name, file_id from dba_data_files order by 2;

ttitle -
center 'Segment Extent Summary' skip 2

col ownr format a8 heading 'Owner' justify c
col type format a15 heading 'Type' justify c trunc
col name format a30 heading 'Segment Name' justify c
col exid format 990 heading 'Extent#' justify c
col fiid format 9990 heading 'File#' justify c
col blid format 9999990 heading 'Block#' justify c
col blks format 999,990 heading 'Blocks' justify c

select owner ownr, segment_name name, segment_type type, extent_id exid, file_id fiid, block_id blid, blocks blks
from dba_extents
where file_id = &file_id
order by block_id
/

FAN      T_USER_ACTIVE_LOG_BAK          TABLE                88     4   911104    1,024
FAN      T_USER_ACTIVE_LOG_BAK          TABLE                89     4   912128    1,024

                                                                     Segment Extent Summary

 Owner            Segment Name               Type       Extent# File#  Block#   Blocks
-------- ------------------------------ --------------- ------- ----- -------- --------
FAN      T_USER_ACTIVE_LOG_BAK          TABLE                90     4   913152    1,024

2381 rows selected.

3675
sys@ORCL>select 3675*1024*1024 from dual;

3675*1024*1024
--------------
    3853516800

USERS表空间已使用3675M,上下都是free的,那么我想resize到3675M,就输出3675
select file_id,block_id,blocks,bytes/1024/1024 m,(block_id + blocks-1)*8192/1024/1024 M,(block_id + blocks-1) from dba_extents where file_id=4 order by 2;
...
         4     911104       1024          8 7125.99219              912127
         4     912128       1024          8 7133.99219              913151
         4     913152       1024          8 7141.99219              914175
这个脚本实际是查除了 block_id + blocks-1 * 块大小,也就是我这个区在 3675M之上的所有对象,因为只有尾部的能直接resize,中间的不可以,所以要把3675之上的所有文件move出去再move回来让空闲空间在尾部


-- SHRINK_DATAFILE.SQL

-- This script lists the object names and types that must be moved in order to resize a datafile to a specified smaller size

-- Input: FILE_ID from DBA_DATA_FILES or FILE# from V$DATAFILE
-- Size in bytes that the datafile will be resized to

SET SERVEROUTPUT ON

DECLARE 
     V_FILE_ID NUMBER; 
     V_BLOCK_SIZE NUMBER; 
     V_RESIZE_SIZE NUMBER; 
BEGIN 
     V_FILE_ID := &FILE_ID; 
     V_RESIZE_SIZE := &RESIZE_FILE_TO; 

     SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID; 

     DBMS_OUTPUT.PUT_LINE('.'); 
     DBMS_OUTPUT.PUT_LINE('.'); 
     DBMS_OUTPUT.PUT_LINE('.'); 
     DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '||V_RESIZE_SIZE||' BYTES'); 
     DBMS_OUTPUT.PUT_LINE('===================================================================');
     DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS'); 
     DBMS_OUTPUT.PUT_LINE('===================================================================');

     for my_record in ( 
          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME 
          FROM DBA_EXTENTS 
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE 
          AND FILE_ID = V_FILE_ID 
          AND SEGMENT_TYPE NOT LIKE '%PARTITION%' 
          ORDER BY 1) LOOP 
               DBMS_OUTPUT.PUT_LINE(my_record.ONAME); 
     END LOOP; 

     DBMS_OUTPUT.PUT_LINE('===================================================================');
     DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS'); 
     DBMS_OUTPUT.PUT_LINE('===================================================================');

     for my_record in ( 
          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - PARTITION = '||PARTITION_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME 
          FROM DBA_EXTENTS 
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE 
          AND FILE_ID = V_FILE_ID 
          AND SEGMENT_TYPE LIKE '%PARTITION%' 
          ORDER BY 1) LOOP 
               DBMS_OUTPUT.PUT_LINE(my_record.ONAME); 
     END LOOP; 

END; 
/

sys@ORCL>@/home/oracle/scripts/shrink_datafile.sql
Enter value for file_id: 4
old   6:      V_FILE_ID := &FILE_ID;
new   6:      V_FILE_ID := 4;
Enter value for resize_file_to: 3853516800
old   7:      V_RESIZE_SIZE := &RESIZE_FILE_TO;
new   7:      V_RESIZE_SIZE := 3853516800;
.
.
.
OBJECTS IN FILE 4 THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO 3853516800 BYTES
===================================================================
NON-PARTITIONED OBJECTS
===================================================================
DYL.DEPT - OBJECT TYPE = TABLE
DYL.EMP - OBJECT TYPE = TABLE
DYL.EMPLOYEES - OBJECT TYPE = TABLE
FAN.A1 - OBJECT TYPE = TABLE
FAN.A2 - OBJECT TYPE = TABLE
FAN.B1 - OBJECT TYPE = TABLE
FAN.DEPT2 - OBJECT TYPE = TABLE
FAN.DIM_DATE - OBJECT TYPE = TABLE
FAN.DLEAF - OBJECT TYPE = TABLE
FAN.EMP - OBJECT TYPE = TABLE
FAN.IDX_B1_ID - OBJECT TYPE = INDEX
FAN.IDX_ID - OBJECT TYPE = INDEX
FAN.IDX_JOBPROCNAME - OBJECT TYPE = INDEX
FAN.IDX_JOB_DEPENDJOB - OBJECT TYPE = INDEX
FAN.IDX_JOB_STATDATE - OBJECT TYPE = INDEX
FAN.IDX_LEAF - OBJECT TYPE = INDEX
FAN.I_SC_SNO - OBJECT TYPE = INDEX
FAN.N1 - OBJECT TYPE = TABLE
FAN.N2 - OBJECT TYPE = TABLE
FAN.SAL - OBJECT TYPE = TABLE
FAN.SC - OBJECT TYPE = TABLE
FAN.SCORE - OBJECT TYPE = TABLE
FAN.STUDENT - OBJECT TYPE = TABLE
FAN.SYS_C009670 - OBJECT TYPE = INDEX
FAN.SYS_C009676 - OBJECT TYPE = INDEX
FAN.SYS_C009683 - OBJECT TYPE = INDEX
FAN.T - OBJECT TYPE = TABLE
FAN.TEST - OBJECT TYPE = TABLE
FAN.TEST_1 - OBJECT TYPE = TABLE
FAN.TICKER - OBJECT TYPE = TABLE
FAN.T_APPS - OBJECT TYPE = TABLE
FAN.T_PLAN - OBJECT TYPE = TABLE
FAN.T_USER_ACTIVE_LOG_BAK - OBJECT TYPE = TABLE
FAN.T_YMJOB_DEPEND - OBJECT TYPE = TABLE
FAN.T_YMJOB_INFO - OBJECT TYPE = TABLE
FAN.T_YMJOB_LOG - OBJECT TYPE = TABLE
FAN.X1 - OBJECT TYPE = TABLE
FAN.Y1 - OBJECT TYPE = TABLE
FAN.Z1 - OBJECT TYPE = TABLE
SCOTT.A - OBJECT TYPE = TABLE
SCOTT.A_1 - OBJECT TYPE = TABLE
SCOTT.B - OBJECT TYPE = TABLE
SCOTT.B_1 - OBJECT TYPE = TABLE
SCOTT.C - OBJECT TYPE = TABLE
SCOTT.DEPT1 - OBJECT TYPE = TABLE
SCOTT.DEPT2 - OBJECT TYPE = TABLE
SCOTT.DEPT3 - OBJECT TYPE = TABLE
SCOTT.EMP2 - OBJECT TYPE = TABLE
SCOTT.EMP3 - OBJECT TYPE = TABLE
SCOTT.EMP4 - OBJECT TYPE = TABLE
SCOTT.EMP_BAK - OBJECT TYPE = TABLE
SCOTT.GROUP_TEST - OBJECT TYPE = TABLE
SCOTT.IDX_ID - OBJECT TYPE = INDEX
SCOTT.IDX_R_DATABASE_ATTRIBUTE_AK - OBJECT TYPE = INDEX
SCOTT.IDX_R_STEP_ATTRIBUTE_LOOKUP - OBJECT TYPE = INDEX
SCOTT.IDX_R_STEP_DATABASE_LU1 - OBJECT TYPE = INDEX
SCOTT.IDX_R_STEP_DATABASE_LU2 - OBJECT TYPE = INDEX
SCOTT.IDX_TRANS_ATTRIBUTE_LOOKUP - OBJECT TYPE = INDEX
SCOTT.ID_TEST4_ID_NAME - OBJECT TYPE = INDEX
SCOTT.OS_USER_SERVICE - OBJECT TYPE = TABLE
SCOTT.R_DATABASE - OBJECT TYPE = TABLE
SCOTT.R_DATABASE_ATTRIBUTE - OBJECT TYPE = TABLE
SCOTT.R_DATABASE_CONTYPE - OBJECT TYPE = TABLE
SCOTT.R_DATABASE_TYPE - OBJECT TYPE = TABLE
SCOTT.R_JOBENTRY_TYPE - OBJECT TYPE = TABLE
SCOTT.R_LOGLEVEL - OBJECT TYPE = TABLE
SCOTT.R_REPOSITORY_LOG - OBJECT TYPE = TABLE
SCOTT.R_STEP - OBJECT TYPE = TABLE
SCOTT.R_STEP_ATTRIBUTE - OBJECT TYPE = TABLE
SCOTT.R_STEP_DATABASE - OBJECT TYPE = TABLE
SCOTT.R_STEP_TYPE - OBJECT TYPE = TABLE
SCOTT.R_TRANSFORMATION - OBJECT TYPE = TABLE
SCOTT.R_TRANS_ATTRIBUTE - OBJECT TYPE = TABLE
SCOTT.R_TRANS_HOP - OBJECT TYPE = TABLE
SCOTT.R_USER - OBJECT TYPE = TABLE
SCOTT.R_VERSION - OBJECT TYPE = TABLE
SCOTT.SYS_C009522 - OBJECT TYPE = INDEX
SCOTT.SYS_C009724 - OBJECT TYPE = INDEX
SCOTT.SYS_C009725 - OBJECT TYPE = INDEX
SCOTT.SYS_C009726 - OBJECT TYPE = INDEX
SCOTT.SYS_C009727 - OBJECT TYPE = INDEX
SCOTT.SYS_C009729 - OBJECT TYPE = INDEX
SCOTT.SYS_C009730 - OBJECT TYPE = INDEX
SCOTT.SYS_C009732 - OBJECT TYPE = INDEX
SCOTT.SYS_C009733 - OBJECT TYPE = INDEX
SCOTT.SYS_C009744 - OBJECT TYPE = INDEX
SCOTT.SYS_C009747 - OBJECT TYPE = INDEX
SCOTT.SYS_C009748 - OBJECT TYPE = INDEX
SCOTT.SYS_C009749 - OBJECT TYPE = INDEX
SCOTT.SYS_C009750 - OBJECT TYPE = INDEX
SCOTT.SYS_C009753 - OBJECT TYPE = INDEX
SCOTT.SYS_C009764 - OBJECT TYPE = INDEX
SCOTT.SYS_IL0000074312C00005$$ - OBJECT TYPE = LOBINDEX
SCOTT.SYS_IL0000074328C00006$$ - OBJECT TYPE = LOBINDEX
SCOTT.SYS_IL0000074332C00004$$ - OBJECT TYPE = LOBINDEX
SCOTT.SYS_IL0000074340C00004$$ - OBJECT TYPE = LOBINDEX
SCOTT.SYS_IL0000074340C00005$$ - OBJECT TYPE = LOBINDEX
SCOTT.SYS_IL0000074346C00006$$ - OBJECT TYPE = LOBINDEX
SCOTT.SYS_IL0000074383C00004$$ - OBJECT TYPE = LOBINDEX
SCOTT.SYS_IL0000074387C00007$$ - OBJECT TYPE = LOBINDEX
SCOTT.SYS_LOB0000074312C00005$$ - OBJECT TYPE = LOBSEGMENT
SCOTT.SYS_LOB0000074328C00006$$ - OBJECT TYPE = LOBSEGMENT
SCOTT.SYS_LOB0000074332C00004$$ - OBJECT TYPE = LOBSEGMENT
SCOTT.SYS_LOB0000074340C00004$$ - OBJECT TYPE = LOBSEGMENT
SCOTT.SYS_LOB0000074340C00005$$ - OBJECT TYPE = LOBSEGMENT
SCOTT.SYS_LOB0000074346C00006$$ - OBJECT TYPE = LOBSEGMENT
SCOTT.SYS_LOB0000074383C00004$$ - OBJECT TYPE = LOBSEGMENT
SCOTT.SYS_LOB0000074387C00007$$ - OBJECT TYPE = LOBSEGMENT
SCOTT.TEST - OBJECT TYPE = TABLE
SCOTT.TEST1 - OBJECT TYPE = TABLE
SCOTT.TEST2 - OBJECT TYPE = TABLE
SCOTT.TREE - OBJECT TYPE = TABLE
SCOTT.T_EXCE - OBJECT TYPE = TABLE
SCOTT.T_I - OBJECT TYPE = INDEX
SCOTT.T_LOG - OBJECT TYPE = TABLE
SCOTT.T_PAYLOG - OBJECT TYPE = TABLE
SCOTT.T_PAYLOG_TMP - OBJECT TYPE = TABLE
SCOTT.T_STUDENT - OBJECT TYPE = TABLE
SCOTT.T_STUDENT_1 - OBJECT TYPE = TABLE
SCOTT.USER_LOGIN_BAK - OBJECT TYPE = TABLE
SH.DIMENSION_EXCEPTIONS - OBJECT TYPE = TABLE
===================================================================
PARTITIONED OBJECTS
===================================================================
FAN.P_TEST - PARTITION = SYS_P81 - OBJECT TYPE = TABLE PARTITION
FAN.P_TEST - PARTITION = SYS_P82 - OBJECT TYPE = TABLE PARTITION
FAN.P_TEST - PARTITION = SYS_P83 - OBJECT TYPE = TABLE PARTITION
FAN.P_TEST - PARTITION = SYS_P84 - OBJECT TYPE = TABLE PARTITION
FAN.P_TEST - PARTITION = SYS_P85 - OBJECT TYPE = TABLE PARTITION
FAN.P_TEST - PARTITION = SYS_P86 - OBJECT TYPE = TABLE PARTITION
FAN.P_TEST - PARTITION = SYS_P87 - OBJECT TYPE = TABLE PARTITION
FAN.P_TEST - PARTITION = SYS_P88 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160101 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160102 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160103 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160104 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160105 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160106 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160107 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160108 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160109 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160110 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160111 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160112 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160113 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160114 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160115 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160116 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160117 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160118 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160119 - OBJECT TYPE = TABLE PARTITION
FAN.T_USER_ACTIVE_LOG - PARTITION = P160120 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P1 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P10 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P11 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P12 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P13 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P14 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P15 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P16 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P17 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P18 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P19 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P2 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P20 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P21 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P22 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P23 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P24 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P25 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P26 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P27 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P28 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P29 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P3 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P30 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P31 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P32 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P4 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P5 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P6 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P7 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P8 - OBJECT TYPE = TABLE PARTITION
SCOTT.A_32 - PARTITION = P9 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P1 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P10 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P11 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P12 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P13 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P14 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P15 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P16 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P17 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P18 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P19 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P2 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P20 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P21 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P22 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P23 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P24 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P25 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P26 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P27 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P28 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P29 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P3 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P30 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P31 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P32 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P4 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P5 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P6 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P7 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P8 - OBJECT TYPE = TABLE PARTITION
SCOTT.B_32 - PARTITION = P9 - OBJECT TYPE = TABLE PARTITION

PL/SQL procedure successfully completed.


修改后的shrink_datafile
SET SERVEROUTPUT ON

DECLARE 
     V_FILE_ID NUMBER; 
     V_BLOCK_SIZE NUMBER; 
     V_RESIZE_SIZE NUMBER; 
     v_MvToTablespace varchar2(200);
BEGIN 
     V_FILE_ID := &FILE_ID; 
     V_RESIZE_SIZE := &RESIZE_FILE_TO; 
     v_MvToTablespace := &MvToTablespace;

     SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID; 

     DBMS_OUTPUT.PUT_LINE('.'); 
     DBMS_OUTPUT.PUT_LINE('.'); 
     DBMS_OUTPUT.PUT_LINE('.'); 
     DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '||V_RESIZE_SIZE||' MEGA BYTES'); 
     DBMS_OUTPUT.PUT_LINE('===================================================================');
     DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS'); 
     DBMS_OUTPUT.PUT_LINE('===================================================================');

     for my_record in ( 
          SELECT case when segment_type='TABLE' then 'alter table '||OWNER||'.'||SEGMENT_NAME||' move tablespace '||v_MvToTablespace||';'
                      when segment_type='INDEX' then 'alter index '||OWNER||'.'||segment_name||' rebuild tablespace '||v_MvToTablespace||';' end as sql_stat 
          from(
          select distinct owner,segment_name,segment_type
          FROM DBA_EXTENTS 
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE/1024/1024 > V_RESIZE_SIZE 
          AND FILE_ID = V_FILE_ID
          AND SEGMENT_TYPE NOT LIKE '%PARTITION%' and SEGMENT_TYPE not like 'LOB%'
          ORDER BY 1)) LOOP 
               DBMS_OUTPUT.PUT_LINE(my_record.sql_stat); 
     END LOOP; 


     DBMS_OUTPUT.PUT_LINE('===================================================================');
     DBMS_OUTPUT.PUT_LINE('LOB OBJECTS'); 
     DBMS_OUTPUT.PUT_LINE('===================================================================');

     for my_record in (
          select 'alter table '||owner||'.'||table_name||' move lob('||column_name||') store as (tablespace '||v_MvToTablespace||');' as sql_stat
          from (
          select distinct e.owner,e.segment_name,e.segment_type,l.table_name,l.column_name
          FROM DBA_EXTENTS e,dba_lobs l
          WHERE e.owner=l.owner and e.segment_name=l.segment_name 
          and (block_id + blocks-1)*V_BLOCK_SIZE/1024/1024 > V_RESIZE_SIZE 
          AND FILE_ID = V_FILE_ID
          AND SEGMENT_TYPE = 'LOBSEGMENT'
          ORDER BY 1)) LOOP
               DBMS_OUTPUT.PUT_LINE(my_record.sql_stat);
     end loop;

     DBMS_OUTPUT.PUT_LINE('===================================================================');
     DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS'); 
     DBMS_OUTPUT.PUT_LINE('===================================================================');

     for my_record in ( 
          select 'alter table '||OWNER||'.'||SEGMENT_NAME||' move partition '||PARTITION_NAME||' tablespace '||v_MvToTablespace||';' as sql_stat
          from (
          SELECT DISTINCT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE
          FROM DBA_EXTENTS 
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE/1024/1024 > V_RESIZE_SIZE 
          AND FILE_ID = V_FILE_ID 
          AND SEGMENT_TYPE LIKE '%PARTITION%' 
          ORDER BY 1)) LOOP 
               DBMS_OUTPUT.PUT_LINE(my_record.sql_stat); 
     END LOOP; 

end;
/

:LOB类型在建立含有lob字段的表时，oracle会自动为lob字段建立两个单独的segment,一个用来存放数据（segment_type=LOBSEGMENT），另一个用来存放索引（segment_type=LOBINDEX）。默认它们会存储在和表一起的表空间。我们对表MOVE时，LOG类型字段和该字段的索引不会跟着MOVE，必须要单独来进行MOVE，语法如下如：


alter table t321 move tablespace users;
alter table t321 move lob(en) store as (tablespace users);
alter table t321 move partition p1 tablespace xx;

通过上面的脚本生成的语句将对象move到bak,再move回来

再次查看 users表空间已经降到3734
column file_name format a50; 
column tablespace_name format a15; 
column highwater format 9999999999; 
select a.tablespace_name 
,a.file_name 
,(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 "highwater(M)"
from dba_data_files a 
,(select file_id,max(block_id) maximum 
from dba_extents 
group by file_id) b 
,dba_extents c 
,(select value db_block_size 
from v$parameter 
where name='db_block_size') d 
where a.file_id = b.file_id 
and c.file_id = b.file_id 
and c.block_id = b.maximum 
order by a.tablespace_name,a.file_name 
/ 
TABLESPACE_NAME FILE_NAME                                          highwater(M)
--------------- -------------------------------------------------- ------------
SYSAUX          /oracle/oradata/orcl/sysaux01.dbf                    645.992188
SYSTEM          /oracle/oradata/orcl/system01.dbf                    643.117188
UNDOTBS1        /oracle/oradata/orcl/undotbs01.dbf                   199.054688
USERS           /oracle/oradata/orcl/users01.dbf                     3734.99219


sys@ORCL>alter database datafile '/oracle/oradata/orcl/users01.dbf' resize 3735M;

数据库已更改。

检查bak表空间中还有无对象
sys@ORCL>select owner,segment_name from DBA_SEGMENTS WHERE TABLESPACE_NAME='BAK' ;

no rows selected

drop tablespace bak including contents and datafile