SELECT * FROM USERS WHERE CLUE > 0

July 27, 2005 8:58 AM

I'm playing (oracle) DBA for a day over the next two weeks and need to create public synonyms for all tables in a schema. How would I go about doing so?

I'm a developer and don't usually do more than basic DML and stored proc stuff. I am familiar with the following syntax:

CREATE PUBLIC SYNONYM employees
FOR hr.employees@sales

But I need to do it for all tables in the schema and don't feel like doing it by hand for the 4000 tables we've got. Google just gives me a lot of reference material :-( There must be a way to use a select with the data dictionary and then generate all the sql required, or something built in. Any suggestions? P.S. I have access to the Enterprise Manager.

posted by furtive to computers & internet (7 comments total)

Here's a quick-and-dirty script. I'm in the middle of a DB stress test now, I'll answer any questions once I'm done. Do you want synonyms for the views/procs/etc?

set lines 300 pages off

spool syns

SELECT 'CREATE PUBLIC SYNONYM '||table_name||' FOR '||owner||'.'||table_name||';' cmd
FROM ALL_TABLES
WHERE OWNER='HR';

@syns.lst
posted by neilkod at 9:56 AM on July 27, 2005

whoops, i missed your db_link. Sorry.

SELECT 'CREATE PUBLIC SYNONYM '||table_name||' FOR ' ||owner||'.'||table_name||'@sales;'
FROM all_tables@sales
WHERE owner='HR';
posted by neilkod at 9:59 AM on July 27, 2005

Ok, stress test is over ;) I can talk now.

The select statement queries all_tables@sales, which will show you which tables on the sales db you can see. We'll limit all_tables@sales to the tables owned by 'HR'.

The SELECT clause generates a CREATE SYNONYM command for each table owned by hr on the sales database.

the set commands at the top format your sqlplus output. It will allocate 300 characters per line, which is more than enough, but just a forced habit on my part. Setting pages(ize) to zero will eliminate page breaks.

The spool syns command tells oracle to write output to syns.lst. I also forgot a spool off command, but the script will run without it(hey I was busy!!!).

Finally the @syns.lst command tells sqlplus to execute the contents of syns.lst, which is a bunch of create SYNONYM commands.
posted by neilkod at 10:09 AM on July 27, 2005

neilkod, I'm just curious... Why did you choose to generate all the CREATE PUBLIC SYNONYM statements in a file, and run them at one go, instead of dynamically generating & executing them in a pl/slql block?
posted by of strange foe at 11:32 AM on July 27, 2005

because i'm old school?

God-forbid he's pre-8i and had to use dbms_sql?

Actually that's a good point, of strange foe.

I dont know, I always seem to use this method myself instead of execute immediate(). On the other hand, I maintain multiple instances and I like having things written out to files, but that's just me.

furtive, email me (its in the profile) if you'd like the dynamically-generated/executed version.
posted by neilkod at 11:56 AM on July 27, 2005

God-forbid he's pre-8i and had to use dbms_sql?

Heh, and here some are hoping 9i will go away someday.

By the way, nice title, furtive.
posted by of strange foe at 12:40 PM on July 27, 2005

I have the T-Shirt.
posted by neilkod at 2:21 PM on July 27, 2005