Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I did some research and finally came to this conclusion:

Database instance = processes + memory

Database = (physical) log files + control files + data files.

  1. Is this understanding correct?
  2. Can an application use multiple database instances of the same database?
  3. Can a table in database be used by multiple database instances at the same time?

Thanks.

share|improve this question
1  
This distinction ("Database Instance" vs. "Database") only applies to Oracle, AFAIK, and is related to Oracle's implementation model. –  RBarryYoung Jul 8 at 20:53
1  
Not really, take Firebird for example. They allow you to create multiple instances on the same computer and name them (FB 2.5 and above, at least). Mysql also. Both of the above can access multiple databases with one instance. –  Kitet Jul 8 at 21:02

2 Answers 2

up vote 1 down vote accepted

I'm guessing that you're talking about Oracle here. See my reply to this question about the difference between a schema and a database in Oracle. See also my comment in reply to Phil Sumner about using tables that aren't in the same schema - yes it's possible, but not an ideal solution.

[EDIT in response to OP's comment]

Yes, what you have said is exactly how it works in an RDBMS - which is what Oracle, MS Server and MySQL are (quibbles about not being _true_ RDBMSs notwithstanding).

To answer your question in the terms in which you posed it:

You said:

Database instance = processes + memory

Yes, an "instance" can be seen as this - it's primarily Oracle terminology. Others may call it the "server" or (unfortunately!) the "database". It is best to distinguish between the instance (in cpu/memory structures) and the physical files).

Database = (physical) log files + control files + data files.

A database (in the modern RDBMS sense) is what you have written, except you missed out the actual data (+ log files, control files, initialisation parameter (normally text) files.

Unfortunately, this is where things get very confused in modern terminology.

I would use (if I were trying to be very clear) the term "system" (or even running system) for the software (instance) + the database (files on disk).

Just be aware that these terms can be abused and mangled and there is no one prevailing definitive term accepted in general use.

To answer this

Correct me if I am wrong, tables are created and stored in databases and a database instance uses these tables present inside database to do insert, delete, query, etc.

You are absolutely correct in this - at least for RDBMSs.

share|improve this answer
    
This is indeed my understanding from reading about oracle database. But here I am talking about databases in general. Correct me if I am wrong, tables are created and stored in databases and a database instance uses these tables present inside database to do insert, delete, query, etc. –  AKL Jul 10 at 18:09
    
So the terms are abused which is why I am confused almost every time someone tries to explain with these terms. They use terms at wrong places unknowingly or knowingly. Thanks for the help :D –  AKL Jul 12 at 6:28

Technically instance resides in memory and database (physical files) resides on disk. There can be an instance without a database (an example is a started instance right before you create a database). There also can be a database without an instance (example of this is database files residing on disk but instance is not running, or files copied to some other location, something along those lines). Typically you use database by connecting your client to it's instance in order to do some work on data contained within. So an instance, upon startup, associates itself with one or more databases (Firebird, for example - one server process can be used to connect to multiple databases).

Second question: Only in some High-Availability environment, namely RAC on Oracle, where multiple instances are clustered to service the same database. This is transparent to end-users, so practically no, it can't. If you're talking about non-clustered configurations then no - no two instances can use same set of database files, therefore no application can use more than one instance connected to the same set of database files. At least not in RDBMS I know about. However an application can, theoretically, use two instances servicing two distinct copies of the same database, why not. Only I can't see real-world reason to do so.

Third question: already answered. No database can be serviced by two instances unless it's some kind of HA environment.

EDIT about object types

This is what resulted from a query of select distinct object_type from dba_objects to my Ora 10g, it surprised even myself:

CONSUMER GROUP, INDEX PARTITION, SEQUENCE, QUEUE, SCHEDULE, TABLE PARTITION, RULE, PROCEDURE, OPERATOR, LOB PARTITION, WINDOW, LOB, PACKAGE, PACKAGE BODY, LIBRARY, RULE SET, PROGRAM, TYPE BODY, CONTEXT, TRIGGER, JOB CLASS, UNDEFINED, DIRECTORY, MATERIALIZED VIEW, TABLE, INDEX, SYNONYM, VIEW, FUNCTION, WINDOW GROUP, CLUSTER, TYPE, RESOURCE PLAN, EVALUATION CONTEXT, JOB

share|improve this answer
    
From your reply, I am thinking if database and tables are one and the same. –  AKL Jul 10 at 18:05
    
Database consists of OBJECTS, examples of which are: tables, views, stored procedures, packages, functions, users, triggers, generators (sequences), indexes, constraints (e.g. keys) and many, many more object types. –  Kitet Jul 10 at 23:52
    
Nice! Thanks for the support. :D –  AKL Jul 12 at 6:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.