On Alter Table Statements(Add-Drop-Modify Columns, Constraints) In Oracle

While requirments are changing, data structures are changing too. Some times you need to change your structure of table. Oracle supplies a bunch of alter table statements. I wrote these statements that can be used in Oracle:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> drop table t;
Table dropped
SQL> create table t(a number, z number constraint ck_10 check(z > 10) );
Table created
SQL> alter table t add constraint pk_t primary key(z);
Table altered
SQL> alter table t add constraint unique_t unique(a);
Table altered
SQL> alter table t add constraint fk_t foreign key(z) references t(a) on delete set null;--on delete cascade
Table altered
SQL> alter table t drop primary key cascade;--deletes also fk
Table altered
SQL> alter table t drop constraint pk_t;
alter table t drop constraint pk_t
ORA-02443: Cannot drop constraint  - nonexistent constraint
SQL> alter table t add k number default 4 not null;
Table altered
SQL> alter table t rename column k to kk;
Table altered
SQL> alter table t modify kk varchar2(9) default 'test';
Table altered
SQL> INSERT INTO t(a,kk) values(1,'asdfghjkl');
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT t.kk, length(t.kk) len FROM t;
KK               LEN
--------- ----------
asdfghjkl          9
SQL> alter table t modify kk varchar2(4);
alter table t modify kk varchar2(4)
ORA-01441: cannot decrease column length because some value is too big
SQL> alter table t modify kk varchar2(16);
Table altered
SQL> alter table t set unused(kk);
Table altered
SQL> SELECT * FROM t;
         A          Z
---------- ----------
         1
SQL> SELECT * FROM user_unused_col_tabs;
TABLE_NAME                          COUNT
------------------------------ ----------
T                                       1
Z                                       1
SQL> alter table t drop unused columns;
Table altered
SQL> alter table t drop  column z;
Table altered