SQL script gen lệnh tác động partition theo tên bảng, high_value
You asked:
Chào Nam, mình muốn truncate dữ liệu của một số bảng partition.
- Đầu vào: tên bảng & thời gian xóa dữ liệu.
- Đầu ra: một danh sách câu lệnh truncate.
Bạn có gợi ý nào cho mình không?
And I said:
Có 2 gợi ý cho bạn:
- Gợi ý 1: sử dụng excel, viết 1 lệnh truncate rồi kéo (from PhucVV)
- Gợi ý 2: sử dụng sql
SELECT 'alter table ' || table_name || ' truncate partition ' || partition_name || ';'
from (
SELECT table_name, partition_name,
substr(high_value, instr(high_value, '''') + 2, 19) date_str,
substr(high_value, instr(high_value, '''', 1, 3) + 2, 21) date_fmt
from (
WITH xml AS (
SELECT dbms_xmlgen.getxmltype('select table_name, partition_name, high_value from user_tab_partitions where table_name in ' ||
'(' ||
'''DAS_SUBS_VISIT_LOC_D'',' ||
'''DAS_SRV_CDR'',' ||
'''DAS_VASP_ACTIVE'',' ||
'''DAS_SUBS_CHARGE_LOC_D'',' ||
'''DAS_BC_FILE_SUBS'',' ||
'''DAS_VOUCHER'',' ||
'''DAS_SRV_SUBS_REG'',' ||
'''DAS_VASP_DEL'',' ||
'''DAS_FR_CDR'',' ||
'''DAS_VASP_REG'',' ||
'''DAS_SRV_SUBS_UNREG'')'
) as x
from dual
)
SELECT extractvalue(rws.object_value, '/ROW/TABLE_NAME') table_name,
extractvalue(rws.object_value, '/ROW/PARTITION_NAME') PARTITION_name,
extractValue(rws.object_value, '/ROW/HIGH_VALUE') high_value
FROM xml x,
TABLE(xmlsequence(EXTRACT(x.x, '/ROWSET/ROW'))) rws)
)
WHERE to_date (date_str, date_fmt) > DATE '2016-04-16'
AND to_date (date_str, date_fmt) <= DATE '2016-04-18' + 1
order by table_name, to_date (date_str, date_fmt) asc;
Ví dụ đầu ra:
alter table DAS_BC_FILE_SUBS truncate partition P20160416;
alter table DAS_BC_FILE_SUBS truncate partition P20160417;
alter table DAS_BC_FILE_SUBS truncate partition P20160418;
alter table DAS_FR_CDR truncate partition P20160416;
alter table DAS_FR_CDR truncate partition P20160417;
alter table DAS_FR_CDR truncate partition P20160418;
alter table DAS_SRV_CDR truncate partition P20160416;
alter table DAS_SRV_CDR truncate partition P20160417;
alter table DAS_SRV_CDR truncate partition P20160418;
alter table DAS_SRV_SUBS_REG truncate partition P20160416;
alter table DAS_SRV_SUBS_REG truncate partition P20160417;
alter table DAS_SRV_SUBS_REG truncate partition P20160418;
alter table DAS_SRV_SUBS_UNREG truncate partition P20160416;
alter table DAS_SRV_SUBS_UNREG truncate partition P20160417;
alter table DAS_SRV_SUBS_UNREG truncate partition P20160418;
alter table DAS_SUBS_CHARGE_LOC_D truncate partition P20160416;
alter table DAS_SUBS_CHARGE_LOC_D truncate partition P20160417;
alter table DAS_SUBS_CHARGE_LOC_D truncate partition P20160418;
alter table DAS_SUBS_VISIT_LOC_D truncate partition P20160416;
alter table DAS_SUBS_VISIT_LOC_D truncate partition P20160417;
alter table DAS_SUBS_VISIT_LOC_D truncate partition P20160418;
alter table DAS_VASP_ACTIVE truncate partition P20160416;
alter table DAS_VASP_ACTIVE truncate partition P20160417;
alter table DAS_VASP_ACTIVE truncate partition P20160418;
alter table DAS_VASP_DEL truncate partition P20160416;
alter table DAS_VASP_DEL truncate partition P20160417;
alter table DAS_VASP_DEL truncate partition P20160418;
alter table DAS_VOUCHER truncate partition P20160416;
alter table DAS_VOUCHER truncate partition P20160417;
alter table DAS_VOUCHER truncate partition P20160418;
Tham khảo https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9522731800346411985