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:
1 | select tablespace_name, file_id, file_name, round (bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name
|
Copy after login
The second step: Increase the required table space size :
Method 1:
1 | alter database datafile '表空间位置'resize 新的尺寸
|
Copy after login
For example:
1 | alter database datafile '\oracle\oradata\anita_2008.dbf' resize 4000m
|
Copy after login
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
1 | alter tablespace 表空间名称add datafile '新的数据文件地址' size 数据文件大小
|
Copy after login
For example:
1 | alter tablespace ESPS_2008 add datafile '\oracle\oradata\anita_2010.dbf' size 1000m
|
Copy after login
Method 3: Set the table space to automatically expand.
1 | alter database datafile '数据文件位置' autoextend on next 自动扩展大小maxsize 最大扩展大小
|
Copy after login
For example:
1 | alter database datafile '\oracle\oradata\anita_2008.dbf' autoextend on next 100m maxsize 10000m
|
Copy after login
Step 3: Query table space usage:
1 2 3 4 | 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;
|
Copy after login
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!