分类： Oracle
2012-03-14 15:19:46
## 以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>
