Oracle DBA–查看錶空間的使用方式SQL語句

1. SQL語句1：

--1、查看錶空間的名稱及大小<br />SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size<br /> FROM dba_tablespaces t, dba_data_files d<br /> WHERE t.tablespace_name = d.tablespace_name<br /> GROUP BY t.tablespace_name;<br />--2、查看錶空間物理檔案的名稱及大小<br />SELECT tablespace_name,<br /> file_id,<br /> file_name,<br /> round(bytes / (1024 * 1024), 0) total_space<br /> FROM dba_data_files<br /> ORDER BY tablespace_name;<br />--3、查看復原段名稱及大小<br />SELECT segment_name,<br /> tablespace_name,<br /> r.status,<br /> (initial_extent / 1024) InitialExtent,<br /> (next_extent / 1024) NextExtent,<br /> max_extents,<br /> v.curext CurExtent<br /> FROM dba_rollback_segs r, v$rollstat v<br /> WHERE r.segment_id = v.usn(+)<br /> ORDER BY segment_name;<br />--4、查看控制檔案<br />SELECT name FROM v$controlfile;<br />--5、查看記錄檔<br />SELECT member FROM v$logfile;<br />--6、查看錶空間的使用方式<br />SELECT SUM(BYtes) / (1024 * 1024) as free_space, tablespace_name<br /> FROM dba_free_space<br /> GROUP BY tablespace_name;<br />SELECT A.TABLESPACE_NAME,<br /> A.BYTES TOTAL,<br /> B.BYTES USED,<br /> C.BYTES FREE,<br /> (B.BYTES * 100) / A.BYTES "% USED ",<br /> (C.BYTES * 100) / A.BYTES "% FREE "<br /> FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C<br /> WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME<br /> AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;<br />--7、查看資料庫庫對象<br />SELECT owner, object_type, status, COUNT(*) COUNT#<br /> FROM all_objects<br /> GROUP BY owner, object_type, status;<br />--8、查看資料庫的版本　<br />SELECT version<br /> FROM Product_component_version<br /> WHERE SUBSTR(PRODUCT, 1, 6) = 'Oracle';<br />--9、查看資料庫的建立日期和歸檔方式<br />SELECT Created, Log_Mode, Log_Mode FROM V$Database;<br />

 

2.SQL語句2：
--1G=1024MB<br />--1M=1024KB<br />--1K=1024Bytes<br />--1M=11048576Bytes<br />--1G=1024*11048576Bytes=11313741824Bytes<br />SELECT a.tablespace_name "資料表空間名",<br /> total "資料表空間大小",<br /> free "資料表空間剩餘大小",<br /> (total - free) "資料表空間使用大小",<br /> total/(1024*1024*1024) "資料表空間大小(G)",<br /> free/(1024*1024*1024) "資料表空間剩餘大小(G)",<br /> (total - free)/(1024*1024*1024) "資料表空間使用大小(G)",<br /> ROUND((total - free) / total, 4) * 100 "使用率 %"<br /> FROM (SELECT tablespace_name, SUM(bytes) free<br /> FROM DBA_FREE_SPACE<br /> GROUP BY tablespace_name) a,<br /> (SELECT tablespace_name, SUM(bytes) total<br /> FROM DBA_DATA_FILES<br /> GROUP BY tablespace_name) b<br /> WHERE a.tablespace_name = b.tablespace_name