Modify the Oracle table structure

 

Time: 2008/02/28
Author: skate

Summary of table modification today to prevent future forgetting. Is it better to remember it !!!

Change the table structure

1. Edit table fields

Modify the Data Type of a column (generally, the length is limited and there are many restrictions when you change it to a different type ):

Syntax:
Alter table table name modify (column name data type );

Eg1:

Alter table skate_test modify (Author Number (10, 0 ))

When modifying the column length, you can only edit the length larger than the existing field actually saved. Otherwise, the following error is prompted:

ORA-01441: cannot reduce column length because some values are too large

Eg2:

Alter table skate_test modify (author varchar2 (10 ))

When modifying the Data Type of a column, the modified column must be empty; otherwise, the following error is prompted:

ORA-01439: to change the data type, the column to be modified must be empty

2. rename a column:

Syntax:

Alter table table name rename column current column name to new column name;
Eg1:

Alter table skate_test rename column author to authorer_new

 

3. Add a column

Syntax:

Alter table table name Add (column name data type );

Eg1:

Alter table skate_test add (Author Number (38,0) not null );

4. delete a column

Syntax:

Alter table Table Name drop column name;

Eg1:

Alter table skate_test drop column author

5. rename a table

Syntax:

Alter table current table name Rename to new table name;

Eg1:

Alter table skate_test Rename to test_sakte

5. Add comments to the table

Comment column on table name. Column name is 'comment content'; // modify the comment of a table column
 
Comment on table movo_new.test_sakte is 'comment content'; // modify the comment of the table

--- To be continued ---

 

 

----------------------------------------------------------------------

3. rename a column:

Syntax:

Alter table table name rename column current column name to new column name;
Eg1:

Alter table skate_test rename column author to authorer_new