ORA-01466: unable to read data – table definition has changed

Where I currently work a significant proportion of business is done on-line, via the web or mobile app. For the OLTP side of the systems this means we have a strong focus on performance and availability. As an example, we deliberately avoid package state in our database packages so we can deploy new versions of code without having to take the system down. Similarly, many database maintenance activities are performed on-line too… which is why I was concerned when I arrived at work one morning to find an on-line session had encountered the following exception:

ORA-01466: unable to read data - table definition has changed

The operation in question was a simple SELECT against a single interval partitioned table. Cross referencing the application logs to the database activity the error occurred at the same time that a partition was dropped on the table. The partition drop was part of a standard job to remove old data. The DDL issued against the table was:

ALTER TABLE partition_test SET INTERVAL (INTERVAL '7' DAY)
ALTER TABLE partition_test DROP PARTITION earliest_partition

The first command shifts the last range partition to the last partition of the table, converting the interval partitions to range partitions, thereby avoiding the exception thrown if an attempt is made to drop the last range partition. This then allows the dropping of old partitions without trouble.

Testing using the packaged routines in separate sessions failed to generate ORA-01466. No matter the order that the SELECT or DDL was executed no problems were encountered. Reading up on ORA-01466 on-line, one scenario that can give rise to the error is the use of read only queries, which led to how the error occurred…

Let’s use a simple interval partitioned table populated with 50 rows to illustrate the problem:

CREATE TABLE partition_test
   (id      NUMBER(5))
PARTITION BY RANGE (id) INTERVAL (10)
(PARTITION partition_test_p1 VALUES LESS THAN (10))
/

INSERT INTO partition_test
SELECT ROWNUM
FROM   dual
CONNECT BY ROWNUM <= 50
/

COMMIT
/

If we execute our partition maintenance commands followed immediately by a simple SELECT then we get a correct result:

SQL> ALTER TABLE partition_test SET INTERVAL (10)
  2  /

Table altered.

SQL> ALTER TABLE partition_test DROP PARTITION partition_test_p1
  2  /

Table altered.

SQL> SELECT *
  2  FROM   partition_test
  3  WHERE  id = 40
  4  /

        ID
----------
        40

However if our SELECT is done within a session that is read only:

SQL> ALTER TABLE partition_test SET INTERVAL (10)
  2  /

Table altered.

SQL> ALTER TABLE partition_test DROP PARTITION partition_test_p1
  2  /

Table altered.

SQL> SET TRANSACTION READ ONLY
  2  /

Transaction set.

SQL> SELECT *
  2  FROM   partition_test
  3  WHERE  id = 40
  4  /
FROM   partition_test
       *
ERROR at line 2:
ORA-01466: unable to read data - table definition has changed

From Oracle Support information, if a read only query if issued immediately against a table that has had DDL done on it then it will result in ORA-01466.

So, going back to the original system error, the developer of the client application confirmed that the session was put into read-only mode as it had no requirement to modify data. Sensible thinking on behalf of the developer but unfortunately it resulted in the problem. The solution was to simply run the session in standard read/write mode.

AusOUG Conference 2014

I’m on the train home after a very informative two days at the Perth AusOUG conference. It was great to hear some very useful talks by both local and international speakers, including:

• Bjoern Rost
• Connor McDonald
• Craig Shallahamer
• Gavin Soorma
• Jeff Kemp
• Penny Cookson
• Tim Hall

If you ever have the opportunity to attend an AusOUG conference or hear any of these speakers at some other conference then I would highly recommend it! A big thanks to the conference organisers, AusOUG, the sponsors and Oracle.

PL/SQL functions and cursor fetch size

After reviewing some top SQL performance statistics I noticed one query with executions with the 10,000s but fetches in the millions. The query was embedded in a package routine that simply opened a ref cursor, allowing the associated application to pull the results on demand. After working out how many rows the cursor returned it was clear that the application was fetching one row at a time.

The application opening the cursor was written in C# and used ODP.Net to access the database. By default ODP.Net uses a 64k buffer to fetch data so for some reason it was under the impression that it could only retrieve one row at a time from the cursor. Back to the cursor…

The cursor contained a number of calculated columns and these calculations were done using PL/SQL functions, i.e. it resembled:

SELECT col1
,      col2
,      plsql_func1(...)
,      plsql_func2(...)
FROM   ...

The following package code uses the EMP and DEPT tables to illustrate the problem with the above strategy. The package contains a routine that opens a cursor containing EMP details. Some of the attributes are straight from the table, some are calculated using internal SQL functions and one uses a PL/SQL function to return the department name from the department id.

CREATE OR REPLACE PACKAGE pkg_emp
AS

TYPE t_emp_rec IS RECORD (empno          emp.empno%TYPE
                         ,formatted_name emp.ename%TYPE
                         ,hiredate       emp.hiredate%TYPE
                         ,dname          dept.dname%TYPE
                         ,mgr_flag       VARCHAR2(1));

TYPE t_emp_cur IS REF CURSOR RETURN t_emp_rec;

-- Function to return the department name from it id
FUNCTION dept_name (p_deptno  IN emp.deptno%TYPE)
RETURN dept.dname%TYPE;
   
-- Routine to return employee details   
PROCEDURE get_emps (p_emp_cur OUT t_emp_cur);

END pkg_emp;
/

CREATE OR REPLACE PACKAGE BODY pkg_emp
AS

-- Function to return the department name from it id
FUNCTION dept_name (p_deptno  IN emp.deptno%TYPE)
RETURN dept.dname%TYPE
AS
   l_dname  dept.dname%TYPE;
BEGIN
   SELECT dname
   INTO   l_dname
   FROM   dept
   WHERE  deptno = p_deptno;
   
   RETURN l_dname;
EXCEPTION 
   WHEN NO_DATA_FOUND THEN
      RETURN NULL;
END dept_name;

-- Routine to return employee details   
PROCEDURE get_emps (p_emp_cur OUT t_emp_cur)
AS
BEGIN
   OPEN p_emp_cur
   FOR
   SELECT e.empno
   ,      INITCAP(e.ename) AS formatted_name -- SQL function calculation
   ,      e.hiredate
   ,      dept_name (e.deptno) AS dname -- PL/SQL function calculation
   ,      CASE WHEN EXISTS (SELECT 'x'
                            FROM   emp e1
                            WHERE  e1.mgr = e.empno)
               THEN 'Y'
               ELSE 'N'
          END AS mgr_flag -- more complex SQL function calculation
   FROM   emp e;
END get_emps;

END pkg_emp;
/

We can get Oracle to tell us how it sees the cursor returned by the GET_EMPS routine by using DBMS_SQL.DESCRIBE. The following displays the cursor columns and the maximum length each can have.

DECLARE
  l_emp_cur   pkg_emp.t_emp_cur;
  l_cur_no    NUMBER;
  l_col_cnt   INTEGER;
  l_rec_tab   DBMS_SQL.DESC_TAB;
BEGIN

  pkg_emp.get_emps (l_emp_cur);
  l_cur_no := DBMS_SQL.TO_CURSOR_NUMBER (l_emp_cur);
 
  DBMS_SQL.DESCRIBE_COLUMNS(l_cur_no, l_col_cnt, l_rec_tab);

  FOR i IN 1..l_col_cnt
  LOOP
     DBMS_OUTPUT.PUT_LINE('Column ' || l_rec_tab(i).col_name || ' max length of ' || TO_CHAR(l_rec_tab(i).col_max_len));
  END LOOP;
 
  DBMS_SQL.CLOSE_CURSOR(l_cur_no);
END;
/

The output from the above is:

Column EMPNO max length of 22
Column FORMATTED_NAME max length of 10
Column HIREDATE max length of 7
Column DNAME max length of 4000
Column MGR_FLAG max length of 1

Looking at the DNAME, which is the PL/SQL function calculated attribute, we see a maximum length of 4,000. This might appear strange given that the function is declared to return a string based on the DEPT.DNAME column, which is defined as VARCHAR2(14). However Oracle only uses the base data type and ignores the length constraint. This means that Oracle will consider the PL/SQL function to be capable of returning a string of the maximum length allowed, which is 4,000 bytes. It’s worth noting that this only affects PL/SQL function calls. Calls to Oracle’s internal functions do not suffer from this problem as evidenced by the FORMATTED_NAME and MGR_FLAG columns.

Going back to the original ORDP.Net application, should a cursor return 8 or more PL/SQL function calculated columns then it will exceed half of the 64k buffer and will be restricted to fetching one row at a time.

How do we overcome this problem? The answer is to wrap the PL/SQL function calls in a CAST to tell Oracle the actual length it can expect from the functions, i.e.:

   SELECT e.empno
   ,      INITCAP(e.ename) AS formatted_name -- SQL function calculation
   ,      e.hiredate
   ,      CAST(dept_name (e.deptno) AS VARCHAR2(14)) AS dname -- PL/SQL function calculation
   ,      CASE WHEN EXISTS (SELECT 'x'
                            FROM   emp e1
                            WHERE  e1.mgr = e.empno)
               THEN 'Y'
               ELSE 'N'
          END AS mgr_flag -- more complex SQL function calculation
   FROM   emp e;

Running our DBMS_SQL.DESCRIBE routine using the CAST function gives us to result we want:

Column EMPNO max length of 22
Column FORMATTED_NAME max length of 10
Column HIREDATE max length of 7
Column DNAME max length of 14
Column MGR_FLAG max length of 1

After adding in the CAST calls the number of fetches required by the C# application dropped to a much more reasonable 2, instead of the 600+ it had been doing previously.