ORACLE EBS 表空间控制

--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes

SELECT a.tablespace_name "表空间名",
       total "表空间大小",
       free "表空间剩余大小",
       (total - free) "表空间使用大小",
       total / (1024 * 1024 * 1024) "表空间大小(G)",
       free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
       (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
       ROUND((total - free) / total, 4) * 100 "使用率 %"
  FROM (SELECT tablespace_name, SUM(bytes) free
          FROM DBA_FREE_SPACE
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM DBA_DATA_FILES
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name

 

SELECT *
  FROM (SELECT TS.TABLESPACE_NAME,
               NVL(ROUND(TOTAL_D.TOTAL_BYTES / POWER(2, 20), 0), 0) TOTAL_M,
               NVL(ROUND(FREE_D.FREE_BYTES / POWER(2, 20), 0), 0) FREE_M,
               ROUND(NVL(FREE_D.FREE_BYTES, 0) /
                     (NVL(TOTAL_D.TOTAL_BYTES, 0) + 0.000001),
                     4) * 100 "Free%"
          FROM (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL_BYTES
                  FROM DBA_DATA_FILES DDF
                 GROUP BY DDF.TABLESPACE_NAME) TOTAL_D,
               (SELECT TABLESPACE_NAME, SUM(BYTES) FREE_BYTES
                  FROM DBA_FREE_SPACE DFS
                 GROUP BY DFS.TABLESPACE_NAME) FREE_D,
               DBA_TABLESPACES TS
         WHERE TS.TABLESPACE_NAME = TOTAL_D.TABLESPACE_NAME(+)
           AND TS.TABLESPACE_NAME = FREE_D.TABLESPACE_NAME(+)) TS_FREE_SPACE
 WHERE 1 = 1
   --AND TS_FREE_SPACE.TABLESPACE_NAME != 'TEMP'
   --AND TS_FREE_SPACE.TABLESPACE_NAME = 'HPTS_DATA'
 ORDER BY "Free%";

 

SELECT ddf.file_name
  FROM Dba_Data_Files ddf
 where ddf.tablespace_name = 'APPS_TS_TX_IDX'; 
 

/u01/tst2/db/tst2data/TST2/datafile/o1_mf_apps_ts__7f0vk8j4_.dbf
 --如果小于4G。直接RESIZEM
 

ALTER DATABASE DATAFILE '/data/orauat/oradata/uat/hpts_data_01.dbf' RESIZE 4096M;

 --如果大于4G。则增加数据文件

 ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '/u01/tst2/db/tst2data/TST2/datafile/o1_mf_apps_ts__7f0vk8j4_01.dbf' SIZE 1000M;--（增加100M到APPS_TS_TX_DATA表空间）

 

 ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '/u01/tst2/db/tst2data/TST2/datafile/o1_mf_apps_ts__7f0vk8j4_02.dbf' SIZE 1000M;--（增加100M到APPS_TS_TX_DATA表空间）