Oracle 查看表空间大小及其扩展

    在ORACLE数据库中，所有数据从逻辑结构上看都是存放在表空间当中，当然表空间下还有段、区、块等逻辑结构。从物理结构上看是放在数据文件中。一个表空间可由多个数据文件组成。系统中默认创建的几个表空间：SYSTEM，SYSAUX，USERS，UNDOTBS1，EXAMPLE，TEMP还有用户自己建立的表空间，可以对其进行分成三类

　　永久表空间                存放永久性数据，如表，索引等。

　　临时表空间                不能存放永久性对象，用于保存数据库排序，分组时产生的临时数据。

　　UNDO表空间             保存数据修改前的镜象。

我们可以通过下面几个系统视图查看基本信息：

--包含数据库中所有表空间的描述信息

SELECT * FROM DBA_TABLESPACES

--包含当前用户的表空间的描叙信息

SELECT * FROM USER_TABLESPACES

--包含从控制文件中获取的表空间名称和编号信息

SELECT * FROM V$TABLESPACE;

查看数据文件

  --包含数据文件以及所属的表空间的描述信息

SELECT * FROM DBA_DATA_FILES

--包含临时数据文件以及所属的表空间的描述信息

SELECT * FROM DBA_TEMP_FILES

--包含从控制文件中获取的数据文件的基本信息，包括它所属的表空间名称、编号等

SELECT * FROM V$DATAFILE

--包含所有临时数据文件的基本信息

SELECT * FROM V$TEMPFILE

可以使用以下脚本查看数据库表空间使用情况：

SQL1：

SELECT DBF.TABLESPACE_NAME,

       DBF.TOTALSPACE "总量(M)",

       DBF.TOTALBLOCKS AS 总块数,

       DBF.TOTALSPACE-DFS.FREESPACE "使用量(M)",

       DBF.TOTALBLOCKS-DFS.FREEBLOCKS AS 使用块数,      

       DFS.FREESPACE "剩余总量(M)",

       DFS.FREEBLOCKS "剩余块数",

       (DFS.FREESPACE / DBF.TOTALSPACE) * 100 "空闲比例"

  FROM (SELECT T.TABLESPACE_NAME,

               SUM(T.BYTES) / 1024 / 1024 TOTALSPACE,

               SUM(T.BLOCKS) TOTALBLOCKS

          FROM DBA_DATA_FILES T

         GROUP BY T.TABLESPACE_NAME) DBF,

       (SELECT TT.TABLESPACE_NAME,

               SUM(TT.BYTES) / 1024 / 1024 FREESPACE,

               SUM(TT.BLOCKS) FREEBLOCKS

          FROM DBA_FREE_SPACE TT

         GROUP BY TT.TABLESPACE_NAME) DFS

 WHERE TRIM(DBF.TABLESPACE_NAME) = TRIM(DFS.TABLESPACE_NAME);

 SQL2：

SELECT A.TABLESPACE_NAME,

       A.BYTES/ 1024 / 1024 TOTAL,

       B.BYTES/ 1024 / 1024 USED,

       C.BYTES/ 1024 / 1024 FREE,

       (B.BYTES * 100) / A.BYTES "% USED ",

       (C.BYTES * 100) / A.BYTES "% FREE "

  FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C

 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME

   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

 SQL3：

SELECT Total.name "Tablespace Name",

       Free_space,

       (total_space - Free_space) Used_space,

       total_space

  FROM (select tablespace_name, sum(bytes / 1024 / 1024) Free_Space

          from sys.dba_free_space

         group by tablespace_name) Free,

       (select b.name, sum(bytes / 1024 / 1024) TOTAL_SPACE

          from sys.v_$datafile a, sys.v_$tablespace B

         where a.ts# = b.ts#

         group by b.name) Total

 WHERE Free.Tablespace_name = Total.name;

如果发现表空间快满了，则需要增加相应的表空间。增加表空间可以按照以下步骤：

1. 此表空间是大文件表空间

    oracle引入了大文件表空间的概念，正常表空间的数据文件可以有很多个，每个大小最大为32G，而大文件表空间的数据文件只有一个，所以如果该表空间是大文件表空间的话，则无法增加数据文件，只能增加文件大小。

   select bigfile from dba_tablespaces where tablespace_name = '表空间名称'  ----返回 YES 则是大文件表空间，返回NO，就是普通的表空间（小文件的）。 一般来说大数据文件表空间都是自动扩展的，不需要手动增加大小，

   select * from dba_data_files  ------里面有个autoextensible 字段，表示是否自动扩展；maxblocks  表示每次自动扩展多大

   如若不是，则需要增加数据文件大小来增加表空间，先查看表空间的名字及文件所在位置：

select tablespace_name, file_id, file_name,

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

from dba_data_files

order by tablespace_name

  增大所需表空间大小：

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

例如：

alter database datafile '\oracle\oradata\test.dbf' resize 4000m

2.此表空间是普通文件表空间

此类型的表空间增加有2种方法，一种是为表空间增加数据文件，另一种方法是增加表空间原有数据文件尺寸。

两种方法，一种是为表空间增加数据文件： 

代码如下:

alter tablespace users add datafile '/opt/oracle/oradata/esop/test02.dbf' size 200M; 

另一种方法是增加表空间原有数据文件尺寸： 

代码如下:

alter database datafile '/opt/oracle/oradata/esop/test01.dbf' resize 200M; 

一般情况下，表空间增长都设置为自动，可以用参数设置

增加表空间的方式增加： 

alter tablespace 表空间名称 add datafile 表空间存放路径  size 表空间大小 autoextend on next 增长的大小 maxsize 空间最大值(如果不限制空间最大值就用unlimited)

修改表空间的方式增加： 

alter database datafile 表空间文件路径 AUTOEXTEND（自动扩展） ON NEXT 表空间满后增加的大小

增加完后可以再用上述的脚本再进行查询可以发现表空间大小的改变！

设置表空间不足时自动增长

1. 查看表空间是否自动增长

SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;

2. 设置表空间自动增长

ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON;//打开自动增长

ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON NEXT 200M ;//每次自动增长200m

ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON NEXT 200M MAXSIZE 1024M;//每次自动增长200m，数据表最大不超过1G