Modify table with few additional columns with not null constraint and default value

There was a new requirement to add few columns to a big table (few gigs in size) with not null constraints and default value.

The developer came up with a script and asked me to review,

alter table T1 add F1 varchar2(10);
alter table T1 add F2 varchar2(10);
alter table T1 add F3 varchar2(10);

update T1 set F1=' ',F2=' ',F3=' ';


alter table T1 modify F1 not null;
alter table T1 modify F2 not null;
alter table T1 modify F3 not null;



Oracle has fantastic feature to do all the above steps in one go ....:()   Here you go....

alter table T1 add (F1 varchar2(10) default ' ' not null, F2 varchar2(10) default ' ' not null, F3 varchar2(10) default ' ' not null);

The above DDL ran faster and easy to manage it.

How to move a index from one tablespace to another

How to move index from tablespace to another tablespace online.

SQL>

OWNER                          INDEX_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------ ---------------

SYS                                  TEST_SC                               OLD_INDEX

I want to move TEST_SC which is wrongly created in OLD_INDEX tablespace.  User want to move to NEW_INDEX tablespace,

SQL>alter index <INDEX_NAME> rebuild tablespace <NEW TABLESPACE> PARALLEL NOLOGGING;

SQL>alter index TEST_SC rebuild tablespace NEW_INDEX PARALLEL;

SQL>

OWNER                          INDEX_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------ ---------------

SYS                                  TEST_SC                               NEW_INDEX

User carefully with NOLOGGING option if you have DR. 

If you are moving large index, check in V$SESSION_LONGOPS to see how much it progressed and how long it will take (approx).