背景：
当我给EBS打NLS补丁的时候，发现表空间快满了，结果迅速增加表空间。
操作步骤：
以下是查询表空间利用率的语句：
select
a.a1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2/1024/1024 表空间大小M,
(b.b2-a.a2)/1024/1024 已使用M,
substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
from
(select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1;
2. 以下是结果:
SQL> select
a.a1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2/1024/1024 表空间大小M,
(b.b2-a.a2)/1024/1024 已使用M,
substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
from
(select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1; 2 3 4 5 6 7 8 9 10 11 12
表空间名称 类型 区管理 表空间大小M 已使用M
------------------------------ --------- ---------- ----------- ----------
利用率
---------------
APPS_TS_QUEUES PERMANENT LOCAL 1000 99.625
9.962
CTXD PERMANENT LOCAL 19 17.2109375
90.58
OWAPUB PERMANENT LOCAL 10 .078125
.7812
表空间名称 类型 区管理 表空间大小M 已使用M
------------------------------ --------- ---------- ----------- ----------
利用率
---------------
APPS_TS_TOOLS PERMANENT LOCAL 500 .125
.025
APPS_UNDOTS1 UNDO LOCAL 1802 1349.75
74.90
APPS_TS_NOLOGGING PERMANENT LOCAL 64 38.375
59.96
表空间名称 类型 区管理 表空间大小M 已使用M
------------------------------ --------- ---------- ----------- ----------
利用率
---------------
APPS_TS_ARCHIVE PERMANENT LOCAL 866 499.875
57.72
APPS_TS_TX_IDX PERMANENT LOCAL 7736.75 7056.375
91.20
APPS_TS_SEED PERMANENT LOCAL 2982 2862
95.97
表空间名称 类型 区管理 表空间大小M 已使用M
------------------------------ --------- ---------- ----------- ----------
利用率
---------------
SYSTEM PERMANENT DICTIONARY 12012.1328 10812.6328
90.01
APPS_TS_INTERFACE PERMANENT LOCAL 1041 787.5
75.64
ODM PERMANENT LOCAL 11 9.5625
86.93
表空间名称 类型 区管理 表空间大小M 已使用M
------------------------------ --------- ---------- ----------- ----------
利用率
---------------
OLAP PERMANENT LOCAL 17.2421875 15.4921875
89.85
APPS_TS_TX_DATA PERMANENT LOCAL 5680.125 5424.375
94.20
PORTAL PERMANENT LOCAL 100 .46875
.4687
表空间名称 类型 区管理 表空间大小M 已使用M
------------------------------ --------- ---------- ----------- ----------
利用率
---------------
APPS_TS_SUMMARY PERMANENT LOCAL 1146 892.75
77.90
APPS_TS_MEDIA PERMANENT LOCAL 1446 1169.125
80.85
17 rows selected.
SQL>
分析结果：
从结果可以看到：（
APPS_TS_TX_DATA PERMANENT LOCAL 5680.125 5424.375
94.20 ），这个表空间快满了。那么就给这个表空间增加1G空间。
4. 查看表空间的名字和所属文件
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
5。 以下是结果：
SQL> select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name; 2 3 4
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
APPS_TS_ARCHIVE 398
/u01/db/apps_st/data/a_archive01.dbf
866
APPS_TS_INTERFACE 395
/u01/db/apps_st/data/a_int01.dbf
1026
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
APPS_TS_INTERFACE 17
/u01/db/apps_st/data/a_int02.dbf
15
APPS_TS_MEDIA 400
/u01/db/apps_st/data/a_media01.dbf
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
1446
APPS_TS_NOLOGGING 397
/u01/db/apps_st/data/a_nolog01.dbf
64
APPS_TS_QUEUES 8
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
/u01/db/apps_st/data/a_queue02.dbf
500
APPS_TS_QUEUES 399
/u01/db/apps_st/data/a_queue01.dbf
500
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
APPS_TS_SEED 407
/u01/db/apps_st/data/a_ref02.dbf
1172
APPS_TS_SEED 16
/u01/db/apps_st/data/a_ref03.dbf
310
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
APPS_TS_SEED 394
/u01/db/apps_st/data/a_ref01.dbf
1500
APPS_TS_SUMMARY 396
/u01/db/apps_st/data/a_summ01.dbf
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
1146
APPS_TS_TOOLS 12
/u01/db/apps_st/data/apps_ts_tools01.dbf
500
APPS_TS_TX_DATA 14
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
/u01/db/apps_st/data/a_txn_data04.dbf
884
APPS_TS_TX_DATA 21
/u01/db/apps_st/data/a_txn_data05.dbf
1000
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
APPS_TS_TX_DATA 392
/u01/db/apps_st/data/a_txn_data01.dbf
1381
APPS_TS_TX_DATA 401
/u01/db/apps_st/data/a_txn_data02.dbf
1985
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
APPS_TS_TX_DATA 402
/u01/db/apps_st/data/a_txn_data03.dbf
1430
APPS_TS_TX_IDX 406
/u01/db/apps_st/data/a_txn_ind05.dbf
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
1911
APPS_TS_TX_IDX 405
/u01/db/apps_st/data/a_txn_ind04.dbf
1038
APPS_TS_TX_IDX 404
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
/u01/db/apps_st/data/a_txn_ind03.dbf
1138
APPS_TS_TX_IDX 403
/u01/db/apps_st/data/a_txn_ind02.dbf
1573
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
APPS_TS_TX_IDX 393
/u01/db/apps_st/data/a_txn_ind01.dbf
1089
APPS_TS_TX_IDX 15
/u01/db/apps_st/data/a_txn_ind06.dbf
989
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
APPS_UNDOTS1 20
/u01/db/apps_st/data/undo2.dbf
29
APPS_UNDOTS1 379
/u01/db/apps_st/data/undo01.dbf
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
1773
CTXD 6
/u01/db/apps_st/data/ctxd01.dbf
19
ODM 9
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
/u01/db/apps_st/data/odm.dbf
11
OLAP 19
/u01/db/apps_st/data/olap01.dbf
0
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
OLAP 10
/u01/db/apps_st/data/olap.dbf
17
OWAPUB 7
/u01/db/apps_st/data/owad01.dbf
10
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
PORTAL 314
/u01/db/apps_st/data/portal01.dbf
100
SYSAUX 11
/u01/db/apps_st/data/sysaux01.dbf
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
334
SYSAUX 18
/u01/db/apps_st/data/sysaux02.dbf
141
SYSTEM 4
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
/u01/db/apps_st/data/system04.dbf
1014
SYSTEM 3
/u01/db/apps_st/data/system03.dbf
1015
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
SYSTEM 2
/u01/db/apps_st/data/system02.dbf
1015
SYSTEM 1
/u01/db/apps_st/data/system01.dbf
1014
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
SYSTEM 288
/u01/db/apps_st/data/system10.dbf
677
SYSTEM 354
/u01/db/apps_st/data/system11.dbf
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
1014
SYSTEM 352
/u01/db/apps_st/data/system09.dbf
656
SYSTEM 5
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
/u01/db/apps_st/data/system05.dbf
1013
SYSTEM 295
/u01/db/apps_st/data/system06.dbf
762
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
SYSTEM 351
/u01/db/apps_st/data/system07.dbf
762
SYSTEM 353
/u01/db/apps_st/data/system08.dbf
1812
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
TOTAL_SPACE
-----------
SYSTEM 13
/u01/db/apps_st/data/system12.dbf
1259
45 rows selected.
SQL>
以上可见表空间名称和数据文件对应关系：
（
APPS_TS_TX_DATA 392
/u01/db/apps_st/data/a_txn_data01.dbf
1381 ）
5。 下面就给这个表空间增加1G的数据文件
6.
用system/manager登录PL/SQL Developer
新建一个command窗口
运行如下SQL语句：
ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE
'/d01/dba/vis01data/tx_data14.dbf' SIZE 1000M;
（增加1000M到APPS_TS_TX_DATA表空间）
注意：执行上面命令是增加tx_data**.dbf文件, **请用“不存在SQL结果”里的数字代替，一般为SQL中查询中的最大数字加1。
阅读(2039) | 评论(0) | 转发(0) |
