[chrisplough]

Hello!

I've got a friend who is learning how to install OTM and isn't a very strong DBA. He's been asking about commands for modifying and creating tablespaces within an Oracle database.

While these are readily available on the internet, I feel they're applicable enough to OTM to warrant a post here.

Resize a datafile:

alter database datafile '/u01/oradata/otmdb/INDX01.dbf' resize 2000M;

Create a tablespace and associated datafile:

create tablespace BPL_DAY7
  logging
  datafile '/u01/oradata/otmdb/BPL_DAY701.dbf' 
  size 100M
  autoextend on
  next 100M maxsize 2000M
  extent management local;

Create a temporary tablespace and associated datafile:

create temporary tablespace TEMP 
  tempfile '/u01/oradata/otmdb/TEMP01.dbf' 
  size 1000m 
  autoextend on 
  next 100m maxsize 2000m
  extent management local;

Add a datafile to an existing tablespace:

alter tablespace INDX
  add datafile '/u01/oradata/otmdb/INDX02.dbf' size 2000M autoextend on next 100m maxsize 4000M;

Add a datafile to an existing temporary tablespace:

ALTER TABLESPACE TEMP
    add tempfile '/u01/oradata/otmdb/TEMP02.dbf' size 1000M autoextend on next 100m maxsize 2000M;

In addition, here are some great sites for getting more detailed commands and info:

• Oracle Tablespaces
• http://www.adp-gmbh.ch/ora/sql/create_tablespace.htm

Hope this helps!

--Chris