oracle schema and instance

[ben oliver]

Hello, Want someone help me understand --

1. Schema and instance, which one is at the higher level ? e.g. does schema include several instances or vice versa ?

2. For a connection url jdbc racle:thin:@localhost:1521:ABC

is "ABC" called schema name or instance name ?

Thank you.

[Paul Campbell]

Originally posted by ben oliver:
Hello, Want someone help me understand --

1. Schema and instance, which one is at the higher level ? e.g. does schema include several instances or vice versa ?

2. For a connection url jdbc racle:thin:@localhost:1521:ABC

is "ABC" called schema name or instance name ?

Thank you.

The instance is the instance of the database and the database instance may contain one to many schemas.

[Chris Hendy]

To be finicky:

A database is a collection of physical operating system files (parameter files, data files, redo log files, control files, temp files, password files). A database is identified by its SID (system identifier - a name chosen on creation - usually describing its purpose e.g. 'LIVE', 'QA', 'DEVEL').

An instance is a set of Oracle processes (on unix / linux this will be pmon, smon, lgwr, dbwr etc - on windows these will be threads in oracle.exe) and an SGA (a shared memory structure containing the Fixed SGA, the shared pool, redo buffer, block buffers etc).

More than one instance can mount and open the same database at the same time (Oracle RAC for example, or old style Oracle Parallel Server). An instance can only mount and open one database at a time.

Most oracle users tend to think of a Database and an Instance as interchangeable terms - fair enough for normal conversation as long as you remember the real definitions - especially if working in a clustered environment or talking to a DBA.

A Schema is all the database objects created by a database user (e.g. tables, indexes, database packages). A database can contain many schemas.

The "ABC" of your question is the ORACLE SID of the database.
[ October 28, 2007: Message edited by: Chris Hendy ]

[Paul Campbell]

That's true Chris... I was trying to keep it simple... literally instance is the complete database environment, including the RDBMS software, table structure, stored

[ben oliver]

Then I am more confused. I found that in my jdbc code, I never specifies the "schema", I only specified the instance. How could that work ? As many beginners I am using Oracle 10g XE version and found it is very cool.

My second question is -- in most of the java jdbc sample codes, it specifies the connection as follows

String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc racle:thin:@localhost:152:abc";
String username = "scott";
String password = "tiger";
Class.forName(driver);
DriverManger.getConnection(url,username, password);
...

So they never specify the "schema". How come ???

Thanks.

[Chris Hendy]

You are connecting as user scott, so you have access to scott's schema. Also if user scott has been granted access to another schema then your connection can make use of that schema as well.

So if you created the demo tables emp and dept as user scott, and your jdbc connection is as user scott, then you can select/insert/update/delete the emp and dept tables in scott's schema.

As a slightly more realistic example (and there are other ways of doing this) say an application consists of tables/indexes and stored packages/procedures/functions. A oracle user APPOWNER is created, and as the APPOWNER user the application tables/indexes - yada yada are created. An oracle user scott is created as an application user. Scott doesn't create any scema objects of his own. Scott is granted select/insert/update/delete on APPOWNER's tables and execute on APPOWNER's stored code objects. Soctt can access the application tables and code by qualifying it with the schema name (insert into appowner.app_table...) or public or private synonyms are created for APPOWNERS objects so scott can just say INSERT INTO app_table.