Oracle Environment and SQL Extensions
In this module, you will look at the basic components that make up Oracle's database software and log into SQL*Plus, a tool for interacting directly with the database using SQL commands.
Tablespaces and the Structure of the Database
People who have worked with computers for any period of time are familiar with the concept of a file, which is a place on disk where information is stored and has a name. Its size is usually not fixed and if you add information to the file, it will grow larger and take up more disk space up to the maximum available. This process is managed by the operating system, and often involves distributing the
information in the file over several smaller sections of the disk that are not physically near each other.
The operating system handles the logical connection of these smaller sections without you being aware of this process. To the user, the file looks like a contiguous unit. Oracle uses files as a part of its organizational scheme, but its logical structure goes beyond the concept of a file. A datafile is an operating system file used to store Oracle data. Each datafile is assigned to a tablespace
which is a logical division within the database.
Tablespaces commonly include
- SYSTEM (for Oracle's internal data dictionary),
- SYSAUX (for auxiliary internal objects),
- USERS (for user objects),
and others for application tables, indexes, and additional database structures. The datafiles can have a fixed size or can be allowed to extend themselves automatically when they are filled, up to a defined limit. To add more space to a tablespace, you can manually extend your datafiles or add new datafiles. New rows can then be added to existing tables, and those tables may then have rows in multiple datafiles.
Each table has a single area of disk space, called a segment, set aside for it in the tablespace. Each segment, in turn, has an initial area of disk space, called the initial extent, set aside for it in the tablespace.
Once the segment has used up this space, the next extent, another single area of disk space, is set aside for it. When it has used this up as well, yet another next extent is set aside. This process continues with every table until the whole tablespace is full. At that point, someone has to add a new file to the tablespace or extend the files of the tablespace before any more growth in the tables can take place.
Every database also contains a SYSTEM tablespace, which contains the data dictionary as well as the names and locations of all the tablespaces, tables, indexes, and clusters for this database. The objects within the SYSTEM tablespace are owned by the SYS and SYSTEM users; no other users should own objects in this tablespace because they may impact the rest of the database.
By the end of this module, you will know how to:
- Identify the primary components of an Oracle database
- Name several data dictionary views and their purpose
- Log into the database with SQL*Plus
- Distinguish between standard SQL and Oracle's SQL extensions and describe when to use each
The first lesson looks at the Oracle database structure using a detailed diagram.