概述
由於之前開發在數據庫設計時,將xml文件存放到數據庫上,幾年後這些xml數據已累計佔用了600多G,業務部門確認可以刪除後開始執行大表回收計劃。
一、環境確認
1、查看錶數據大小
<code>select
d.owner,d.segment_name,d.partition_name,d.segment_type,d.tablespace_name,d.bytes/1024
/1024
/1024
GBfrom
dba_segments dorder
by
d.bytesdesc
;select
owner, table_name, column_name, segment_name, index_namefrom
dba_lobswhere
segment_name ='I_TRANSACTION_XML_BLOB'
/<code>
2、查看錶數據量
目前I_TRANSACTION表總數為11860346條,I_TRANSACTION表2020-01-01後數據量為1522162
<code>select
count
(*)from
I_TRANSACTION;select
count
(*)from
I_TRANSACTIONwhere
insert_date>to_date
('2020-01-01'
,'yyyy-mm-dd'
);/<code>
3、查看錶定義、主鍵、外鍵、觸發器(備份)
表定義、主鍵、索引從PLSQL獲取即可
<code>SELECT
*FROM
DBA_TRIGGERSWHERE
TABLE_NAME='I_TRANSACTION'
;SELECT
DBMS_METADATA.GET_DDL('TRIGGER'
,'觸發器名字'
,'GLOGOWNER'
)FROM
DUAL;select
u.owner, u.table_name,'alter table '
|| table_name ||' drop constraint '
||constraint_name ||';'
,u.statusfrom
user_constraints uwhere
constraint_type ='R'
and
table_name ='I_TRANSACTION'
;select
a.owner"zhujian_owner"
,a.table_name"zhujian_tab"
,b.column_name"zhujian_col"
,C.OWNER"waijian_owner"
,c.table_name"waijian_tab"
,d.column_name"waijian_col"
,C.constraint_name,'alter table '
|| C.table_name ||' drop constraint '
|| C.constraint_name ||';'
"drop constraint"
from
user_constraints aleft
join
user_cons_columns bon
a.constraint_name = b.constraint_nameleft
join
user_constraints CON
C.R_CONSTRAINT_NAME = a.constraint_nameleft
join
user_cons_columns don
c.constraint_name = d.constraint_namewhere
a.constraint_type ='P'
and
a.table_name ='I_TRANSACTION'
order
by
a.table_name;/<code>
二、停機備份
計劃保留2020年4月15日後的數據,之前的數據不做保留。
1、關閉應用系統以及接口平臺
手工關閉後,確認關閉後在執行以下備份表工作。
2、確認rman備份正常
確保前一天rman正常備份
<code>SELECT
*FROM
V$RMAN_STATUSWHERE
START_TIME >=TO_DATE
(&START_TIME,'YYYY-MM-DD HH24:MI:SS'
)AND
END_TIME <=TO_DATE
(&END_TIME ,'YYYY-MM-DD HH24:MI:SS'
)AND
OPERATION ='BACKUP'
AND
STATUS
='COMPLETED'
SELECT
A.RECID"BACKUP SET"
, A.SET_STAMP,DECODE
(B.INCREMENTAL_LEVEL,''
,DECODE
(BACKUP_TYPE,'L'
,'Archivelog'
,'Full'
),1
,'Incr-1級'
,0
,'Incr-0級'
, B.INCREMENTAL_LEVEL)"Type LV"
, B.CONTROLFILE_INCLUDED"包含CTL"
,DECODE
(A.STATUS,'A'
,'AVAILABLE'
,'D'
,'DELETED'
,'X'
,'EXPIRED'
,'ERROR'
)"STATUS"
, A.DEVICE_TYPE"Device Type"
, A.START_TIME"Start Time"
, A.COMPLETION_TIME"Completion Time"
, A.ELAPSED_SECONDS"Elapsed Seconds"
, A.BYTES/1024
/1024
/1024
"Size(G)"
, A.COMPRESSED, A.TAG"Tag"
, A.HANDLE"Path"
FROM
GV$BACKUP_PIECE A, GV$BACKUP_SET BWHERE
A.SET_STAMP = B.SET_STAMPAND
A.DELETED ='NO'
RDERBY
A.COMPLETION_TIMEDESC
;restore
database
validate
;/<code>
3、備份大表最近半個月數據並檢查數據
因為耗時過久這裡不考慮expdp備份,提前在正式環境模擬備份表測試,保留4月15號後表的數據,耗時5380s,DATA表空間消耗17G。
備份時同時觀察undo表空間、數據表空間以及告警日誌。
<code>create
table
I_TRANSACTION_bak200501as
select
*from
I_TRANSACTIONwhere
insert_date>to_date
('2020-04-15 00:00:00'
,'yyyy-mm-dd hh24:mi:ss'
);select
count
(*)from
I_TRANSACTION_bak200501;select
*from
I_TRANSACTIONorder
by
insert_datedesc
;/<code>
三、truncate表
因為delete一個600G的表幾天是做不了的,且產生歸檔日誌過大,很容易影響數據庫性能,所以採取truncate方案。
刪除時同時觀察undo表空間、數據表空間以及告警日誌。
1、truncate表
truncate是一個DDL命令,這樣一旦執行,事務將無法回滾。將更新數據字典,將數據字典裡相關的數據予以刪除,然後將表的數據塊全部釋放,並且將表的HWM下降到最低,但是,在我們處理很大的表的時候,如果處理的表佔巨大的空間,在truncate去釋放表的數據塊的消耗是巨大的,在這個過程中對處理的表是不能訪問。
為了儘量減小truncate大表是對系統的影響,加上 reuse storage, 這樣通知處理表的時候,在更新完數據字典以後,並不馬上釋放所有的數據塊,HWM也進行更新,下降到低水位,然後用 deallocate unused keep xxM在系統比較空閒的時候,來釋放數據塊。
在執行keep 0mb 之前,其他用戶已經向表裡插入了數據,則不會真的把表所有數據塊釋放,只是釋放沒有用的數據塊而已。
<code>truncate
table
I_TRANSACTIONreuse
storage
; /<code>
2、確認表數據量、主鍵、外鍵是否有影響
<code>SELECT
*FROM
DBA_TRIGGERSWHERE
TABLE_NAME='I_TRANSACTION'
;SELECT
DBMS_METADATA.GET_DDL('TRIGGER'
,'觸發器名字'
,'GLOGOWNER'
)FROM
DUAL;select
u.owner, u.table_name,'alter table '
|| table_name ||' drop constraint '
||constraint_name ||';'
,u.statusfrom
user_constraints uwhere
constraint_type ='R'
and
table_name ='I_TRANSACTION'
;select
a.owner"zhujian_owner"
,a.table_name"zhujian_tab"
,b.column_name"zhujian_col"
,C.OWNER"waijian_owner"
,c.table_name"waijian_tab"
,d.column_name"waijian_col"
,C.constraint_name,'alter table '
|| C.table_name ||' drop constraint '
|| C.constraint_name ||';'
"drop constraint"
from
user_constraints aleft
join
user_cons_columns bon
a.constraint_name = b.constraint_nameleft
join
user_constraints CON
C.R_CONSTRAINT_NAME = a.constraint_nameleft
join
user_cons_columns don
c.constraint_name = d.constraint_namewhere
a.constraint_type ='P'
and
a.table_name ='I_TRANSACTION'
order
by
a.table_name;/<code>
3、查看錶空間大小
可以看到表空間已降下來了
4、轉移表空間
<code>alter
table
I_TRANSACTION_bak200501move
tablespace
LOB3;/<code>
四、回收高水位並重新收集統計信息(以下秒執行)
注意:
alter table I_TRANSACTION shrink space compact; --壓縮階段 (oracle建議在高峰時間壓縮)
alter table I_TRANSACTION shrink space; --收縮階段(oracle建議在不忙的時候收縮,收縮會產生排他鎖,因此其他用戶不能對收縮的表經行任何操作)
alter table I_TRANSACTION shrink space cascade; --不僅收縮I_TRANSACTION 表的,還收縮I_TRANSACTION 相關表
<code>Alter
table
I_TRANSACTIONenable
row
movement
;alter
table
I_TRANSACTIONshrink
space
cascade
;ALTER
TABLE
I_TRANSACTIONMODIFY
LOB
(XML_BLOB) (SHRINK
SPACE
CASCADE
);analyze
table
I_TRANSACTIONcompute
statistics
;Alter
table
I_TRANSACTIONdisable
row
movement
;/<code>
覺得有用的朋友多幫忙轉發哦!後面會分享更多devops和DBA方面的內容,感興趣的朋友可以關注下~