How to increase table space in oracle

In Oracle, you can use the alter statement to add a table space. The syntax is "alter tablespace table space name add datafile 'file path' SIZE initial size AUTOEXTEND ON NEXT automatic expansion size."

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

How to add a table space in oracle

Oracle adds a table space

Syntax:

alter tablespace {表空间名字} add datafile '物理数据文件路径' SIZE 『初始大小M』 AUTOEXTEND ON NEXT 『自动扩展大小M』

Example:

alter tablespace MMLOTTERY add datafile '+DATA/ora11g/datafile/mmlottery08.dbf' size 30720m autoextend on next 200m;

Note: If you add a table If the file name of the space is repeated, an error will be reported, as follows:

SQL> alter tablespace MMLOTTERY add datafile '+DATA/ora11g/datafile/mmlottery08.dbf' size 30720m autoextend on next 200m; alter tablespace MMLOTTERY add datafile '+DATA/ora11g/datafile/mmlottery08.dbf' size 30720m autoextend on next 200m * ERROR at line 1: ORA-01537: cannot add file '+DATA/ora11g/datafile/mmlottery08.dbf' - file already part of database

If the datafile is added to the table space by mistake, the deletion operation will be performed.

alter tablespace MMLOTTERY drop datafile '+DATA/ora11g/datafile/mmlottery08.dbf';

or

alter database datafile '+DATA/ora11g/datafile/mmlottery08.dbf' offline drop;

Extension:

Query the specified table space

SQL statement:

select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space_MB from dba_data_files where tablespace_name = 'MMLOTTERY' order by tablespace_name;

Query results:

TABLESPACE_NAME FILE_ID FILE_NAME TOTAL_SPACE_MB ------------------- ---------- ------------------------------------------- -------------- MMLOTTERY 18 +DATA/ora11g/datafile/mmlottery01.dbf 30720 MMLOTTERY 19 +DATA/ora11g/datafile/mmlottery02.dbf 30720 MMLOTTERY 20 +DATA/ora11g/datafile/mmlottery03.dbf 30720 MMLOTTERY 22 +DATA/ora11g/datafile/mmlottery04.dbf 30720 MMLOTTERY 23 +DATA/ora11g/datafile/mmlottery05.dbf 30720 MMLOTTERY 26 +DATA/ora11g/datafile/mmlottery06.dbf 30720 MMLOTTERY 27 +DATA/ora11g/datafile/mmlottery07.dbf 30720 7 rows selected.

Recommended tutorial: "Oracle Video Tutorial"

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