Oracle SQL，[參考]查看ORACLE DB信息的一些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;

Oracle SQL、5、查看日志文件

??? select member from v$logfile;

6、查看表空間的使用情況

select * from( select sum(bytes)/(1024*1024)  as "free_space(M)",tablespace_name
from dba_free_space
group 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