Modify Table Structure in oracle

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 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. Add a column

Syntax:

Alter table name ADD (column name, data type );

Eg1:

Alter table skate_test ADD (author NUMBER (38,0) not null );

3. rename a column:

Syntax:

Alter table name rename column current COLUMN name TO new COLUMN name;

Eg1:

Alter table skate_test rename column author TO authorer_new

4. delete a column

Syntax:

Alter 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