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
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
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.
-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|
Before Adding column
Alter table add column
alter table emp add(ext varchar2(10));
After Adding column
Now dropping that column
alter table emp drop column ext;
Hope you like the article on How to alter table add column in oracleRelated articles
oracle create table syntax
Alter Table in Oracle
alter table move
DROP TABLE ORACLE statement
check oracle table size
ora-20005: object statistics are locked