Re: Conceptual hang-up: Database v. tablespace v. schema v. etc...

You probably should read through the Oracle7 Server manual where it discusses architecture of Oracle to get a good reading on all this terminology. But, I will try to give it a shot for you and see if I can help without making you more confused than before <Smile>..

First, there is an Oracle instance. This is a complete install of all of the Oracle components, but you can think of it as one Oracle database SYSTEM comprised of the background processes and the System Global Area (SGA) and control files. When you issue a CREATE DATABASE, this is not really one big data file as you mentioned. An instance and a database have pretty much the same meaning. When you create a database, you are really just defining its components, its control files, giving it a name, etc. Don't get this confused with data files. That comes next.

Now, this one instance can have multiple tablespaces. A tablespace is nothing more than a logical entity for holding tables and the data in tables. The tablespace represents a chunk of disk storage. There can be numerous datafiles which make up a single tablespace, or there may only be one datafile defined for a given tablespace. These datafiles can be small or large, and can span different disk volumes. But the key here is to think of a tablespace as just a place of allocated disk storage to hold the tables, indexes, and data. The data dictionary also resides in tables and views in a tablespace, but that tablespace is always named SYSTEM and it's good to not mix in application tables and objects with it. You should create some application-specific tablespaces to hold objects for your applications. For example, if you have a HR system, you might want a tablespace called HRDATA and another called HRINDEX to separate table data and index data. You might also have some other tablespaces called USERS for general use by users, TEMP for temporary storage during sorts, and so forth.

Using your analogy, if I were setting up a library application (or database to be specific) I might do the following:

Create an instance of Oracle release 7.2.3 and call it PROD for production.  The DATABASE name is PROD in this case. Now, I need a schema to hold everything related to the library system. In Oracle, I would create a userid call LIBRARY which will be the owner of all the tables, indexes, views, etc for my library system. People would not actually log on as LIBRARY, that is just a userid established to own all these objects. I would create individual userids for the end users and grant them access to the LIBRARY user's objects. The LIBRARY userid is the same thing as a schema in this case. Now, I would create a tablespace to hold all the data. Let's call the tablespace LIBDATA. I'm going to create it with two datafiles just so I can spread the data across more than one disk volume. I can now alter the userid LIBRARY so that its DEFAULT tablespace is LIBDATA. Any time I create a table or index for LIBRARY now, it will be created in the tablespace LIBDATA. You don't need a different tablespace for things like books, borrowers, audio-visual, etc. Those would just be separate TABLES which can all go into the same tablespace. Or, if you wanted to, you COULD create some of these tables in different tablespaces if you really wanted to. The point is that you don't NEED to.

Okay, I'm happy now. I have my LIBRARY database all set up. Now, I decide I'd like to have a TEST version of all this too, and one that will not affect production if someone goofs up during testing. So, I could just create a new INSTANCE of Oracle and this time I will call it TEST. It can have all the same type components as PROD, but they will not be the SAME files and tables as PROD. They will just be copies residing in their own tablespaces and datafiles.

Article Unavailable Received on Thu Nov 07 1996 - 00:00:00 CST