Oracle 10g and Oracle database instance - difference?

Question

I am getting confused with the terms database and database instance. They was I see it is that it is possible to have a single Oracle 10g database, but create seperate instances or table spaces within this single database. I would be grateful if someone could cofirm.

Many Thanks

Answer 1

The database is where you create and store you tables. To store tables and data you need disk space and Oracle lets you allocate that in special chunks called table spaces. You decide if you want many smaller ones or a lesser number of bigger ones.

The instance is a set of background processes that allows you to connect and work with the tables, much like a browser lets you explore a web site.

In most cases you will have one instance per database. However, in large production installations you can have multiple instances per database. An instance has its limitations, for example in there is a limit to how many users can be concurrently connected and still be able to get good performance. So, you may want to create more than one instance and have your Boston office work with Instance A and your Chicago offise use instance B. Instances usually live on separate machines. This feature was known before as Oracle Parallel Server (not to be confused with parallel query), and is now part of the so called "grid" technology.

It is very comon for IS people in leadership positions to use the word "instance" as a synonym for "database". Like, "we need to create a new test instance" - when they really need a new test database. There is a perception among some that "instance" is the new term for "database". When you hear this from a $200 an hour Big Five consultant - that is your clue that they don't have a clue.

Answer 2

The database contains tables and other objects including the data dictionary. These are stored in table spaces. There will usually be several TSs in a database for performance reasons.

The instance is the memory areas and background process like DBWn and LGWR, SMON, PMON and so on. You will only have one instance per database.

Try reading this
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10742/instance.htm
managing the Oracle instance