oralce 查看表空间使用情况

柔情只为你懂 2022-04-13 10:16 189阅读 0赞

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

sql汇总使用：

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