How to alter table add column oracle

How to alter table add column in oracle Database

You Add a new column using the ALTER TABLE ADD COLUMN statement in Oracle

The table must be contained in your schema To alter a table, or you should either have ALTER object privilege for the table or the ALTER ANY TABLE system privilege.
If a view, materialized view, trigger, function-based index, check constraint, function, procedure of package depends on a base table, the alteration of the base table or its columns can affect the dependent object For example PLSQL objects become invalid if the dependent table object is changed and you have to make them valid again

We can add new column to the table definition Using alter table add command

how to add column using alter table command in Oracle

ALTER TABLE tech_master ADD (name  varchar2(9));
Table Altered

Before 11g,if a new column is added to a table, the column is initially NULL unless you specify the DEFAULT clause. . When you specify a default value, the database immediately updates each row with the default value. Note that this can take some time, and that during the update, there is an exclusive DML lock on the table.
For some types of tables (for example, tables without LOB columns), if you specify both a NOT NULL constraint and a default value, the database can optimize the column add operation and greatly reduce the amount of time that the table is locked for DML. Same is the case for non null column addition with default value

Oracle has introduced fast add column feature in 11g, So when we add not null column with default value or null column with default value, oracle add the column immediately and does not update the each row with the default value. S
o it does incur any penalty for redo and undo generation.When a user selects the column for an existing record, Oracle gets the fact about the default value from the data dictionary and returns it to the user.

You cannot specified where column is to appear. The newly added column becomes the last column

Similarly we can add multiple columns to the table

ALTER TABLE table_name
ADD (col1 column datatype [default  expr],
col2 column datatype [default  expr],
...
coln column datatype [default  expr]);

Example
ALTER TABLE customer_master
ADD (location_name varchar2(100),
last_login date);

Column Naming Conventions are same as in the case of Create table statement

The name you choose for a column must follow these standard rules:
The name must begin with a letter A-Z or a-z
it can contain numbers and underscores

Could be UPPER or lower case
Can be up to 30 characters in length
Cannot use the same name of another existing object in your schema

Must not be an Oracle server and SQL reserved word

See also How to drop table in oracle

Data types are again same as create table statement

Character	-CHAR, NCHAR, VARCHAR2 & NVARCHAR2. – The CHAR datatype is a fixed-length alphanumeric string which has a maximum length in bytes. -When creating a CHAR datatype, the database will preserve space for the incoming data and if the data is shorter than maximum size, it will be space-padded to the right -The VARCHAR2 datatype is a variable-length alphanumeric string, which has a maximum length in bytes. It can store up to 4000 bytes.
Number	-NUMBER -The NUMBER datatype stores number with precision and scale. -Numeric datatypes store negative and positive integers fixed-point numbers and floating-point numbers – When a column is defined as NUMBER (6, 2), the range of values can be stored from –9999.99 to 9999.99. Oracle rounds the floating-point numbers.
Date and Time	–DATE, TIMESTAMP (with time zone or local time zone), INTERVAL YEAR TO MONTH & INTERVAL DAY TO SECOND. -The DATE data type is used to store date and time information. -This data type has a number of specific functions for manipulating, formatting and viewing its data. – The DATE data type holds storage of seven bytes and has information about century, year, month, day, hours, minutes & seconds. – The NLS_DATE_FORMAT parameter can be changed to control the viewing of the data. The SYSDATE function returns the current date
Large Objects	-BLOB (binary large object), CLOB (character large object), NCLOB & BFILE -Columns of these datatypes can store unstructured data including text, image, video, and spatial data. -The CLOB datatype can store up to eight terabytes of character data using the CHAR database character set. -The BLOB datatype is used to store unstructured binary large objects such as those associated with image and video data where the data is simply a stream of “bit” values. -The BFILE data type value works as a file locator or pointer to file on the server’s file system. The maximum file size supported is 8TB to 128TB.
Long	Variable length character data up to 2 G
rowid	A 64 base number system representing the unique address of the row in the table

Example

Before Adding column

Desc emp

Alter table add column

alter table emp add(ext varchar2(10));

After Adding column

Desc emp

Now dropping that column

alter table emp drop column ext;

Hope you like the article on How to alter table add column in oracle

How to alter table add column in oracle Database

Column Naming Conventions are same as in the case of Create table statement

Related Posts

Leave a Comment Cancel Reply