熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL語言 >> 正文

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

2022-06-13   來源: SQL語言 

  查看用戶的回滾段的信息

  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
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.