Oracle DBA--查看表空间的使用情况SQL语句

1. SQL语句1：

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

 

2.SQL语句2：
--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