Tables are objects, which store the user data. In Oracle, tables can be permanent or temporary.
Permanent Table:
Syntax
CREATE TABLE table_name
(
{col_name.col_datatype [[CONSTRAINT const_name][col_constraint]]},….
[table_constraint],…
)
[As query]
A table can have a maximum of 1000 columns. Only one column of type LONG is allowed per table.
Tabel_name, col_name, const_name: A string up to 30 characters length. Can be made up to A- Z, 0- 9, $, _, #. Must begin with non- numeric Oracle data characters.
Col_datatype: Can be any one data type available in Oracle.
Col_constraint: A restriction on the column. Can be of the following types – PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, CHECK, can be named. Only one PRIMARY KEY allowed per table.
Table_constraint: A restriction on single or multiple columns. The types are same as in col_constraint. (NULL constraint is not allowed here)
As query: Query is an SQL statement using SELECT command. SELECT command returns the rows from tables. Useful if the table being created is based on an existing table. New table need not have all the columns of the old table. Names of columns can be different. All or part of the data can be copied.
Any object created by a user is accessible to the user and the DBA only. To make the object accessible to other users, the creator or the DBA must explicitly give permission to others.
Example:
CREATE TABLE emp
(
Empno NUMBER (4),
Ename VARCHAR2 (10),
Deptno NUMBER (2),
Job CHAR (9),
Hiredate DATE
);
If a table is created as shown above, then there is no restriction on the data that can be stored in the table. However, if we wish to put some restriction on the data, which can be stored in the table, then we must supply some constraints for the columns.
For example, if we want
1) to make empno as the primary key of the table.
2) to ensure that the ename column does not contain NULL values
3) the job column to have only UPPERCASE entries
4) to put the current date as the default date in hiredate column in case data is not supplied for the column.
The create statement can be rewritten as:
CREATE TABLE emp
(
Empno NUMBER (4) CONSTRAINT P_KEY_PRIMARY KEY,
Ename VARCHAR2 (10) CONSTRAINT ENAME_NOT_NULL NOT NULL,
Deptno NUMBER (2),
Job CHAR (9) CONSTRAINT JOB_ALL_UPPER
CHECK (job = UPPER (job)),
Hiredate DATE DEFAULT SYSDATE
);
Now if we have a table DEPT which has following description,
CREATE TABLE dept
(
Deptno NUMBER (2),
Dname VARCHAR2 (14) NOT NULL,
Loc VARCHAR2 (14) NOT NULL
);
In EMP table, for deptno column, if we want to allow only those values that already exist in deptno column of DEPT table, we must enforce what is known as REFERENTIAL INTEGRITY.
To enforce REFERENTIAL INTEGRITY, declare deptno field of DEPT table as PRIMARY KEY and deptno field of EMP table as FOREIGN KEY as follows.
CREATE TABLE Dept
(
Deptno NUMBER (2) CONSTRAINT DEPTNO_P_KEY PRIMARY KEY,
Dname VARCHAR2 (14) NOT NULL,
Loc VARCHAR2 (14) NOT NULL
);
CREATE TABLE Emp
(
Empno NUMBER (4) CONSTRAINT P_KEY_PRIMARY KEY,
Ename VARCHAR2 (10) CONSTRAINT ENAME_NOT_NULL NOT NULL,
Deptno NUMBER (2),
Job CHAR (9) CONSTRAINT JOB_ALL_UPPER
CHECK (job = UPPER (job)),
Hiredate DATE DEFAULT SYSDATE,
CONSTRAINT DEPTNO_F_KEY FOREIGN KEY (Deptno)
REFERENCES Dept (Deptno)
);
In the above example FOREIGN KEY has been declared as a table constraint. Same can be given as column constraint as follows.
CREATE TABLE Emp
(
Empno NUMBER (4) CONSTRAINT P_KEY_PRIMARY KEY,
Ename VARCHAR2 (10) CONSTRAINT ENAME_NOT_NULL NOT NULL,
Deptno NUMBER (2) CONSTRAINT DEPTNO_F_KEY REFERENCES Dept (Deptno),
Job CHAR (9) CONSTRAINT JOB_ALL_UPPER
CHECK (job = UPPER (job)),
Hiredate DATE DEFAULT SYSDATE
);
The following example shows how to create a new table based on an existing table
CREATE TABLE Newemp (Emp#, Emp_Name, Hire_Date)
AS
(SELECT Empno, Ename, HIREDATE from emp
WHERE Hiredate > ’01-jan-82’
);
Note:
A new table will be created
It will contain empno, ename, hiredate of all employees hired after 1st Jan 1982
Constraint on an old table will not be applicable for a new table
Temporary Tables:
Tables can be created which are temporary in nature. These tables can be used as a scratch pad. The table can be configured so that it holds the data for a transaction (till a commit or a rollback) or for the entire session.
The advantage of these tables is that no redo information is needed to be stored. The tables are created in the temporary tablespace assigned to the user.
Example:
CREATE GLOBAL TEMPORARY TABLE emp_temp
(
eno number, ename varchar2 (20)
)
ON COMMIT DELETE ROWS;
In this example the moment the user commits or rollbacks the transaction, the data in the table is deleted. If the user TRUNCATE command on a temporary table, then only the rows which were created in his session gets truncated, rows from other sessions does not get deleted.
You can also use ON COMMIT PRESERVE ROWS, to keep the rows after the transaction is complete. The rows will be deleted when the session is terminated.
Data of the temporary table is only visible within its defined scope, (transaction or session).
Difference/ Comparison between regular and temporary tables.
1) Indexes may be created on temporary tables, but their scope is same as that of the tables they correspond to.
2) Triggers are supported against temporary tables
3) A view may be created using a temporary table, but a view may not be creates using both a temporary and a regular table.
4) Definition of a temporary table may be exported but the data may be not.
