How to alter table add column oracle

Last updated on March 3rd, 2019 at 06:16 pm

-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. So 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

 

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

 

We can also modify, rename,drop  column in the tables. Here is the detail on it

 

 

Rename existing column in the table Oracle allows you rename existing columns in a table. Use the RENAME COLUMN clause of the ALTER TABLE statement to rename a column.

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

Set A Column Unused and Drop the unused column if you are concerned about the resource consumed in dropping the column then you can use the ALTER TABLE…SET UNUSED statement.

This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns.

– A column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.

Example

ALTER TABLE <table_name>
SET UNUSED COLUMN <column_name>;

 

We can drop the unused column later on when the resource is lower using the command

ALTER TABLE <table_name>
Drop  UNUSED COLUMN ;

 

How to drop column of a table W e can drop the column from table using below command

ALTER TABLE table_name
DROP COLUMN col_name;

How to change  column datatype of a table W e can change  the column datatype in table using below command

ALTER TABLE table_name
MODIFY column_name col_type;

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

Related articles

Oracle Create table Syntax,Tip and Examples

How to Alter Table in Oracle

How to rebuild the table in oracle using alter table move

DROP TABLE ORACLE statement

Query to check table size in Oracle

How to find table where statistics are locked

Leave a Reply