What is Oralce?
The Oracle is an ORDBMS (object-relational database management system) developed and being marketed by Oracle Corporation.
Explain the Oracle storage mechanism.
The Oracle RDBMS stores data physically in the form of data files and logically in the form of tablespaces.
Tablespaces can contain different types of memory segments like Index Segments, Data Segments, etc. Segments in turn can contain one or more extents.
What are extents and data blocks?
Data blocks form the basic units of data storage in the database system whereas groups of contiguous data blocks are called as Extents.
What are constraints in Oracle and what are different types of constraints available?
A Constraint can be defined as a rule that restricts the values in a database.
Different types of Constraints
* NOT NULL Constraint - does not allow any null values in a specified column. In other words, these are mandatory values to inserted (read as * marked fields from front end). For example, I have a date of birth column in my employee table and the user must the same for all data processing and other operations. If the column is defined as NOT NULL the column does not accept any null values even by mistake.
* UNIQUE Constraint - does not allow repetition of values, but allows null values. For example, a column email id is unique value and at the sametime is not mandatory.
* PRIMARY KEY Constraint - combination of NOT NULL and UNIQUE. The values are mandatory and are not repetitive. For example, the bank account number in a bank's account table.
* FOREIGN KEY Constraint - expects values in one table to be present in another table. For example, the employees present in department table must first be present in employee table.
* CHECK Constraint - expects only a specified range of values. A gender column can expect only 2 values - male or female.
What are DDL commands in Oracle?
DDL (Data Definition Language) statements define the database schema or structure. It does nothing with the data present inside them.
* CREATE - Create Objects
* ALTER - Alters the structure of the objects in the database
* DROP - delete the objects (this is different from DML delete statement, read next topics for the differences)
* TRUNCATE - delete all the data in the tables, including all the spaces allocated for records
* COMMENT - adds user readable comments about an object.
* RENAME - Renames an object
What are DML commands in Oracle?
DML (Data Manipulation Language) statements are used to manage data within the objects.
* SELECT - fetches/retrieves data from the database objects
* INSERT - inserts data into a database table
* UPDATE - updates already existing data in a database table
* DELETE - deletes all the table from a table (unless you specify a WHERE condition), the space allocated for the records remain
* MERGE - insert or update depending on the specified condition (also called as UPSERT operation)
* CALL - calls a Java subprogram or PL/SQL block of code
* EXPLAIN PLAN - explains the access path to execution
* LOCK TABLE - controls the concurrency mechanism.
What are DCL commands in Oracle?
* GRANT - gives access rights for an user to database
* REVOKE - withdraws access rights (given with the GRANT command)
What are TCL commands in Oracle?
TCL (Transaction Control) statements manages the changes done by the DML statements. It allows statements to be grouped together into logical transactions.
* COMMIT - save work done
* SAVEPOINT - identify a point in a transaction to which you can later roll back
* ROLLBACK - restore database to original since the last COMMIT
* SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
What is the difference between DROP and DELETE commands ?
DROP is a DDL command whereas DELETE is a DML command. DROP deletes the database objects but DELETE removes data inside the objects.
