Create a table from the query

The query result can be saved as a new table.

CREATE TABLE table_name
[(Name [NULL | NOT NULL] ...)]
AS SELECT query;

List the names of the new table can be omitted if they are correctly indicated the columns in the SELECT statement.

If you are given a list of columns the new table, the number of positions must coincide with the number of items appearing in the selection list SELECT statement.

Example

To create a table EMP_CLERK, we write:

CREATE TABLE EMP_CLERK
AS
SELECT empno, ename, JOB, SAL
FROM EMP
WHERE JOB = 'CLERK';

Manual processed.

Description of the table get created DESC command:

Changing the table definition

To change the table definition, use the command ALTER TABLE. It allows adding new columns, change the maximum size, value and integrity of management conditions.

Add columns

To add a new column is ADD clause.

ALTER TABLE name
ADD column_name type (size),
column_name type (size),
, ...);

Example

ALTER TABLE emp
ADD adress VARCHAR2 (40);

Managing the integrity

Terms of adding integrity to the table as a column, except that we use syntax such as the definition of terms in the definitions of the columns.

ALTER TABLE name
ADD (CONSTRAINT condition_name condition_type condition
, ...);

Checking the conditions of integrity on and off:

Switching on:

ALTER TABLE ENABLE CONSTRAINT name condition_name;

Switching off:

ALTER TABLE DISABLE CONSTRAINT name condition_name;

When you turn on the conditions of integrity checking of Oracle verifies that all rows of the table meet the given criteria. If even one check fails, the condition is not enabled.

Integrity of condition can be removed using the DROP clause:

ALTER TABLE name
DROP CONSTRAINT condition_name;
Modify the column definition
To modify the column definition is used MODIFY clause:
ALTER TABLE name
MODIFY column-name type (size) [NULL | NOT NULL]
, ...);

You can not change the columns in which there are NULL values ​​to NOT NULL.
For non-empty table, you can not add a column with NOT NULL property.

You can not reduce the size of a column or change its size if the column is not empty.

Delete tables

To remove the tables, use the command DROP TABLE.

DROP TABLE name;

Removal of the table to:

• loss of all data contained herein, and all indices associated with the table
• all perspectives and synonyms based on the table lose their meaning
• the order is automatically approved (irreversible)
• if there are signs that foreign keys are associated with a removable table that the removal will fail, unless we add at the end of clause CASCADE constraints
• a table owner can remove a table or database administrator

Renaming a table

By typing the table name RENAME TABLE statement:

TABLE RENAME TO new_name Old_Name;