ORACLE資料庫降低高水位線方法
本檔案是針對表空間收縮的檔案，主要包括以下幾個流程：
ü 收集當前資料庫相關資訊
ü 降低資料庫表高水位線
ü Resize 收縮資料檔案
具體細節詳見以下章節。
|
操作型別 |
預估時間 |
實際時間 |
|
資料庫資訊收集 |
30min |
10min |
|
降低高水位線 |
30min |
20min |
|
Resize 資料檔案 |
30min |
10min |
SQL> SELECT total.tablespace_name,
Round(total.MB, 2) AS Total_MB,
Round(total.MB - free.MB, 2) AS Used_MB,
Round(( 1 - free.MB / total.MB ) * 100, 2)
|| '%' AS Used_Pct
FROM (SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;
TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT
------------------------------ ---------- ---------- -----------------------------------------
SYSAUX 5000 1359.19 27.18%
SYSTEM 1024 625.13 61.05%
TS_AUD 4000 1483.19 37.08%
TS_BJCA 512 118.63 23.17%
TS_BT 512 .06 .01%
TS_CHDD 2000 864.06 43.2%
TS_DATA 2167482 496135.81 22.89%
TS_FCCH 512 1.94 .38%
TS_FDC 50000 36266.88 72.53%
TS_INDEX 390000 294789.81 75.59%
TS_LOG 30000 23551.81 78.51%
TS_RMAN 512 266.06 51.97%
TS_SB 6000 4594 76.57%
TS_SCJY 10000 5618.5 56.19%
TS_WHMS 512 310.38 60.62%
TS_ZLPT 2048 484.06 23.64%
UNDOTBS1 60000 59999.63 100%
USERS 5000 1060.06 21.2%
# 與客戶描述的一致，TS_DATA表空間量大，但實際使用量小。
SQL> select owner,tablespace_name ,sum(bytes)/1024/1024/1024 G from dba_segments where tablespace_name = 'TS_DATA' group by owner,tablespace_name;
OWNER TABLESPACE_NAME G
------------------------------ ------------------------------ ----------
TMC TS_DATA 454.085266
IBMS_SEC TS_DATA .005004883
PLATFORM TS_DATA .290893555
SZFDC TS_DATA .661071777
GTB_APP TS_DATA .20111084
FDC_ZJJYJZX TS_DATA 7.76208496
SZFDCOA TS_DATA 1.01373291
BWPLATFORM TS_DATA 16.6900024
SZFDC_CA TS_DATA 2.73272705
FDC_YJZX TS_DATA 1.35357666
FLOOR TS_DATA 108.474243
SHENBAO TS_DATA .002502441
IBMS TS_DATA 1.8303833
FCCH TS_DATA .26361084
PUCHA TS_DATA .19140625
FDCYS TS_DATA 7.56640625
RIS_ACCESS TS_DATA 1.08044434
17 rows selected.
大表
SQL> select * from (
2 select owner, segment_name, segment_type, tablespace_name, bytes/1024/1024/1024 "G"
3 from dba_segments where segment_type = 'TABLE' and tablespace_name = 'TS_DATA' order by bytes desc)
4 where rownum <= 10;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME G
------------------- ------------------------------------ ------------------ ------------------- ----------
TMC FDC_INFO_QRY_LOG_2020 TABLE TS_DATA 144.490234
TMC TMC_LOG TABLE TS_DATA 86.6728516
TMC BIN$tvmjhUBAC37gVAALXeBfRg==$0 TABLE TS_DATA 50.9677734
TMC BIN$tvSoznk+ICjgVAALXeBfRg==$0 TABLE TS_DATA 30.59375
TMC REMC_WS_LOG_REQUEST TABLE TS_DATA 9.87109375
FDCYS SYS_20191171126389483_TEMP TABLE TS_DATA 3.4375
BWPLATFORM SOA_SYSTEM_LOG TABLE TS_DATA 3.125
FDCYS TRADE_RECORD TABLE TS_DATA 2.90625
TMC JG_TBL_FDC_EST_REGISTE TABLE TS_DATA 2.87890625
TMC MLOG$_FDC_TMC_CONTRACT TABLE TS_DATA 1.44433594
大索引
SQL> select * from (
2 select owner, segment_name, segment_type, tablespace_name, bytes/1024/1024/1024 "G"
3 from dba_segments where segment_type = 'INDEX' and tablespace_name = 'TS_DATA' order by bytes desc)
4 where rownum <= 10;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME G
----------------- ---------------------------- ------------------ ------------------ ----------
TMC TMC_LOG_PK INDEX TS_DATA 13.03125
BWPLATFORM SOA_SYSTEM_LOG INDEX TS_DATA .433837891
TMC INDEX_USER INDEX TS_DATA .419921875
TMC INDEX_ORGAN INDEX TS_DATA .390625
BWPLATFORM PK_PLAT_USER_LOGIN_LOG INDEX TS_DATA .267150879
TMC SEI_IX_NAME INDEX TS_DATA .216796875
TMC FTVC_IX_EXPIRE_DATE INDEX TS_DATA .1953125
FDCYS I_TR_YWBJSJ INDEX TS_DATA .1953125
BWPLATFORM SYS_C0066879 INDEX TS_DATA .1484375
TMC FTVC_UK_CERT_NO_FO_ID INDEX TS_DATA .1328125
10 rows selected.
SQL> select tablespace_name, block_size,contents, extent_management, segment_space_management, allocation_type, segment_space_management from dba_tablespaces where tablespace_name='TS_DATA';
TABLESPACE_NAME BLOCK_SIZE CONTENTS EXTENT_MAN SEGMEN ALLOCATIO SEGMEN
------------------------------ ---------- --------- ---------- ------ --------- ------
TS_DATA 8192 PERMANENT LOCAL AUTO SYSTEM AUTO
SQL> col file_name for a50
SQL> select file_name, tablespace_name, bytes/1024/1024/1024 "used G", maxbytes/1024/1024/1024 "total G" from dba_data_files where tablespace_name = 'TS_DATA' order by bytes;
FILE_NAME TABLESPACE_NAME used G total G
-------------------------------------------------- ------------------------------ ---------- ----------
/Oradata/2nd_fdc/TS_DATA74.dbf TS_DATA 5 0
/Oradata/2nd_fdc/TS_DATA76.dbf TS_DATA 5 0
/Oradata/2nd_fdc/TS_DATA75.dbf TS_DATA 5 0
/Oradata/2nd_fdc/TS_DATA38.dbf TS_DATA 19.53125 0
/Oradata/2nd_fdc/TS_DATA39.dbf TS_DATA 19.53125 0
/Oradata/2nd_fdc/TS_DATA40.dbf TS_DATA 19.53125 0
/Oradata/2nd_fdc/TS_DATA41.dbf TS_DATA 19.53125 0
/Oradata/2nd_fdc/TS_DATA42.dbf TS_DATA 19.53125 0
/Oradata/2nd_fdc/TS_DATA65.dbf TS_DATA 19.53125 0
/Oradata/2nd_fdc/TS_DATA68.dbf TS_DATA 29.2773438 29.296875
/Oradata/2nd_fdc/TS_DATA69.dbf TS_DATA 29.2773438 29.296875
/Oradata/2nd_fdc/TS_DATA03.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA04.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA05.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA06.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA07.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA08.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA09.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA10.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA15.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA16.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA17.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA20.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA21.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA22.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA23.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA24.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA25.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA26.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA28.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA29.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA30.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA31.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA32.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA33.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA34.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA35.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA36.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA43.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA44.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA45.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA46.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA47.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA48.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA49.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA50.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA51.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA52.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA53.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA54.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA55.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA56.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA57.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA58.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA59.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA60.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA61.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA62.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA63.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA64.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA66.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA67.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA02.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA37.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA01.dbf TS_DATA 29.296875 0
/Oradata/2nd_fdc/TS_DATA71.dbf TS_DATA 30 0
/Oradata/2nd_fdc/TS_DATA72.dbf TS_DATA 30 0
/Oradata/2nd_fdc/TS_DATA73.dbf TS_DATA 30 0
/Oradata/2nd_fdc/TS_DATA70.dbf TS_DATA 30 0
/Oradata/2nd_fdc/TS_DATA27.dbf TS_DATA 31.9550781 31.9999847
/Oradata/2nd_fdc/TS_DATA12.dbf TS_DATA 31.9921875 31.9999847
/Oradata/2nd_fdc/TS_DATA13.dbf TS_DATA 31.9921875 31.9999847
/Oradata/2nd_fdc/TS_DATA18.dbf TS_DATA 31.9921875 31.9999847
/Oradata/2nd_fdc/TS_DATA19.dbf TS_DATA 31.9921875 31.9999847
/Oradata/2nd_fdc/TS_DATA11.dbf TS_DATA 31.9921875 31.9999847
/Oradata/2nd_fdc/TS_DATA14.dbf TS_DATA 31.9921875 31.9999847
76 rows selected.
SQL> select max(block_id)*8/1024/1024 "G size" from dba_extents where tablespace_name='TS_DATA';
G size
----------
31.9920731
-- 檢查分割槽索引（包括分割槽表的分割槽本地索引，分割槽表的分割槽全域性索引），如果存在重建，並修改並行度
select 'alter index '||a.index_owner||'.'||a.index_name||' rebuild partition '||a.partition_name ||' parallel 10;' from
dba_ind_partitions a
where a.status='UNUSABLE';
select 'alter index '||a.index_owner||'.'||a.index_name||' parallel 1;' from
dba_ind_partitions a
where a.status='UNUSABLE’;
-- 檢查普通索引(包括普通表的索引，分割槽表的普通全域性索引)，如果存在重建，並修改並行度
select 'alter index '||a.owner||'.'||a.index_name||' rebuild parallel 10;' from
dba_indexes a
where a.status='UNUSABLE';
select 'alter index '||a.owner||'.'||a.index_name||' parallel 1;' from
dba_indexes a
where a.status='UNUSABLE’;
# 比較表的行數和表的大小關係。如果行數為0，而表的當前佔用大小減去初始化時的大小（INITIAL_EXTENT）後依然很大，那麼說明該表有高水位。 ===== 為了保證結果準確，建議先對錶進行統計資訊收集。
# 檢視TMC使用者高水位
SQL> SELECT D.OWNER,
2 ROUND(D.NUM_ROWS / D.BLOCKS, 2),
3 D.NUM_ROWS,
4 D.BLOCKS,
5 D.TABLE_NAME,
6 ROUND((d.BLOCKS*8-D.INITIAL_EXTENT/1024)/1024) t_size
7 FROM DBA_TABLES D
8 WHERE D.BLOCKS > 10
9 AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5
10 AND d.OWNER = 'TMC' ;
OWNER ROUND(D.NUM_ROWS/D.BLOCKS,2) NUM_ROWS BLOCKS TABLE_NAME T_SIZE
-------- ---------------------------- ---------- ---------- ------------------------------ ----------
TMC 2.6 2597 1000 EX_ZQ_MIDDLE_ACCESS 8
TMC 2.31 30 13 EX_ZW_SRV_CONFIG 0
TMC 1.1 66 60 FDC_ROLES_CATALOG -1
TMC .35 21 60 FDC_ORGANTYPE_SUBSYSTEM -1
TMC .1 6 58 FDC_USER_DEV_UPLOAD 0
TMC 3.05 1149260 377166 JG_TBL_FDC_EST_REGISTE 2947
TMC 0 0 1522 JG_TBL_JSYDGH 12
TMC 0 0 2410 JG_TBL_LAND_CONTRACT 19
TMC 4.83 713497 147670 1154
TMC 1.94 3646 1882 SIREA_REPORT_3 15
TMC 2.56 625 244 DZZZ_FDC_YS_PREPROJECT 2
TMC .22 9703 44390 FDC_ORGAN_REPORT_DEV_USER 347
TMC 0 0 13 SYS_20185311110401376_TEMP 0
TMC 1.57 1902326 1210229 REMC_WS_LOG_REQUEST 9455
# 檢視錶上邊的索引
SQL> select index_name, status from dba_indexes where owner = 'TMC' and table_name = 'JG_TBL_FDC_EST_REGISTE';
INDEX_NAME STATUS
------------------------------ --------
EST_REGISTE_PARCEL_NO VALID
EST_REGISTE_PROJ_NO VALID
SQL> select index_name, status from dba_indexes where owner = 'TMC' and table_name = 'FDC_YS_CONTRACT_APPENDIX';
INDEX_NAME STATUS
------------------------------ --------
SYS_IL0000060570C00006$$ VALID
SYS_IL0000060570C00020$$ VALID
FYCA_IX_FYC_ID VALID
FYCA_IX_YS_FYB_ID VALID
SYS_IL0000060570C00026$$ VALID
FYCA_PK VALID
6 rows selected.
SQL> select index_name, status from dba_indexes where owner = 'TMC' and table_name = 'REMC_WS_LOG_REQUEST';
INDEX_NAME STATUS
------------------------------ --------
SYS_IL0000142741C00026$$ VALID
SYS_IL0000142741C00027$$ VALID
SYS_IL0000142741C00028$$ VALID
# 檢視FDCYS使用者高水位線
SELECT D.OWNER,
ROUND(D.NUM_ROWS / D.BLOCKS, 2),
D.NUM_ROWS,
D.BLOCKS,
D.TABLE_NAME,
ROUND((d.BLOCKS*8-D.INITIAL_EXTENT/1024)/1024) t_size
FROM DBA_TABLES D
WHERE D.BLOCKS > 10
AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5
AND d.OWNER = 'FDCYS' ;
OWNER ROUND(D.NUM_ROWS/D.BLOCKS,2) NUM_ROWS BLOCKS TABLE_NAME T_SIZE
------------------------------ ---------------------------- ---------- ---------- ------------------------------ ----------
FDCYS 0 0 449636 SYS_20191171126389483_TEMP 3513
# 檢視錶上的索引
SQL> select index_name,status from dba_indexes where owner = 'FDCYS' and table_name = 'SYS_20191171126389483_TEMP';
INDEX_NAME STATUS
------------------------------ --------
SYS_C0060562 VALID
# TMC 使用者收縮高水位線
alter table TMC.JG_TBL_FDC_EST_REGISTE enable row movement;
alter table TMC.JG_TBL_FDC_EST_REGISTE shrink space;
alter index TMC.EST_REGISTE_PARCEL_NO rebuild;
alter index TMC.EST_REGISTE_PROJ_NO rebuild;
alter table TMC.JG_TBL_FDC_EST_REGISTE disable row movement;
alter table TMC.FDC_YS_CONTRACT_APPENDIX enable row movement;
alter table TMC.FDC_YS_CONTRACT_APPENDIX shrink space;
alter index TMC.FYCA_IX_FYC_ID rebuild parallel 4 nologging;
alter index TMC.FYCA_IX_YS_FYB_ID rebuild parallel 4 nologging;
alter index TMC.FYCA_PK rebuild parallel 4 nologging;
alter index TMC.FYCA_IX_FYC_ID parallel 1 ;
alter index TMC.FYCA_IX_YS_FYB_ID parallel 1 ;
alter index TMC.FYCA_PK parallel 1 ;
alter index TMC.FDC_YS_CONTRACT_APPENDIX disable row movement;
alter table TMC.REMC_WS_LOG_REQUEST enable row movement;
alter table TMC. REMC_WS_LOG_REQUEST shrink space;
alter table TMC.REMC_WS_LOG_REQUEST disable row movement;
# FDCYS 使用者收縮高水位線
alter table FDCYS.SYS_20191171126389483_TEMP enable row movement;
alter table FDCYS.SYS_20191171126389483_TEMP shrink space;
alter index SYS_C0060562 rebuild;
alter table FDCYS.SYS_20191171126389483_TEMP disable row movement;
exec dbms_stats.gather_table_stats('TMC','JG_TBL_FDC_EST_REGISTE',cascade=>true);
exec dbms_stats.gather_table_stats('TMC','FDC_YS_CONTRACT_APPENDIX',cascade=>true);
exec dbms_stats.gather_table_stats('TMC','REMC_WS_LOG_REQUEST',cascade=>true);
exec dbms_stats.gather_table_stats('FDCYS','SYS_20191171126389483_TEMP',cascade=>true);
如果是大表，建議使用如下語句進行統計資訊收集
說明： 需要替換使用者名稱、表名、及分割槽名 （如果是分割槽表）
如果要單獨分析表的某個分割槽，將 --granularity 和 -- partname=>'p200902', 這句註釋去掉，並替換分割槽名
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'NETFORCE',
tabname=>'TBL_PROCESS_DRAFT',
--granularity => 'PARTITION',
--partname=>'POPERATIONPROCESS0102',
estimate_percent=>1,
method_opt=>'FOR ALL COLUMNS SIZE 1',
no_invalidate=>false,
cascade=>true,
degree => 10);
end ;
/
資料檔案
# 檢視最大可resize的資料檔案大小
col name for a50
col resizecmd for a90
select a.file#,
a.name,
a.bytes / 1024 / 1024 CurrentMB,
ceil(HWM * a.block_size / 1024 / 1024) Resizeto,
(a.bytes - HWM * a.block_size) / 1024 / 1024 releaseMB,
'alter database datafile ''' || a.name || ''' resize ' ||
ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCmd
from v$datafile a,
(select file_id, max(block_id + blocks - 1) HWM
from dba_extents
where tablespace_name = 'TS_DATA'
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM * a.block_size) > 0
order by 5;
FILE# NAME CURRENTMB RESIZETO RELEASEMB RESIZECMD
----- -------------------------------- ---------- ---------- ---------- -------------------------------------------------------------------------
7 /Oradata/2nd_fdc/TS_DATA02.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA02.dbf' resize 30000M;
9 /Oradata/2nd_fdc/TS_DATA04.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA04.dbf' resize 30000M;
6 /Oradata/2nd_fdc/TS_DATA01.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA01.dbf' resize 30000M;
41 /Oradata/2nd_fdc/TS_DATA29.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA29.dbf' resize 30000M;
12 /Oradata/2nd_fdc/TS_DATA07.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA07.dbf' resize 30000M;
61 /Oradata/2nd_fdc/TS_DATA45.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA45.dbf' resize 30000M;
26 /Oradata/2nd_fdc/TS_DATA17.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA17.dbf' resize 30000M;
31 /Oradata/2nd_fdc/TS_DATA22.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA22.dbf' resize 30000M;
10 /Oradata/2nd_fdc/TS_DATA05.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA05.dbf' resize 30000M;
19 /Oradata/2nd_fdc/TS_DATA12.dbf 32760 32760 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA12.dbf' resize 32760M;
14 /Oradata/2nd_fdc/TS_DATA09.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA09.dbf' resize 30000M;
13 /Oradata/2nd_fdc/TS_DATA08.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA08.dbf' resize 30000M;
11 /Oradata/2nd_fdc/TS_DATA06.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA06.dbf' resize 30000M;
83 /Oradata/2nd_fdc/TS_DATA65.dbf 20000 20000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA65.dbf' resize 20000M;
20 /Oradata/2nd_fdc/TS_DATA13.dbf 32760 32760 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA13.dbf' resize 32760M;
18 /Oradata/2nd_fdc/TS_DATA11.dbf 32760 32760 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA11.dbf' resize 32760M;
24 /Oradata/2nd_fdc/TS_DATA15.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA15.dbf' resize 30000M;
15 /Oradata/2nd_fdc/TS_DATA10.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA10.dbf' resize 30000M;
49 /Oradata/2nd_fdc/TS_DATA36.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA36.dbf' resize 30000M;
39 /Oradata/2nd_fdc/TS_DATA27.dbf 32722 32722 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA27.dbf' resize 32722M;
25 /Oradata/2nd_fdc/TS_DATA16.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA16.dbf' resize 30000M;
8 /Oradata/2nd_fdc/TS_DATA03.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA03.dbf' resize 30000M;
30 /Oradata/2nd_fdc/TS_DATA21.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA21.dbf' resize 30000M;
45 /Oradata/2nd_fdc/TS_DATA32.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA32.dbf' resize 30000M;
29 /Oradata/2nd_fdc/TS_DATA20.dbf 30000 30000 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA20.dbf' resize 30000M;
27 /Oradata/2nd_fdc/TS_DATA18.dbf 32760 32760 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA18.dbf' resize 32760M;
28 /Oradata/2nd_fdc/TS_DATA19.dbf 32760 32760 .0625 alter database datafile '/Oradata/2nd_fdc/TS_DATA19.dbf' resize 32760M;
35 /Oradata/2nd_fdc/TS_DATA23.dbf 30000 30000 .125 alter database datafile '/Oradata/2nd_fdc/TS_DATA23.dbf' resize 30000M;
76 /Oradata/2nd_fdc/TS_DATA60.dbf 30000 30000 .125 alter database datafile '/Oradata/2nd_fdc/TS_DATA60.dbf' resize 30000M;
74 /Oradata/2nd_fdc/TS_DATA58.dbf 30000 30000 .125 alter database datafile '/Oradata/2nd_fdc/TS_DATA58.dbf' resize 30000M;
21 /Oradata/2nd_fdc/TS_DATA14.dbf 32760 32760 .125 alter database datafile '/Oradata/2nd_fdc/TS_DATA14.dbf' resize 32760M;
50 /Oradata/2nd_fdc/TS_DATA37.dbf 30000 30000 .1875 alter database datafile '/Oradata/2nd_fdc/TS_DATA37.dbf' resize 30000M;
40 /Oradata/2nd_fdc/TS_DATA28.dbf 30000 30000 .1875 alter database datafile '/Oradata/2nd_fdc/TS_DATA28.dbf' resize 30000M;
44 /Oradata/2nd_fdc/TS_DATA31.dbf 30000 30000 .3125 alter database datafile '/Oradata/2nd_fdc/TS_DATA31.dbf' resize 30000M;
58 /Oradata/2nd_fdc/TS_DATA43.dbf 30000 30000 .3125 alter database datafile '/Oradata/2nd_fdc/TS_DATA43.dbf' resize 30000M;
43 /Oradata/2nd_fdc/TS_DATA30.dbf 30000 30000 .3125 alter database datafile '/Oradata/2nd_fdc/TS_DATA30.dbf' resize 30000M;
67 /Oradata/2nd_fdc/TS_DATA51.dbf 30000 30000 .3125 alter database datafile '/Oradata/2nd_fdc/TS_DATA51.dbf' resize 30000M;
82 /Oradata/2nd_fdc/TS_DATA64.dbf 30000 30000 .5625 alter database datafile '/Oradata/2nd_fdc/TS_DATA64.dbf' resize 30000M;
56 /Oradata/2nd_fdc/TS_DATA42.dbf 20000 20000 .625 alter database datafile '/Oradata/2nd_fdc/TS_DATA42.dbf' resize 20000M;
85 /Oradata/2nd_fdc/TS_DATA67.dbf 30000 29973 27.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA67.dbf' resize 29973M;
84 /Oradata/2nd_fdc/TS_DATA66.dbf 30000 29645 355.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA66.dbf' resize 29645M;
102 /Oradata/2nd_fdc/TS_DATA74.dbf 5120 3580 1540.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA74.dbf' resize 3580M;
90 /Oradata/2nd_fdc/TS_DATA70.dbf 30720 29121 1599.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA70.dbf' resize 29121M;
103 /Oradata/2nd_fdc/TS_DATA75.dbf 5120 3422 1698.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA75.dbf' resize 3422M;
91 /Oradata/2nd_fdc/TS_DATA71.dbf 30720 28993 1727.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA71.dbf' resize 28993M;
104 /Oradata/2nd_fdc/TS_DATA76.dbf 5120 3295 1825.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA76.dbf' resize 3295M;
88 /Oradata/2nd_fdc/TS_DATA69.dbf 29980 22035 7945.9375 alter database datafile '/Oradata/2nd_fdc/TS_DATA69.dbf' resize 22035M;
86 /Oradata/2nd_fdc/TS_DATA68.dbf 29980 21958 8022.9375 alter database datafile '/Oradata/2nd_fdc/TS_DATA68.dbf' resize 21958M;
92 /Oradata/2nd_fdc/TS_DATA72.dbf 30720 21246 9474.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA72.dbf' resize 21246M;
93 /Oradata/2nd_fdc/TS_DATA73.dbf 30720 20626 10094.875 alter database datafile '/Oradata/2nd_fdc/TS_DATA73.dbf' resize 20626M;
本次表空間高水位線收縮動作共釋放 50G 左右空間。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23825935/viewspace-2932729/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle資料庫管理——表資料庫高水位及shrink操作Oracle資料庫
- ORACLE 高水位線（HWM）Oracle
- oracle高水位線處理Oracle
- 一、oracle 高水位線詳解Oracle
- Oracle資料庫高水位釋放——LOB欄位空間釋放Oracle資料庫
- Oracle案例10——HWM（高水位線）效能優化Oracle優化
- oracle 高水位分析處理Oracle
- Oracle 高水位查詢和處理方法彙總Oracle
- Oracle RMAN 連線資料庫認證方法Oracle資料庫
- oracle資料庫CPU特別高的解決方法Oracle資料庫
- Python 連線 Oracle資料庫PythonOracle資料庫
- 使用instantclient連線oracle資料庫clientOracle資料庫
- [Navicate]Navicate連線Oracle資料庫Oracle資料庫
- Kettle8.2連線Oracle資料庫失敗解決方法Oracle資料庫
- ORACLE 如何診斷高水位爭用（enq: HW – contention）OracleENQ
- Oracle dos連線資料庫基本操作Oracle資料庫
- ORACLE 配置連線遠端資料庫Oracle資料庫
- 【AWR】Oracle資料庫建立awr基線Oracle資料庫
- 掌握Oracle資料庫效能調優方法Oracle資料庫
- [20181224]使用odbc連線oracle資料庫.txtOracle資料庫
- Oracle資料庫 ASM磁碟線上擴容Oracle資料庫ASM
- oracle資料庫連續相同資料的統計方法Oracle資料庫
- 瀚高資料庫日誌挖掘方法資料庫
- 【BUILD_ORACLE】在Oracle cloud資料庫“插拔”PDB的方法UIOracleCloud資料庫
- 【資料庫資料恢復】Oracle資料庫ASM磁碟組掉線如何恢復資料？資料庫資料恢復OracleASM
- 【資料庫資料恢復】ASM磁碟組掉線的Oracle資料庫資料恢復案例資料庫資料恢復ASMOracle
- python使用cx_Oracle連線oracle資料庫獲取常用資訊PythonOracle資料庫
- jdbc連線oracle rac資料庫的寫法JDBCOracle資料庫
- PL/SQL Developer連線遠端Oracle資料庫SQLDeveloperOracle資料庫
- PowerDesigner實現Oracle資料庫連線生成模型Oracle資料庫模型
- 聊聊flink水位線
- Oracle資料庫中convert()函式，在瀚高資料庫中如何替換使用？Oracle資料庫函式
- 【PG資料庫】PG資料庫的安裝及連線方法資料庫
- PHP中的資料庫連線方法PHP資料庫
- 「Oracle」Oracle 資料庫安裝Oracle資料庫
- Oracle資料庫升級或資料遷移的方法探討Oracle資料庫
- 非常實用的Oracle資料庫資料恢復方法案例Oracle資料庫資料恢復
- Oracle資料庫配置Oracle資料庫