EXTERNAL TABLES

An external table is a database file or even a text file on local disk which MUST BE :

1- column types in excel or word files match column types of the database table .

i.e: if first column in database table is NUMBER , first column in excel file should be NUMBER TOO !


2- character length must match too .

i.e: if first column in database table is NAME and accepts only 4 characters to be inserted , first column in excel file is NAME and contains value 'Ahmed' which is 5 characters length ... this causes an error when you try to select data from external table.


3- READ , WRITE on directory .. these privileges must be granted to user creating an external table.

-- EXTERNAL TABLES ARE USEFUL TO IMPORT YOUR MS EXCEL FILES OR DATA STORED IN ANY FILE TO YOUR DATABASE !!

EXAMPLE :

CREATE TABLE TEST1
(TEST NUMBER(3) , NAME VARCHAR2(25), LOCATION VARCHAR2(25))

ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER
DEFAULT DIRECTORY MYDIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY newline
NOLOGFILE
NOBADFILE
FIELDS TERMINATED BY ',' )
LOCATION ( 'TEXTFILE.TXT)) ;

EASY !!

Oracle DataBase BackUp

DATABASE BACKUP

BackUp: Server Managed Backup & User Managed Backup .

Steps to backup your oracle database:

1- Enable Archive Logging ( SqlPlus:> Alter Database Archivelog ; )

2- Create Rman Target / Catalog .

Target: System/password@database

Catalog: Rman/password@database

Where : Rman is a user name which was granted Recovery_Catalog_Owner Role .

3- Enable Control File Auto Backup ( Rman> Configure ControlFile Autobackup on )

4-

Rman> Target system/password@database Catalog Rman/password@database

Rman> Create catalog tablespace Rman

Rman> Register database

5- Backup your database

Rman> Backup Incremental Level 0 Database

-----------------------------------------

Oracle Basics

Here Are Some Of The Most Popular yet used Oracle statements :



* Types Of Statements : DML - DDL - DCL .



* Types Of Priviliges : System Priviliges , Object Priviliges .



* Types Of Data : Table - View - Index - Sequence .... ( those are the most important ) .



* Types Of Joins : Equi-Join (Inner-Join) , Outer-Join ( Right Outer-Join , Left Outer-Join) , Self-Join , Non-Equi-Join , Cross-Join .



* SubQuery : Used to Define A Variable BASED On UNKNOWN CONDITION(s) .



EX:



SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID = ( SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME LIKE 'Abel' ;



The Subquery used after (=) mark to define the variable Department_id based on unknown condition ( DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME LIKE 'Abel' )



* SAVEPOINT : saved checkpoint which you can roll back your changes to . roll back all user changes to this point.


Index : Schema Object , Used by Oracle Server to Make Retrieval of rows easier .

* Advantages Of VIEWS :

1- Restrict Data Access .

2- Made Complex Queries Easier .

3- Provide Data Independence .

4- Increase Performance .

5- Can be made Read Only , may have Check Option (check constraint).

Oracle DataBase Constraints

Constraint : Rules to be forced when ever a user try inserting data into a constraint controlled column.

Types Of Constraints : Primary Key - Foreign Key - Unique - Not Null - Check Constraint

* Primary Key : Guarantees that data inserted by user in this column ( Not Null , Unique )

* Not Null : Value Must Be Inserted , Cannot be blank.

* Unique : value cannot be repeated in 2 rows , if we consider a column called e-mail which have Unique Constraint .. this means that 2 users cannot have the same data in the e-mail field .

* Foreign Key : The relation between Primary Key (PK) and Foreign Key (FK) is : 1 to many .

ex: if user_id column (1) is primary key in the table Employees , this user_id may have data in another table , say Departments .

so , in the Departments table , a Foreign Key must be added to use the User_id Column from Employees .
This Departments table contains data like department_id , department_name (many) .

so this confirms relation is (1) to (many) .

* Check Constraint : It Checks user inserted data to satisfy pre defined condition . and reject the process if the condition is not met .

Create Table My_Employees
(Id Number (4) , Salary Number (6,2) Constraint My_Employees_salary_c Check(Salary>1000) ;

the previous example shows check constraint name "My_Employees_salary_c" and the condition to be applied "Salary>1000"