MOS(Doc ID 1029252.6)最佳实践
来源：互联网 发布：淘宝开店流程步骤ppt 编辑：程序博客网 时间：2024/06/10 02:34
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.992188SYSTEM /oracle/oradata/orcl/system01.dbf 643.117188UNDOTBS1 /oracle/oradata/orcl/undotbs01.dbf 199.054688USERS /oracle/oradata/orcl/users01.dbf 7141.99219SELECT 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 3370SYSTEM 700 643.12 91.87% 56.88 32124.8644 56.88SYSAUX 680 621.81 91.44% 58.19 32146.1744 34UNDOTBS1 200 91.69 45.85% 108.31 108.31 11.06RMAN 50 1 2.00% 49 49 49sys@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 <4SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS M----------------------------------- ---------- ---------- ---------- ----------T_USER_ACTIVE_LOG_BAK 4 913152 1024 8T_USER_ACTIVE_LOG_BAK 4 912128 1024 8T_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 3370USERS 4 533632 128 1USERS 4 533888 128 1USERS 4 537216 896 7USERS 4 550784 768 6USERS 4 551808 3456 27USERS 4 576360 24 .19USERS 4 584344 80 .63USERS 4 584440 8 .06USERS 4 584472 16 .13USERS 4 584496 80 .63USERS 4 592528 24 .19USERS 4 628432 24 .19USERS 4 675616 992 7.75USERS 4 681216 512 4USERS 4 681752 104 .81USERS 4 690928 16 .13USERS 4 691736 104 .81USERS 4 706904 40 .31USERS 4 707096 104 .81USERS 4 724816 48 .38USERS 4 725528 104 .81USERS 4 742936 104 .81USERS 4 762088 24 .19USERS 4 762392 104 .81USERS 4 779344 48 .38USERS 4 779648 128 1USERS 4 779800 1000 7.81USERS 4 800280 1000 7.81USERS 4 801304 1000 7.81USERS 4 821296 464 3.63USERS 4 821784 1000 7.81USERS 4 828152 8 .06USERS 4 914176 3328 26913152 + 1024 正好等于 9141764号文件最后一个区起始block_id = 914176 是一块空闲空间 ,大小26M与我们之前查询得到的USERS /oracle/oradata/orcl/users01.dbf 7141.99219刚好吻合 7168 - 26 = 7142 M。所以该表空间现在只能resize到 7142 Msys@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$DATAFILESET ECHO OFFSET PAGESIZ 25column file_name format a50select file_name, file_id from dba_data_files order by 2;ttitle -center 'Segment Extent Summary' skip 2col ownr format a8 heading 'Owner' justify ccol type format a15 heading 'Type' justify c trunccol name format a30 heading 'Segment Name' justify ccol exid format 990 heading 'Extent#' justify ccol fiid format 9990 heading 'File#' justify ccol blid format 9999990 heading 'Block#' justify ccol blks format 999,990 heading 'Blocks' justify cselect owner ownr, segment_name name, segment_type type, extent_id exid, file_id fiid, block_id blid, blocks blksfrom dba_extentswhere file_id = &file_idorder by block_id/FAN T_USER_ACTIVE_LOG_BAK TABLE 88 4 911104 1,024FAN 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,0242381 rows selected.3675sys@ORCL>select 3675*1024*1024 from dual;3675*1024*1024-------------- 3853516800USERS表空间已使用3675M,上下都是free的,那么我想resize到3675M,就输出3675select 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 toSET SERVEROUTPUT ONDECLARE 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.sqlEnter value for file_id: 4old 6: V_FILE_ID := &FILE_ID;new 6: V_FILE_ID := 4;Enter value for resize_file_to: 3853516800old 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 = TABLEDYL.EMP - OBJECT TYPE = TABLEDYL.EMPLOYEES - OBJECT TYPE = TABLEFAN.A1 - OBJECT TYPE = TABLEFAN.A2 - OBJECT TYPE = TABLEFAN.B1 - OBJECT TYPE = TABLEFAN.DEPT2 - OBJECT TYPE = TABLEFAN.DIM_DATE - OBJECT TYPE = TABLEFAN.DLEAF - OBJECT TYPE = TABLEFAN.EMP - OBJECT TYPE = TABLEFAN.IDX_B1_ID - OBJECT TYPE = INDEXFAN.IDX_ID - OBJECT TYPE = INDEXFAN.IDX_JOBPROCNAME - OBJECT TYPE = INDEXFAN.IDX_JOB_DEPENDJOB - OBJECT TYPE = INDEXFAN.IDX_JOB_STATDATE - OBJECT TYPE = INDEXFAN.IDX_LEAF - OBJECT TYPE = INDEXFAN.I_SC_SNO - OBJECT TYPE = INDEXFAN.N1 - OBJECT TYPE = TABLEFAN.N2 - OBJECT TYPE = TABLEFAN.SAL - OBJECT TYPE = TABLEFAN.SC - OBJECT TYPE = TABLEFAN.SCORE - OBJECT TYPE = TABLEFAN.STUDENT - OBJECT TYPE = TABLEFAN.SYS_C009670 - OBJECT TYPE = INDEXFAN.SYS_C009676 - OBJECT TYPE = INDEXFAN.SYS_C009683 - OBJECT TYPE = INDEXFAN.T - OBJECT TYPE = TABLEFAN.TEST - OBJECT TYPE = TABLEFAN.TEST_1 - OBJECT TYPE = TABLEFAN.TICKER - OBJECT TYPE = TABLEFAN.T_APPS - OBJECT TYPE = TABLEFAN.T_PLAN - OBJECT TYPE = TABLEFAN.T_USER_ACTIVE_LOG_BAK - OBJECT TYPE = TABLEFAN.T_YMJOB_DEPEND - OBJECT TYPE = TABLEFAN.T_YMJOB_INFO - OBJECT TYPE = TABLEFAN.T_YMJOB_LOG - OBJECT TYPE = TABLEFAN.X1 - OBJECT TYPE = TABLEFAN.Y1 - OBJECT TYPE = TABLEFAN.Z1 - OBJECT TYPE = TABLESCOTT.A - OBJECT TYPE = TABLESCOTT.A_1 - OBJECT TYPE = TABLESCOTT.B - OBJECT TYPE = TABLESCOTT.B_1 - OBJECT TYPE = TABLESCOTT.C - OBJECT TYPE = TABLESCOTT.DEPT1 - OBJECT TYPE = TABLESCOTT.DEPT2 - OBJECT TYPE = TABLESCOTT.DEPT3 - OBJECT TYPE = TABLESCOTT.EMP2 - OBJECT TYPE = TABLESCOTT.EMP3 - OBJECT TYPE = TABLESCOTT.EMP4 - OBJECT TYPE = TABLESCOTT.EMP_BAK - OBJECT TYPE = TABLESCOTT.GROUP_TEST - OBJECT TYPE = TABLESCOTT.IDX_ID - OBJECT TYPE = INDEXSCOTT.IDX_R_DATABASE_ATTRIBUTE_AK - OBJECT TYPE = INDEXSCOTT.IDX_R_STEP_ATTRIBUTE_LOOKUP - OBJECT TYPE = INDEXSCOTT.IDX_R_STEP_DATABASE_LU1 - OBJECT TYPE = INDEXSCOTT.IDX_R_STEP_DATABASE_LU2 - OBJECT TYPE = INDEXSCOTT.IDX_TRANS_ATTRIBUTE_LOOKUP - OBJECT TYPE = INDEXSCOTT.ID_TEST4_ID_NAME - OBJECT TYPE = INDEXSCOTT.OS_USER_SERVICE - OBJECT TYPE = TABLESCOTT.R_DATABASE - OBJECT TYPE = TABLESCOTT.R_DATABASE_ATTRIBUTE - OBJECT TYPE = TABLESCOTT.R_DATABASE_CONTYPE - OBJECT TYPE = TABLESCOTT.R_DATABASE_TYPE - OBJECT TYPE = TABLESCOTT.R_JOBENTRY_TYPE - OBJECT TYPE = TABLESCOTT.R_LOGLEVEL - OBJECT TYPE = TABLESCOTT.R_REPOSITORY_LOG - OBJECT TYPE = TABLESCOTT.R_STEP - OBJECT TYPE = TABLESCOTT.R_STEP_ATTRIBUTE - OBJECT TYPE = TABLESCOTT.R_STEP_DATABASE - OBJECT TYPE = TABLESCOTT.R_STEP_TYPE - OBJECT TYPE = TABLESCOTT.R_TRANSFORMATION - OBJECT TYPE = TABLESCOTT.R_TRANS_ATTRIBUTE - OBJECT TYPE = TABLESCOTT.R_TRANS_HOP - OBJECT TYPE = TABLESCOTT.R_USER - OBJECT TYPE = TABLESCOTT.R_VERSION - OBJECT TYPE = TABLESCOTT.SYS_C009522 - OBJECT TYPE = INDEXSCOTT.SYS_C009724 - OBJECT TYPE = INDEXSCOTT.SYS_C009725 - OBJECT TYPE = INDEXSCOTT.SYS_C009726 - OBJECT TYPE = INDEXSCOTT.SYS_C009727 - OBJECT TYPE = INDEXSCOTT.SYS_C009729 - OBJECT TYPE = INDEXSCOTT.SYS_C009730 - OBJECT TYPE = INDEXSCOTT.SYS_C009732 - OBJECT TYPE = INDEXSCOTT.SYS_C009733 - OBJECT TYPE = INDEXSCOTT.SYS_C009744 - OBJECT TYPE = INDEXSCOTT.SYS_C009747 - OBJECT TYPE = INDEXSCOTT.SYS_C009748 - OBJECT TYPE = INDEXSCOTT.SYS_C009749 - OBJECT TYPE = INDEXSCOTT.SYS_C009750 - OBJECT TYPE = INDEXSCOTT.SYS_C009753 - OBJECT TYPE = INDEXSCOTT.SYS_C009764 - OBJECT TYPE = INDEXSCOTT.SYS_IL0000074312C00005$$ - OBJECT TYPE = LOBINDEXSCOTT.SYS_IL0000074328C00006$$ - OBJECT TYPE = LOBINDEXSCOTT.SYS_IL0000074332C00004$$ - OBJECT TYPE = LOBINDEXSCOTT.SYS_IL0000074340C00004$$ - OBJECT TYPE = LOBINDEXSCOTT.SYS_IL0000074340C00005$$ - OBJECT TYPE = LOBINDEXSCOTT.SYS_IL0000074346C00006$$ - OBJECT TYPE = LOBINDEXSCOTT.SYS_IL0000074383C00004$$ - OBJECT TYPE = LOBINDEXSCOTT.SYS_IL0000074387C00007$$ - OBJECT TYPE = LOBINDEXSCOTT.SYS_LOB0000074312C00005$$ - OBJECT TYPE = LOBSEGMENTSCOTT.SYS_LOB0000074328C00006$$ - OBJECT TYPE = LOBSEGMENTSCOTT.SYS_LOB0000074332C00004$$ - OBJECT TYPE = LOBSEGMENTSCOTT.SYS_LOB0000074340C00004$$ - OBJECT TYPE = LOBSEGMENTSCOTT.SYS_LOB0000074340C00005$$ - OBJECT TYPE = LOBSEGMENTSCOTT.SYS_LOB0000074346C00006$$ - OBJECT TYPE = LOBSEGMENTSCOTT.SYS_LOB0000074383C00004$$ - OBJECT TYPE = LOBSEGMENTSCOTT.SYS_LOB0000074387C00007$$ - OBJECT TYPE = LOBSEGMENTSCOTT.TEST - OBJECT TYPE = TABLESCOTT.TEST1 - OBJECT TYPE = TABLESCOTT.TEST2 - OBJECT TYPE = TABLESCOTT.TREE - OBJECT TYPE = TABLESCOTT.T_EXCE - OBJECT TYPE = TABLESCOTT.T_I - OBJECT TYPE = INDEXSCOTT.T_LOG - OBJECT TYPE = TABLESCOTT.T_PAYLOG - OBJECT TYPE = TABLESCOTT.T_PAYLOG_TMP - OBJECT TYPE = TABLESCOTT.T_STUDENT - OBJECT TYPE = TABLESCOTT.T_STUDENT_1 - OBJECT TYPE = TABLESCOTT.USER_LOGIN_BAK - OBJECT TYPE = TABLESH.DIMENSION_EXCEPTIONS - OBJECT TYPE = TABLE===================================================================PARTITIONED OBJECTS===================================================================FAN.P_TEST - PARTITION = SYS_P81 - OBJECT TYPE = TABLE PARTITIONFAN.P_TEST - PARTITION = SYS_P82 - OBJECT TYPE = TABLE PARTITIONFAN.P_TEST - PARTITION = SYS_P83 - OBJECT TYPE = TABLE PARTITIONFAN.P_TEST - PARTITION = SYS_P84 - OBJECT TYPE = TABLE PARTITIONFAN.P_TEST - PARTITION = SYS_P85 - OBJECT TYPE = TABLE PARTITIONFAN.P_TEST - PARTITION = SYS_P86 - OBJECT TYPE = TABLE PARTITIONFAN.P_TEST - PARTITION = SYS_P87 - OBJECT TYPE = TABLE PARTITIONFAN.P_TEST - PARTITION = SYS_P88 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160101 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160102 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160103 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160104 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160105 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160106 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160107 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160108 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160109 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160110 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160111 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160112 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160113 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160114 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160115 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160116 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160117 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160118 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160119 - OBJECT TYPE = TABLE PARTITIONFAN.T_USER_ACTIVE_LOG - PARTITION = P160120 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P1 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P10 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P11 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P12 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P13 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P14 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P15 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P16 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P17 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P18 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P19 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P2 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P20 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P21 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P22 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P23 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P24 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P25 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P26 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P27 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P28 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P29 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P3 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P30 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P31 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P32 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P4 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P5 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P6 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P7 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P8 - OBJECT TYPE = TABLE PARTITIONSCOTT.A_32 - PARTITION = P9 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P1 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P10 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P11 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P12 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P13 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P14 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P15 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P16 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P17 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P18 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P19 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P2 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P20 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P21 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P22 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P23 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P24 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P25 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P26 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P27 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P28 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P29 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P3 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P30 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P31 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P32 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P4 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P5 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P6 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P7 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P8 - OBJECT TYPE = TABLE PARTITIONSCOTT.B_32 - PARTITION = P9 - OBJECT TYPE = TABLE PARTITIONPL/SQL procedure successfully completed.修改后的shrink_datafileSET SERVEROUTPUT ONDECLARE 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表空间已经降到3734column 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.992188SYSTEM /oracle/oradata/orcl/system01.dbf 643.117188UNDOTBS1 /oracle/oradata/orcl/undotbs01.dbf 199.054688USERS /oracle/oradata/orcl/users01.dbf 3734.99219sys@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 selecteddrop tablespace bak including contents and datafile 0 0
- MOS(Doc ID 1029252.6)最佳实践
- 转MOS 中文文档列表 - Oracle Database (Doc ID 1533057.1)
- 【转载自mos文章】Get Proactive : Oracle Database (Doc ID 1527355.1)
- 【翻译自mos文章】How to Set or Switch Oracle Homes on Windows (Doc ID 969581.1)
- 获取Goroutine Id的最佳实践
- 获取Goroutine Id的最佳实践
- atitit.提升兼容性最佳实践 p825.doc
- MOS 文档 ID 1212703.
- 【转载自mos中文文章】如何下载并运行Oracle数据库预升级实用程序 (Doc ID 1577379.1)
- Hbase rowKey 最佳实践 和 mysql id 对比
- 【转载自mos中文文章】RAC 环境中 gc block lost 和私网通信性能问题的诊断 (Doc ID 1674865.1)
- Doc ID 888828.1
- 最佳实践
- 最佳实践
- 最佳实践
- 翻译mos文章 scn headroom ID 1376995.1
- 翻译mos关于scnhealthcheck.sql( ID 1393363.1)
- 关于备份和恢复的10 个最佳实践 (文档 ID 1549189.1)
- shared_ptr线程安全性全面分析
- B树代码
- Ubuntu安装搜狗拼音
- DELPHI基于线程的定时器和一个泛型对象池
- JAVA WEB学习--环境配置
- MOS(Doc ID 1029252.6)最佳实践
- 屏幕旋转调用的方法
- JAVA设计模式
- sql server ddl触发器限制用户创建表
- bootstrap2.x与3的区别
- Android基础——2.Android Studio
- java .class
- 浅谈iOS视频开发 - 小书sky
- 占位2——自定义Dialog，写工具类DialogUtils
