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>
