DataBase Management System (DBMS) controls the storage, management/organization, and retrieval of data. DBMS consists of three major parts:
- Kernel code
- Data dictionary (metadata)
- Query language
The most widely known and used model of any database is the relational model. The relational model is built on three major pillars: structures, operations, and rules. This is an integral part of RDBMS (Relational DBMS) which finally extends to ORDBMS (Object-relational DBMS). Oracle is an ORDBMS that allows users to store complex business objects in a relational database such as user-defined types, inheritance, and polymorphism.
Every DBMS should be capable of:
- Storing, retrieving and manipulating (updating) data
- Supporting transactions
- Allowing users to access the data dictionary
- Ability for recovery
- Managing and securing access to data/data manipulation
- Enforcing data integrity by setting rules
Kernel code
Kernel code is responsible for memory management and data storage. Whatever happens in the DBMS – Kernel code is involved. You can learn more about what happens in the DBMS during query execution in the article about Oracle Architecture.
Data dictionary
The data dictionary is a key part of any DBMS. It contains the definition of all objects in the database (e.g. tables, views, indexes, procedures, and others), information about space allocation and usage by objects, users and their roles and privileges, integrity constraints and much more. This information is read-only and structured in tables and views. Thanks to their complexity they are used from end users to database administrators. Oracle updates these dictionaries regularly to reflect all changes in the DBMS (new objects, privileges, users, ….).
There are three main categories distinguished by a prefix as shown in the table below.
| PREFIX | Comment |
|---|---|
| user_% | all object in user's schema |
| all_% | extended view of object what user can access |
| dba_% | database admin's view - all objects |
Dual table
The dual table is a small table in the data dictionary that users can refer to. It is usually used to generate a set of sample data, return function results, etc in order to prevent querying any database table. There is only one column “DUMMY” containing the value of “X“.
Examples of usage:
/* to get the content of DUAL table */
SELECT * FROM dual;
/* to get the system datetime */
SELECT sysdate FROM dual;
/* to trim leading zeros from the given string */
SELECT ltrim('000123','0') FROM dual;
/* to generate a date range */
SELECT DATE '2000-01-01'+level FROM dual CONNECT BY level <= 30;
* curious what does the LTRIM do or what does CONNECT BY mean? Read the article about Single-row functions
Dynamic Performance Views
Oracle maintains set of virtual tables that reflect current database activity. These tables start with V$ and that is why they are sometimes referred to as V$ views.
Users can find there information such as:
- Memory usage
- Session information
- System information
- Statistics
- SQL executions
- and many more system information
They are mostly used for performance monitoring and tuning.
Query Language
The query language is a language used to access or manipulated data; commonly referred to as Structured Query Language (SQL). The purpose of SQL is to provide an interface for users and allow to them to operate inside an Oracle database. Having said that, SQL statements (or queries, if you will) are just instructions for Oracle.
There are three major types of SQL commands
- DDL – Create, Alter, Drop, Truncate, Comment, Rename, Grant, Revoke, Analyze, Audit, Flashback, Noaudit, Purge, Undrop
- DML – Select, Insert, Update, Delete, Merge, Call, Explain Plan, Lock Table
- TCL – Commit, Rollback, Savepoint, Set Transaction
Quiz: Where can you find a list of all database objects?
Answer: