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 tech_master ADD (name varchar2(9));
-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]);
ALTER TABLE customer_master
ADD (location_name varchar2(100),
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.
-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>
|Add a new column||We can add new column to the table definition
Using alter table add command
ALTER TABLE <table_name>
-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.
ALTER TABLE <table_name>
We can drop the unused column later on when the resource is lower using the command
ALTER TABLE <table_name>
|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