Oracle表空间

来源：互联网发布：小米盒子刷linux 编辑：程序博客网时间：2024/05/10 21:31
Oracle表空间--1G=1024MB--1M=1024KB--1K=1024Bytes--1M=11048576Bytes--1G=1024*11048576Bytes=11313741824BytesSELECT 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。直接RESIZEMALTER 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表空间）
0 0