Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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. |
