Creating and Modifying Tables

View All Scripts Login to Run Script

Script Name Creating and Modifying Tables
Description This example creates the admin_emp table in the current schema and modifies the table in various ways. It is based on examples in the Oracle Database Administrator's Guide.
Area SQL General
Referenced In Database Administrator's Guide
Contributor Oracle
Created Monday October 05, 2015

Statement 1
This example creates the admin_emp table in the current schema and modifies the table in various ways. The empno column is the primary key in this table. The primary key is the column or set of columns that uniquely identifies a row in a table. Only one primary key can be defined for each table. The NOT NULL constraint is applied to the ename and deptno columns. A NOT NULL constraint requires that a column of a table contain no null values. The hiredate column uses the SYSDATE function. The SYSDATE function returns the current date and time set for the operating system on which the database server resides. The photo column is of data type BLOB, which is a member of the set of data types called large objects (LOBs). LOBs are used to store semi-structured data (such as an XML tree) and unstructured data (such as the stream of bits in a color image). The hrly_rate column is defined as a virtual column. This column computes the employee's hourly rate as the yearly salary divided by 2,080 (the number of hours worked in a year). The STORAGE clause specifies the size of the first extent (50k).
Creating a Sample Table
```
CREATE TABLE admin_emp (  
         empno      NUMBER(5) PRIMARY KEY,  
         ename      VARCHAR2(15) NOT NULL,  
         job        VARCHAR2(10),  
         mgr        NUMBER(5),  
         hiredate   DATE DEFAULT (sysdate),  
         photo      BLOB,  
         sal        NUMBER(7,2),  
         hrly_rate  NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),  
         comm       NUMBER(7,2),  
         deptno     NUMBER(3) NOT NULL,  
         comments   VARCHAR2(4000),  
         status     VARCHAR2(10))  
   STORAGE (INITIAL 50K)
```
```
Table created.
```
Statement 2
This statement alters the admin_emp table to add a new column named bonus.
Adding A Column To A Table
```
ALTER TABLE admin_emp 
      ADD (bonus NUMBER (7,2))
```
```
Table altered.
```
Statement 3
This statement renames the comm column of the admin_emp table.
Renaming A Column In A Table
```
ALTER TABLE admin_emp 
      RENAME COLUMN comm TO commission
```
```
Table altered.
```
Statement 4
This statement drops both the bonus and commission columns.
Dropping Columns In A Table
```
ALTER TABLE admin_emp DROP (bonus, commission)
```
```
Table altered.
```
Statement 5
If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE...SET UNUSED statement. This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed. To mark the hiredate and mgr columns as unused, execute this statement.
Marking Columns In A Table As Unused
```
ALTER TABLE admin_emp SET UNUSED (hiredate, mgr)
```
```
Table altered.
```
Statement 6
The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space. In this ALTER TABLE statement, the optional clause CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.
Dropping Unused Columns From A Table
```
ALTER TABLE admin_emp DROP UNUSED COLUMNS CHECKPOINT 250
```
```
Table altered.
```
Statement 7
This statement drops the table.
Dropping A Table
```
DROP TABLE admin_emp
```
```
Table dropped.
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database