测试环境

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 星期四 5月 25 15:09:24 2017

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

连接到:

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 >

第一步：查看表空间的名字及文件所在位置：

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;

表空间扩容有三个方法：

方法一：增大所需表空间大小：

alter database datafile '表空间位置' resize 新的尺寸

例如：

alter database datafile '/ora_data/icsdb/system01.dbf' resize 500M;

对于oracle数据库的表空间，除了用手动增加数据文件大小外，还可以增加数据文件数量等方式扩展表空间大小。

方法二：增加数据文件个数

alter tablespace 表空间名称 add datafile '新的数据文件地址' size 数据文件大小

例如：

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

方法三：设置表空间自动扩展。

alter database datafile '数据文件位置' autoextend on next 自动扩展大小 maxsize 最大扩展大小

例如：

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

查询表空间使用情况：

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;

1、查询当前表空间使用情况

col FILE_NAME format a50;

col SPACE_NAME format a15;

select b.file_name file_name,b.tablespace_name space_name,

b.bytes/1024/1024 munM,(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM,

substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "use%"

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.bytes

order by b.tablespace_name;

2、查询各表占用空间情况，可以通过blocks块数判断空间占用情况

select table_name,tablespace_name,num_rows,blocks from all_tables where owner='IPEMS_DEV_EMS' order by blocks asc;

3、结合业务需要可以对历史数据表进行删除，如果需要删除的表较多，可通过拼接自动生成SQL脚本，也可利用notpad++工具手动拼接SQL脚本

alter table IPEMS_DEV_EMS.DO_5C6B3296CD1E_1062 move tablespace IPEMS01;

4、增加数据文件扩展表空间

alter tablespace IPEMS01 add datafile 'D:\APP\ADMINISTRATOR\ORADATA\IPEMS\IPEMS0104.DBF' size 30000m reuse autoextend on next 50M;

5、直接增大现有数据文件扩展表空间

ALTER DATABASE DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DBFIRST\DITS.DBF' RESIZE 400M;

6、关于oracle数据文件大小的说明：

64位OS 和64位oracle，默认oracle表空间数据文件用的BLOCKSIZE是8k，表空间数据文件最大是32G(最大blocks 2^22-1，单个数据文件最大为最大blocks*block_size (2^22-1)*8/1024/1204/1024)。要扩大单个数据文件的大小限制主要修改block_size的大小

uname -a 查看linuxOS的位数，sqlplus 登录oracle会显示oracle的位数，show parameter block;在命令行窗口可以查看数据块大小；

计算机右键“属性”查看windows的位数；

32位OS和32位oracle，默认oracle表空间datafile用的BLOCKSIZE也是8k，表空间数据文件最大也是32G。试了一下64G文件，也没有问题。

所以最终限制数据文件大小的还是oracle数据库，而不是操作系统。

7、oracle11g官方文档中关于物理数据库相关的限制

https://docs.oracle.com/cd/E11882_01/server.112/e40402/limits002.htm#REFRN0042