Oracle-查看表空间大小(小总结)

1.方式一：表空间名和大小；

select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0) ts_size
  from dba_tablespaces t, dba_data_files d
 where t.tablespace_name = d.tablespace_name
 group by t.tablespace_name;

显示结果：

1        DATATB        50480
2        UNDOTBS1        5436
3        SYSAUX        5199
4        TBS_MEDIATION_D01        5120
5        USERS        2053
6        TBS_MEDIATION_DEF        5120
7        PARAMETERTB        5120
8        SYSTEM        4796
9        TBS_MEDIATION_I01        5120
10        INDXTB        20480

2.方式二：表空间名和剩余大小，使用大小，总大小；

SELECT Total.name "Tablespace Name",
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name;

显示结果：

1        DATATB        9025.125        41454.875        50480
2        SYSAUX        3279.8125        1919.1875        5199
3        UNDOTBS1        5410.5625        25.4375        5436
4        TBS_MEDIATION_D01        5119.9375        0.0625        5120
5        USERS        2052.5        0.5        2053
6        TBS_MEDIATION_DEF        4926.75        193.25        5120
7        PARAMETERTB        5118.375        1.625        5120
8        SYSTEM        1981        2815        4796
9        TBS_MEDIATION_I01        5119.9375        0.0625        5120
10        INDXTB        9714.25        10765.75        20480

或者(推荐)：

SELECT a.tablespace_name "表空间名",
       total 表空间大小,
       free 表空间剩余大小,
       (total - free) 表空间使用大小,
       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;

显示结果：
1        DATATB        52932116480        6322520064        46609596416        88.06
2        SYSAUX        5451546624        3439132672        2012413952        36.91
3        UNDOTBS1        5700059136        5669126144        30932992        0.54
4        TBS_MEDIATION_D01        5368709120        5368643584        65536        0
5        USERS        2152726528        2152202240        524288        0.02
6        TBS_MEDIATION_DEF        5368709120        5166071808        202637312        3.77
7        PARAMETERTB        5368709120        5367005184        1703936        0.03
8        SYSTEM        5028970496        2077229056        2951741440        58.69
9        TBS_MEDIATION_I01        5368709120        5368643584        65536        0
10        INDXTB        21474836480        10160963584        11313872896        52.68

3.方式二：最优方式；

select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);

显示结果：
1        DATATB                 20480        2621440       20479.4375       2621368       99.9972534179688
2        SYSAUX                 1159.1875    148376        60.0625          7688          5.18143095918477
3        UNDOTBS1               1555         199040        1525.5625        195272        98.1069131832797
4        TBS_MEDIATION_D01      5120         655360        5119.9375        655352        99.998779296875

4.查看当前用户各表所占表空间；

Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name；