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

查看用户的回滚段的信息

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_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

[] [] []