Friday, 27 March 2009

Can one resize tablespaces and data files?

Add more files to tablespaces
To add more space to a tablespace, one can simply add another file to it.

Example:
ALTER TABLESPACE USERS ADD DATAFILE '/oradata/orcl/users1.dbf' SIZE 100M;

Resize datafiles
One can manually increase or decrease the size of a datafile from Oracle 7.2 using the following command:
ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;

Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.

Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimations of space requirements.
Extend datafiles

Also, datafiles can be allowed to automatically extend if more space is required. Look at the following commands:

CREATE TABLESPACE pcs_data_ts DATAFILE 'c:ora_appspcspcsdata1.dbf' SIZE 3M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
DEFAULT STORAGE ( INITIAL 10240
NEXT 10240
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0)
ONLINE
PERMANENT;

ALTER DATABASE DATAFILE 1 AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

No comments:

Post a Comment