|
--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,status;?
--8、查看数据库的版本 ?
SELECT version?
FROM product_component_version?
WHERE substr(product,1,6) = ‘Oracle‘;?
--9、查看数据库的创建日期和归档方式?
SELECT created,log_mode,log_mode FROM v$database;?
?
--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?
（编辑：锡盟站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
