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.
- In Oracle SQL Developer, on the left side open connection node, then click on the table node to open it.
- Then select the table you want to alter and do the right click on it.
- From the shortcut menu select Edit and Edit Table window will open.
- There you can make any changes and after making the changes click on OK to apply.
Alter Table Structure Using Toad for Oracle
Follow these steps to alter table using Toad.
- Click on the menu Database > Schema Browser.
- Then the schema browser window will open and will display the table list.
- Select the table you want to alter and do the right click on it.
- From the shortcut menu, select Alter Table and alter table window will open.
- Make the required changes into the table and click OK to apply.
