修改单节点EBS表空间过程

寻找满的表空间

select a.file_id "file_id",a.tablespace_name "Tablespace_name", a.bytes/1024000 "Size M",(a.bytes-sum(nvl(b.bytes,0)))/1024000 "Used", sum(nvl(b.bytes,0))/1024000 "Free", sum(nvl(b.bytes,0))/a.bytes*100 "%free" from dba_data_files a, dba_free_space b where a.file_id=b.file_id(+) group by a.tablespace_name , a.file_id,a.bytes order by a.tablespace_name;

SELECT df.tablespace_name TABLESPACE,        df.total_space TOTAL_SPACE,        fs.free_space FREE_SPACE,        df.total_space_mb TOTAL_SPACE_MB,        (df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,        fs.free_space_mb FREE_SPACE_MB,        ROUND(100 * (fs.free_space / df.total_space), 2) PCT_FREE   FROM (SELECT tablespace_name,                SUM(bytes) TOTAL_SPACE,                ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB           FROM dba_data_files          GROUP BY tablespace_name) df,        (SELECT tablespace_name,                SUM(bytes) FREE_SPACE,                ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB           FROM dba_free_space          GROUP BY tablespace_name) fs  WHERE df.tablespace_name = fs.tablespace_name(+)  ORDER BY fs.tablespace_name;

发现较满的表空间为： ----APPS_TS_INTERFACE   APPS_TS_TX_IDX  APPS_TS_SEED    APPS_TS_MEDIA   APPS_UNDOTS1 SL SYSAUX

 

查找出tablespace_name 

2.查找 表空间确实已满后，使用以下SQL 确认 该表空间对应的DBF文件

select * from dba_data_files DDF WHERE ddf.TABLESPACE_NAME in ('APPS_TS_INTERFACE','APPS_TS_TX_IDX','APPS_TS_SEED','APPS_TS_MEDIA','APPS_UNDOTS1','SL','SYSAUX') order by ddf.TABLESPACE_NAME;

 查找出 FILE_NAME  较大为 APPS_TS_INTERFACE    /u01/oracle/FIN/db/apps_st/data/a_int02.dbf APPS_TS_MEDIA        /u01/oracle/FIN/db/apps_st/data/a_media01.dbf APPS_TS_SEED         /u01/oracle/FIN/db/apps_st/data/a_ref03.dbf APPS_TS_TX_IDX       /u01/oracle/FIN/db/apps_st/data/a_txn_ind06.dbf APPS_UNDOTS1         /u01/oracle/FIN/db/apps_st/data/undo01.dbf SL                   /u01/oracle/FIN/db/apps_st/data/sl_data01.dbf SYSAUX              /u01/oracle/FIN/db/apps_st/data/sysaux02.dbf

之后使用序号加一的方式扩大表空间

3.执行以下脚本 扩展表空间： alter tablespace APPS_TS_INTERFACE add datafile '/u01/oracle/FIN/db/apps_st/data/a_int03.dbf' size 100m; alter tablespace APPS_TS_MEDIA add datafile         '/u01/oracle/FIN/db/apps_st/data/a_media02.dbf'  size 100m; alter tablespace APPS_TS_SEED add datafile          '/u01/oracle/FIN/db/apps_st/data/a_ref04.dbf'  size 100m; alter tablespace APPS_TS_TX_IDX add datafile        '/u01/oracle/FIN/db/apps_st/data/a_txn_ind07.dbf' size 100m; alter tablespace APPS_UNDOTS1 add datafile          '/u01/oracle/FIN/db/apps_st/data/undo02.dbf'  size 100m; alter tablespace SL add datafile                    '/u01/oracle/FIN/db/apps_st/data/sl_data02.dbf'  size 100m; alter tablespace SYSAUX add datafile               '/u01/oracle/FIN/db/apps_st/data/sysaux03.dbf'  size 100m; alter tablespace APPS_TS_TX_IDX add datafile        '/u01/oracle/FIN/db/apps_st/data/a_txn_ind08.dbf' size 800m; ---再次查询