--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
更多oracle 查看表空间的大小及使用情况sql语句。