Oracle 查看表空间的大小及使用情况sql语句

sql1:

--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;
--2、查看表空间物理文件的名称及大小
select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
from dba_data_files
order by tablespace_name;
--3、查看回滚段名称及大小
select segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
from dba_rollback_segs r, v$rollstat v
where r.segment_id = v.usn(+)
order by segment_name;
--4、查看控制文件
select name from v$controlfile;
--5、查看日志文件
select member from v$logfile;
--6、查看表空间的使用情况
select sum(bytes) / (1024 * 1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;
select a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% used ",
(c.bytes * 100) / a.bytes "% free "
from sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name;
--7、查看数据库库对象
select owner, object_type, status, count(*) count#
from all_objects
group by owner, object_type, status;
--8、查看数据库的版本　
select version
from product_component_version
where substr(product, 1, 6) = 'oracle';
--9、查看数据库的创建日期和归档方式
select created, log_mode, log_mode from v$database;

sql2:

--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