Oracle DB consists of two layers, physical and logical. The way data is physically stored does not affect access to the logical structure, because the physical and logical layers are separate. The physical layer is the part accessible from the operating system. These consist of data file, control file and log file. The logical layer consists of schema objects one or more tablespaces, tables, views, sequences, synonyms, indexes, DB links, procedures, functions, and packages. Although the physical layer can be accessed by the operating system, the logical layer can only be accessed by connecting to Oracle and executing SQL commands. The relationship between the physical and logical layer is shown below.
Physical Layer : An Oracle DB is physically composed of one or more data files, two or more log files, and one or more control files. The physical structures used in Oracle DB are summarized in the following section.
1.Data Files : Oracle DB can contain one or more data files. Data files are files that hold all the data in the DB. Data in logical DB structures such as tables and indexes are physically kept in data files. A data file has features that can increase its own space when the space allocated for it is full.
2. Log files : The purpose of log files is to save all changes made to the data. A ReDo log consists of ReDo records. The main task of the ReDo log is to keep track of all changes made to the data.
3. Control file : Oracle DB has a control file. The control file contains information about the DB, such as the physical structure of the DB, its name, data files, the name of the log files and their location on the disk, and the creation date of the DB. Control file is also used to recovery DB.
Logical Layer : The logical layer of Oracle DB includes tablespaces, schema objects, data blocks, extensions, and fragments.
1.Schema and Schema Objects : Schema objects are known as logical data storage structures. A schema is owned by a DB user, and that schema has the same name as that user. These structures, which are defined for the user to perform certain tasks on the DB, are tables, views, synonyms, indexes, DB links, procedures, functions, and packages. A schema is a group of these objects.
2. Tables : All data to be accessed by users are stored in tables. Each table has a name and each consists of rows and columns. Each table consists of at least one and at most 256 columns.
3. Views : The views are a virtual table created by taking the desired fields from one or more tables with the prepared queries. Operations such as deletion or updating cannot be performed on an image.
4. Materialized Views : A normal view does not take up space in the DB. Every time the query is run, the required operation/calculation is done again.
If your query takes minutes, hours or even days to produce results, these queries can be inserted into temporary tables. Here materialized view also works like a temporary table. It runs the query at the time interval you want and inserts it into a table. When you run the same query, it does not calculate over and over.
5. Sequences : Sequence is used to automatically number records in tables. Sequence number is generated automatically by DB. Sequence numbers are generated independently of the tables. That is, a sequence number can be used for one or more tables.
6. Synonyms : A synonym is an alias for a table, view, sequence, procedure, function or package. A synonym does not take up space in the DB, except for the definition in the data dictionary. Synonyms can be used for security or more comfortable coding.
7. Procedures and Functions : Procedures and functions are sets of PL/SQL and SQL statements that are put together to do specific jobs or solve a specific problem. These are stored in the DB in a compiled form and can be used by users or DB applications. The only difference between Functions and Procedures is that functions return a value when called.
8. Packages : Packages are the structure where the procedures, functions, variables and other structures that are related to each other are brought together and stored in the DB. This global variable provides additional features such as being able to be defined and called in any procedure within the package. It also provides a performance increase as the packages are checked for errors as a whole, compiled and then loaded into memory.
9. Triggers : Triggers are PL/SQL, Java or C procedures that are automatically invoked by the DB when a user or DB system action occurs, when a table or view changes.
Triggers provide several avenues for DB management. For example, it can be used to automate data creation, monitor data change, provide complex security authorization, etc.
