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:
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:
[email protected] 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.
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.
153 views last month, 6 views today