1

When I run the following query:

SELECT S.OWNER   AS SYN_OWNER,
  S.SYNONYM_NAME AS SYN_NAME,
  S.TABLE_OWNER  AS OBJ_OWNER,
  S.TABLE_NAME   AS OBJ_NAME,
  CASE
    WHEN O.OWNER IS NULL
    THEN 'MISSING'
    ELSE O.STATUS
  END AS OBJ_STATUS
FROM DBA_SYNONYMS S
LEFT JOIN DBA_OBJECTS O
ON S.TABLE_OWNER = O.OWNER
AND S.TABLE_NAME = O.OBJECT_NAME
WHERE O.OWNER   IS NULL
OR O.STATUS     != 'VALID';

It gives me the following output:

PUBLIC  GV$LOADPSTAT    SYSTEM  GV_$LOADPSTAT   INVALID
PUBLIC  GV$LOADISTAT    SYSTEM  GV_$LOADISTAT   INVALID
PUBLIC  V$LOADPSTAT SYSTEM  V_$LOADPSTAT    INVALID
PUBLIC  V$LOADISTAT SYSTEM  V_$LOADISTAT    INVALID
PUBLIC  LOADER_CONSTRAINT_INFO  SYSTEM  LOADER_CONSTRAINT_INFO  MISSING
PUBLIC  LOADER_COL_INFO SYSTEM  LOADER_COL_INFO MISSING
PUBLIC  GV$DB_TRANSPORTABLE_PLATFORM    SYS GV$_DB_TRANSPORTABLE_PLATFORM   MISSING
PUBLIC  LOADER_COL_TYPE SYSTEM  LOADER_COL_TYPE MISSING
PUBLIC  LOADER_OID_INFO SYSTEM  LOADER_OID_INFO MISSING
PUBLIC  LOADER_PARAM_INFO   SYSTEM  LOADER_PARAM_INFO   MISSING
PUBLIC  LOADER_FULL_ATTR_NAME   SYSTEM  LOADER_FULL_ATTR_NAME   MISSING
PUBLIC  LOADER_INTCOL_INFO  SYSTEM  LOADER_INTCOL_INFO  MISSING
PUBLIC  LOADER_NESTED_VARRAYS   SYSTEM  LOADER_NESTED_VARRAYS   MISSING
PUBLIC  LOADER_LOB_FLAGS    SYSTEM  LOADER_LOB_FLAGS    MISSING
PUBLIC  LOADER_DIR_OBJS SYSTEM  LOADER_DIR_OBJS MISSING
PUBLIC  GV$SYSTEM_FIX_CONTROL   SYS GV$SYSTEM_FIX_CONTROL   MISSING
PUBLIC  LOADER_PART_INFO    SYSTEM  LOADER_PART_INFO    MISSING
PUBLIC  LOADER_COL_FLAGS    SYSTEM  LOADER_COL_FLAGS    MISSING
SYS DEF$_SCHEDULE   SYSTEM  DEF$_SCHEDULE   MISSING
PUBLIC  GV$TRANSPORTABLE_PLATFORM   SYS GV$_TRANSPORTABLE_PLATFORM  MISSING
PUBLIC  LOADER_TRIGGER_INFO SYSTEM  LOADER_TRIGGER_INFO MISSING
PUBLIC  LOADER_TAB_INFO SYSTEM  LOADER_TAB_INFO MISSING

If I try to recompile or drop these:

ALTER PUBLIC SYNONYM GV_$LOADPSTAT COMPILE;

It says:

SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"

How could this be so? I'm logged in as SYSTEM.

If I try to drop it:

DROP PUBLIC SYNONYM GV_$LOADPSTAT;

It says:

SQL Error: ORA-01432: public synonym to be dropped does not exist
01432. 00000 -  "public synonym to be dropped does not exist"

How can it not exist when the query result shows it?

1

First of all, DBA_SYNONYMS.SYNONYM_NAME is the name of the synonym. DBA_SYNONYMS.TABLE_NAME is the name of the object the synonym points to. In your query, it is not the synonym that is INVALID, but the object that it points to. It is not the synonym that needs to be fixed, but the object.

If I try to recompile or drop these:

ALTER PUBLIC SYNONYM GV_$LOADPSTAT COMPILE;

It says:

SQL Error: ORA-01031: insufficient privileges

01031. 00000 - "insufficient privileges"

How could this be so? I'm logged in as SYSTEM.

It is a bug on your version.

If I try to drop it:

DROP PUBLIC SYNONYM GV_$LOADPSTAT; It says:

SQL Error: ORA-01432: public synonym to be dropped does not exist

01432. 00000 - "public synonym to be dropped does not exist"

How can it not exist when the query result shows it?

Then name of the synonym is GV$LOADPSTAT. You are trying to drop the object (view) here, called: GV_$LOADPSTAT. And that view is owned by SYS, not SYSTEM.

Where you see MISSING, the synonyms points to a non-existent object. Replace the object or drop the synonym.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.