Thursday, January 31, 2013

Why is my Package or Procedure Invalid?

I had a problem earlier this week with a package which kept going INVALID. Once I had worked out the reason, I decided to do a worked example of it on Oracle 11.2.0.2.7. I created users called SOMEBODY, JLS and CBS. JLS has nothing to do with the pop group of the same name and CBS has nothing to do with the news network. You will notice that they have some unusual permissions. This is to save me having to connect as SYS later in the example. It has nothing to do with the problem:

conn / as sysdba
Connected.
SQL> create user somebody
  2  identified by somebody
  3  default tablespace users
  4  quota unlimited on users
  5  /

User created.
 
SQL> grant create session, create table to somebody
  2  /
 
Grant succeeded.
 
SQL> create user jls
  2  identified by jls
  3  default tablespace users
  4  quota unlimited on users
  5  /
 
User created.
 
SQL> grant
  2  create session,
  3  create procedure,
  4  update any table,
  5  select any table,
  6  select any dictionary to jls
  7  /
 
Grant succeeded.
 
SQL> create user cbs
  2  identified by cbs
  3  default tablespace users
  4  quota unlimited on users
  5  /
 
User created.
 
SQL> grant
  2  create session,
  3  create table,
  4  select any dictionary,
  5  create public synonym to cbs
  6  /
 
Grant succeeded.

SQL>

SOMEBODY created a table called SITE:
 
SQL> conn somebody/somebody
Connected.
SQL> create table site (col1 varchar2(15))
  2  /
 
Table created.

SQL>

JLS created a procedure to update SOMEBODY's SITE table using its fully qualified name:
 
SQL> conn jls/jls
Connected.
SQL> create or replace procedure update_site as
  2  begin
  3  update somebody.site set col1 = '10 High Street';
  4  end;
  5  /
 
Procedure created.

SQL>

DBA_DEPENDENCIES showed that JLS's UPDATE_SITE procedure referenced SOMEBODY's SITE table:
 
SQL> select referenced_owner, referenced_name
  2  from dba_dependencies
  3  where owner = 'JLS'
  4  and name = 'UPDATE_SITE'
  5  /
 
REFERENCED_OWNER     REFERENCED_NAME
-------------------- ------------------------------
SYS                  SYS_STUB_FOR_PURITY_ANALYSIS
SOMEBODY             SITE

SQL>

CBS created its own SITE table but this did not affect the validity of JLS's UPDATE_SITE procedure:
SQL> conn cbs/cbs
Connected.
SQL> create table site (col2 number)
  2  /
 
Table created.
 
SQL> select owner, object_name, object_type
  2  from dba_objects
  3  where status = 'INVALID'
  4  /
 
no rows selected
 
SQL> drop table site
  2  /
 
Table dropped.

SQL>

A public synonym called SITE was created for SOMEBODY's SITE table:
 
SQL> create or replace public synonym site for somebody.site
  2  /
 
Synonym created.

SQL>

CBS recreated its SITE table. JLS's UPDATE_SITE procedure remained VALID:
 
SQL> create table site (col2 number)
  2  /
 
Table created.
 
SQL> select owner, object_name, object_type
  2  from dba_objects
  3  where status = 'INVALID'
  4  /
 
no rows selected
 
SQL> drop table site
  2  /
 
Table dropped.

SQL>

JLS changed its UPDATE_SITE procedure to access SOMEBODY's SITE table via the public synonym:
 
SQL> conn jls/jls
Connected.
SQL> create or replace procedure update_site as
  2  begin
  3  update site set col1 = '10 High Street';
  4  end;
  5  /
 
Procedure created.

SQL>

DBA_DEPENDENCIES showed that JLS's UPDATE_SITE procedure referenced a public synonym called SITE:
 
SQL> select referenced_owner, referenced_name
  2  from dba_dependencies
  3  where owner = 'JLS'
  4  and name = 'UPDATE_SITE'
  5  /
 
REFERENCED_OWNER     REFERENCED_NAME
-------------------- ------------------------------
SYS                  SYS_STUB_FOR_PURITY_ANALYSIS
PUBLIC               SITE

SQL>

CBS checked that there were no INVALID objects in the database and recreated its SITE table. This made JLS's UPDATE_SITE procedure INVALID.
    
SQL> conn cbs/cbs
Connected.
SQL> select owner, object_name, object_type
  2  from dba_objects
  3  where status = 'INVALID'
  4  /
 
no rows selected
 
SQL> create table site (col2 number)
  2  /
 
Table created.
 
SQL> select owner, object_name, object_type
  2  from dba_objects
  3  where status = 'INVALID'
  4  /
 
OWNER      OBJECT_NAME     OBJECT_TYPE
---------- --------------- ---------------
JLS        UPDATE_SITE     PROCEDURE

SQL>

The procedure then had to be recompiled: 

SQL> conn jls/jls
Connected.
SQL> alter procedure update_site compile
  2  /
 
Procedure altered.
 
SQL>

So, there you have it. If a compiled procedure or package accesses a table via a public synonym, then another user creates a table with the same name as the public synonym, the compiled procedure or package will be invalidated.

Incidentally, when this happened for real, a package with over 3000 lines of code became INVALID. When a UNIX script then tried to run it, Oracle did not attempt to recompile the package automatically, it just failed with a tnsnames related error, possibly because the package accessed data over a database link. It was later recompiled without errors and the UNIX script ran successfully.

I have already looked at a very simple example relating to automatic procedure recompilation. I will be looking at it in more detail in the near future as it is clearly not as straightforward as I thought at first.

Saturday, January 26, 2013

EXP-00008 and ORA-00904

A colleague tried to use an Oracle 10 Windows Vista client to export a schema from an Oracle 11.1.0.6.0 database. The export failed and he came to me for help. I have reproduced the error below in a Command Prompt session:

C:\Users\j0294094>exp parfile=paramfile
 
Export: Release 10.2.0.4.0 - Production on Thu Dec 27 16:06:55 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P15 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user CBS
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user CBS
About to export CBS's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export CBS's tables via Conventional Path ...
Etc
Etc
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
EXP-00008: ORACLE error 904 encountered
ORA-00904: "OLEVEL": invalid identifier
EXP-00000: Export terminated unsuccessfully
 
C:\Users\j0294094>

I believe he had this problem because the export utility was on a lower version than the database. One possible solution is to run the export on the UNIX server hosting the database, where the client and the database versions are the same. As you can see below, this runs successfully. However, I’m not sure if this is the best way forwards as Oracle no longer supports export for general use. I’m going to suggest to the developer that he considers using datapump instead:

Solaris > exp parfile=paramfile
 
Export: Release 11.1.0.6.0 - Production on Thu Dec 27 16:34:57 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P15 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user CBS
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user CBS
About to export CBS's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export CBS's tables via Conventional Path ...
Etc
Etc
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
Solaris >

Thursday, January 24, 2013

How to Add Conditions After a Where Clause

I went on an Oracle 9i DBA Performance and Tuning course in 2005 and was looking through the course notes recently to find something to blog about. They suggested that if you had conditions after a WHERE clause joined by AND, you should put the test which was most likely to fail first. This would then save Oracle the bother of evaluating the subsequent condition(s). This seemed reasonable so I decided to try it out.

I copied the contents of DBA_TABLES into a table of my own called T1 and duplicated its contents repeatedly until it had over three million rows. Then I checked that every row had TABLE_LOCK set to ENABLED and that no rows had an owner called BLAH.

SQL> create table t1 as select * from dba_tables
  2  /
 
Table created.
 
SQL> begin
  2  for a in 1..11 loop
  3  insert into t1 select * from t1;
  4  end loop;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> select count(*) from t1
  2  /
 
  COUNT(*)
----------
   3217408
 
SQL> select count(*) from t1 where table_lock = 'ENABLED'
  2  /
 
  COUNT(*)
----------
   3217408
 
SQL> select count(*) from t1 where owner = 'BLAH'
  2  /
 
  COUNT(*)
----------
         0
 
SQL>

Then I ran the first SELECT statement as follows. If the course notes were correct, the first condition should reject every row and the second condition should never be evaluated:

SQL> alter system flush shared_pool
  2  /
 
System altered.
 
SQL> conn /
Connected.
SQL> select count(*) from t1
  2  where owner = 'BLAH'
  3  and table_lock = 'ENABLED'
  4  /
 
  COUNT(*)
----------
         0
 
SQL> select a.value/100 "CPU Used"
  2  from v$mystat a, v$sysstat b
  3  where a.statistic# = b.statistic#
  4  and name = 'CPU used by this session'
  5  /
 
  CPU Used
----------
     13.83
 
SQL>

Then I ran the second SELECT statement shown below. It was identical to the first but the conditions were swapped round. If the course notes were correct, the first condition should accept each row, forcing Oracle to evaluate the second condition every time. Notice how the CPU Used figure increased. I repeated this test five times and got similar results each time. So far so good:

SQL> alter system flush shared_pool
  2  /
 
System altered.
 
SQL> conn /
Connected.
SQL> select count(*) from t1
  2  where table_lock = 'ENABLED'
  3  and owner = 'BLAH'
  4  /
 
  COUNT(*)
----------
         0
 
SQL> select a.value/100 "CPU Used"
  2  from v$mystat a, v$sysstat b
  3  where a.statistic# = b.statistic#
  4  and name = 'CPU used by this session'
  5  /
 
  CPU Used
----------
     14.27
 
SQL>

The course notes also suggested that if you had conditions after a WHERE clause joined by OR, you should put the test which was most likely to succeed first. This would then save Oracle the bother of evaluating the subsequent condition(s). I decided to try this out too, using the table T1, which I created above.

I ran the third SELECT statement like this. If the course notes were correct, the first condition should accept every row and the second condition should never be evaluated: 

SQL> alter system flush shared_pool
  2  / 

System altered.
 
SQL> conn /
Connected.
SQL> select count(*) from t1
  2  where table_lock = 'ENABLED'
  3  or owner = 'BLAH'
  4  /

  COUNT(*)
----------
   3217408

SQL> select a.value/100 "CPU Used"
  2  from v$mystat a, v$sysstat b
  3  where a.statistic# = b.statistic#
  4  and name = 'CPU used by this session'
  5  / 

  CPU Used
----------
     14.17 

SQL>

Finally, I ran the fourth SELECT statement. It was identical to the third but the conditions were swapped round. If the course notes were correct, the first condition should reject each row, forcing Oracle to evaluate the second condition every time. Notice how the CPU Used figure increased. I repeated this test five times as well and got similar results each time. I think this demonstrated that the course notes were correct:

SQL> alter system flush shared_pool
  2  /
 
System altered.
 
SQL> conn /
Connected.
SQL> select count(*) from t1
  2  where owner = 'BLAH'
  3  or table_lock = 'ENABLED'
  4  /
 
  COUNT(*)
----------
   3217408
 
SQL> select a.value/100 "CPU Used"
  2  from v$mystat a, v$sysstat b
  3  where a.statistic# = b.statistic#
  4  and name = 'CPU used by this session'
  5  /
 
  CPU Used
----------
     14.79
 
SQL>

Sunday, January 13, 2013

ORA-00904

I noticed this in Oracle 11. However, I do not think it is a bug as the same thing happens in Oracle 9. I joined two SELECT * statements with a UNION and added an ORDER BY at the end. This gave me an ORA-00904. It seemed to me that the ORDER BY did not know the names of the columns in the output from the SELECT *:
 
SQL> create table andrew (col1 number)
  2  /
 
Table created.
 
SQL> create table john (col1 number)
  2  /
 
Table created.
 
SQL> select *
  2  from user_tables
  3  where table_name = 'JOHN'
  4  union
  5  select *
  6  from user_tables
  7  where table_name = 'ANDREW'
  8  order by table_name
  9  /
order by table_name
         *
ERROR at line 8:
ORA-00904: "TABLE_NAME": invalid identifier
 
SQL>
 
I found the following workarounds. One of them may be suitable if you hit this problem yourself. I used SELECT TABLE_NAME instead of SELECT *:
 
SQL> select table_name
  2  from user_tables
  3  where table_name = 'JOHN'
  4  union
  5  select table_name
  6  from user_tables
  7  where table_name = 'ANDREW'
  8  order by table_name
  9  /
 
TABLE_NAME
------------------------------
ANDREW
JOHN
 
SQL>
 
I removed the ORDER BY. For the purposes of this example, I also added SET HEAD OFF and removed some blank lines from the output:
 
SQL> set head off
SQL> select *
  2  from user_tables
  3  where table_name = 'JOHN'
  4  union
  5  select *
  6  from user_tables
  7  where table_name = 'ANDREW'
  8  /
 
ANDREW
USERS
                               VALID            10
                    1        255          65536
                      1  2147483645
                           YES N
         1          1     N ENABLED
          NO               N N NO  DEFAULT DISABLED NO
NO                  DISABLED YES
                               DISABLED DISABLED
                   NO  NO
 
JOHN
USERS
                               VALID            10
                    1        255          65536
                      1  2147483645
                           YES N
         1          1     N ENABLED
          NO               N N NO  DEFAULT DISABLED NO
NO                  DISABLED YES
                               DISABLED DISABLED
                   NO  NO
 
SQL>
 
I looked at the USER_TABLES view, saw that TABLE_NAME was the first column and used ORDER BY 1 instead:
 
SQL> desc user_tables
Name                       Null?    Type
-------------------------- -------- ------------------
TABLE_NAME                 NOT NULL VARCHAR2(30)
TABLESPACE_NAME                     VARCHAR2(30)
CLUSTER_NAME                        VARCHAR2(30)
IOT_NAME                            VARCHAR2(30)
STATUS                              VARCHAR2(8)
PCT_FREE                            NUMBER
PCT_USED                            NUMBER
INI_TRANS                           NUMBER
MAX_TRANS                           NUMBER
INITIAL_EXTENT                      NUMBER
NEXT_EXTENT                         NUMBER
MIN_EXTENTS                         NUMBER
MAX_EXTENTS                         NUMBER
PCT_INCREASE                        NUMBER
FREELISTS                           NUMBER
FREELIST_GROUPS                     NUMBER
LOGGING                             VARCHAR2(3)
BACKED_UP                           VARCHAR2(1)
NUM_ROWS                            NUMBER
BLOCKS                              NUMBER
EMPTY_BLOCKS                        NUMBER
AVG_SPACE                           NUMBER
CHAIN_CNT                           NUMBER
AVG_ROW_LEN                         NUMBER
AVG_SPACE_FREELIST_BLOCKS           NUMBER
NUM_FREELIST_BLOCKS                 NUMBER
DEGREE                              VARCHAR2(10)
INSTANCES                           VARCHAR2(10)
CACHE                               VARCHAR2(5)
TABLE_LOCK                          VARCHAR2(8)
SAMPLE_SIZE                         NUMBER
LAST_ANALYZED                       DATE
PARTITIONED                         VARCHAR2(3)
IOT_TYPE                            VARCHAR2(12)
TEMPORARY                           VARCHAR2(1)
SECONDARY                           VARCHAR2(1)
NESTED                              VARCHAR2(3)
BUFFER_POOL                         VARCHAR2(7)
ROW_MOVEMENT                        VARCHAR2(8)
GLOBAL_STATS                        VARCHAR2(3)
USER_STATS                          VARCHAR2(3)
DURATION                            VARCHAR2(15)
SKIP_CORRUPT                        VARCHAR2(8)
MONITORING                          VARCHAR2(3)
CLUSTER_OWNER                       VARCHAR2(30)
DEPENDENCIES                        VARCHAR2(8)
COMPRESSION                         VARCHAR2(8)
COMPRESS_FOR                        VARCHAR2(18)
DROPPED                             VARCHAR2(3)
READ_ONLY                           VARCHAR2(3)
 
SQL> select *
  2  from user_tables
  3  where table_name = 'JOHN'
  4  union
  5  select *
  6  from user_tables
  7  where table_name = 'ANDREW'
  8  order by 1
  9  /
 
ANDREW
USERS
                               VALID            10
                    1        255          65536
                      1  2147483645
                           YES N
         1          1     N ENABLED
          NO               N N NO  DEFAULT DISABLED NO
NO                  DISABLED YES
                               DISABLED DISABLED
                   NO  NO
 
JOHN
USERS
                               VALID            10
                    1        255          65536
                      1  2147483645
                           YES N
         1          1     N ENABLED
          NO               N N NO  DEFAULT DISABLED NO
NO                  DISABLED YES
                               DISABLED DISABLED
                   NO  NO
 
SQL>

I put the UNION inside brackets and put the ORDER BY outside the brackets: 

SQL> select * from
  2  (select *
  3   from user_tables
  4   where table_name = 'JOHN'
  5   union
  6   select *
  7   from user_tables
  8   where table_name = 'ANDREW')
  9  order by table_name
10  /
 
ANDREW
USERS
                               VALID            10
                    1        255          65536
                      1  2147483645
                           YES N
         1          1     N ENABLED
          NO               N N NO  DEFAULT DISABLED NO
NO                  DISABLED YES
                               DISABLED DISABLED
                   NO  NO
 
JOHN
USERS
                               VALID            10
                    1        255          65536
                      1  2147483645
                           YES N
         1          1     N ENABLED
          NO               N N NO  DEFAULT DISABLED NO
NO                  DISABLED YES
                               DISABLED DISABLED
                   NO  NO
 
SQL>