>>CREATE PUBLIC SYNONYM MyTable FOR SCHEMA.MyTable
>>
>>The MyTable synonym MUST be unique in the entire Oracle database across all schemas. So if Schema1 has a MyTable1 and Schema2 has a MyTable1, a MyTable1 public synonym can only point to one of them.
>
>Every example I looked at uses no schema prefixes - they just connect and fire away at select * from customers. So how are they doing this? Would this be a database that only has a single (default schema) then? But that can't be it either since they are uysually using sample data...

They are either connecting as the owner of the tables or using public synonyms for all the tables.

>Is there no equivalent of
>
>USE

No equivalent.

>The above Create Synonym would be a major hassle as you'd have to do this for each table. Is this a per connection thing or a permanent admin thing?

Yes you would need a synonym for each table, but this is a one shot deal that only has to be done once. Just about every command I can think of requires the granting of a privilege to be able to run any type of command. Either the DBA has to run a script to create all the synonyms for you or s/he has to grant the CREATE PUBLIC SYNONYM privilege to your User ID or grant you a Role that in turn has been granted this priv.

>>Each user can have a set of their own [private] synonyms that can point to any table in the Oracle database as long as that user has SELECT privs on the table. These synonyms only have to be unique to that User's ID.
>>
>>Usually, the Oracle DBA will limit your ability to create a public synonym as this could screw up existing synonyms.
>>
>>>The terminology of Oracle is really getting me here. Schema is what would be the equivalent of a SQL Server database?
>>
>>Kind of. A schema is really just a [generic] User ID created for a specific app to be the owner of all the objects, including tables, created under that ID. The biggest difference is all of this is contained under the Oracle instance and can not be "detached" from the instance and "attached" to another Oracle instance. You have to perform an export function to get a "dump" file of all the objects and data. Then you can import that file into another instance of Oracle as long as any Roles defined in the original instance already exist in the new instance.
>
>This really sounds like big fun. THey really did a great job on usability on that one <g>...

Yeah, it can be a real bear exporting from one version of Oracle and importing into a different version.

>I have problems with everything in the admin module. Can't create a new instance - it doesn't accept my passwords even though they are valid. Can't figure out how to add a new 'schema'...

This is really nothing than CREATE USER ...

What really usually happens is the DBA will create a new tablespace for the app. This tablespace is just an allocated chunk of space on the disk that is reserved by an actual file [usually ends in DBF of all things].

To do all of this still requires the DBA grant you the necessary privs.

>I can see why Oracle is such an expensive solution and kept that way on purpose...

If you can get your hands on Personal Oracle, you can at least install it on your own PC for development and grant yourself DBA privs so you can see what you really need to do without all the privileges roadblocks.

When you have to do repetitive tasks on numerous objects like tables, it is real easy to create self-writing script files that will then do the tedious work for you. In the SQL Plus utility [command line access to the Oracle DB], you can create a script file to create all the synonyms for you:

set head off
set feedback off
spool C:\MyOracleScriptFiles\Cr8_synonyms.sql

select 'create public synonym ' || table_name ||
' for userid.' || table_name || ';'
from user_tables order by table_name;

spool off

-- the next line actually runs the script you just created:

@C:\MyOracleScriptFiles\Cr8_synonyms.sql

set feedback on
set head on

The next problem is figuring out all the tables and views available to you to help get this kind of info out of Oracle.

Mark McCasland
Midlothian, TX USA