How to alter table add column oracle



Last updated on April 1st, 2017 at 08:11 am

-You Add a new column using the ALTER TABLE statement.

– 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

alter table add column 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 DM. 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
  • Can contain numbers and underscores
  • Can be in UPPER of 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

 

Modify physical characteristics (INITRANS or storage parameters) We can modify the storage parameter of the table using Alter table statement.  We can modify inittrans like this

ALTER TABLE TABLE_NAME INITRANS 10;

Moving the table to new segments or tablespace We can move the non partition table or partition of the table to new segment or new tablespace if required

Alter table table_name move tablespace <tablespace name>

We can even use the move command to change any storage parameter of the tables which are not modified using alter table command

Alter Table Change Data Type We can change the datatype of any column using alter table modify command

ALTER TABLE <table_name>
MODIFY (<column_name>  <new_data_type>);

Add a new column We can add new column to the table definition

Using alter table add command

ALTER TABLE <table_name>
ADD (<New column_name>  <new_data_type>);

-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 updates each row in the new column with the values specified.

 

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

 

Add, modify or drop integrity constraints associated with the table or you can enable/disable the constraints also Add constraints is done using alter table add constraints

ALTER TABLE EMP ADD CONSTRAINT EMP_FK FOREIGN KEY (DEPT_NO) REFERENCES DEPT(DEPT_NO);

Dropping Constraints – is done using the ALTER TABLE DROP CONSTRAINT <constraint_name> command;

Enabling/Disabling Constraints – Constraints can be created in DISABLE/ENABLE mode or can be disabled or enabled using the ALTER TABLE ENABLE/DISABLE CONSTRAINT <constraint_name> command;

Rename table name Oracle allows you to rename the table name also

Rename <table name>  to <new table name>;

Alteration of table cache/nocache, Compression, parallelism Oracle allows Alteration of table cache/nocache, Compression, parallelism

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

Related articles

how to create table in oracle

All you need to know about oracle create tablespace statement

Oracle PLSQL Tables

How to work with date in Oracle sql

Things you need to know about Oracle SQL developer


Leave a Reply