CODEWITHSUNDEEP
sqloracle-databasedatatablepartitioningdate-range
Pooja
Pooja

Reputation: 333

Data is not getting inserted into proper partition

I have a table with proper partition.

Code snippet:

CREATE TABLE RSA_DBA.RSA_ADHOC_ACCT
(   
    RSA_ACCT_DTL_GID NUMBER(12,0) NOT NULL, 
    EFF_DT DATE NOT NULL, 
    RSA_CMPSN_REC_GID NUMBER(12,0), 
    ACCT_ID NUMBER(12,0), 
    ACCT_CMPSN_AMT NUMBER(16,3), 
    INSRT_USER VARCHAR2(30 BYTE) DEFAULT USER NOT NULL, 
    INSRT_TS DATE DEFAULT SYSDATE NOT NULL, 
    UPDT_USER VARCHAR2(30 BYTE) DEFAULT USER NOT NULL, 
    UPDT_TS DATE DEFAULT SYSDATE NOT NULL, 
    PRCS_RUN_ID NUMBER(12,0), 
    SRC_TS DATE, 
    VLD_IND CHAR(1 BYTE), 

    CONSTRAINT PK_RSA_ADHOC_ACCT_DTL_AI 
        PRIMARY KEY (RSA_ACCT_DTL_GID, EFF_DT)
    PARTITION BY RANGE (EFF_DT) 
    PARTITION PTN_M20221201  VALUES LESS THAN (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    PARTITION PTN_M20230101  VALUES LESS THAN (TO_DATE(' 2023-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    PARTITION PTN_M20230201  VALUES LESS THAN (TO_DATE(' 2023-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     :
     :
     :
     PARTITION PTN_M20241001  VALUES LESS THAN (TO_DATE(' 2024-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
     PARTITION PTN_M20241101  VALUES LESS THAN (TO_DATE(' 2024-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
)

When I am trying to insert data, few data got inserted properly in respective partition while others didn't.

Sharing screenshot of data available data in table:

SQL:

select trunc(EFF_DT), count(ACCT_CMPSN_AMT) 
from RSA_DBA.RSA_ADHOC_ACCT
group by  trunc(EFF_DT)
order by 1;

enter image description here

Now we we look into table "dba_tab_partitions" to check if data are properly inserted I can see only 2 partition has proper data, highlighted.

SQL:

SELECT table_name, partition_name, num_rows
FROM dba_tab_partitions
WHERE table_name  like 'RSA_ADHOC_ACCT' and NUM_ROWS > 0
ORDER BY table_name, partition_name;

enter image description here

Please note: here I can see for other partition data is very huge. Can someone guide me - where can I fix this issue? It's a PROD issue.

Upvotes: 0

Views: 50

Answers (1)

Pooja
Pooja

Reputation: 333

When checked issue found in "Gathering statistics". Since the data volume was high and hence oracle didn't performed proper partition maintenance. when checked object_id of the inserted data with respect to eff_dt it was matching with the partion object_id value(obtained using dba_objects)

SQL:

select distinct substr(dbms_rowid.rowid_object(rowid),1,15) object_id , eff_dt
from RSA_ADHOC_ACCT;

Object_id obtained:

enter image description here

Thus just by executing below SQL, I was able to get these data reflected corresponding to partition.

EXEC dbms_stats.gather_table_stats(ownname => 'RSA_DBA', tabname => RSA_ADHOC_ACCT, estimate_percent => 100);

Upvotes: 1

Related Questions