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
begin
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;
end;
/

–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’?
    Declare
    l_stmt Varchar2(100);
    Begin

    For r In (

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

    )
    Loop

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

    Exception
    When Others Then
    dbms_output.put_line(Sqlerrm);
    dbms_output.put_line(”);
    End;

    End Loop;

    End;

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

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

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

    Regards,
    Satender

  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.
    KR,
    St0ma

Comments are closed.