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>

ORA-01789 and ORA-01790

This was tested on Oracle 11.1.0.6.0 running on Windows XP. You can merge the results from two or more SELECT statements with UNION:

SQL> l
  1  select owner, table_name
  2  from dba_tables
  3  union
  4  select owner, index_name
  5* from dba_indexes
SQL> /

OWNER                     TABLE_NAME
------------------------- -------------------------
CTXSYS                    DR$ACTIVELOGS
CTXSYS                    DR$CLASS
CTXSYS                    DR$DBO
CTXSYS                    DR$DELETE
CTXSYS                    DR$FEATURE_USED
CTXSYS                    DR$FEAT_KEY
CTXSYS                    DR$FREQTOKS
CTXSYS                    DR$INDEX
CTXSYS                    DR$INDEX_CDI_COLUMN
CTXSYS                    DR$INDEX_ERROR
CTXSYS                    DR$INDEX_OBJECT
CTXSYS                    DR$INDEX_PARTITION
etc.

Each SELECT statements must return the same number of columns. If not, you get an ORA-01789:

SQL> l
  1  select owner, table_name, last_analyzed
  2  from dba_tables
  3  union
  4  select owner, index_name
  5* from dba_indexes
SQL> /
select owner, table_name, last_analyzed
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result
columns

SQL>

The column definitions from each SELECT must match the corresponding column definitions from the other SELECTs. Otherwise, you get an ORA-01790:

SQL> l
  1  select table_name, last_analyzed
  2  from dba_tables
  3  union
  4  select index_name, index_type
  5* from dba_indexes
SQL> /
select table_name, last_analyzed
                   *
ERROR at line 1:
ORA-01790: expression must have same datatype as
corresponding expression

You get the same error messages in these situations with UNION ALL, INTERSECT and MINUS.

ORA-03297

This example, which I tested in an Oracle 9 database, explains the cause of this error message. First create a 20 megabyte tablespace with 1 datafile. Put 2 tables in it, each with a single 9 megabyte extent:

SQL> create tablespace andrew
  2  datafile '/database/andrew.dbf'
  3  size 20m
  4  extent management dictionary
  5  /

Tablespace created.

SQL> create table tab1(col1 number)
  2  tablespace andrew
  3  storage (initial 9m)
  4  /

Table created.

SQL> create table tab2(col1 number)
  2  tablespace andrew
  3  storage (initial 9m)
  4  /

Table created.

SQL>

Next create a map of the tablespace. I wrote this SQL in 2002 but it still seems to work. Its output shows the two 9 megabyte tables at the start of the datafile and a 2 megabyte free area at the end:

SQL> select file_id, block_id, segment_name, bytes

  2  from dba_extents

  3  where tablespace_name = 'ANDREW'

  4  union

  5  select file_id, block_id, 'FREE', bytes

  6  from dba_free_space

  7  where tablespace_name = 'ANDREW'

  8  order by file_id, block_id

  9  /

   FILE_ID   BLOCK_ID SEGMENT_NAME         BYTES

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

        26          2 TAB1               9441280

        26       2307 TAB2               9441280

        26       4612 FREE               2084864

SQL>

Now drop the first table and redo the tablespace map. This shows a 9 megabyte free area at the start of the datafile where TAB1 used to be:

SQL> drop table tab1

  2  /

Table dropped.

SQL> select file_id, block_id, segment_name, bytes

  2  from dba_extents

  3  where tablespace_name = 'ANDREW'

  4  union

  5  select file_id, block_id, 'FREE', bytes

  6  from dba_free_space

  7  where tablespace_name = 'ANDREW'

  8  order by file_id, block_id

  9  /

   FILE_ID   BLOCK_ID SEGMENT_NAME         BYTES

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

        26          2 FREE               9441280

        26       2307 TAB2               9441280

        26       4612 FREE               2084864

SQL>

Try to resize the datafile to 10 megabytes as it now only contains a 9 megabyte table. This fails as it can only recoup space from the end of the datafile and TAB2 is there already:

SQL> alter database

  2  datafile '/cisdpt/ebedpt1/ebe_tables/andrew.dbf'

  3  resize 10m

  4  /

alter database

*

ERROR at line 1:

ORA-03297: file contains used data beyond requested

RESIZE value

SQL>

You need to do something to free up the space at the end of the  datafile. On this occasion you are in luck. If you move TAB2 within the tablespace, it will take up the space at the start of the datafile:

SQL> alter table tab2 move

  2  /

Table altered.

SQL>

Redo the tablespace map. This shows a large enough area of free space at the end of the datafile to allow it to be resized successfully:

SQL> select file_id, block_id, segment_name, bytes

  2  from dba_extents

  3  where tablespace_name = 'ANDREW'

  4  union

  5  select file_id, block_id, 'FREE', bytes

  6  from dba_free_space

  7  where tablespace_name = 'ANDREW'

  8  order by file_id, block_id

  9  /

   FILE_ID   BLOCK_ID SEGMENT_NAME         BYTES

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

        26          2 TAB2               9441280

        26       2307 FREE              11526144

SQL> alter database

  2  datafile '/cisdpt/ebedpt1/ebe_tables/andrew.dbf'

  3  resize 10m

  4  /

Database altered.

SQL>

Redo the tablespace map to show the effect of the resize:

SQL> select file_id, block_id, segment_name, bytes

  2  from dba_extents

  3  where tablespace_name = 'ANDREW'

  4  union

  5  select file_id, block_id, 'FREE', bytes

  6  from dba_free_space

  7  where tablespace_name = 'ANDREW'

  8  order by file_id, block_id

  9  /

   FILE_ID   BLOCK_ID SEGMENT_NAME         BYTES

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

        26          2 TAB2               9441280

        26       2307 FREE               1040384

SQL>

Finally, drop the tablespace:

SQL> drop tablespace andrew
  2  including contents and datafiles
  3  /

Tablespace dropped.

SQL>

And now for a history lesson. I started to use Oracle 7 in the late 1990s. I had a development server all to myself and would often use create controlfile to clone databases. On one occasion I made the datafiles smaller beforehand using the resize command shown above. This caused the create controlfile to fail with an actual file size of <num> is smaller than correct size of <num> blocks message. The only workaround I found was to return the files to their original size and try again. For a long time I thought I was doing something wrong but I eventually discovered that this problem was caused by bug 309181, which was apparently fixed in Oracle 7.3.3.
 

UNION v UNION ALL

This was tested on an Oracle 11 database. A UNION ALL will merge 2 result sets. A UNION does the same then removes duplicate values:

SQL> create table union_test1 (one_col number)

  2  /

 

Table created.

 

SQL> insert into union_test1 values (1)

  2  /

 

1 row created.

 

SQL> insert into union_test1 values (2)

  2  /

 

1 row created.

 

SQL> insert into union_test1

  2  select * from union_test1

  3  /

 

2 rows created.

 

SQL> select * from union_test1

  2  /

 

   ONE_COL

----------

         1

         2

         1

         2

 

SQL> create table union_test2 (one_col number)

  2  /

 

Table created.

 

SQL> insert into union_test2 values (2)

  2  /

 

1 row created.

 

SQL> insert into union_test2 values (3)

  2  /

 

1 row created.

 

SQL> insert into union_test2

  2  select * from union_test2

  3  /

 

2 rows created.

 

SQL> select * from union_test2

  2  /

 

   ONE_COL

----------

         2

         3

         2

         3

 

SQL> select * from union_test1

  2  union all

  3  select * from union_test2

  4  /

 

   ONE_COL

----------

         1

         2

         1

         2

         2

         3

         2

         3

 

8 rows selected.

 

SQL> select * from union_test1

  2  union

  3  select * from union_test2

  4  /

 

   ONE_COL

----------

         1

         2

         3

 

SQL>

Oracle Precedence

This is a hypothetical example and, contrary to what I say below, I have nothing against French or German cars. I wanted to buy a new car so I made up a shortlist of manufacturers and put them in a table:

SQL> create table short_list
  2  (manufacturer varchar2(12))
  3  /

Table created.

SQL> insert into short_list values ('BMW')
  2  /

1 row created.

SQL> insert into short_list values ('Renault')
  2  /

1 row created.

SQL> insert into short_list values ('Subaru')
  2  /

1 row created.

SQL>

Then I decided I did not want to buy a French car so I made a list of French manufacturers and put them in a second table: 
 
SQL> create table french
  2  (manufacturer varchar2(12))
  3  /

Table created.

SQL> insert into french values ('Citroen')
  2  /

1 row created.

SQL> insert into french values ('Renault')
  2  /

1 row created.

SQL>

Then I decided I did not want to buy a German car either so I made a list of German manufacturers and put them in a third table:

SQL> create table german
  2  (manufacturer varchar2(12))
  3  /

Table created.

SQL> insert into german values ('BMW')
  2  /

1 row created.

SQL> insert into german values ('Mercedes')
  2  /

1 row created.

SQL>

Then I decided to see which manufacturers were still in my short list. My first attempt did not produce the result I expected. I think Oracle evaluated the not in before the union so the German manufacturers were still in the list:

SQL> select manufacturer from short_list
  2  where manufacturer not in
  3  (select manufacturer from french)
  4  union
  5  (select manufacturer from german)
  6  /

MANUFACTURER
------------
BMW
Mercedes
Subaru
 
SQL>

Swapping the select statements before and after the union confirmed that the problem was caused by Oracle's precedence rules. This time, the French manufacturers were still in the list:

SQL> select manufacturer from short_list
  2  where manufacturer not in
  3  (select manufacturer from german)
  4  union
  5  (select manufacturer from french)
  6  /

MANUFACTURER
------------
Citroen
Renault
Subaru

SQL>

To get the correct result I had to surround the select statements before and after the union with an extra set of brackets. This told Oracle to evaluate the union first:

SQL> select manufacturer from short_list
  2  where manufacturer not in
  3  ((select manufacturer from french)
  4    union
  5  (select manufacturer from german))
  6  /

MANUFACTURER
------------
Subaru

SQL>