Home » SQL » How to Alter the Table Structure in Oracle?

How to Alter the Table Structure in Oracle?

In Oracle, use ALTER TABLE statement to alter the table structure. I am giving below some examples to alter table structure in Oracle using ALTER TABLE Statement, using Oracle SQL Developer and using Toad for Oracle.

The following are the things you can do using ALTER TABLE statement in Oracle.

1 Adding an integrity constraint
2 Adding integrity constraints to object-type columns
3 Adding or modifying index-only table characteristics
4 Adding or modifying LOB columns
5 Adding or modifying object type, nested table type, or varray type column for a table
6 Adding, modifying, splitting, moving, dropping, or truncating table partitions
7 Adding, modifying, splitting, moving, dropping, or truncating table subpartitions
8 Allowing or disallowing writes to a table
9 Changing the rows per block of storage for a table
10 Adding and Dropping a column
11 Enabling, disabling, or dropping an integrity constraint or trigger
12 Explicit deallocation of the unused space of a table
13 Modification of the degree of parallelism for a table
14 Modification of the LOGGING/NOLOGGING attributes
15 Modification of the real storage attributes of a nonpartitioned table or the default attributes of a partitioned table
16 Modification of the storage characteristics or other parameters
17 Physically moving a table
18 Renaming of a table or a table partition
19 Modify columns to a larger size
20 Modify columns that have all null values to be shorter, or to a different data type
21 Alter the PCTFREE, PCTUSED, INITRANS or MAXTRANS for any table
22 Alter the storage clause for any table
23 Explicitly allocate a new extent and specify the specific datafile for the tablespace and the specific instance for multi-instance databases
24 Drop or disable a given constraint as shown in the following example:

Alter Table Syntax

ALTER TABLE table_name alter_specifications

Modify Table Structure Using ALTER TABLE command Examples

Add Columns to Table

The following example will add a designation column to EMP table.

ALTER TABLE emp ADD designation VARCHAR2(100);

Modify Data Type of a Column

The following example will modify column data type from number to varchar2 for deptno column in EMP table.

ALTER TABLE emp MODIFY deptno VARCHAR2(10);

Add Pirmary Key to a Table

The following example will add primary key pk_emp for empno column in EMP table.

ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY (empno);

Enable Triggers for a Table

The following statement enables all the triggers associated with the EMP table.

ALTER TABLE EMP ENABLE ALL TRIGGERS;

Renaming a Column in a Table

The following example renames the sal column of the EMP table to salary.

ALTER TABLE EMP RENAME COLUMN sal TO salary;

Alter Table Structure Using Oracle SQL Developer

Follow these steps to alter table structure using Oracle SQL Developer.

  1. In Oracle SQL Developer, on the left side open connection node, then click on the table node to open it.
  2. Then select the table you want to alter and do the right click on it.
  3. From the shortcut menu select Edit and Edit Table window will open.
  4. There you can make any changes and after making the changes click on OK to apply.

modify table structure using Oracle SQL Developer

Alter Table Structure Using Toad for Oracle

Follow these steps to alter table using Toad.

  1. Click on the menu Database > Schema Browser.
  2. Then the schema browser window will open and will display the table list.
  3. Select the table you want to alter and do the right click on it.
  4. From the shortcut menu, select Alter Table and alter table window will open.
  5. Make the required changes into the table and click OK to apply.

alter table using Toad for Oracle

Reference:

Oracle Docs: Alter Table

See also: