How NOT to Install Oracle Software

Once upon a time there was a DBA who always used the Oracle Universal Installer (OUI) to install Oracle software. Then a manager asked him to install it by copying an Oracle home from one place to another using operating system commands. The DBA said this was a bad idea but was told to do it anyway as it had never caused any problems in the past. The new Oracle home worked OK for some time until the company hit an Oracle bug. Oracle knew about this bug and there was a patch for it. However, the company were unable to apply it as OPatch (Oracle's patching utility) would not work. Oracle could not help them either as the installation method used was unsupported. After that the company always used the OUI to install Oracle software and OPatch worked happily ever after.

I think this was the bug which caused the problem. It was bug 4192148, also known as bug 4604896. It affected Oracle 9.2.0.7.0. The following SQL statement creates a view. Line 2 contains a select a.* and the comma separating it from the bytes column is at the start of line 3:

SQL> create or replace view object_sizes as

  2  select a.*

  3  , bytes

  4  from user_objects a, user_segments b

  5  where a.object_name = b.segment_name

  6  /

View created.

SQL>

You can select object_name, which is at the start of select a.*:

SQL> select object_name from object_sizes

  2  where rownum < 6

  3  /

OBJECT_NAME

-------------------------------------------------------

TOT_READ_WRITES

PACSTAT1

PACSTAT2

LOCK_TYPES

MFSESS_IO

SQL>

... and you can select secondary, which is at the end:

SQL> select secondary from object_sizes

  2  where rownum < 6

  3  /

S

-

N

N

N

N

N

SQL>

... but if you try to select bytes, you get an ORA-00936:

SQL> select bytes from object_sizes

  2  where rownum < 6

  3  /

select bytes from object_sizes

                  *

ERROR at line 1:

ORA-00936: missing expression

SQL>

That's because the view is not stored correctly. There is a comma at the end of the 4th line after a."SECONDARY". There is another comma at the start of the 5th line before bytes but there is nothing between them:

SQL> set long 4000

SQL> select text from user_views

  2  where view_name = 'OBJECT_SIZES'

  3  /

TEXT

-------------------------------------------------------

select a."OBJECT_NAME",a."SUBOBJECT_NAME",a."OBJECT_ID"

,a."DATA_OBJECT_ID",a."OBJECT_TYPE",a."CREATED",a."LAST

_DDL_TIME",a."TIMESTAMP",a."STATUS",a."TEMPORARY",a."GE

NERATED",a."SECONDARY",

, bytes

from user_objects a, user_segments b

where a.object_name = b.segment_name

SQL>

You cannot recompile the view either:

SQL> alter view object_sizes compile

  2  /

alter view object_sizes compile

                              *

ERROR at line 1:

ORA-00936: missing expression

SQL>

Assuming you have used the OUI to install the Oracle software, you can apply the appropriate patch (I can no longer remember the number but I guess it must still be on My Oracle Support). If not, you can rewrite the view by putting the comma on the same line as the select a.* then everything works correctly:

SQL> create or replace view object_sizes as

  2  select a.*,

  3  bytes

  4  from user_objects a, user_segments b

  5  where a.object_name = b.segment_name

  6  /

View created.

SQL> select object_name from object_sizes

  2  where rownum < 6

  3  /

OBJECT_NAME

-------------------------------------------------------

TOT_READ_WRITES

PACSTAT1

PACSTAT2

LOCK_TYPES

MFSESS_IO

SQL> select secondary from object_sizes

  2  where rownum < 6

  3  /

S

-

N

N

N

N

N

SQL> select bytes from object_sizes

  2  where rownum < 6

  3  /

     BYTES

----------

     16384

     40960

     32768

     49152

     16384

SQL> set long 4000

SQL> select text from user_views

  2  where view_name = 'OBJECT_SIZES'

  3  /

TEXT

-------------------------------------------------------

select a."OBJECT_NAME",a."SUBOBJECT_NAME",a."OBJECT_ID"

,a."DATA_OBJECT_ID",a."OBJECT_TYPE",a."CREATED",a."LAST

_DDL_TIME",a."TIMESTAMP",a."STATUS",a."TEMPORARY",a."GE

NERATED",a."SECONDARY",

bytes

from user_objects a, user_segments b

where a.object_name = b.segment_name

SQL> alter view object_sizes compile

  2  /

View altered.

SQL>