數據庫進階：數據庫管理員日常工作中必備的sql列表[3]

　　查看用戶的回滾段的信息

　　select susername rnname from v$session s v$transaction t v$rollstat r v$rollname rn
　　where ssaddr = tses_addr and txidusn = rusn and rusn = rnusn

　　生成執行計劃

　　explain plan set statement_id=a for &;

　　查看執行計劃

　　select lpad( *(level))||operation operationoptionsOBJECT_NAMEposition from plan_table
　　start with id= and statement_id=a connect by prior id=parent_id and statement_id=a

　　查看內存中存的使用

　　select decode(greatest(class)decode(classDataSortHeaderto_char(class))Rollback) Class
　　sum(decode(bitand(flag))) Not Dirtysum(decode(bitand(flag))) Dirty
　　sum(dirty_queue) On Dirtycount(*) Total
　　from x$bh group by decode(greatest(class)decode(classDataSortHeaderto_char(class))Rollback);

　　查看表空間狀態

　　select tablespace_nameextent_managementsegment_space_management from dba_tablespaces;
　　select table_namefreelistsfreelist_groups from user_tables;

　　查看系統請求情況

　　SELECT DECODE (name summed dirty write queue length value)/
　　DECODE (name write requests value) Write Request Length
　　FROM v$sysstat WHERE name IN ( summed dirty queue length write requests) and value>;

　　計算data buffer命中率

　　select avalue + bvalue logical_reads cvalue phys_reads
　　round( * ((avalue+bvalue)cvalue) / (avalue+bvalue)) BUFFER HIT RATIO
　　from v$sysstat a v$sysstat b v$sysstat c
　　where astatistic# = and bstatistic# = and cstatistic# = ;
　　SELECT name ((physical_reads/(db_block_gets+consistent_gets)))* H_RATIO FROM v$buffer_pool_statistics;

　　查看內存使用情況

　　select least(max(bvalue)/(*)sum(abytes)/(*)) shared_pool_used
　　max(bvalue)/(*) shared_pool_sizegreatest(max(bvalue)/(*)sum(abytes)/(*))
　　(sum(abytes)/(*)) shared_pool_avail((sum(abytes)/(*))/(max(bvalue)/(*)))* avail_pool_pct
　　from v$sgastat a v$parameter b where (apool=shared pool and aname not in (free memory)) and bname=shared_pool_size;

　　查看用戶使用內存情況

　　select username sum(sharable_mem) sum(persistent_mem) sum(runtime_mem)
　　from sysv_$sqlarea a dba_users b
　　where aparsing_user_id = buser_id group by username;

　　查看對象的緩存情況

　　select OWNERNAMESPACETYPENAMESHARABLE_MEMLOADSEXECUTIONSLOCKSPINSKEPT
　　from v$db_object_cache where type not in (NOT LOADEDNONEXISTENTVIEWTABLESEQUENCE)
　　and executions> and loads> and kept=NO order by ownernamespacetypeexecutions desc;
　　select typecount(*) from v$db_object_cache group by type;

　　查看庫緩存命中率

　　select namespacegets gethitratio* gethitratiopinspinhitratio* pinhitratioRELOADSINVALIDATIONS from v$librarycache

　　查看某些用戶的hash

　　select ausername count(bhash_value) total_hashcount(bhash_value)count(unique(bhash_value)) same_hash
　　(count(unique(bhash_value))/count(bhash_value))* u_hash_ratio
　　from dba_users a v$sqlarea b where auser_id=bparsing_user_id group by ausername;

　　查看字典命中率

　　select (sum(getmisses)/sum(gets)) ratio from v$rowcache;

　　查看undo段的使用情況

　　SELECT dsegment_nameextentsoptsizeshrinksaveshrinkaveactivedstatus
　　FROM v$rollname nv$rollstat sdba_rollback_segs d
　　WHERE dsegment_id=nusn(+) and dsegment_id=susn(+);

　　無效的對象

　　select ownerobject_typeobject_name from dba_objects where status=INVALID;
　　select constraint_nametable_name from dba_constraints where status=INVALID;

　　求出某個進程並對它進行跟蹤

　　select ssidsserial# from v$session sv$process p where spaddr=paddr and pspid=&;
　　exec dbms_systemSET_SQL_TRACE_IN_SESSION(&&true);
　　exec dbms_systemSET_SQL_TRACE_IN_SESSION(&&false);

　　求出鎖定的對象

　　select doobject_namesession_idprocesslocked_mode
　　from v$locked_object lo dba_objects do where loobject_id=doobject_id;

　　求當前session的跟蹤文件

　　SELECT pvalue || / || pvalue || _ora_ || pspid || ora filename
　　FROM v$process p v$session s v$parameter p v$parameter p
　　WHERE pname = user_dump_dest AND pname = instance_name
　　AND paddr = spaddr AND saudsid = USERENV(SESSIONID) AND pbackground is null AND instr(pprogramCJQ) = ;

　　求對象所在的文件及塊號

　　select segment_nameheader_fileheader_block
　　from dba_segments where segment_name like &;

　　求對象發生事務時回退段及塊號

　　select asegment_nameaheader_fileaheader_block
　　from dba_segments adba_rollback_segs b
　　where asegment_name=bsegment_name and bsegment_id=&

　　i的在線重定義表

　　/*如果在線重定義的表沒有主鍵需要創建主鍵*/
　　exec dbms_redefinitioncan_redef_table(cybercafeannouncement);
　　create table anno as select * from announcement
　　exec dbms_redefinitionstart_redef_table(cybercafeannouncementanno);
　　exec dbms_redefinitionsync_interim_table(cybercafeannouncementanno);
　　exec dbms_redefinitionfinish_redef_table(cybercafeannouncementanno);
　　drop table anno
　　exec dbms_redefinitionabort_redef_table(cybercafeannouncementanno);

　　常用的logmnr腳本(cybercafe)

　　exec sysdbms_logmnr_dbuild(dictionary_filename =>esaldictionary_location =>/home/oracle/logmnr);
　　exec sysdbms_logmnradd_logfile(logfilename=>/home/oracle/oradata/esal/archive/_dbf ptions=>sysdbms_logmnrnew);
　　exec sysdbms_logmnradd_logfile(logfilename=>/home/oracle/oradata/esal/archive/_dbf ptions=>sysdbms_logmnraddfile);
　　exec sysdbms_logmnradd_logfile(logfilename=>/home/oracle/oradata/esal/archive/_dbf ptions=>sysdbms_logmnraddfile);
　　exec sysdbms_logmnradd_logfile(logfilename=>/home/oracle/oradata/esal/archive/_dbf ptions=>sysdbms_logmnraddfile);
　　exec sysdbms_logmnrstart_logmnr(dictfilename=>/home/oracle/logmnr/esalora);
　　create table logmnr as select * from v$logmnr_contents;

　　與權限相關的字典

　　ALL_COL_PRIVS表示列上的授權用戶和PUBLIC是被授予者
　　ALL_COL_PRIVS_MADE表示列上的授權用戶是屬主和被授予者
　　ALL_COL_RECD表示列上的授權用戶和PUBLIC是被授予者
　　ALL_TAB_PRIVS表示對象上的授權用戶是PUBLIC或被授予者或用戶是屬主
　　ALL_TAB_PRIVS_MADE表示對象上的權限用戶是屬主或授予者
　　ALL_TAB_PRIVS_RECD表示對象上的權限用戶是PUBLIC或被授予者
　　DBA_COL_PRIVS數據庫列上的所有授權
　　DBA_ROLE_PRIVS顯示已授予用戶或其他角色的角色
　　DBA_SYS_PRIVS已授予用戶或角色的系統權限
　　DBA_TAB_PRIVS數據庫對象上的所有權限
　　ROLE_ROLE_PRIVS顯示已授予用戶的角色
　　ROLE_SYS_PRIVS顯示通過角色授予用戶的系統權限
　　ROLE_TAB_PRIVS顯示通過角色授予用戶的對象權限
　　SESSION_PRIVS顯示用戶現在可利用的所有系統權限
　　USER_COL_PRIVS顯示列上的權限用戶是屬主授予者或被授予者
　　USER_COL_PRIVS_MADE顯示列上已授予的權限用戶是屬主或授予者
　　USER_COL_PRIVS_RECD顯示列上已授予的權限用戶是屬主或被授予者
　　USER_ROLE_PRIVS顯示已授予給用戶的所有角色
　　USER_SYS_PRIVS顯示已授予給用戶的所有系統權限
　　USER_TAB_PRIVS顯示已授予給用戶的所有對象權限
　　USER_TAB_PRIVS_MADE顯示已授予給其他用戶的對象權限用戶是屬主
　　USER_TAB_PRIVS_RECD顯示已授予給其他用戶的對象權限用戶是被授予者

　　如何用dbms_stats分析表及模式？

　　exec dbms_statsgather_schema_stats(ownname=>&USER_NAMEestimate_percent=>dbms_statsauto_sample_size
　　method_opt => for all columns size autodegree=> DBMS_STATSDEFAULT_DEGREE);
　　exec dbms_statsgather_schema_stats(ownname=>&USER_NAMEestimate_percent=>dbms_statsauto_sample_sizecascade=>true);
　　/*
　　FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
　　FOR COLUMNS [size clause] column|attribute [size_clause] [column|attribute [size_clause]]
　　where size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
　　integerNumber of histogram buckets Must be in the range []
　　REPEATCollects histograms only on the columns that already have histograms
　　AUTOOracle determines the columns to collect histograms based on data distribution and the workload of the columns
　　SKEWONLYOracle determines the columns to collect histograms based on the data distribution of the columns
　　*/

[]  []  []  

From:http://tw.wingwit.com/Article/program/SQL/201311/16148.html