How to move a table to a tablespace with higher block size

For improving performance we need to move table to a different tablespace with higher block size. Higher blocksize increase the performance of select query tremendously. The following short article shows the process of creating the higher block tablespace and moving the table to it.

Step 1

Set 32k block size cache inside SGA.

SQL> alter system set db_32k_cache_size=2G ;

Step 2

Create the tablespace.

create tablespace apps_ts_32k datafile '/D02/db/apps_st/data/apps_ts_32k.dbf' size 10G BLOCKSIZE 64k;

Step 3

give the current table owner user grant to this new tablespace.

SQL> alter user alr QUOTA 10G ON apps_ts_32k;

SQL> grant unlimited tablespace to alr;

Step 4

Move the table to new tablespace.

alter table alr.ALR_OUTPUT_HISTORY move tablespace apps_ts_32k;

Step 5

Rebuild the indexes. 

Moving the table will disable all indexes. Rebuild them after moving table.

ALTER INDEX alr.ALR_OUTPUT_HISTORY_N1 REBUILD;

You may encounter following errors during this process.

Errors you may get

ORA-00382: 65536 not a valid block size, valid range [2048..32768]

You'll get this error if you try to set tablespace with more than 32K block size.

ORA-29339: tablespace block size 32768 does not match configured block sizes

If you skipped the step 1 and directly try to create tablespace as mentioned in step2, it'll give above error. First create 32k cache size in SGA.

ORA-01950: no privileges on tablespace 'APPS_TS_32K'

You'll get the above error if you skip step 3.