Currently I have a scenario that involves switching a synonym definition after the completion of a scheduled job. The job will create a table with an identifier of `even` or `odd` to correspond with the hour being even or odd. What we are currently doing is this: 

```
odd_job:  

create foo_odd ...  
replace foo_syn as foo_odd

```

and 

```
even_job:  

create foo_even ...  
replace foo_syn as foo_even

```

What is happening is that during normal production the `foo_syn` is in a locked state. So what we are looking for is a production capable way of swapping synonym definitions. 

The question is how can we swap a synonym definition in a production level system with minimum user interruption in Oracle 10g?

**From the comments** 

> 
> Does foo\_syn have any dependent objects?
> 
> 
> 

No `foo_syn` is nothing more than a pointer to a table that I generate. That is there are no procedures that need to be recompiled for this switch. 

> 
> That sounds like a really strange thing to do. Can you explain a bit
>  what that switch is for/how it is used?
> 
> 
> 

Sure. We have an application that interfaces with the database, the SQL that is executed from Java (business logic queries) has a reference to `foo_syn`. Because of the dynamic nature of the data it is a guarantee that the hourly swap will give new results that are important as we try to get closer to real time. Prior to this it was a once a day and be happy with it type scenario. 

The reasoning for the swap is I do not want dynamic SQL (in terms of table names) to be a part of my application queries. So therefore the database does a switch on the newer data set without changing the name of the synonym that is referenced as part of my application.