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 only takes a minute to sign up.
Sign up to join this community
I did some research and finally came to this conclusion:
Database instance = processes + memory
Database = (physical) log files + control files + data files.
Thanks.
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.
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
