What is Oracle Database?
Oracle Corporation primary business is to deal with the database and its products.
Oracle Database is a relational database (A database in which data are stored in the form of relation (tables) of 2-dimension which have a row as “Tuple” and column as “domain”) with a features of object oriented programming.
An Oracle database is a set of logically related data treated as a unit whose purpose is to store and retrieve information efficiently. A database server is the key for solving the problems related to Information management. A server has to handle bulk amount of data for concurrent access in multiuser environment.
Oracle Database is the first database designed for enterprise computing, the most flexible, reliable and cost effective way to manage information and its related applications. There is no need to be bother, because capacity can be easily added or reallocated from the resource as needed.
The database has a Physical and Logical Structure both are separated so that will not interfere in each other structure and we can change any of it without affecting other.
Physical Database Structures:
Physical Database Structure of Oracle has following components:
- Control Files
- Online Redo Log Files
- Archived Redo Log Files
- Parameter Files
- Alert and Trace Log Files
- Backup Files
Each Oracle Database has one or more files which stores data and data of logical database structures are stored in a data files allocated for that database.
Features of Data Files:
- One or more data files form a logical unit of database storage called tablespace.
- A data file can be related to only one table space.
- Datafiles can be defined to extend automatically when they are full.
A database also have a Control File which specify the physical structure of the database, including Database name, Names and locations of datafiles and redo log files and Timestamp of database creation.
Online Redo Log Files
Each Oracle database has two or more online redo log files. These online redo log files are merged with the duplicate of redo log files, which are known as the redo log for the database.
Archived Redo Log Files
Archived redo log files are are created by database and placed in offline mode of online redo log files. It archives redo log files when the database is in desired mode.
Parameter files have a list of configured parameters needed to configure a file. Both Piles (parameter files) and spfiles (server parameter files) allow us to store and manage initialized parameter permanently at server-side disk. A server parameter file has some additional advantages:
The file is updated simultaneously when some parameter values are changed in the active instance.
The file is located at center for access by all instance of database.
Alert and Trace Log Files
Associated Trace files are generated by each server and log files and if an internal error is detected by a process, the process pool inform about the error to its associated trace file. Some of the data written to a trace file is meant for the database administrator, while other information is useful for Support Services of Oracle. Trace file information is also used to tune applications and instances. The alert files a special trace file. The alert log of a database is a log of messages and errors.
To restore a file take backup of it. Generally we restore a file when a user or media resources damaged or deleted original file data.
User-managed backups and recovery mechanism requires us to generally restore backup files before we can perform a trial recovery of the backups.
Server-managed backups and recovery manages task the backup process, such as recovery process, as well as the scheduling of backup, such as apply it to the correct backup file when recoveries are essential.
Overview of Logical Database Structures
This section discusses logical storage structures which comprise of data blocks, extents, segments, and tablespaces. These structures (logical storage) enable Oracle Database to have fine-grained control of disk space use.
This section has following components:
- Oracle Database Data Blocks
Oracle Database Data Blocks
Oracle Database data is stored in data blocks. One data block represents a particular amount of number of bytes of physical database space on disk. The standard block size is allocated by the DB_BLOCK_SIZE initialization parameter. In extension, we can declare up to four other block sizes too. A database uses and allocates free database space to data blocks of oracle.
The next logical database is an extent. A extent is a specific number of contiguous blocks of data, obtained in a single allocation and used to store a particular type of information.
Above extents, the level of logical database storage is a segment. A segment is a set of extents which is allocated for a rollback segment, index, table, or for temporary use by a session, SQL parser or transaction. In relation to physical database structures, all the extents belonging to a segment exist in the same table space, but they can be in different data files.
When the extents of a segment are full or occupied, Oracle dynamically allocates another extent for that segment. The extents of a segment may or may not be contiguous on disk because it is allocated only when needed.
Each database is divided logically called Tablespaces, which consist of related segments, blocks, extent and data. For example, tablespaces group together all application objects to make efficient some administrative operations.
One or more datafiles are explicitly created for each tablespaces to physically store the data of all logical structures in a tablespaces.
Advantages of Oracle Database:
- Center of management for admin and data
- Follow a Standard
Disadvantages of Oracle Database:
- Inefficient for Recursive processing
- Incompability and Complexity
Let us take the example of a very simple database with just 2 tables:
The Student table has just three columns:
- employee_ name
The Course table has just two columns:
- course _id
- course _name
There is a relationship between these two tables via the course_id column in each table. The course_id column in the Student table relates to the course_id column in the Course table. This enables we to assign courses to student and determine which student studies which course.
Syntax of Creating a table in Oracle:
Create Table <TableName>(“column1” “data type”,”column2” “data type”,……………..);
Creating a Student Table in Oracle:
Create Table Student(student_id varchar2(10) primary key, student_name varchar2(25),course_id references Course);
Creating Course Table:
Create Table Student(course_id varchar2(10) primary key, course_name varchar2(25));
Inserting Data into Tables:
INSERT INTO <TableName> (column1,column2) VALUES(Value1,Value2);
Inserting a Data in CourseTable: Insert into Course (1,’BCA’);
1 row Inserted.
Similarly the Student and Course Table look likes:
Selecting a all the rows of a table:
|Select * from|
Selecting particular column with some condition:
|Select column1, column2….. from|
Note:We can also add where clause to it
Select * From Student
Select course_id, student name from Student where student_id=123;
Updating Data in Oracle:
Update <TanleName> set Columnname=Value;
Note: We can also add where clause to it
Update Course set course_name=”MSC” where Course_id=1;
1 row updated
Deleting a row from a Table:
DELETE FROM <TableName>
Delete from Course where course_id=1;
1 Row Deleted.
Renaming a table:
Rename <OldName> <NewName>
Rename Course Course1
Drop a table from a Database:
Drop Table <TableName>
Drop table Course;
Truncate a Table From a Database:
Truncate Table <TableName>
Truncate Table Student
In this article, we studied the most efficient and simple queries of Oracle to be performed on a database to get efficient data easily with no inconsistency and duplicity.
We studied how to create, insert, update data in Oracle database and perform some simple operations.
In next article, we studied some more tasks to be performed on Database like how to use clauses like (join, having etc).