To increase a tablespace's size, you can increase the datafile sizes associated with the tablespace. First, query the Data Dictionary to see the files associated with the tablespace:
SELECT file_name,bytes FROM dba_data_files WHERE tablespace_name='ts_name';
Then increase the file's sizes with a command similar to the following:
ALTER DATABASE DATAFILE '/directory/filename' RESIZE size;>Where size is something like "100K", "10M" or "1G". Make sure the new size is larger than the old size. Alternatively, you can let the file automatically grow when it runs out of space by issuing the following:
ALTER DATABASE DATAFILE '/directory/filename' AUTOEXTEND ON NEXT xxxM MAXSIZE yyyM;
The last alternative is to add a new datafile to the tablespace similar to the following:
ALTER TABLESPACE ts_name ADD DATAFILE '/directory/filename' AUTOEXTEND ON NEXT xxxM MAXSIZE yyyM;
For more on Oracle backup and recovery:
Database storage management all-in-one guide: This learning guide will help you become an expert in backup and recovery.
Oracle 11g: Backup and Recovery concepts: Download this chapter to learn the basic concepts behind Oracle's backup and recovery mechanisms.
Dig Deeper on Oracle database administration
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading