Oracle Table Space Expansion method

Test environment

Os:redhat 6.7

oracle:11.2.0.4

[Email protected] ~]# su-oracle

[Email protected] ~]$ Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.4.0 Production on Thu May 25 15:09:24 2017

Copyright (c) 1982, Oracle. All rights reserved.

Connect to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the partitioning, OLAP, Data Mining and Real application testing options

[Email protected] >

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

Set Linesize 200

Col file_name for A50

Col Tablespace_name for A20

Select Tablespace_name,

FILE_ID,

file_name,

Round (Bytes/(1024x768), 0) Total_space

From Sys.dba_data_files

Order BY Tablespace_name;

There are three ways to scale a tablespace:

Method One: Increase the size of the required table space:

ALTER DATABASE datafile ' table space position ' Resize new dimensions

For example:

ALTER DATABASE datafile '/ORA_DATA/ICSDB/SYSTEM01.DBF ' resize 500M;

For the table space of an Oracle database, in addition to manually increasing the size of the data file , you can also increase the size of the table space by increasing the number of data files .

Method Two: Increase the number of data files

Alter tablespace tablespace name add datafile ' New data file address ' size data file

For example:

Alter tablespace system add datafile '/ora_data/icsdb/system02.dbf ' size 1G;

Method Three: Set table Space Auto-expansion.

ALTER DATABASE datafile ' data file Location ' Autoextend on next auto scale size maxsize maximum extended size

For example:

ALTER DATABASE datafile '/ORA_DATA/ICSDB/SYSTEM02.DBF ' autoextend on next 500m maxsize 10000m;

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) * 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;

This article is from the "Record Learning" blog, please be sure to keep this source http://laobaiv1.blog.51cto.com/2893832/1929403

Oracle Table Space Expansion method