Sample Sql Create tablespace in Oracle

Sample query to create a Table Space in Oracle .

create tablespace MM_USAGE datafile
'/u04/oradata/PROD/mm_usage01.dbf' size 2G autoextend off,
 '/u04/oradata/PROD/mm_usage02.dbf' size 2G autoextend off,
 '/u04/oradata/PROD/mm_usage03.dbf' size 2G autoextend off,
 '/u04/oradata/PROD/mm_usage04.dbf' size 2G autoextend off,
 '/u04/oradata/PROD/mm_usage05.dbf' size 2G autoextend off,
 '/u04/oradata/PROD/mm_usage06.dbf' size 2G autoextend off,
 '/u04/oradata/PROD/mm_usage07.dbf' size 2G autoextend off,
 '/u04/oradata/PROD/mm_usage08.dbf' size 2G autoextend off
 logging
 extent management local;

For index also need to create table space

create tablespace MM_USAGE_INDX
 datafile '/u05/oradata/PROD/mm_usage_indx01.dbf' size 2G autoextend off,
 '/u05/oradata/PROD/mm_usage_indx02.dbf' size 2G autoextend off,
 '/u05/oradata/PROD/mm_usage_indx03.dbf' size 2G autoextend off,
 '/u05/oradata/PROD/mm_usage_indx04.dbf' size 2G autoextend off,
 '/u05/oradata/PROD/mm_usage_indx05.dbf' size 2G autoextend off
 logging
 extent management local;

 

In Sequence:

select * from dba_tablespaces;
select * from dba_tables where table_name = 'USAGE_XXXX' and owner = 'XXXX';

select * from dba_data_files where tablespace_name = 'XX_USAGE';

alter tablespace MM_USAGE add datafile '/u04/oradata/METERPROD/mm_usage10.dbf' size 2G autoextend off;

//Query tablespace information

select * from dba_tablespaces

//Move a table from users table space to other

alter table <tablename> move tablespace MM_MIG_USERS

//Rebuild index in new table space

alter index <iname> rebuild tablespace <tablespace name>

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: