SQL script gen lệnh tác động partition theo tên bảng, high_value
Wiki » 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

oraclepartitionsqltruncate