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!