Shulou(Shulou.com)06/01 Report--

Test environment

OS:RedHat 6.7

Oracle:11.2.0.4

[root@iccsdb01 ~] # su-oracle

[oracle@iccsdb01 ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thursday May 25 15:09:24 2017

Copyright (c) 1982, 2013, 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

SYS@icsdb >

Step 1: check the name of the tablespace and the location of the file:

Set linesize 200

Col FILE_NAME for a50

Col TABLESPACE_NAME for a20

Select tablespace_name

File_id

File_name

Round (bytes / (1024 * 1024), 0) total_space

From sys.dba_data_files

Order by tablespace_name

There are three ways to expand tablespaces:

Method 1: increase the required tablespace size:

New size of alter database datafile 'tablespace location' resize

For example:

Alter database datafile'/ ora_data/icsdb/system01.dbf' resize 500m

For the table space of oracle database, in addition to manually increasing the data file size, you can also increase the number of data files and other ways to expand the table space size.

Method 2: increase the number of data files

Alter tablespace tablespace name add datafile 'new datafile address' size datafile size

For example:

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

Method 3: set the tablespace to expand automatically.

Alter database datafile 'data file location' autoextend on next automatic extension size maxsize maximum extension size

For example:

Alter database datafile'/ ora_data/icsdb/system02.dbf' autoextend on next 500m maxsize 10000m

Query tablespace 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.