首页 > 数据库 > Oracle > 正文
增加oracle表空间
2012-03-15       个评论      
收藏    我要投稿
## 以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
点击复制链接 与好友分享!回本站首页
相关TAG标签 空间
上一篇:oracle视图
下一篇:oracle嵌套循环跳出内部循环体
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站