查看数据库的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_tablespaces
order by tablespace_name;

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

column db_block_size new_value blksz noprint
select 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                 opt
FROM
   dba_rollback_segs a
  , dba_segments b
  , v$rollname c
  , v$rollstat d
WHERE
    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 "ree_space(M)",tablespace_name
from dba_free_space
group by tablespace_name) order by "ree_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;