ALTER TABLE Oracle

THE ORACLE ALTER TABLE

With oracle alter table you can do some thing:

- add new columns to the table with ALTER TABLE COLUMN

Es: ALTER TABLE table_name ADD COLUMN columns definition;

- add integrity constraints to a table

Es: ALTER TABLE table_name ADD COLUMN columns definition;

- modify an existing column's definition (datatype, length, default value, and not null integrity constraint).

Es: ALTER TABLE table_name MODIFY COLUMN column1 NOT NULL

- modify data block space usage parameters (PCTFREE, PCTUSED)

- modify transaction entry settings (INITRANS, MAXTRANS)

- modify storage parameters (NEXT, PCTINCREASE, etc.)

- enable or disable integrity constraints associated with the table

- drop integrity constraints associated with the table

When altering the column definitions of a table, you can only increase the length of an existing column, unless the table has no records. You can also alter sql server to decrease the length of a column in an empty table. For columns of datatype CHAR, increasing the length of a column might be a time consuming operation that requires substantial additional storage, especially if the table contains many rows. This is because the CHAR value in each row must be blank-padded to satisfy the new column length. For alter table sql you change the datatype (for example, from VARCHAR2 to CHAR), then the data in the column does not change. However, the length of new CHAR columns might change, due to blank-padding requirements.

Alter table in oralce for a pl sql table

Use the SQL command ALTER TABLE in Oralce to alter a pl sql table.

For example: ALTER TABLE tab PCTFREE 30 PCTUSED 60;

HOW TO ADD COLUMN?

To add a column you must use:

ALTER TABLE table_name ADD COLUMN columns definition;