Home » RDBMS Server » Performance Tuning » Help - dropping partitions(2 Merged) (Oracle, 10.2.0.4)
| Help - dropping partitions(2 Merged) [message #542871] |
Fri, 10 February 2012 10:48  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Hi,
The below partitoned table having 38 partitions and each partition have every day subpartitons. Now we want to drop the subpartitions that are older than 15 months.
we have 16 tables like this and need to do this activity in all these 16 tables.
I need your help to do this activity in a fastest way.
Note:
=====
it is a 3 node RAC database. We are facing "cursor pin S wait on X" and "kksfbc" wait event frequently.it mostly occur when we are having high load on the database. We are bouncing db if it occurs. For this separate plan is going on to move to 11g. we need to take this into our consideration while plan for dropping the partitions.
SQL> select table_owner,TABLE_NAME,PARTITION_NAME,SUBPARTITION_COUNT,TABLESPACE_NAME,LAST_ANALYZED,GLOBAL_STATS,num_rows from dba_tab_partitions
2 where table_name = 'EDX_RPT_SRVC_CHRG_DTL_FACT_X';
TABLE_O TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT TABLESPACE_NAME LAST_ANAL GLO NUM_ROWS
------- ------------------------------ -------------- ------------------ ------------------------------ --------- --- ----------
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP01 525 EDX_REPORT_CDRDATA_TS_01 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP02 524 EDX_REPORT_CDRDATA_TS_02 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP03 527 EDX_REPORT_CDRDATA_TS_03 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP04 525 EDX_REPORT_CDRDATA_TS_04 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP05 523 EDX_REPORT_CDRDATA_TS_05 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP06 529 EDX_REPORT_CDRDATA_TS_06 31-JAN-12 YES 2387
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP07 524 EDX_REPORT_CDRDATA_TS_07 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP08 527 EDX_REPORT_CDRDATA_TS_08 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP09 525 EDX_REPORT_CDRDATA_TS_09 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP10 486 EDX_REPORT_CDRDATA_TS_10 07-FEB-12 YES 811559
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP11 482 EDX_REPORT_CDRDATA_TS_11 07-FEB-12 YES 619502
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP12 485 EDX_REPORT_CDRDATA_TS_12 07-FEB-12 YES 656996
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP13 484 EDX_REPORT_CDRDATA_TS_13 07-FEB-12 YES 575693
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP14 485 EDX_REPORT_CDRDATA_TS_14 07-FEB-12 YES 727971
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP15 484 EDX_REPORT_CDRDATA_TS_15 07-FEB-12 YES 570569
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP16 484 EDX_REPORT_CDRDATA_TS_16 07-FEB-12 YES 593904
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP17 483 EDX_REPORT_CDRDATA_TS_17 07-FEB-12 YES 4792555
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP18 482 EDX_REPORT_CDRDATA_TS_18 07-FEB-12 YES 653706
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP19 507 EDX_REPORT_CDRDATA_TS_19 09-FEB-12 YES 5237260
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP20 533 EDX_REPORT_CDRDATA_TS_20 31-JAN-12 YES 5819440
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP30 557 EDX_REPORT_LARGEDATA_TS_01 31-JAN-12 YES 13627954
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP21 559 EDX_REPORT_LARGEDATA_TS_01 31-JAN-12 YES 5820874
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP31 557 EDX_REPORT_LARGEDATA_TS_02 31-JAN-12 YES 12635593
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP22 559 EDX_REPORT_LARGEDATA_TS_02 31-JAN-12 YES 5822645
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP32 558 EDX_REPORT_LARGEDATA_TS_03 31-JAN-12 YES 12210507
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP23 558 EDX_REPORT_LARGEDATA_TS_03 31-JAN-12 YES 5835137
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP33 583 EDX_REPORT_LARGEDATA_TS_04 31-JAN-12 YES 11379172
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP24 558 EDX_REPORT_LARGEDATA_TS_04 31-JAN-12 YES 5837251
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP34 579 EDX_REPORT_LARGEDATA_TS_05 31-JAN-12 YES 13101460
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP25 558 EDX_REPORT_LARGEDATA_TS_05 31-JAN-12 YES 5843077
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP35 582 EDX_REPORT_LARGEDATA_TS_06 31-JAN-12 YES 11975073
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP26 558 EDX_REPORT_LARGEDATA_TS_06 31-JAN-12 YES 5820046
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP36 581 EDX_REPORT_LARGEDATA_TS_07 31-JAN-12 YES 11719129
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP27 560 EDX_REPORT_LARGEDATA_TS_07 31-JAN-12 YES 5820107
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP37 580 EDX_REPORT_LARGEDATA_TS_08 31-JAN-12 YES 11134419
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP28 560 EDX_REPORT_LARGEDATA_TS_08 31-JAN-12 YES 5823127
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP38 581 EDX_REPORT_LARGEDATA_TS_09 31-JAN-12 YES 11859682
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP29 546 EDX_REPORT_LARGEDATA_TS_09 31-JAN-12 YES 1960627
38 rows selected.
There are 3 partitioned indexes on this tables. Detail is below.
INDEX_NAME COLUMN_NAME TABLE_NAME
------------------------------ ---------------------------------- ------------------------------
PK_SRVC_CHRG_DTL_FACT_X SRVC_CHRG_DTL_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
PK_SRVC_CHRG_DTL_FACT_X LOAD_DATE EDX_RPT_SRVC_CHRG_DTL_FACT_X
PK_SRVC_CHRG_DTL_FACT_X CUST_GROUP_ID EDX_RPT_SRVC_CHRG_DTL_FACT_X
NK_ERSCDFX_CHARGETYPEKEY_X CHARGE_TYPE_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
NK_ERSCDFX_CHARGETYPEKEY_X SERVICE_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
NK_ERSCDFX_CHARGETYPEKEY_X STATEMENT_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
NK_ERSCDFX_CHARGETYPEKEY_X LOAD_DATE EDX_RPT_SRVC_CHRG_DTL_FACT_X
NK_ERSCDFX_CHARGETYPEKEY_X CUST_GROUP_ID EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL CUST_GROUP_ID EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL SRVC_CHRG_DTL_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL FLEX_FIELD_CHR1 EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL SERVICE_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL STATEMENT_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL ACCOUNT_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL LOAD_DATE EDX_RPT_SRVC_CHRG_DTL_FACT_X
SQL> select INDEX_NAME,PARTITION_NAME,SUBPARTITION_COUNT,TABLESPACE_NAME,LAST_ANALYZED,GLOBAL_STATS from dba_ind_partitions
2 where INDEX_NAME = 'NK_ERSCDFX_CHARGETYPEKEY_X';
INDEX_NAME PARTITION_NAME SUBPARTITION_COUNT TABLESPACE_NAME LAST_ANAL GLO
------------------------------ -------------- ------------------ ------------------------------ --------- ---
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP30 557 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP31 557 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP32 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP33 583 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP35 582 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP37 580 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP38 581 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP28 560 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP29 546 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP16 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP14 485 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP08 527 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP01 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP19 507 EDX_REPORT_MEDIUM_IDX_ID 09-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP20 533 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP36 581 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP34 579 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP02 524 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP03 527 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP04 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP05 523 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP06 529 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP07 524 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP10 486 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP12 485 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP13 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP15 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP17 483 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP18 482 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP09 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP11 482 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP21 559 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP22 559 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP23 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP24 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP26 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP27 560 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP25 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
38 rows selected.
SQL> select INDEX_NAME,PARTITION_NAME,SUBPARTITION_COUNT,TABLESPACE_NAME,LAST_ANALYZED,GLOBAL_STATS from dba_ind_partitions
2 where INDEX_NAME = 'PK_SRVC_CHRG_DTL_FACT_X';
INDEX_NAME PARTITION_NAME SUBPARTITION_COUNT TABLESPACE_NAME LAST_ANAL GLO
------------------------------ -------------- ------------------ ------------------------------ --------- ---
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP31 557 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP32 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP33 583 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP34 579 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP35 582 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP36 581 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP37 580 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP21 559 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP20 533 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP19 507 EDX_REPORT_MEDIUM_IDX_ID 09-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP06 529 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP04 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP30 557 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP38 581 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP01 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP02 524 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP05 523 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP07 524 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP08 527 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP09 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP10 486 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP12 485 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP13 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP14 485 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP15 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP16 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP17 483 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP18 482 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP11 482 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP03 527 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP23 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP24 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP25 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP26 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP28 560 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP29 546 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP22 559 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP27 560 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
38 rows selected.
SQL> select INDEX_NAME,PARTITION_NAME,SUBPARTITION_COUNT,TABLESPACE_NAME,LAST_ANALYZED,GLOBAL_STATS from dba_ind_partitions
2 where INDEX_NAME = 'ERSCDF_RL';
INDEX_NAME PARTITION_NAME SUBPARTITION_COUNT TABLESPACE_NAME LAST_ANAL GLO
------------------------------ -------------- ------------------ ------------------------------ --------- ---
ERSCDF_RL P_CUSTGRP30 557 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP31 557 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP34 579 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP36 581 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP37 580 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP38 581 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP26 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP25 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP22 559 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP32 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP19 507 EDX_REPORT_MEDIUM_IDX_ID 09-FEB-12 YES
ERSCDF_RL P_CUSTGRP27 560 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP33 583 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP35 582 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP01 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP02 524 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP03 527 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP04 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP05 523 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP06 529 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP07 524 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP08 527 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP09 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP10 486 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP11 482 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP12 485 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP14 485 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP15 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP16 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP18 482 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP20 533 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP13 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP17 483 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP21 559 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP23 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP28 560 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP29 546 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP24 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
38 rows selected. [Updated on: Fri, 10 February 2012 11:04] Report message to a moderator |
|
|
|
|
|
| Help - dropping partitions [message #543234 is a reply to message #542871] |
Mon, 13 February 2012 10:03  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Hi,
The below partitoned table having 38 partitions and each partition have every day subpartitons. Now we want to drop the subpartitions that are older than 15 months.
we have 16 tables like this and need to do this activity in all these 16 tables.
I need your help to do this activity in a fastest way.
Note:
=====
it is a 3 node RAC database. We are facing "cursor pin S wait on X" and "kksfbc" wait event frequently.it mostly occur when we are having high load on the database. We are bouncing db if it occurs. For this separate plan is going on to move to 11g. we need to take this into our consideration while plan for dropping the partitions.
SQL> select table_owner,TABLE_NAME,PARTITION_NAME,SUBPARTITION_COUNT,TABLESPACE_NAME,LAST_ANALYZED,GLOBAL_STATS,num_rows from dba_tab_partitions
2 where table_name = 'EDX_RPT_SRVC_CHRG_DTL_FACT_X';
TABLE_O TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT TABLESPACE_NAME LAST_ANAL GLO NUM_ROWS
------- ------------------------------ -------------- ------------------ ------------------------------ --------- --- ----------
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP01 525 EDX_REPORT_CDRDATA_TS_01 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP02 524 EDX_REPORT_CDRDATA_TS_02 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP03 527 EDX_REPORT_CDRDATA_TS_03 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP04 525 EDX_REPORT_CDRDATA_TS_04 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP05 523 EDX_REPORT_CDRDATA_TS_05 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP06 529 EDX_REPORT_CDRDATA_TS_06 31-JAN-12 YES 2387
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP07 524 EDX_REPORT_CDRDATA_TS_07 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP08 527 EDX_REPORT_CDRDATA_TS_08 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP09 525 EDX_REPORT_CDRDATA_TS_09 31-JAN-12 YES 0
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP10 486 EDX_REPORT_CDRDATA_TS_10 07-FEB-12 YES 811559
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP11 482 EDX_REPORT_CDRDATA_TS_11 07-FEB-12 YES 619502
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP12 485 EDX_REPORT_CDRDATA_TS_12 07-FEB-12 YES 656996
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP13 484 EDX_REPORT_CDRDATA_TS_13 07-FEB-12 YES 575693
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP14 485 EDX_REPORT_CDRDATA_TS_14 07-FEB-12 YES 727971
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP15 484 EDX_REPORT_CDRDATA_TS_15 07-FEB-12 YES 570569
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP16 484 EDX_REPORT_CDRDATA_TS_16 07-FEB-12 YES 593904
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP17 483 EDX_REPORT_CDRDATA_TS_17 07-FEB-12 YES 4792555
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP18 482 EDX_REPORT_CDRDATA_TS_18 07-FEB-12 YES 653706
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP19 507 EDX_REPORT_CDRDATA_TS_19 09-FEB-12 YES 5237260
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP20 533 EDX_REPORT_CDRDATA_TS_20 31-JAN-12 YES 5819440
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP30 557 EDX_REPORT_LARGEDATA_TS_01 31-JAN-12 YES 13627954
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP21 559 EDX_REPORT_LARGEDATA_TS_01 31-JAN-12 YES 5820874
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP31 557 EDX_REPORT_LARGEDATA_TS_02 31-JAN-12 YES 12635593
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP22 559 EDX_REPORT_LARGEDATA_TS_02 31-JAN-12 YES 5822645
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP32 558 EDX_REPORT_LARGEDATA_TS_03 31-JAN-12 YES 12210507
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP23 558 EDX_REPORT_LARGEDATA_TS_03 31-JAN-12 YES 5835137
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP33 583 EDX_REPORT_LARGEDATA_TS_04 31-JAN-12 YES 11379172
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP24 558 EDX_REPORT_LARGEDATA_TS_04 31-JAN-12 YES 5837251
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP34 579 EDX_REPORT_LARGEDATA_TS_05 31-JAN-12 YES 13101460
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP25 558 EDX_REPORT_LARGEDATA_TS_05 31-JAN-12 YES 5843077
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP35 582 EDX_REPORT_LARGEDATA_TS_06 31-JAN-12 YES 11975073
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP26 558 EDX_REPORT_LARGEDATA_TS_06 31-JAN-12 YES 5820046
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP36 581 EDX_REPORT_LARGEDATA_TS_07 31-JAN-12 YES 11719129
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP27 560 EDX_REPORT_LARGEDATA_TS_07 31-JAN-12 YES 5820107
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP37 580 EDX_REPORT_LARGEDATA_TS_08 31-JAN-12 YES 11134419
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP28 560 EDX_REPORT_LARGEDATA_TS_08 31-JAN-12 YES 5823127
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP38 581 EDX_REPORT_LARGEDATA_TS_09 31-JAN-12 YES 11859682
OLAP EDX_RPT_SRVC_CHRG_DTL_FACT_X P_CUSTGRP29 546 EDX_REPORT_LARGEDATA_TS_09 31-JAN-12 YES 1960627
38 rows selected.
There are 3 partitioned indexes on this tables. Detail is below.
INDEX_NAME COLUMN_NAME TABLE_NAME
------------------------------ ---------------------------------- ------------------------------
PK_SRVC_CHRG_DTL_FACT_X SRVC_CHRG_DTL_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
PK_SRVC_CHRG_DTL_FACT_X LOAD_DATE EDX_RPT_SRVC_CHRG_DTL_FACT_X
PK_SRVC_CHRG_DTL_FACT_X CUST_GROUP_ID EDX_RPT_SRVC_CHRG_DTL_FACT_X
NK_ERSCDFX_CHARGETYPEKEY_X CHARGE_TYPE_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
NK_ERSCDFX_CHARGETYPEKEY_X SERVICE_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
NK_ERSCDFX_CHARGETYPEKEY_X STATEMENT_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
NK_ERSCDFX_CHARGETYPEKEY_X LOAD_DATE EDX_RPT_SRVC_CHRG_DTL_FACT_X
NK_ERSCDFX_CHARGETYPEKEY_X CUST_GROUP_ID EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL CUST_GROUP_ID EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL SRVC_CHRG_DTL_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL FLEX_FIELD_CHR1 EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL SERVICE_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL STATEMENT_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL ACCOUNT_KEY EDX_RPT_SRVC_CHRG_DTL_FACT_X
ERSCDF_RL LOAD_DATE EDX_RPT_SRVC_CHRG_DTL_FACT_X
SQL> select INDEX_NAME,PARTITION_NAME,SUBPARTITION_COUNT,TABLESPACE_NAME,LAST_ANALYZED,GLOBAL_STATS from dba_ind_partitions
2 where INDEX_NAME = 'NK_ERSCDFX_CHARGETYPEKEY_X';
INDEX_NAME PARTITION_NAME SUBPARTITION_COUNT TABLESPACE_NAME LAST_ANAL GLO
------------------------------ -------------- ------------------ ------------------------------ --------- ---
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP30 557 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP31 557 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP32 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP33 583 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP35 582 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP37 580 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP38 581 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP28 560 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP29 546 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP16 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP14 485 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP08 527 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP01 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP19 507 EDX_REPORT_MEDIUM_IDX_ID 09-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP20 533 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP36 581 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP34 579 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP02 524 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP03 527 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP04 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP05 523 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP06 529 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP07 524 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP10 486 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP12 485 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP13 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP15 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP17 483 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP18 482 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP09 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP11 482 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP21 559 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP22 559 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP23 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP24 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP26 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP27 560 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
NK_ERSCDFX_CHARGETYPEKEY_X P_CUSTGRP25 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
38 rows selected.
SQL> select INDEX_NAME,PARTITION_NAME,SUBPARTITION_COUNT,TABLESPACE_NAME,LAST_ANALYZED,GLOBAL_STATS from dba_ind_partitions
2 where INDEX_NAME = 'PK_SRVC_CHRG_DTL_FACT_X';
INDEX_NAME PARTITION_NAME SUBPARTITION_COUNT TABLESPACE_NAME LAST_ANAL GLO
------------------------------ -------------- ------------------ ------------------------------ --------- ---
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP31 557 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP32 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP33 583 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP34 579 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP35 582 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP36 581 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP37 580 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP21 559 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP20 533 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP19 507 EDX_REPORT_MEDIUM_IDX_ID 09-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP06 529 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP04 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP30 557 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP38 581 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP01 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP02 524 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP05 523 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP07 524 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP08 527 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP09 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP10 486 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP12 485 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP13 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP14 485 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP15 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP16 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP17 483 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP18 482 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP11 482 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP03 527 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP23 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP24 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP25 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP26 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP28 560 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP29 546 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP22 559 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
PK_SRVC_CHRG_DTL_FACT_X P_CUSTGRP27 560 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
38 rows selected.
SQL> select INDEX_NAME,PARTITION_NAME,SUBPARTITION_COUNT,TABLESPACE_NAME,LAST_ANALYZED,GLOBAL_STATS from dba_ind_partitions
2 where INDEX_NAME = 'ERSCDF_RL';
INDEX_NAME PARTITION_NAME SUBPARTITION_COUNT TABLESPACE_NAME LAST_ANAL GLO
------------------------------ -------------- ------------------ ------------------------------ --------- ---
ERSCDF_RL P_CUSTGRP30 557 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP31 557 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP34 579 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP36 581 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP37 580 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP38 581 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP26 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP25 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP22 559 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP32 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP19 507 EDX_REPORT_MEDIUM_IDX_ID 09-FEB-12 YES
ERSCDF_RL P_CUSTGRP27 560 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP33 583 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP35 582 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP01 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP02 524 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP03 527 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP04 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP05 523 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP06 529 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP07 524 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP08 527 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP09 525 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP10 486 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP11 482 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP12 485 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP14 485 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP15 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP16 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP18 482 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP20 533 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP13 484 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP17 483 EDX_REPORT_MEDIUM_IDX_ID 07-FEB-12 YES
ERSCDF_RL P_CUSTGRP21 559 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP23 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP28 560 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP29 546 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
ERSCDF_RL P_CUSTGRP24 558 EDX_REPORT_MEDIUM_IDX_ID 31-JAN-12 YES
38 rows selected. |
|
|
|
| Re: Help - dropping partitions [message #543244 is a reply to message #543234] |
Mon, 13 February 2012 11:46 |
 |
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
|
Senior Member |
|
|
Beware, I've never done partitioning yet. According to the documentation, one should drop the table behind
Documentation
Create a query that creates all ddl statement
something like
select 'DROP '||owner||'.'||TABLE_NAME||' from DBA_TAB_SUBPARTITIONS
where to_date(right(subpartition_name,6),'YYYYMMDD')<add_months(trunc(sysdate),-15);'
Execute the generated sql statements or have the list of tables parsed by a cursor.
[Updated on: Mon, 13 February 2012 12:40] by Moderator Report message to a moderator |
|
|
|
| Re: Help - dropping partitions [message #543339 is a reply to message #543244] |
Tue, 14 February 2012 04:13 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Hi Flyby, Thanks for the response.
we can do this activity in 2 ways. I am not sure is there any other approach apart from the below. As per my knowledge,the 1st approach will be best here.However I just want to have some genius opinion on this.
a) deleting data from subpartitions and then drop subpartitions.
b) drop subpartitions and then rebuild indexes.
[Updated on: Tue, 14 February 2012 04:18] Report message to a moderator |
|
|
|
|
|
| Re: Help - dropping partitions(2 Merged) [message #543734 is a reply to message #542871] |
Thu, 16 February 2012 10:35 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Quote:
We are facing "cursor pin S wait on X" and "kksfbc" wait event frequently
When you experience this wait did you by any chance had a look at the p1, p2 and the sql_id? This is my best guess by looking at the wait event pattern especially "kksfbc" . AFAIK this event will occur only when it needs to create a child cursor because it cannot reuse the current child cursor for some reason (look at the v$sql_shared_cursor for the reason why it thinks it's as a mismatch) and it has to walk through a long chain in the library cache. When it walks and another session want to do the same then you will experience this combination of wait event.
Check this link about v$sql_Shared_cursor.
http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2124.htm
Also check this link on how to do session sampling.
http://blog.tanelpoder.com/2011/01/09/snapper-3-52-with-oracle-9-2-support-2/
If you managed to find the sql_id then try to look for that sql in v$sql or gv$sql. If you cannot find it then try to look for it in gv$open_cursor. If you end up find your sql there and if it starts with table_1_f_xx then try to convert that xx from hex value to a decimal value which should give you the object_id. Use this object_id to find the underlying object from dba_objects and see if that shed any further light on this problem.
Happy sampling sessions and come back to us with your findings.
Also for what it is worth check these bugs if they are of any help to you.
Bug 6795880
Bug 5500044
Thanks
Raj
[Updated on: Thu, 16 February 2012 10:35] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 16:55:02 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
