查看ORACLE資料庫資訊的一些SQL

oracle|資料|資料庫
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;