Press "Enter" to skip to content

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.

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