Database Terminology in Oracle

The terminology is inevitable, so lets go over some of the most commonly used terms. These are the terms you will need to understand in order to read Oracle texts or get assistance from others more familiar with Oracle. Some of the terminologies which are frequently used in Oracle are explained in brief in this article. A beginner in oracle at times are confused and lost with some basic terminologies used in oracle or even other databases. DBA: Stand for database administrator. Everyone has their own idea of what a DBA does, and many articles have been published on this single topic. A DBA is usually involved in the logical design of a database, performs the physical design and implementation, assists developers with difficult database issues, and is responsible for ensuring good performance, proper security, and backup and recovery strategies.

This Article Summarizes few of the most commonly used database terminologies, it is beyond the scope of this article to cover all the database terminologies of oracle and to explain each of the following terminologies in detail.

Database: In the Oracle arena, a database is a collection of operating system files that make up one physical data store or node. There can be many, many different types of data, each with its own attributes, stored in one database. Its not uncommon for users of simple desktop database products to think of a database as one collection of records, all with the same attributes. In Oracle, we would call that a table, not a database.

Database Name: Every Oracle database has a name, typically eight characters or less. There is also a global name, which is the database name with a domain suffix. The default domain is .WORLD which is fine for most purposes. In small to medium sized shops, its a good idea to give each database a unique name and use one common domain.

Instance: An instance is a set of operating system processes and shared memory structures that allow an application to access data in a database. Database applications never access an Oracle database directly. Rather, they communicate with Oracle server processes that perform all database access on an applications behalf. The processes that make up the instance are responsible for such tasks as writing updates to disk, coordinating distributed transactions with remote instances, and rolling back incomplete transactions when an application crashes.

Note that when a database is accessible to applications, it has an instance. In most environments, there will be a one-to-one ratio between databases and instances. But they are not the same thing and the two terms should not be used interchangeably. When Oracle Parallel Server is used, multiple Oracle instances (each running on a separate physical server) will access one common database.

Instance Name: Every Oracle instance has a name. SID (standing for system identifier) is another term for instance name. On Unix platforms instance names can be up to eight characters in length. The purpose of the instance name is so that applications may identify which instance they wish to access when multiple instances are running on one server. (Consider a machine with four Oracle databases and four instances.) When Oracle Parallel Server is not being used, the convention is to give the instance the same name as the database it accesses. This will help preserve your sanity, although it is technically not required.

SGA: The SGA, or system global area, is a collection of shared memory structures created by an Oracle instance. The SGA includes a cache of most recently accessed data blocks, a cache of most recently executed SQL statements, latches for implementing locking mechanisms, among many other things. The SGA is implemented on Unix platforms as a collection of shared memory segments. Each process of the instance, as well as each server process used by applications to access the database, attaches itself to the SGA upon startup.

Starting the database: You open an Oracle database to applications by starting up an instance and mounting it to the database.

SQL*Net: If an application wants to access an Oracle database via an instance running on the same machine as the application itself, an Oracle server process will be started and the application will communicate with the server process through traditional interprocess communication (IPC) mechanisms. But if the application needs to access a database running on another server, then Oracle's networking infrastructure gets involved. Historically, this infrastructure was called SQL*Net. But when Oracle introduced Oracle8, they changed the name to Net8. Think of them as the same thing, although Net8 is newer and has some additional features.

Client/Server: Oracle internally uses a client/server architecture. Even if you are not developing a fat client application using the client/server model, there still is a client/server architecture at the database level. The application wishing to access Oracle is seen as a client, and the Oracle server process is a server. The client sends requests to the server, and the server satisfies them.

Username: Oracle supports many authentication mechanisms, but the most common is a standard username and password mechanism. Each user in an Oracle database has a unique name up to 30 characters in length. Separate usernames in the database, of course, don�t have to map to separate real-life users. Twenty different application developers could sign on to a database using the same username and password. Conversely, one person could sign on to a database five times, each time specifying a different username and password.

Schema: A schema is a separate namespace for database objects such as tables, indexes, and stored procedures. Every database object resides in exactly one schema. Each database user has exactly one schema that bears the same name as their username. This makes the term username synonymous with schema.

Data Dictionary: Each Oracle database has a repository of information indicating all of the users defined in the database and all of the objects they own (tables, indexes, stored procedures, and so on). The data dictionary is actually a set of database tables with names like user$ and tab$. You should never update these tables manually, but it's fine for you to query them if you like.Data dictionary is basically a meta data of data which is most frequently used by Oracle DBA's for various DBA work.

SYS and SYSTEM: Every Oracle database has two special users, SYS and SYSTEM. Think of SYS the way you think of root on a Unix system. The SYS user owns all of the internal structures that make up the data dictionary for the database, and the SYS user also has every possible privilege on the entire database. The SYSTEM user, meanwhile, has DBA privileges and is often used as a generic DBA account.

Control Files, Data Files, Redo Logs, and Parameter Files: These are the physical files that make up an Oracle database. details for each of these physical file is beyond the scope of this article.

Tablespace: A tablespace is a named collection of one or more physical files used for storing database objects. Tablespaces mask certain implementation details from application developers. For example, a DBA can rename a data file without affecting the name of a tablespace, or an application developer can specify that certain data should be stored in a particular tablespace without needing to know if the data will be stored in one file or striped across several files.

Blocks : Smallest unit of storage for data in oracle database

Extent: An extent is one contiguous chunk of physical storage within a tablespace. An extent can vary in size from 2 Kb to 2 Gb, as long as the storage is contiguous.

Segment: A segment is a collection of extents that belong to one object. Essentially, a segment is the physical storage used to hold the data for an object. Most segments in a database will hold the contents of a table or an index, but there are other types of segments as well.

Rollback Segment: A rollback segment stores information for a transaction that is still in progress. This allows Oracle to back out the transaction if the application requests a rollback. Rollback segments also allow Oracle to offer incredible concurrency by enabling one session to update data while another session is querying the very same data the querying session can use the data in the rollback segment to reconstruct what the data looked like before it was updated.

Temporary Segment: A temporary segment holds a partial result set when too much data is involved to complete the operation in memory. Temporary segments are most commonly used when sorting data for ordering, grouping, or building an index.

Identifier: Most identifiers in Oracle whether names of users, tablespaces, tables, indexes, or even PL/SQL variables are limited to 30 bytes in length and are case-insensitive. To make an identifier case-sensitive, or to use an Oracle reserved word as an identifier, enclose the identifier in double quotes. Unless quoted, identifiers must start with a letter and may include letters, digits, and certain (but not all) special characters like _, $, and #.