ORACLE DBA 常用語句1

1、查看錶空間的名稱及大小 select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_sizefrom dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by t.tablespace_name; 2、查看錶空間物理檔案的名稱及大小 select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name; 3、查看復原段名稱及大小 select segment_name, tablespace_name, r.status,(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,max_extents, v.curext CurExtentFrom dba_rollback_segs r, v$rollstat vWhere r.segment_id = v.usn(+)order by segment_name; 4、查看控制檔案 select name from v$controlfile; 5、查看記錄檔 select member from v$logfile;select * from v$logfile; 6、查看錶空間的使用方式 select sum(bytes)/(1024*1024) as free_space,tablespace_namefrom dba_free_spacegroup by 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_versionWhere SUBSTR(PRODUCT,1,6)='Oracle'; 9、查看資料庫的建立日期和歸檔方式 Select Created, Log_Mode, Log_Mode From V$Database; 10、捕捉運行很久的SQL column username format a12column opname format a16column progress format a8 select username,sid,opname,      round(sofar*100 / totalwork,0) || '%' as progress,      time_remaining,sql_textfrom v$session_longops , v$sqlwhere time_remaining <> 0and sql_address = addressand sql_hash_value = hash_value