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 Go to next message
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

Re: Help - dropping partitions [message #543094 is a reply to message #542871] Mon, 13 February 2012 02:13 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Can I have response on this?
Help - dropping partitions [message #543234 is a reply to message #542871] Mon, 13 February 2012 10:03 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Flyby
Messages: 146
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 Go to previous messageGo to next message
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 [message #543561 is a reply to message #543339] Wed, 15 February 2012 04:36 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi Genius, can anyone help me here?
Re: Help - dropping partitions(2 Merged) [message #543734 is a reply to message #542871] Thu, 16 February 2012 10:35 Go to previous messageGo to next message
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

Re: Help - dropping partitions [message #543736 is a reply to message #543339] Thu, 16 February 2012 10:45 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

a) deleting data from subpartitions and then drop subpartitions.
b) drop subpartitions and then rebuild indexes.

Why do you want to do that ? deleting data from subpartitions and then dropping it. Just drop the subpartition and rebuild index at the same time. Check this link.

http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_3001.htm#i2126499

But I don't need to tell you as you may already know that this action is irreversable and not only that it will invalidate all the current valid cursors related to that table.

Regards

Raj
Previous Topic: Handling NULL values in the Database
Next Topic: need help to understand this execution plan
Goto Forum:
  


Current Time: Sun Nov 23 21:06:11 CST 2014

Total time taken to generate the page: 0.06809 seconds