Partition DDL

Partitions


select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||'  PARTITION '||PARTITION_NAME||' uncompress; '  from all_tab_partitions WHERE table_name='OR_LINE_FACTS';



select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' uncompress; '  from all_tab_partitions WHERE table_name='CSW_OR_LINE_FACTS';


select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' compress; '  from all_tab_partitions WHERE table_name in ('PO_RECEIVING_FACTS','PRIORITY_FACTS','SALES_FACTS','SALES_PROD_SUPP_ORG_MONTH_AGG','SALE_IBT_INVOICE_FACTS','SLS_PRD_SP_ORG_MO_AGG');


select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' compress; '  from all_tab_partitions WHERE table_name in ('W_AR_BALANCE_CUSTOMER_F','W_KRONOS_HOURS_F','W_OMNI_SALES_ORDER_LINE_F','W_RP_MED_ASSIGNMENT_DETAILS_F','W_RP_MED_ASSIGNMENT_F');


select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' compress; '  from all_tab_partitions WHERE table_name in ('AR_BALANCE_STG_HIST','CISP_ALLOCATED_REV_COSTS_HIST','CISP_ALLOCATED_REV_COSTS_HIST','DISCRETE_HIST','INSBRN_HIST','INSBRN_NK_HIST','INSBRN_NK_HIST','KVI_SUMMARY_HIST','MED_KVI_SUMMARY','OHTLNE_HIST','OHTLNE_HIST','ORSLNE_HIST','ORSLNE_HIST','WFCTOTAL_HIST');


select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' compress; '  from all_tab_partitions WHERE table_name in ('SALES_F','ZIU_DAILY_PLANNED_ORDER_FACTS','Z_INTPART_TAB','Z_INTPART_TAB','Z_TEST');

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' compress; '  from all_tab_partitions WHERE table_name in ('SALES_FACTS_NEW');

SUB partitions
____________

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move SUBPARTITION '||SUBPARTITION_NAME||' compress; '  from all_tab_subpartitions WHERE table_name in ('KVI_SUMMARY_WBREAKS_FINAL_STG','KVI_SUMMARY_WBREAKS_STG','OMSTG.MED_KVI_SUMMARY','OMSTG.KVI_SUMMARY_WBREAKS_STG');


Advance compression query
________________________

 select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' ONLINE ROW STORE COM
PRESS ADVANCED NOLOGGING PARALLEL 4; '  from all_tab_partitions WHERE table_name in ('W_AR_BALANCE_CUSTOMER_F','W_KRONOS_HOURS_F','W_OMNI_SALES_ORDER_LINE_F');