In today’s article, we will examine how to change tables in a table using the ALTER command.
1. Change Table Name.
|
1 2 3 |
SQL> RENAME ISLEM TO ISLEM_NEW; Table renamed. |
2. Add Columns to the Table
The value of existing records in this column is updated to NULL.
|
1 2 3 |
SQL> ALTER TABLE ISLEM_NEW ADD (TEST VARCHAR2(20)); Table altered. |
If NULL is not desired, a DEFAULT value is assigned.
|
1 2 3 |
SQL> ALTER TABLE ISLEM_NEW ADD (TEST_2 VARCHAR2(20) DEFAULT 'BURCU', TEST_3 NUMBER DEFAULT '13'); Table altered. |
3. Change the Column
|
1 2 3 |
SQL> ALTER TABLE ISLEM_NEW MODIFY TEST VARCHAR2(30); Table altered. |
If a column is given a default value with the MODIFY word, existing records are not affected. It is valid only for records to be newly inserted.
We can increase the size of a column, but when decreasing it, it is necessary to know the size of the largest data, otherwise problems may occur.
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> SELECT MAX (LENGTH (ACCOUNTNAME)) FROM IPTVMWCACCOUNT; MAX(LENGTH(ACCOUNTNAME)) ------------------------ 12 SQL> ALTER TABLE IPTVMWCACCOUNT MODIFY ACCOUNTNAME VARCHAR2 (10); ALTER TABLE IPTVMWCACCOUNT MODIFY ACCOUNTNAME VARCHAR2 (10) * ERROR at line 1: ORA-01441: cannot decrease column length because some value is too big |
4. Change the Column Name
|
1 2 3 |
SQL> ALTER TABLE ISLEM_NEW RENAME COLUMN TEST TO TEST_4; Table altered. |
