Concepts

An Oracle Instance

An instance is a running database. As soon as you start your database on your server, and Oracle allocates memory to something called the system global area (SGA) and starts some of the database processes, you have an Oracle instance . It contains all the parts you need to make an Oracle database operational, such as the system monitor, process monitor, database writer, checkpoint and log writer, the data files that contain the tables, the control files that contain configuration information, the redo log files and archive files, other configuration files, and security information.

This goes beyond your local server installation because the term instance can refer to an Oracle system that is a node on a network that shares files. Also be aware that there is only one database per instance. And here's some interesting trivia: An Oracle database is limited, in 8 i , to 4 petabytes (PB). The number of files per database varies by block size. A 2K block size supports up to 20,000 files, a 16K block size up to 65,536 files. If you're interested, I suggest you take a look at some of the more detailed technical information from Oracle, where system blocks, data blocks, extents, and segments are all discussed.

Tablespaces

A tablespace is an area of storage that has one or more data files that hold the data for tables, indexes, and all other database structures. Each Oracle instance usually has multiple tablespaces. For example, with every Oracle database, there is a tablespace called SYSTEM , and normally the DBA creates other tablespaces for applications, programming, and so forth. The SYSTEM tablespace is automatically created and holds the data dictionary and the location information for all the tables, indexes, other tablespaces, and so forth that make up this particular database.

One good practice is to create a temporary tablespace for sorts . Sorts are used by actions such as joins, the ORDER BY clause, the GROUP BY clause, and the ANALYZE function, and sometimes performance can be improved immensely if a temporary tablespace for sorts is available. We will use this feature later, when we are creating users for our database.

You can simply put everything (tables, user spaces, work areas, etc.) into the SYSTEM tablespace, but for practical reasons you will be creating many tablespaces. Usually there is a TEMP tablespace for tables that are not permanent. And in many cases it makes sense to assign individual tablespaces to each user or group of users. This is a way of controlling disk use, simplifying backup and restore operations, and protecting and balancing your system.

Just be aware that the term tablespace refers to a physical amount of storage space, and you will be slicing it up to meet your needs. Look at it this way:

Now you can see why it is important to spend a bit of time calculating table sizes, the number of users, and so forth. There is a limit to how much storage space each system has, and you must plan carefully so that your customers don't get error messages.

To sum up, a tablespace is a physical storage space, and each table in the tablespace is assigned a piece of that space called a segment . Further, each segment starts out with a certain size, called the initial extent , and grows according to what has been defined in the NEXT EXTENT clause. Yes, you can run out of room if the tablespace gets full. At that point you will have to increase the overall tablespace size, or if only one file in the tablespace has reached the maximum size, you can increase the space allocated to that one file.

Schemas

Within every Oracle instance are schemas that contain all the database objects, such as views, tables, indexes, sequences, and so forth. Now, and this is important, each schema in a database belongs to a user. By default, when you create a user, that user gets his or her own schema, and the schema has the user's name . Everything the user creates will reside in his or her own schema.

Here's what you have to know: Database objects that are created in a schema can be accessed only by the user who owns them, or by users who have permission to access them. The syntax for accessing tables in a schema other than your own (except for the SYSTEM schema), is schema_name.table_name ”for example, Guerrilla_Schema.Test_Questions .

As you can see, a schema is another way to protect tables, views, and other objects from unauthorized access.

Synonyms

A synonym is another name for a table or view. All tables and views belong to a schema, and they must be identified by that schema unless it is the SYSTEM schema, remember? Always having to identify tables and views by their schemas can become unwieldy, so synonyms are created. Usually the DBA creates public synonyms for tables and views that everyone will use, and if you have access to someone else's tables and views, you can also create synonyms for yourself for those objects.

Oracle Home

An Oracle home is where all the software will reside. You can have multiple Oracle homes, each one containing a release of the same Oracle products. In other words, you could have both a test version of release 8.x.x or 9.x.x, and a production version of 8.x.x or 9.x.x, on the same server. Or you might have an Oracle 7.x database already installed on your server. Each release will have its own Oracle home. You will have to know the paths to the various Oracle homes when you have multiple Oracle installations on your server so that you can connect to the database you want. Otherwise you will be connected to the most recently installed database.

Transaction Processing

Transaction processing is important because Oracle has developed techniques to prevent half-completed transactions in case of a system or program failure. The transaction is written to a series of files, buffers, and logs, and if anything problematic happens along the way, the system rolls back the activity to prevent incomplete updates from occurring. In other words, if you moved $100,000 from your savings account to your checking account, and the money was deposited in your checking account before it could be debited from your savings, and the system went down, Oracle would back out the transaction as soon as the system came back up. Otherwise you would have an extra $100,000 that you would have to return. Oracle works on the "all or none" idea. If either of the two transactions fails ”the deposit or the withdrawal ”then the entire process is backed out.

System Identifier

Each Oracle instance must have a unique name, and this name is called the system identifier , or SID .