How to solve ORA-04063: view “SYS.ALL_QUEUE_TABLES

Question

Jdev version 11.1.1.7.1

I am using Oracle Database 10g XE & I am able to connect to the DB and retrieve all tables in HR schema. But when I am trying to create "Business components from Table" I am getting the above mentioned error. I have uninstalled Jdeveloper and once again reinstalled it. Still I am getting the same error.

Here is the description of the error :-

The following SQL statement failed :

SELECT /*OracleDictionaryQueries.ALL_ORACLE_OBJECT_QUERY(3)*/
   O.OBJECT_NAME, O.OBJECT_TYPE, O.OBJECT_ID
FROM   ALL_OBJECTS O
WHERE  O.OWNER = ?
AND    O.OBJECT_NAME LIKE ?
AND    O.OBJECT_TYPE IN (?, ?, ?)
AND    O.SUBOBJECT_NAME IS NULL
AND    O.SECONDARY = 'N'
AND    ( O.OBJECT_TYPE <> 'INDEX' OR
     (
      EXISTS (SELECT 1
              FROM   ALL_INDEXES I
              WHERE  I.OWNER = O.OWNER
              AND    I.INDEX_NAME = O.OBJECT_NAME
         AND    I.DROPPED = 'NO'
             )
     )
   )
AND    ( O.OBJECT_TYPE <> 'TRIGGER' OR
     (
      EXISTS (SELECT 1
              FROM   ALL_TRIGGERS TR
              WHERE  TR.OWNER = O.OWNER
              AND    TR.TRIGGER_NAME = O.OBJECT_NAME
              AND   (   TR.BASE_OBJECT_TYPE <> 'TABLE'
                     OR EXISTS ( SELECT 1 FROM ALL_TABLES T2
                                 WHERE  T2.OWNER = TR.TABLE_OWNER
                                 AND    T2.TABLE_NAME = TR.TABLE_NAME
                            AND    T2.DROPPED = 'NO'
                                )
                    )
             )
     )
   )
AND    ( O.OBJECT_TYPE <> 'TYPE' OR
     EXISTS (SELECT 1
              FROM   ALL_TYPES T
              WHERE  T.OWNER = O.OWNER
              AND    T.TYPE_NAME = O.OBJECT_NAME)
   )
AND    ( O.OBJECT_NAME NOT LIKE 'AQ$%' OR
     NOT EXISTS
     ( SELECT 1
       FROM   ALL_QUEUE_TABLES QT
       WHERE  O.OWNER = QT.OWNER
       AND    (   O.OBJECT_NAME = 'AQ$'||QT.queue_table
               OR O.OBJECT_NAME like 'AQ$'||QT.queue_table||'^_%' escape '^'
               OR O.OBJECT_NAME like 'AQ$^_'||QT.queue_table||'^_%' escape      '^')
      )
   )
UNION ALL
SELECT /*OracleDictionaryQueries.ALL_TABLE_ORACLE_QUERY*/
   X.TABLE_NAME
,     'TABLE' OBJECT_TYPE
,     (SELECT O.OBJECT_ID
   FROM   ALL_OBJECTS O
   WHERE  O.OWNER = X.OWNER
   AND O.OBJECT_TYPE = 'TABLE'
   AND O.OBJECT_NAME = X.TABLE_NAME) OBJECT_ID
FROM (
SELECT T.TABLE_NAME, T.OWNER
FROM   ALL_TABLES T
WHERE  T.OWNER = ?
AND    T.NESTED = 'NO'
AND    T.TABLE_NAME LIKE ?
AND    T.IOT_NAME IS NULL
AND    T.SECONDARY = 'N'
AND    T.DROPPED = 'NO'
MINUS
SELECT QT.QUEUE_TABLE, QT.OWNER
FROM   ALL_QUEUE_TABLES QT
WHERE  QT.OWNER = ?
AND    QT.QUEUE_TABLE LIKE ?
MINUS
SELECT MV.MVIEW_NAME, MV.OWNER
FROM   ALL_MVIEWS MV
WHERE  MV.OWNER = ?
AND    MV.MVIEW_NAME LIKE ?
MINUS
SELECT MVL.LOG_TABLE, MVL.LOG_OWNER
FROM   ALL_SNAPSHOT_LOGS MVL
WHERE  MVL.LOG_OWNER = ?
AND    MVL.LOG_TABLE LIKE ?
MINUS
SELECT RU.TABLE_NAME, RU.OWNER
FROM   ALL_TABLES RU
WHERE  RU.TABLE_NAME LIKE 'RUPD%'
AND    RU.TEMPORARY = 'Y'
AND    RU.OWNER = ?
AND    EXISTS (SELECT 1
           FROM   ALL_TAB_COLUMNS ATC
           WHERE  ATC.OWNER = RU.OWNER
           AND    ATC.TABLE_NAME = RU.TABLE_NAME
           AND    ATC.COLUMN_NAME = 'CHANGE_VECTOR$$')
) X
UNION ALL
SELECT T.TABLE_NAME
,     'TABLE' OBJECT_TYPE
,     (SELECT O.OBJECT_ID
   FROM   ALL_OBJECTS O
   WHERE  O.OWNER = T.OWNER
   AND O.OBJECT_TYPE = 'TABLE'
   AND O.OBJECT_NAME = T.TABLE_NAME) OBJECT_ID
FROM   ALL_OBJECT_TABLES T
WHERE  T.OWNER = ?
AND    T.TABLE_NAME LIKE ?
AND    T.IOT_NAME IS NULL
AND    T.NESTED = 'NO'
AND    T.SECONDARY = 'N'
AND    T.DROPPED = 'NO'

Can someone please help me out ?

Answer 1

Assuming that your database is not corrupted, a possible cause of this is that the user you're logged in to the database with doesn't have permission to access the SYS.ALL_QUEUE_TABLES view. To quickly test this you can execute SELECT * FROM SYS.ALL_QUEUE_TABLES. If you're unable to select data from ALL_QUEUE_TABLES, have your DBA grant you the appropriate access (you probably just need SELECT) or, if this is a system where you have access to an appropriately privileged account, log in as the privileged user and execute GRANT SELECT ON SYS.ALL_QUEUE_TABLES TO whatever_user_needs_access, replacing whatever_user_needs_access with the user name you need to give the access grant to. Best of luck.