数据库进阶：数据库管理员日常工作中必备的sql列表[3]

查看用户的回滚段的信息

select s username rn name from v$session s v$transaction t v$rollstat r v$rollname rnwhere s saddr = t ses_addr and t xidusn = r usn and r usn = rn usn

生成执行计划

explain plan set statement_id= a for &

查看执行计划

select lpad( *(level ))||operation operation options OBJECT_NAME position from plan_tablestart with id= and statement_id= a connect by prior id=parent_id and statement_id= a

查看内存中存的使用

select decode(greatest(class ) decode(class Data Sort Header to_char(class)) Rollback ) Class sum(decode(bitand(flag ) )) Not Dirty sum(decode(bitand(flag ) )) Dirty sum(dirty_queue) On Dirty count(*) Total from x$bh group by decode(greatest(class ) decode(class Data Sort Header to_char(class)) Rollback )

查看表空间状态

select tablespace_name extent_management segment_space_management from dba_tablespacesselect table_name freelists freelist_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 a value + b value logical_reads c value phys_reads round( * ((a value+b value) c value) / (a value+b value)) BUFFER HIT RATIO from v$sysstat a v$sysstat b v$sysstat cwhere a statistic# = and b statistic# = and c statistic# = SELECT name ( (physical_reads/(db_block_gets+consistent_gets)))* H_RATIO FROM v$buffer_pool_statistics

查看内存使用情况

select least(max(b value)/( * ) sum(a bytes)/( * )) shared_pool_used max(b value)/( * ) shared_pool_size greatest(max(b value)/( * ) sum(a bytes)/( * )) (sum(a bytes)/( * )) shared_pool_avail ((sum(a bytes)/( * ))/(max(b value)/( * )))* avail_pool_pctfrom v$sgastat a v$parameter b where (a pool= shared pool and a name not in ( free memory )) and b name= shared_pool_size

查看用户使用内存情况

select username sum(sharable_mem) sum(persistent_mem) sum(runtime_mem)from sys v_$sqlarea a dba_users bwhere a parsing_user_id = b user_id group by username

查看对象的缓存情况

select OWNER NAMESPACE TYPE NAME SHARABLE_MEM LOADS EXECUTIONS LOCKS PINS KEPTfrom v$db_object_cache where type not in ( NOT LOADED NON EXISTENT VIEW TABLE SEQUENCE )and executions>and loads>and kept= NO order by owner namespace type executions descselect type count(*) from v$db_object_cache group by type

查看库缓存命中率

select namespace gets gethitratio* gethitratio pins pinhitratio* pinhitratio RELOADS INVALIDATIONS from v$librarycache

查看某些用户的hash

select a username count(b hash_value) total_hash count(b hash_value) count(unique(b hash_value)) same_hash (count(unique(b hash_value))/count(b hash_value))* u_hash_ratiofrom dba_users a v$sqlarea b where a user_id=b parsing_user_id group by a username

查看字典命中率

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

查看undo段的使用情况

SELECT d segment_name extents optsize shrinks aveshrink aveactive d statusFROM v$rollname n v$rollstat s dba_rollback_segs dWHERE d segment_id=n usn(+) and d segment_id=s usn(+)

无效的对象

select owner object_type object_name from dba_objects where status= INVALID select constraint_name table_name from dba_constraints where status= INVALID

求出某个进程 并对它进行跟踪

select s sid s serial# from v$session s v$process p where s paddr=p addr and p spid=&exec dbms_system SET_SQL_TRACE_IN_SESSION(&&true)exec dbms_system SET_SQL_TRACE_IN_SESSION(&&false)

求出锁定的对象

select do object_name session_id process locked_modefrom v$locked_object lo dba_objects do where lo object_id=do object_id

求当前session的跟踪文件

SELECT p value || / || p value || _ora_ || p spid || ora filenameFROM v$process p v$session s v$parameter p v$parameter p WHERE p name = user_dump_dest AND p name = instance_name AND p addr = s paddr AND s audsid = USERENV( SESSIONID ) AND p background is null AND instr(p program CJQ ) =

求对象所在的文件及块号

select segment_name header_file header_blockfrom dba_segments where segment_name like &

求对象发生事务时回退段及块号

select a segment_name a header_file a header_blockfrom dba_segments a dba_rollback_segs bwhere a segment_name=b segment_name and b segment_id= &

i的在线重定义表

/*如果在线重定义的表没有主键需要创建主键*/exec dbms_redefinition can_redef_table( cybercafe announcement )create table anno as select * from announcementexec dbms_redefinition start_redef_table( cybercafe announcement anno )exec dbms_redefinition sync_interim_table( cybercafe announcement anno )exec dbms_redefinition finish_redef_table( cybercafe announcement anno )drop table anno exec dbms_redefinition abort_redef_table( cybercafe announcement anno )

常用的logmnr脚本(cybercafe)

exec sys dbms_logmnr_d build(dictionary_filename =>esal dictionary_location =>/home/oracle/logmnr )exec sys dbms_logmnr add_logfile(logfilename=>/home/oracle/oradata/esal/archive/ _ dbf ptions=>sys dbms_logmnr new)exec sys dbms_logmnr add_logfile(logfilename=>/home/oracle/oradata/esal/archive/ _ dbf ptions=>sys dbms_logmnr addfile)exec sys dbms_logmnr add_logfile(logfilename=>/home/oracle/oradata/esal/archive/ _ dbf ptions=>sys dbms_logmnr addfile)exec sys dbms_logmnr add_logfile(logfilename=>/home/oracle/oradata/esal/archive/ _ dbf ptions=>sys dbms_logmnr addfile)exec sys dbms_logmnr start_logmnr(dictfilename=>/home/oracle/logmnr/esal ora )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_stats gather_schema_stats(ownname=>&USER_NAME estimate_percent=>dbms_stats auto_sample_size method_opt =>for all columns size auto degree=>DBMS_STATS DEFAULT_DEGREE)exec dbms_stats gather_schema_stats(ownname=>&USER_NAME estimate_percent=>dbms_stats auto_sample_size cascade=>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}integer Number of histogram buckets Must be in the range [ ] REPEAT Collects histograms only on the columns that already have histograms AUTO Oracle determines the columns to collect histograms based on data distribution and the workload of the columns SKEWONLY Oracle determines the columns to collect histograms based on the data distribution of the columns*/

lishixinzhi/Article/program/SQL/201311/16148

SELECT ID,

       CASE

         WHEN 语文 >= 数学 THEN

          CASE

            WHEN 语文 >= 英语 THEN

             语文

            ELSE

             英语

          END

         ELSE

          CASE

            WHEN 数学 >= 英语 THEN

             数学

            ELSE

             英语

          END

       END

FROM 成绩表

---