Search This Blog

Tuesday, March 29, 2011

How to move a table in new tablespace with NOLOGGING option

/*
Login as sys or system
create a tablespace with NOLOGGING option
*/

create tablespace sh_rate
datafile 'E:\ORADATA\PROD\DATAFILE\RATES.DBF' size 128M autoextend on maxsize 2048M
segment space management auto
NOLOGGING;
/*
Grant user (The owner of the table[s], indexes etc) to access the new tablespace
*/
alter user parameters quota unlimited on sh_rate;

/*
Move tables in the new tablespace
*/

alter table parameters.sh_rates_temp move tablespace sh_rate;
alter table parameters.sh_rate move tablespace sh_rate;

/*
create,Recreate, or rebuild indexes

Rebuild index : alter index xyz rebuild tablespace sh_rate;
*/
create index parameters.sh_rates_temp_idx1 on parameters.sh_rates_temp(code) tablespace sh_rate;
create index parameters.sh_rate_idx1 on parameters.sh_rate(code) tablespace sh_rate;