Oracle表空间

最新推荐文章于 2024-07-06 19:23:27 发布

萌虫小焕焕

最新推荐文章于 2024-07-06 19:23:27 发布

阅读量739

点赞数

分类专栏： Oracle EBS 文章标签： oracle 表空间 plsql 查询

本文链接：https://blog.csdn.net/gh320/article/details/17245513

版权

Oracle EBS 专栏收录该内容

84 篇文章 11 订阅

订阅专栏

Oracle表空间
--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 4096 m;
--如果大于4G。则增加数据文件
ALTER tablespace apps_ts_tx_idx add datafile '/u01/tst2/db/tst2data/TST2/datafile/o1_mf_apps_ts__7f0vk8j4_01.dbf' size 1000 m; --（增加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 1000 m; --（增加100M到APPS_TS_TX_DATA表空间）