Compatibility Issues

Oracle can be set to use a broad range of version settings, controlled by the compatible setting. Normally upon install this is set to the current version of the database that’s been installed. There may be times, however, when a lower compatibility setting is warranted, possibly for performance reasons. Let’s look at an example where a fairly recent version of Oracle (19c) is configured to use 11.2.0.4 compatibility.

The compatible setting does several things:

• sets datafile compatibility
• sets character limits based on the version
  ( can change the datafile headers
• enables version-specific features based upon structures available in the listed version

When a database is started compatibility is checked and, if necessary, changes are made to bring any datafiles from an older release up to specification for the version configured. Because of this compatibility can never be set to a lower version than specified in the version-specific documentation. When upgrading an 11.2.0.4 database compatibility can remain at 11.2.0.4 to allow prior backups to be used in the event a database restore is required. Once a database is set to a higher compatibility the changes made are not reversible.

Compatibility and optimizer features are different. The optimizer_features_enable parameter sets the optimizer version of the database engine and can be changed at any time to any valid version. Knowing that information it should seem logical that a 19.3.0.0 dsatabase cab be set to 11.2.0.4 compatibility AND 19.1.0 for the optimizer features. The optimizer setting enables execution pathways available to the version setting. Execution steps like index skip scan can be enabled even though the database’s physical compatibility is set to 11.2.0.4. This can create an interesting situation with Data Pump.

With the 11.2.0.4 compatibility file and object names may be limited to 35 characters. This is certainly the case with Data Pump. Job names, associated table names are generated using a sequence, and as that sequence increases so can the length of the file/job/object names. The sequence in question is AQ$_KUPC$DATAPUMP_QUETAB_1_N, owned by SYS. As this sequences increases the names generated become longer until they cross that 35 character threshold. At that point (the sequence value is greater than 9999) errors get thrown:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
ORA-31626: job does not exist
ORA-31638: cannot attach to job SYS_IMPORT_FULL_01 for user xxxx
ORA-06512: at “SYS.KUPV$FT”, line 1142
ORA-06512: at “SYS.KUPV$FT”, line 1744
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT_INT”, line 498
ORA-39077: unable to subscribe agent KUPC$A_1_104552960533000 to queue “KUPC$C_1_20200218104552_0”
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPC$QUE_INT”, line 294
ORA-00972: identifier is too long
ORA-06512: at “SYS.DBMS_AQADM_SYS”, line 9006
ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 1558
ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 3431
ORA-06512: at “SYS.DBMS_RULE_ADM”, line 296
ORA-06512: at “SYS.DBMS_RULEADM_INTERNAL”, line 106
ORA-24000: invalid value “SYS”.”KUPC$C_1_20200218104552_0$10016″, RULE SET should be of the form [SCHEMA.]NAME
ORA-00972: identifier is too long
ORA-06512: at “SYS.DBMS_RULEADM_INTERNAL”, line 109
ORA-06512: at “SYS.DBMS_RULEADM_INTERNAL”, line 97
ORA-06512: at “SYS.DBMS_RULE_ADM”, line 290
ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 3386
ORA-06512: at “SYS.DBMS_PR

and data pump exits unsuccessfully.

One solution to this issue is to set compatible to match the database version. An excellent option if performance is not impacted. However, if the compatibility is set to 11.2.0.4 to retain the performance profile of the database prior to upgrade, this is not a viable solution. Fear not, help is at hand.

It is possible to rebuild the sequence and the associated pieces using scripts provided by Oracle, used to create these objects at database creation:

@$ORACLE_HOME/rdbms/admin/catdph.sql
@$ORACLE_HOME/rdbms/admin/prvtdtde.plb
@$ORACLE_HOME/rdbms/admin/catdpb.sql
@$ORACLE_HOME/rdbms/admin/dbmspump.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

The first four scripts drop and recreate the various Data Pump objects, with the final script performing a recompile of the objects to address any that may have been invalidated when the previous objects were dropped. Note that the sequence in question is NOT recreated by these scripts. That sequence wil; be recreated once either expdp or impdp is called. This, of course, resets the sequence to 0 and provides shorter file/job/object names that the 11.2.0.4 structures can handle.

If this issue affects an installation is would be prudent to check the sequence value periodically. The following PL/SQL code can be used:

set serveroutput on size unlimited

declare
cursor get_seq_val is
select last_number
from dba_sequences
where sequence_name = ‘AQ$_KUPC$DATAPUMP_QUETAB_1_N’;

    v_curr_val      number:=0;
    v_sql           varchar2(400);

begin
open get_seq_val;
fetch get_Seq_val into v_curr_val;
close get_seq_val;
dbms_output.put_line(‘Current value is: ‘||v_curr_val);

    if v_curr_val > 9999 then
            dbms_output.put_line('Sequence value too large, rebuild.');
    end if;

end;
/

Creating another script that executes the rebuild scripts would make it easier to fix the sequence size issue. A reference to that script could be output instead of the generic message shown above. Please note the database does NOT need to be in upgrade mode or restarted to execute the rebuild scripts.

It should be noted that this situation can be created when Data Pump utilities are run frequently, and may only occur in development and/or test environments. It would be wise to set up a script to return the current sequence value so it can be checked occasionally to ensure it is not nearing the magic limit of 10000 (if compatible for the database is set to 11.2.x.a).

Sometimes the most unexpected things can throw a monkey wrench into the works. Forewarned is forearmed, as they say, and this should provide enough warning to allow the DBA to avert disaster with Data Pump. Keep a watchful eye on the current sequence value and Data Pump should stay working regardless of how often is is used. Best to be safe rather than sorry.

Unless being awakened in the middle of the night is the goal.

Compound Interest

Oracle 11g offers a new twist on triggers, the compound trigger, a trigger than can act both before and after an update, insert or delete has occurred. This makes possible the abilty in one trigger to perform processing similar to a stored procedure without having to write such a procedure to call from a traditional trigger. Compound triggers can be used to avoid the dreaded mutating table error or to process and accept or reject updates to a table based upon desired criteria. Before we look at such an example a description of how a compound trigger is constructed is in order.

Compound triggers can have up to four sections:

the BEFORE section
the BEFORE EACH ROW section
the AFTER EACH ROW section
the AFTER section

At least two of the sections must be included (including only one of the four would result in traditional trigger) and it does not matter which two of the sections are used. For example such a trigger can include a BEFORE EACH ROW section and an AFTER section; the two sections need not be ‘matched’ (BEFORE, BEFORE EACH ROW, for instance). Also the COMPOUND TRIGGER STATEMENT must be included so Oracle will recognize the above four constructs and treat them accordingly. The general syntax is:

create or replace trigger <trigger name>
for <insert|update|delete> <of column_name> on <tablename>
      COMPOUND TRIGGER
      <declare section>
      BEFORE
      <before section>
      BEFORE EACH ROW
      <before each row section>
      AFTER EACH ROW
      <after each row section>
      AFTER
      <after section>
END;
/

Since compound triggers are relatively new and many may not have had the opportunity to write or use them I have provided a working example. Setting the stage for this trigger HR has set a restriction on the size of a raise to be given; based on the department the raise cannot exceed 12 percent of the department average salary. A compound trigger can be used to process the raise amounts assigned. Such a compound trigger is shown below, along with several ways of executing the raises:

SQL> create or replace trigger check_raise_on_avg
  2  for update of sal on emp
  3  COMPOUND TRIGGER
  4    Twelve_Percent        constant number:=0.12;
  5
  6    -- Declare collection type and variable:
  7
  8    TYPE Department_Salaries_t  IS TABLE OF Emp.Sal%TYPE
  9                                  INDEX BY VARCHAR2(80);
 10    Department_Avg_Salaries     Department_Salaries_t;
 11    TYPE Sal_t             IS TABLE OF Emp.Sal%TYPE;
 12    Avg_Salaries                Sal_t;
 13    TYPE Deptno_t       IS TABLE OF Emp.Deptno%TYPE;
 14    Department_IDs              Deptno_t;
 15
 16    BEFORE STATEMENT IS
 17    BEGIN
 18      SELECT               AVG(e.Sal), NVL(e.Deptno, -1)
 19        BULK COLLECT INTO  Avg_Salaries, Department_IDs
 20        FROM               Emp e
 21        GROUP BY           e.Deptno;
 22      FOR j IN 1..Department_IDs.COUNT() LOOP
 23        Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
 24      END LOOP;
 25    END BEFORE STATEMENT;
 26
 27    AFTER EACH ROW IS
 28    BEGIN
 29      IF :NEW.Sal - :Old.Sal >
 30        Twelve_Percent*Department_Avg_Salaries(:NEW.Deptno)
 31      THEN
 32        Raise_Application_Error(-20000, 'Raise too large');
 33      END IF;
 34    END AFTER EACH ROW;
 35  END Check_Raise_On_Avg;
 36  /

Trigger created.

SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>
SQL> update emp set sal=sal*1.10 where empno = 7369;

1 row updated.

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        880
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> update emp set sal=sal*1.08 where deptno = 20;

5 rows updated.

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        864
      7499       1600
      7521       1250
      7566       3213
      7654       1250
      7698       2850
      7782       2450
      7788       3240
      7839       5000
      7844       1500
      7876       1188
      7900        950
      7902       3240
      7934       1300

14 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>

Does the trigger reject raises? It certainly does:

SQL> update emp set sal=sal*1.10 where deptno = 30;
update emp set sal=sal*1.10 where deptno = 30
       *
ERROR at line 1:
ORA-20000: Raise too large
ORA-06512: at "BING.CHECK_RAISE_ON_AVG", line 30
ORA-04088: error during execution of trigger 'BING.CHECK_RAISE_ON_AVG'

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL> update emp set sal=sal*1.10 where empno = 7698;
update emp set sal=sal*1.10 where empno = 7698
       *
ERROR at line 1:
ORA-20000: Raise too large
ORA-06512: at "BING.CHECK_RAISE_ON_AVG", line 30
ORA-04088: error during execution of trigger 'BING.CHECK_RAISE_ON_AVG'

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>

The first rejected update unfortunately disallowed all of the raises based on the failure of a few; the second update shows one employee where a 10 percent raise would be greater than 12 percent of the departmental average salary. Of course it is usually rare to see such a large raise given throughout an entire department so such occurrences would be few as raises are usually processed (outside of cost-of-living adjustments) on an individual basis.

Please note that doing the above in a traditional trigger would have resulted in a mutating table error since the table being updated cannot be queried during the update; all successful raises were processed and no such error was thrown.

Compound triggers are a nice addition to an already robust database system; they may not be commonplace but having them available certainly makes application development simpler as business rules that may be unenforceable using a regular trigger can be successfully implemented. They may be considered as ‘specialty tools’ in the database realm but remember that plumbers, builders and mechanics also have tools they only use once in a while and when the situation arises where a compound trigger can be useful it’s good to have them around.

So when do I get my raise?