增加oracle表空间

## 以oracle用户登录(ssh or telnet)数据库主机
$ ssh oracle@数据库主机IP
## 以sysdba角色登录数据库
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 10 15:53:45 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
## 执行以下的语句检查表空间属性：主要查看是否为BIG类型
SQL> select * From v$tablespace;
       TS# NAME      INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
  0 SYSTEM     YES NO  YES
  1 UNDO      YES NO  YES
  2 SYSAUX     YES NO  YES
  3 TEMP      NO  NO  YES
  4 TOOLS     YES NO  YES
  5 TBS_USER_DAT     YES NO  YES
  6 TBS_USER_IDX     YES NO  YES
  7 TBS_RMAN     YES NO  YES
  8 TEST_MU     YES NO  YES
9 rows selected.
SQL> set line 110
SQL> set wrap off
## 执行以下语句查看表空间对应的数据文件：
SQL> select ts#, name from v$datafile;
       TS# NAME
---------- ---------------------------------------------------------------------------------------------------
  0 /opt/oracle/oradata/SOC/system01.dbf
  1 /opt/oracle/oradata/SOC/rbs01.dbf
  2 /opt/oracle/oradata/SOC/sysaux01.dbf
  4 /opt/oracle/oradata/SOC/tools01.dbf
  5 /opt/oracle/oradata/SOC/udata01.dbf
  6 /opt/oracle/oradata/SOC/uindex01.dbf
  7 /opt/oracle/oradata/SOC/rman01.dbf
  8 /opt/oracle/oradata/SOC/test_mu01.dbf
8 rows selected.
SQL>
## 对非大文件类型（BIG字段的值为NO）的表空间（如TOOLS表空间），有两种方式扩大表空间：
## 1、增加新的数据文件
SQL> alter tablespace tools add datafile '/opt/oracle/oradata/SOC/tools02.dbf' size 10 M;
Tablespace altered.
SQL>
## 增加后查看数据文件的增加情况：
SQL> select ts#, round(bytes/1024/1024),name from v$datafile order by ts#;
       TS# ROUND(BYTES/1024/1024) NAME
---------- ---------------------- ----------------------------------------------------------------------------
  0        512 /opt/oracle/oradata/SOC/system01.dbf
  1         32 /opt/oracle/oradata/SOC/rbs01.dbf
  2        256 /opt/oracle/oradata/SOC/sysaux01.dbf
  4        128 /opt/oracle/oradata/SOC/tools01.dbf
  4         10 /opt/oracle/oradata/SOC/tools02.dbf
  5       1000 /opt/oracle/oradata/SOC/udata01.dbf
  6        500 /opt/oracle/oradata/SOC/uindex01.dbf
  7        256 /opt/oracle/oradata/SOC/rman01.dbf
  8        100 /opt/oracle/oradata/SOC/test_mu01.dbf
9 rows selected.
SQL>
## 2、扩大原有的数据文件
SQL> alter database datafile '/opt/oracle/oradata/SOC/tools02.dbf' resize 20 M;
Database altered.
## 扩大/opt/oracle/oradata/SOC/tools02.dbf到20M后再次查看：
SQL> select ts#, round(bytes/1024/1024),name from v$datafile order by ts#;
       TS# ROUND(BYTES/1024/1024) NAME
---------- ---------------------- ----------------------------------------------------------------------------
  0        512 /opt/oracle/oradata/SOC/system01.dbf
  1         32 /opt/oracle/oradata/SOC/rbs01.dbf
  2        256 /opt/oracle/oradata/SOC/sysaux01.dbf
  4        128 /opt/oracle/oradata/SOC/tools01.dbf
  4         20 /opt/oracle/oradata/SOC/tools02.dbf
  5       1000 /opt/oracle/oradata/SOC/udata01.dbf
  6        500 /opt/oracle/oradata/SOC/uindex01.dbf
  7        256 /opt/oracle/oradata/SOC/rman01.dbf
  8        100 /opt/oracle/oradata/SOC/test_mu01.dbf
9 rows selected.
SQL>
## 对大文件类型（BIG字段的值为YES）的表空间，只能扩大原有的数据文件：
## 如下面环境中的TBS_USER_DAT和TBS_USER_IDX表空间都是大文件类型的表空间
SQL> select * from v$tablespace;
       TS# NAME      INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
  0 SYSTEM     YES NO  YES
  1 UNDO      YES NO  YES
  2 SYSAUX     YES NO  YES
  3 TEMP      NO  NO  YES
  4 TOOLS     YES NO  YES
  5 TBS_USER_DAT     YES YES YES
  6 TBS_USER_IDX     YES YES YES
6 rows selected.
SQL>
## 查看表空间文件名以及其大小：
SQL> select ts#, round(bytes/1024/1024),name from v$datafile;
       TS# ROUND(BYTES/1024/1024) NAME
---------- ---------------------- ----------------------------------------------------------------------------
  0       8192 /opt/oracle/oradata/SOC/system01.dbf
  1      16384 /opt/oracle/oradata/SOC/rbs01.dbf
  2       2048 /opt/oracle/oradata/SOC/sysaux01.dbf
  4       1024 /opt/oracle/oradata/SOC/tools01.dbf
  5      51200 /opt/oracle/oradata/SOC/udata01.dbf
  6      25600 /opt/oracle/oradata/SOC/uindex01.dbf
6 rows selected.
SQL>
## 修改数据文件大小：
SQL> alter database datafile '/opt/oracle/oradata/SOC/uindex01.dbf' resize 25610 M;
Database altered.
## 再次查看
SQL> select ts#, round(bytes/1024/1024),name from v$datafile;
       TS# ROUND(BYTES/1024/1024) NAME
---------- ---------------------- ----------------------------------------------------------------------------
  0       8192 /opt/oracle/oradata/SOC/system01.dbf
  1      16384 /opt/oracle/oradata/SOC/rbs01.dbf
  2       2048 /opt/oracle/oradata/SOC/sysaux01.dbf
  4       1024 /opt/oracle/oradata/SOC/tools01.dbf
  5      51200 /opt/oracle/oradata/SOC/udata01.dbf
  6      25610 /opt/oracle/oradata/SOC/uindex01.dbf
6 rows selected.
SQL>
## 检查表空间大小的SQL
SQL> SELECT V1.TABLESPACE_NAME TABLESPACENAME,
       to_char(ROUND(NVL(V1.SPACE,0)))||'(M)' TOTALTABLESPACE,
       to_char(ROUND(NVL(V2.SPACE,0)))||'(M)' FREETABLESPACE
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) V1,
       (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) V2
 WHERE V1.TABLESPACE_NAME = V2.TABLESPACE_NAME( )
 ORDER BY V1.TABLESPACE_NAME;
  2    3    4    5    6    7
TABLESPACENAME         TOTALTABLESPACE       FREETABLESPACE
------------------------------ ------------------------------------------- -----------------------------------
SYSAUX          2048(M)        1753(M)
SYSTEM          8192(M)        7321(M)
TBS_USER_DAT         51200(M)        37238(M)
TBS_USER_IDX         25610(M)        21014(M)
TOOLS          1024(M)        1024(M)
UNDO          16384(M)        15467(M)
6 rows selected.
SQL>



作者 scrit