Tuesday, January 24, 2012

How to extend Tablespace size in Oracle?

To see the size of the Tablespace:

select
file_name,
autoextensible,
(bytes/1024)/1024 usedmb,
(maxbytes/1024)/1024 maxmb,
round((((bytes/1024)/1024)
/ ((maxbytes/1024)/1024)) * 100) used_pct
from dba_data_files
where 1=1
and tablespace_name = 'TEST'
;

To increase the size of the existed datafile:
ALTER DATABASE
DATAFILE ‘/u01/oradata/ datafile01. dbf’
RESIZE 500M;

To add a new datafile to a tablespace use following query:

ALTER TABLESPACE TEST ADD DATAFILE ‘/u01/oradata/ datafile02. dbf’ SIZE 500M;

No comments:

Post a Comment