ORACLE入門之查看數據庫的SQL

　　查看表空間的名稱及大小
　　select ttablespace_name round(sum(bytes/(*))) ts_size
　　from dba_tablespaces t dba_data_files d
　　where ttablespace_name = dtablespace_name
　　group by ttablespace_name;
　　查看表空間物理文件的名稱及大小
　　select tablespace_name file_id file_name
　　round(bytes/(*)) total_space from dba_data_files order by tablespace_name;
　　查看回滾段名稱及大小
　　select segment_name tablespace_name rstatus
　　 (initial_extent/) InitialExtent(next_extent/) NextExtent max_extents vcurext CurExtent From dba_rollback_segs r v$rollstat v
　　Where rsegment_id = vusn(+)
　　order by segment_name ;
　　查看控制文件
　　select name from v$controlfile;
　　查看日志文件
　　select member from v$logfile;
　　查看表空間的使用情況
　　select sum(bytes)/(*) as free_spacetablespace_name
　　from dba_free_space group by tablespace_name;
　　SELECT ATABLESPACE_NAMEABYTES TOTALBBYTES USED CBYTES FREE(BBYTES*)/ABYTES % USED(CBYTES*)/ABYTES % FREE
　　FROM SYSSM$TS_AVAIL ASYSSM$TS_USED BSYSSM$TS_FREE C WHERE ATABLESPACE_NAME=BTABLESPACE_NAME AND ATABLESPACE_NAME=CTABLESPACE_NAME;
　　查看數據庫庫對象
　　select owner object_type status count(*) count# from all_objects group by owner object_type status;
　　查看數據庫的版本　
　　Select version FROM Product_component_version
　　Where SUBSTR(PRODUCT)=Oracle;
　　查看數據庫的創建日期和歸檔方式
　　Select Created Log_Mode Log_Mode From V$Database;

From:http://tw.wingwit.com/Article/program/Oracle/201311/18045.html