How to increase table space size in oracle

Method: 1. Use "alter database datafile table space location resize size" to increase the table space size; 2. Use "alter tablespace table space name add datafile data file address size data file" to increase the table space size.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to increase the size of table space in oracle

The first step: Check the name of the table space and the location of the file:

select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name

The second step: Increase the required table space size :

Method 1:

alter database datafile '表空间位置'resize 新的尺寸

For example:

alter database datafile '\oracle\oradata\anita_2008.dbf' resize 4000m

For the table space of the Oracle database, in addition to manually increasing the size, you can also expand the table space by adding data files, etc. size.

Method 2: Increase the number of data files

alter tablespace 表空间名称add datafile '新的数据文件地址' size 数据文件大小

For example:

alter tablespace ESPS_2008 add datafile '\oracle\oradata\anita_2010.dbf' size 1000m

Method 3: Set the table space to automatically expand.

alter database datafile '数据文件位置' autoextend on next 自动扩展大小maxsize 最大扩展大小

For example:

alter database datafile '\oracle\oradata\anita_2008.dbf' autoextend on next 100m maxsize 10000m

Step 3: 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;

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to increase table space size in oracle. For more information, please follow other related articles on the PHP Chinese website!