Oracle - ALTER TABLE

oracle

How can we add column?

ALTER TABLE users ADD user_name  varchar2(50) DEFAULT 'N';

How can we add multiple columns?

ALTER TABLE tableName ADD (columnName1 CHAR(1) NOT NULL, columnName2 NUMBER);

How can we drop column?

ALTER TABLE table_name DROP COLUMN column_name;

How can we drop multiple columns?

ALTER TABLE tableName DROP (columnName1, columnName2);

How can we rename column?

ALTER TABLE users RENAME COLUMN first_name TO  user_first_name;

How can we change column data type?

ALTER TABLE schemaName.tableName MODIFY (columnName VARCHAR2(50 BYTE) DEFAULT 'Hello');

How can we specify default value for a column?

ALTER TABLE schemaName.tableName MODIFY (columnName VARCHAR2(50 BYTE) DEFAULT 'Hello');

How can we change data type for multiple columns?

ALTER TABLE schemaName.tableName MODIFY (columnName1 VARCHAR2(50 BYTE), columnName2 VARCHAR2(50 BYTE));

How can we rename table?

ALTER TABLE table_old_name RENAME TO table_new_name;

Can we use the ALTER TABLE statement without locking the table?

Adding a NULL column to an Oracle table is a very quick operation as it only updates the data dictionary. This holds an exclusive lock on the table for a very short period of time. It will however, invalidate any depedant stored procedures, views, triggers, etc. These will get recompiled automatically.

From there if necessary you can create index using the ONLINE clause. Again, only very short data dictionary locks. It'll read the whole table looking for things to index, but does not block anyone while doing this.

If you need to add a foreign key, you can do this and get Oracle to trust you that the data is correct. Otherwise it needs to read the whole table and validate all the values which can be slow (create your index first).

If you need to put a default or calculated value into every row of the new column, you'll need to run a massive update or perhaps a little utility program that populates the new data. This can be slow, especially if the rows get alot bigger and no longer fit in their blocks. Locking can be managed during this process. Since the old versino of your application, which is still running, does not know about this column you might need a sneaky trigger or to specify a default.

From there, you can do a switcharoo on your application servers to the new version of the code and it'll keep running. Drop your sneaky trigger.

Alternatively, you can use DBMS_REDEFINITION which is a black box designed to do this sort of thing.

All this is so much bother to test, etc that we just have an early Sunday morning outage whenever we release a major version.

  1. http://stackoverflow.com/questions/463677/alter-table-without-locking-the-table
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License