MDSYS.SDO_COORD_AXES

This was tested on Oracle 11.2. If you need to look at the ORDER column in the above-mentioned table, there only seems to be one way to do it i.e. in upper case and surrounded by double quotes as shown below. I guess this is because ORDER is a reserved word:

 

SQL> desc mdsys.sdo_coord_axes

Name                       Null?    Type

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

COORD_SYS_ID               NOT NULL NUMBER(10)

COORD_AXIS_NAME_ID                  NUMBER(10)

COORD_AXIS_ORIENTATION              VARCHAR2(24)

COORD_AXIS_ABBREVIATION             VARCHAR2(24)

UOM_ID                              NUMBER(10)

ORDER                      NOT NULL NUMBER(5)

 

SQL> select max(order) from mdsys.sdo_coord_axes

  2  /

select max(order) from mdsys.sdo_coord_axes

           *

ERROR at line 1:

ORA-00936: missing expression

 

SQL> select max("order") from mdsys.sdo_coord_axes

  2  /

select max("order") from mdsys.sdo_coord_axes

           *

ERROR at line 1:

ORA-00904: "order": invalid identifier

 

SQL> select max(ORDER) from mdsys.sdo_coord_axes

  2  /

select max(ORDER) from mdsys.sdo_coord_axes

           *

ERROR at line 1:

ORA-00936: missing expression

 

SQL> select max("ORDER") from mdsys.sdo_coord_axes

  2  /

 

MAX("ORDER")

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

           3

 

SQL>

Invisible Indexes (Part 2)

In Oracle 11.1.0.6.0, you could not run DBMS_STATS against an invisible index or a table with an invisible index. I found 2 workarounds. You could run the old ANALYZE command or you could make the index visible on a temporary basis:

SQL> create table my_table

  2  as select * from dba_tables

  3  /

Table created.

SQL> create index my_index

  2  on my_table(owner) invisible

  3  /

Index created.

SQL> exec dbms_stats.gather_table_stats -

> (user,'my_table',cascade=>true);

BEGIN dbms_stats.gather_table_stats  (user,'my_table',cascade=>true); END;

*

ERROR at line 1:

ORA-00904: : invalid identifier

ORA-06512: at "SYS.DBMS_STATS", line 17806

ORA-06512: at "SYS.DBMS_STATS", line 17827

ORA-06512: at line 1

SQL> exec dbms_stats.gather_index_stats(user,'my_index');

BEGIN dbms_stats.gather_index_stats(user,'my_index'); END;

*

ERROR at line 1:

ORA-00904: : invalid identifier

ORA-06512: at "SYS.DBMS_STATS", line 14037

ORA-06512: at "SYS.DBMS_STATS", line 14060

ORA-06512: at line 1

SQL> analyze table my_table compute statistics

  2  /

Table analyzed.

SQL> analyze index my_index validate structure

  2  /

Index analyzed.

SQL> alter index my_index visible

  2  /

Index altered.

SQL> exec dbms_stats.gather_table_stats -

> (user,'my_table',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(user,'my_index');

PL/SQL procedure successfully completed.

SQL>

This is bug 6344547. According to Metalink / My Oracle Support, it was fixed in version 11.1.0.7.0. I was unable to check this but did confirm that it was fixed in version 11.2.0.1.0.

********************

The initialisation parameter optimizer_use_invisible_indexes allows the optimizer to use invisible indexes. In the example below, a table is created with an invisible index. A SELECT statement, which would normally use this index, is run against the table but the index is not used because it is invisible. The initialisation parameter is set to true and the SELECT statement is run again. This time it uses the index:

SQL> create table my_table

  2  as select * from dba_tables

  3  /

Table created.

SQL> create index my_index

  2  on my_table(owner) invisible

  3  /

Index created.

SQL> set autotrace on

SQL> select count(*) from my_table

  2  where owner = 'SYSTEM'

  3  /

  COUNT(*)

----------

       154

Execution Plan

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

Plan hash value: 228900979

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

| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |          |     1 |    17 |     6  (17)| 00:00:01 |

|   1 |  SORT AGGREGATE    |          |     1 |    17 |            |          |

|*  2 |   TABLE ACCESS FULL| MY_TABLE |   143 |  2431 |     6  (17)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("OWNER"='SYSTEM')

Note

-----

   - dynamic sampling used for this statement

Statistics

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

         28  recursive calls

          0  db block gets

        133  consistent gets

          0  physical reads

          0  redo size

        523  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> alter session set optimizer_use_invisible_indexes = true

  2  /

Session altered.

SQL> select count(*) from my_table

  2  where owner = 'SYSTEM'

  3  /

  COUNT(*)

----------

       154

Execution Plan

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

Plan hash value: 4077732364

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

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |          |     1 |    17 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |          |     1 |    17 |            |          |

|*  2 |   INDEX RANGE SCAN| MY_INDEX |   154 |  2618 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='SYSTEM')

Note

-----

   - dynamic sampling used for this statement

Statistics

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

          9  recursive calls

          0  db block gets

         68  consistent gets

          1  physical reads

          0  redo size

        523  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>

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 >

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>