How to fix these broken synonyms

oracleoracle-11g-r2synonyms

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?

Best Answer

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.

Related Solutions

SELECT statement with an explicit list of columns and then an asterisk (*)

Only if you prefix the asterisk with the table name:

select dummy, dual.* from dual;

DUMMY DUMMY 
----- ----- 
X     X

Which limits it's use to tables not joined with the using syntax:

select dummy, d1.* from dual d1 join dual d2 using(dummy)

SQL Error: ORA-25154: column part of USING clause cannot have qualifier
25154. 00000 -  "column part of USING clause cannot have qualifier"
*Cause:    Columns that are used for a named-join (either a NATURAL join
           or a join with a USING clause) cannot have an explicit qualifier.
*Action:   Remove the qualifier.

select d2.dummy, d1.* from dual d1 join dual d2 on(d1.dummy=d2.dummy);

DUMMY DUMMY 
----- ----- 
X     X

ORA-01031 Insufficient Privileges Raised From Within Function

When you have permission to perform an action at the command line but not within a definer's rights stored procedure (and I hope you're using a stored procedure here, not a stored function), the problem is almost certainly that the privilege you need has been granted through a role, not directly to the user that owns the procedure. Inside a definer's rights stored procedure, only privileges that have been granted directly to the owner of the procedure can be used-- privileges granted through a role (such as DBA) cannot be used.

You can verify that the problem is, in fact, that the privilege is granted through a role by disabling roles and then trying to create the user, i.e.

SQL> set role none;
SQL> create user ...

Assuming that fails, as I expect it will, you would need to grant the owner of the function the CREATE USER privilege directly

SQL> grant create user to <<owner of procedure>>

Then, you should be able to create users even with roles disabled and the stored procedure should work.

Best Answer

Related Solutions

SELECT statement with an explicit list of columns and then an asterisk (*)

ORA-01031 Insufficient Privileges Raised From Within Function

Related Question