Constraints:

Constraints:
Not Null A value must be supplied for this column, but values do not have to be unique.
Unique Key Every value in this column must be unique, but null values are allowed.
Primary Key Every value in the column must be unique and cannot be null.
Foreign Key Every value in the column must match a value in another column in this table or some other table; otherwise, the value is null.
Check The value entered in the table must match one of the specified values for this column.

Constraint information is stored in the DBA_CONSTRAINTS and DBA_CONS_COLUMNS data dictionary views.

In addition to defining relationships between tables, you can also use foreign keys to define relationships between two columns within the same table. These types of constraints are referred to as self-referencing foreign keys.

Use ON DELETE CASCADE to remove data with child record.

Dynamic performance views:

Depending on which features are installed and configured, an Oracle 10g database can contain approximately 350 dynamic performance views. Most of these views have names that begin with V$. Examples of Dynamic Performance Views:
--V$DATABASE Contains - about the database itself, such as the database name and when the database was created.
--V$VERSION Shows which software version the database is using.
--V$OPTION Displays which optional components are installed in the database. --V$SQL Displays information about the SQL statements that database users have been issuing.

Data dictionary views:

Depending on which features are installed and configured, an Oracle 10g database can contain more than 1,300 data dictionary views.
Data dictionary views have names that begin with DBA_,ALL_, and USER_.
-The DBA_TABLES view shows information on all the tables in the database.
-ALL_TABLES view, shows only the tables that a particular database user owns or has access to.
For example, if you were logged into the database as a user named PROD7, the ALL_TABLES view would show all the tables owned by the user PROD7 and the tables to which PROD7 has been granted access by other users.
-USER_TABLES view shows only those objects owned by a user.
For example, If the user PROD7 were to examine the USER_TABLES view, only those tables he owns would be displayed.

View:

A view is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used.

A view is only a logical structure that contains no data of its own. Instead, a view is like a window that can be used to look at the contents of another table or tables.

To create a view use the create view command as seen in this example:

Example-1:
create table empinfo(
empid number(4),
ename varchar2(20),
deptno number(2));
INSERT Into empinfo VALUES (1,'Rahim',10);
INSERT Into empinfo VALUES (2,'Karim',10);
INSERT Into empinfo VALUES (3,'Jamal',10);
INSERT Into empinfo VALUES (4,'Kamal',10);

CREATE VIEW view_emp

AS

SELECT empid,ename FROM empinfo;

This command create a view view_emp.

Example-2:
Create single table basic view:
CREATE OR REPLACE VIEW single_view AS
SELECT empid AS ID, ename AS NAME
FROM empinfo;

Example-3:
Create Single Table View With WHERE Clause
CREATE OR REPLACE VIEW single_view AS
SELECT empid AS ID, ename AS NAME
FROM empinfo
WHERE ID>100;

Read Only View:

CREATE OR REPLACE VIEW read_only_view AS
SELECT empid, ename
FROM empinfo
WITH READ ONLY;

UPDATE read_only_view
SET ename = 'Nahar'
WHERE ename = 'Rahim';
ORA-01733: virtual column not allowed here

Force view:
Forces The Creation Of A View Even When The View Will Be Invalid. NoForce Is The Default.

-- assuming the table pq does not exist

CREATE OR REPLACE FORCE VIEW view_force AS
SELECT * FROM pq;

-- ignore the error message

DROP view:
DROP VIEW ;
SELECT view_name FROM user_views;

DROP VIEW single_view;

Benefits of oracle view:
Security: Views are used to hide the table name that actually contain the data when you are querying. Again, views can be used to restrict the columns that a given user has access.

Views help to access to the metadata because the names of the views and the columns in them are much more intuitive than the metadata tables on which they are based.

An Oracle 10g database contains two types of metadata views:
-Data dictionary views
-Dynamic performance views

Commands:

Four categories:
-Queries using the SELECT command.
-Statements using the CREATE, ALTER, or DROP command are classified as Data Definition Language (DDL) commands.
-Statements using the GRANT or REVOKE commands are classified as Data Control Language(DCL) commands.
-Statements using the INSERT, UPDATE, and DELETE commands are classified as Data Manipulation Language (DML) commands.

Transactions: DML commands are used in transactions. A transaction begins with the first DML command that a user issues and ends when the user either makes their changes permanent by issuing a commit command or undoes their changes using the rollback command.

Oracle History

The relational model on which Oracle is built is based on the ground-breaking work of Dr. Edgar Codd, which was first published in 1970 in his paper “A Relational Model of Data for Large Shared Data Banks.”

IBM Corporation was an early adopter of Dr. Codd’s model and also helped to develop the computer language that is used to access all relational databases today—Structured Query Language (SQL).
more..

Oracle 10g editions

There are five editions of Oracle 10g
-Enterprise
-Standard
-Standard Edition One
-Personal
-Lite