Re: Is there a wildcard for synonyms?

I don't think you will have to do anything. Look up the following topic in Oracle documentation - "How Oracle Resolves Schema Object References".

When you refer to an object in a SQL statement, Oracle considers the context of the SQL statement and locates the object in the appropriate namespace. If your code references an unqualified table, view, procedure, function or package, Oracle will look for the refeferenced object in the following order:

• A table in your own schema
• A view in your own schema
• A private synonym for a table or view
• A public synonym

Make your private copies of the development schema's tables and go for it.

No, CREATE TABLE ... AS SELECT won't capture all the constraints. Perhaps use export/import.

mehaase_at_gmail.com wrote:
> I have a schema for development which is shared with several other
> people. I'm writing some code which reads a bunch (maybe 30-40) of the
> tables and uses other things like types, sequences, stored procedures,
> etc. I'd like to be able to run this code in a private schema...so I
> need to create synonyms to all the objects...but there's several
> hundred of them. Is there some kind of wildcard schema that just says
> "look first in my schema and if you don't find the object there then
> look in this other schema?"
>
> Then...I will be inserting/updating to a handful of tables (6-7). I'd
> like not to alter the development schema's tables at all, so I'm
> planning to make private copies, as in
>
> CREATE TABLE test AS SELECT * FROM DEV_SCHEMA.test;
>
> etc...
>
> But this won't capture any constraints on the table, will it? Is there
> a way to copy a table such that you keep all of the contraints,
> indices, etc.?
Received on Fri Jun 02 2006 - 01:17:36 CDT