Home > Database, Oracle > Compile Invalid SYNONYM Oracle

Compile Invalid SYNONYM Oracle

January 11th, 2010

Happy New Year!
Wish you all the great stuff you have been dreaming about to come true in 2010!

I haven’t posted for a long time but my excuse is the busy season at work. At the end of the day I am finding a minute to share a simple but useful hint on generating the COMPILE SQL for invalid PUBLIC SYNONYM objects.

Every time you drop and recreate an object such as a SEQUENCE, the PUBLIC SYNONYM for that objects invalidates (if such exists). In order to generate the COMPILE commands you could use SQL similar to the following two examples:

1. Using SELECT:

-- execute the following statement as sys user
select 'alter public synonym '||object_name||' compile;' from all_objects where status='INVALID' and object_type='SYNONYM';
--copy the results and execute them as sys

2. Using LOOP

— execute the following statement as sys user
for MANIA in (select ao.object_name from all_objects ao where status ='INVALID' and object_type='SYNONYM') loop
-- execute alter public synonym seq.object_name compile;
dbms_output.put_line('alter public synonym '||MANIA.object_name||' compile;');
end loop;

–copy the results and execute them as sys

The result in both cases looks similar to the following:

alter public synonym MAN1_SEQ compile;
alter public synonym MAN2_SEQ compile;
alter public synonym MAN14_SEQ compile;
alter public synonym MAN7_SEQ compile;

It would have been nice to execute the ALTER command directly in the FOR LOOP but unfortunately I could not get a DDL to work inside it.

If anyone knows a better way of doing this I would appreciate a comment.

Greetings from Sao Paulo, BR

— S

Categories: Database, Oracle Tags: , , , ,
  1. MD
    February 16th, 2010 at 00:56 | #1

    — Maybe you can use ‘Execute Immediate’?
    l_stmt Varchar2(100);

    For r In (

    Select *
    From dba_objects
    Where status != ‘VALID’
    And object_type Not Like ‘%BODY%’
    Order By owner


    l_stmt := ‘ALTER ‘ || r.object_type || ‘ “‘ || r.owner || ‘”.”‘ || r.object_name || ‘” COMPILE’;
    dbms_output.put_line(l_stmt || ‘;’);
    Execute Immediate l_stmt;

    When Others Then

    End Loop;


  2. December 14th, 2011 at 20:37 | #2

    Use below ananymous block for better error handling and performing the task in a well manner.

    for r in (select ao.object_name from all_objects ao where status =’INVALID’ and object_type=’SYNONYM’)
    execute Immediate ‘alter public synonym ‘|| r.object_name ||’ compile’;
    dbms_output.put_line(‘Alter public synonym ‘||r.object_name||’ compile.’);
    When Others Then
    dbms_output.put_line(‘Public synonym ‘||r.object_name||’ still invalid.ERROR :’SQLERRM);
    end loop;


  3. SV
    June 22nd, 2012 at 14:45 | #3

    Please include the clause owner = ‘PUBLIC’ or you will be creating public for private synonyms too.

  4. admin
    July 15th, 2012 at 03:50 | #4

    Hi SV,
    My examples generate only ‘alter public synonym’ commands which could be executed only by the SYS as SYSDBA user and privilege and only affects invalid public synonyms.

Comments are closed.