1． 查看本用戶下的各種對(duì)象的sql腳本
sql語句：
表：
select * from cat;
select * from tab;
select table_name from user_tables;
視圖：
select text from user_views where view_name=upper('&view_name');
索引：
select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name;
觸發(fā)器：
select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;
快照：
select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next;
同義詞：
select * from syn;
序列：
select * from seq;
數(shù)據(jù)庫鏈路：
select * from user_db_links;
約束限制：
select table_name,constraint_name,search_condition,status from user_constraints;
本用戶讀取其他用戶對(duì)象的權(quán)限：
select * from user_tab_privs;
本用戶所擁有的系統(tǒng)權(quán)限：
select * from user_sys_privs;
用戶：
select * from all_users order by user_id;
表空間剩余自由空間情況：
select tablespace_name,sum(bytes) 總字節(jié)數(shù),max(bytes),count(*) from dba_free_space group by tablespace_name；
數(shù)據(jù)字典：
select table_name from dict order by table_name;
鎖及資源信息：
select * from v$lock;不包括ddl鎖
數(shù)據(jù)庫字符集：
select name,value$ from props$ where name='nls_characterset';
inin.ora參數(shù)：
select name,value from v$parameter order by name;
sql共享池：
select sql_text from v$sqlarea;
數(shù)據(jù)庫：
select * from v$database
控制文件：
select * from v$controlfile;
重做日志文件信息：
select * from v$logfile;
來自控制文件中的日志文件信息：
select * from v$log;
來自控制文件中的數(shù)據(jù)文件信息：
select * from v$datafile;
nls參數(shù)當(dāng)前值：
select * from v$nls_parameters;
oracle版本信息：
select * from v$version;
描述后臺(tái)進(jìn)程：
select * from v$bgprocess;
查看版本信息：
select * from product_component_version;
2． 監(jiān)控?cái)?shù)據(jù)庫
select '查看數(shù)據(jù)庫的版本' from dual;
select version from product_component_version where substr(product,1,6)='oracle';
select '查看數(shù)據(jù)庫庫對(duì)象' from dual;
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
select '查看數(shù)據(jù)庫的創(chuàng)建日期和歸檔方式' from dual;
select created, log_mode, log_mode from v$database;
select '查看控制文件' from dual;
select name from v$controlfile;
select '查看表空間的名稱及大小' from dual;
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;
select '查看表空間物理文件的名稱及大小' from dual;
select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
select '查看表空間的使用情況' from dual;
select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_space group by tablespace_name ;
select a.tablespace_name,a.bytes total,b.bytes used, c.bytes free, (b.bytes*100)/a.bytes "% used",(c.bytes*100)/a.bytes "% free"
from sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c
where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name;
select '查看日志文件' from dual;
select member from v$logfile;
select '查看回滾段名稱及大小' from dual;
select segment_name, tablespace_name, r.status, (initial_extent/1024) initialextent,(next_extent/1024) nextextent, max_extents, v.curext curextent from dba_rollback_segs r, v$rollstat v where r.segment_id = v.usn(+) order by segment_name;
