ORACLE table Space Extension method

Environment: Linux System Tools: PL/SQL DEVELOPER

First step: See the name of the tablespace and where it is located:

Select Tablespace_name, file_id, file_name,

Round (bytes/(1024*1024), 0) Total_space

From Dba_data_files

ORDER BY Tablespace_name

Step two: Increase the size of the required table space:

ALTER DATABASE datafile ' table space position ' Resize new dimensions

For example:

ALTER DATABASE datafile ' \ORACLE\ORADATA\ANITA_2008.DBF ' resize 4000m

For the table space of an Oracle database, the table space size can be expanded in addition to manually increasing the size of the data file.

Method One: Increase the number of data files

Alter tablespace table space name
Add datafile ' New data file address ' size data file

For example:

Alter Tablespace esps_2008
Add datafile ' \oracle\oradata\anita_2010.dbf ' size 1000m

method Two: Set table space Auto-expansion .

ALTER DATABASE datafile ' data file location '
Autoextend on next auto scale size MaxSize max expansion size

For example:

ALTER DATABASE datafile ' \ORACLE\ORADATA\ANITA_2008.DBF '

Autoextend on next 100m maxsize 10000m

Method Three: Query table space usage:

Select a.tablespace_name,a.bytes/1024/1024 "Sum MB",

(a.bytes-b.bytes)/1024/1024 "used MB", b.bytes/1024/1024 "free MB",

Round (((a.bytes-b.bytes)/a.bytes) *100,2) "used%" from

(select tablespace_name,sum (bytes) bytes from Dba_data_files Group by Tablespace_name) A,

(select Tablespace_name,sum (bytes) Bytes,max (bytes) largest from Dba_free_space Group by Tablespace_name) b

where A.tablespace_name=b.tablespace_name

Order by ((a.bytes-b.bytes)/a.bytes) desc;

ORACLE table Space Extension method