查看ORACLE数据库信息的一些SQL

1、查看表空间的名称及大小

set linesize 140;set pages 200;column tablespace_name format a30;select tablespace_name,min_extents,max_extents,pct_increase,status from dba_tablespaces;select tablespace_name,initial_extent,next_extent,contents,logging,extent_management,allocation_type from dba_tablespacesorder by tablespace_name;

2、查看表空间物理文件的名称及大小

column db_block_size new_value blksz noprintselect value db_block_size from v$parameter where name=@#db_block_size@#;column tablespace_name format a16;column file_name format a60;set linesize 160;select file_name,round(bytes/(1024*1024),0) total_space,autoextensible,increment_by*&blksz/(1024*1024) as incement,maxbytes/(1024*1024) as maxsize from dba_data_files order by tablespace_name;

3、查看回滚段名称及大小

COLUMN roll_name FORMAT a13 HEADING @#Rollback Name@#COLUMN tablespace FORMAT a11 HEADING @#Tablspace@#COLUMN in_extents FORMAT a20 HEADING @#Init/Next Extents@#COLUMN m_extents FORMAT a10 HEADING @#Min/Max Extents@#COLUMN status FORMAT a8 HEADING @#Status@#COLUMN wraps FORMAT 999 HEADING @#Wraps@# COLUMN shrinks FORMAT 999 HEADING @#Shrinks@#COLUMN opt FORMAT 999,999,999 HEADING @#Opt. Size@#COLUMN bytes FORMAT 999,999,999 HEADING @#Bytes@#COLUMN extents FORMAT 999 HEADING @#Extents@#SELECT a.owner || @#.@# || a.segment_name roll_name , a.tablespace_name tablespace , TO_CHAR(a.initial_extent) || @# / @# || TO_CHAR(a.next_extent) in_extents , TO_CHAR(a.min_extents) || @# / @# || TO_CHAR(a.max_extents) m_extents , a.status status , b.bytes bytes , b.extents extents , d.shrinks shrinks , d.wraps wraps , d.optsize optFROM dba_rollback_segs a , dba_segments b , v$rollname c , v$rollstat dWHERE a.segment_name = b.segment_name AND a.segment_name = c.name (+) AND c.usn = d.usn (+)ORDER BY a.segment_name;

4、查看控制文件

    select name from v$controlfile;

5、查看日志文件

    select member from v$logfile;

6、查看表空间的使用情况

select * from( select sum(bytes)/(1024*1024) as "free_space(M)",tablespace_name from dba_free_spacegroup by tablespace_name) order by "free_space(M)";

7、查看数据库库对象

    select owner, object_type, status, count(*) count# from all_objects    group by owner,object_type,status;

8、查看数据库的版本　

    select * from v$version;

9、查看数据库的创建日期和归档方式

    select created,log_mode,log_mode from v$database;

10、查看临时数据库文件

    select STATUS, ENABLED, NAME from v$tempfile;

原文转自：http://www.ltesting.net