Site icon IT Tutorial

ORA-20010: DBMS_STATS INTERNAL ERROR in fill_cstats

I got ” ORA-20010: DBMS_STATS INTERNAL ERROR in fill_cstats ”  error in Oracle database.

 

ORA-20010: DBMS_STATS INTERNAL ERROR in fill_cstats

 

Details of error are as follows.

Getting following error when gathering the table statistics

EXEC DBMS_STATS.gather_table_stats(ownname => 'S', tabname => 'TEST', estimate_percent => 100, cascade => TRUE, granularity=> 'ALL', degree => 2, no_invalidate=>FALSE);
*
ERROR at line 1:
ORA-20010: DBMS_STATS INTERNAL ERROR in fill_cstats : both dmin/dmax and
nmin/nmax are null for table S.TEST , column FIRST_NAME ,
ssize 430241
ORA-06512: at "SYS.DBMS_STATS", line 34757
ORA-06512: at line 1



 

 

DBMS_STATS INTERNAL ERROR in fill_cstats : both dmin/dmax and

This ORA-20010 error is related to the following bug:

bug 22314203 LNX:TPCDS:GATHER TABLE STATS FAILS WITH ORA-6512 AND ORA-20010

The bug is fixed in 12.2

Workaround for the bug is to delete statistics and re-gather statistics

If per above workaround does not work, another potential workaround is using parallel degree of 1:

 

SYS@EXEC DBMS_STATS.gather_table_stats(ownname => 'SCOTT', tabname => 'TEST', estimate_percent => 100, cascade => TRUE, degree => 1);

 

 

another case is as follows.

Created a simple test report and form database application. When attempting to edit records, it tries to update the primary key ( for example: ID) column.  The ID column is generated using a sequence and trigger. The records can be updated fine using another tool such as TOAD or SQL Plus.

Error
——-
The following error occurs when trying to edit and save a record:


ORA-20010: CANNOT UPDATE COLUMN ID IN TABLE ORGANIZATION AS IT USES SEQUENCE. 
ORA-06512: at "<SCHEMA>.<TRIGGER_NAME>", line 2 ORA-04088: error during execution of trigger 
'J<SCHEMA>.<TRIGGER_NAME>'

This error is coming from an AFTER UPDATE trigger on the <COLUMN_ID> field:


CREATE OR REPLACE TRIGGER <SCHEMA>.<TRIGGER_NAME> AFTER UPDATE OF ID
ON <SCHEMA>.<TABLE> FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20010,'CANNOT UPDATE COLUMN ID IN TABLE <TABLE> AS IT USES SEQUENCE.');
END;
/

This indicates that the ID field is being passed in the update statement.  The ID column should not be included in the update statement since the ID field has been flagged as hidden.  The user can’t even see, much less edit, it in the form.  All the form allows them to do is edit the organization name.  So would expect the SQL to be something like update organization set name =’user supplied value’ where id = 7;

Can add, read and delete records in APEX.  Can update records in TOAD, SQL Plus, etc. with the trigger enabled. Can also update records in APEX if the update trigger is disabled.  But should not have to disable the trigger as APEX applications may not be the only applications that are intended to be run against this database, and do not want users updating the primary keys.  For example, users may be allowed access to the database with TOAD.

 

 

Issue with AFTER UPDATE trigger.

When the update trigger fires, it tries to update the primary key. All items on the page which have a source type of Database Column and which can have their value saved in session state, including Hidden items, are included in the UPDATE statement in APEX. That is the expected behavior.

 

 

Modify the trigger code to compare the old and new values of ID, and if they differ, then raise the error to confirm it is actually changing:


CREATE OR REPLACE TRIGGER <SCHEMA>.<TRIGGER_NAME> AFTER UPDATE OF ID
ON <SCHEMA>.<TABLE> FOR EACH ROW
BEGIN
IF :OLD.ID <> :NEW.ID THEN
RAISE_APPLICATION_ERROR(-20010,'CANNOT UPDATE COLUMN ID IN TABLE <TABLE> AS IT USES SEQUENCE.');
END IF;
END;

 

The IF test will check if the values are different and if they are, it will raise the error.
 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

Exit mobile version