2.1.1 EBS表空间查询和扩充

1.查询表空间利用率

--1G=1024MB

--1M=1024KB 

--1K=1024Bytes

--1M=11048576Bytes 

--1G=1024*11048576Bytes=11313741824Bytes 

SELECT d.tablespace_name,
       space "SUM_SPACE(M)",
       blocks sum_blocks,
       space - nvl(free_space, 0) "USED_SPACE(M)",
       round((1 - nvl(free_space, 0) / space) * 100, 2) "USED_RATE(%)",
       free_space "FREE_SPACE(M)"
  FROM (SELECT tablespace_name,
               round(SUM(bytes) / (1024 * 1024), 2) space,
               SUM(blocks) blocks
          FROM dba_data_files
         GROUP BY tablespace_name) d,
       (SELECT tablespace_name,
               round(SUM(bytes) / (1024 * 1024), 2) free_space
          FROM dba_free_space
         GROUP BY tablespace_name) f
 WHERE d.tablespace_name = f.tablespace_name(+)
UNION ALL --if have tempfile
SELECT d.tablespace_name,
       space "SUM_SPACE(M)",
       blocks sum_blocks,
       used_space "USED_SPACE(M)",
       round(nvl(used_space, 0) / space * 100, 2) "USED_RATE(%)",
       nvl(free_space, 0) "FREE_SPACE(M)"
  FROM (SELECT tablespace_name,
               round(SUM(bytes) / (1024 * 1024), 2) space,
               SUM(blocks) blocks
          FROM dba_temp_files
         GROUP BY tablespace_name) d,
       (SELECT tablespace_name,
               round(SUM(bytes_used) / (1024 * 1024), 2) used_space,
               round(SUM(bytes_free) / (1024 * 1024), 2) free_space
          FROM v$temp_space_header
         GROUP BY tablespace_name) f
 WHERE d.tablespace_name = f.tablespace_name(+);

2.查询表空间对应的文件

SELECT t.tablespace_name,
       d.file_name,
       round(SUM(bytes / (1024 * 1024)), 0) ts_size
  FROM dba_tablespaces t, dba_data_files d
 WHERE t.tablespace_name = d.tablespace_name
   AND t.tablespace_name = 'APPS_TS_TX_DATA'
 GROUP BY t.tablespace_name, d.file_name;

3.查询临时表空间对应文件

SELECT * FROM dba_temp_files;
SELECT file_name,FILE_ID,TABLESPACE_NAME,bytes / (1024 * 1024) "SIZE(M)" FROM dba_temp_files ORDER BY 3,2;

4.扩大临时表空间文件大小

ALTER DATABASE TEMPFILE '/u01/prod/db/data/temp0002.dbf' RESIZE 2G;--已实测

--添加表空间文件

alter tablespace temp add tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02.DBF' size 4000m;

5.扩大非临时表空间文件大小

ALTER DATABASE DATAFILE '/u01/prod/db/data/a_queue02.dbf'  
RESIZE 8192M; 

--添加表空间文件
ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE  '/u01/prod/db/data/a_txn_data10.dbf' SIZE 8G