Sunday, July 11, 2010

Oracle tablespace management (add and extend)

Assumption:
1. autoextend is disabled
2. there is enough disk space.
3. tablespace name is
4. datafile name (in full path) is

Step 1 - check the tablespace and datafile relationship:
select file_name, bytes from dba_data_files where tablespace_name = ;

FILE_NAME BYTES
---------------- --------


Step 2 - add new datafile

create and new datafile to the tablespace (in MB)
ALTER TABLESPACE 
ADD DATAFILE '' SIZE M;

Step 3 - extend a resize the size of a particular datafile' (also in MB)

ALTER DATABASE DATAFILE '' RESIZE M