数据库进阶：数据库管理员日常工作中必备的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_tablespaces;select 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 desc;select 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

常见聚合函数，max（最大）、min（最小）、sum（求和）、avg（平均）等，

示例如下，

1、创建测试表，

create table test_group(id number, value number);

2、插入测试数据

insert into test_group values(1,1001);

insert into test_group values(1,1002);

insert into test_group values(1,1003);

insert into test_group values(1,1004);

insert into test_group values(1,1005);

3、查询表中所有记录，select t, rowid from test_group t，

4、编写sql，分别求聚合函数max、min、sum、avg，见：select max(value), min(value), sum(value), avg(value) from test_group t，

实际上 这不是通常意义上的文件系统 它没有磁盘空间 而是使用MySQL 守护程序来存储数据 可以把SQL 表和一些函数通过文件系统来实现 一 怎样实现？ 让我们来看使用实例 [root@localhost /root]# mount t corbafs o `cat /tmp/mysqlcorbafs ior` none /mnt/mysql/ [root@localhost /root]# mount /dev/hda on / type ext (rw) none on /proc type proc (rw) none on /dev/pts type devpts (rw gid= mode= ) /dev/hda on /mnt/win type vfat (rw mode= ) /dev/hda on /mnt/linux type vfat (rw noexec nosuid nodev mode= ) none on /mnt/mysql type corbafs (rw IOR: e d b c a f f c d a e a e e e bc a ef ae c c f) [root@localhost /root]# ls la /mnt/mysql/ total r xr xr x root root dets : uptime dr xr xr x root root dets : test dr xr xr x root root dets : mysql [root@localhost /root]# cat /mnt/mysql/ uptime [root@localhost /root]# cat /mnt/mysql/mysql/user/Host cpq spam ee cpq spam ee localhost localhost localhost localhost localhost localhost [root@localhost /root]# cat /mnt/mysql/mysql/user/Insert_priv N N N N N N Y Y [root@localhost /root]# umount /mnt/mysql/ 二 为什么要这样做呢？ 在一些情形下 这样做能让工作更加轻松 MySQL 和文件系统都能叫做数据库 但是有着绝然不同的概念和优缺点 在文件系统里 对象能很快而且很容易找到 即使改变名字也能很快找到 每一个初学者大概都应该学会move/copy/rename/delete这样的 *** 作 但是SQL 不一样 他通过应用程序来 *** 纵存储在文件系统上的数据 而MySQL 文件系统把SQL 做到了用户级 用户能用他们知道的方式来 *** 作数据库 任何一个新产品需要通过网络存取数据的话 必须支持一些协议以及可能的其他办法通过网络存取文件系统 MySQL 表就可以通过这样的方式来存取 即使MySQL 没有移植到对应的平台 备份和版本控制 普通的文件系统通过任何备份软件就可以实现 数据可以通过diff来比较并且用cvs 来控制版本 更短的编程时间 有时候人们需要保存简单的数据 像当前日期或者站点名字 这些数据很少改变 普通的方法需要使用 连接服务器 > 选择数据库 > 执行命令 > 存储结果 而使用 MySQL 文件系统后 只需要一句话 (PHP实现) include(¨/mountpoint/database/table/field¨); 或者 换一种方式表达 include(¨/mnt/mysql/sitedata/topic/todaytopic¨); 这样就很容易理解 也占用了较少的空间 还可以通过SAMBA 来共享 /mnt/mysql 达到直接修改SQL 数据库的目的 能直接写文本到数据库 或者使用拷贝／粘贴功能把放入数据库 这要比用Perl 或者PHP 写几百行程序省力多了 三 性能如何？ 在发表这篇文章的时候 这个文件系统还处于原型开发阶段 因此 速度还不是很理想 如果到了正式发布的时候 一些数据库功能会比使用 SQL 要快 当然 很多还是没法和SQL 相比 无论是性能上还是功能上 很多复杂的查询依然需要通过SQL 语句来完成 但是 这样节省了很多开发和培训的时间 所以在效率上来说也是一种节省 四 支持的表类型 目前这个文件系统支持所有的表类型 MyISAM DBD HEAP ISAM 五 其他的特色 在第一步开发中实现的还只是只读 很快会有能读写的版本出来 目前的计划是把数据库对象映射成文件和目录对象 让我们来看看例子 < #建立表 CREATE TABLE invoice ( invoice_id int( ) unsigned NOT NULL auto_increment invoice_no int( ) unsigned DEFAULT NOT NULL payee char( ) DEFAULT NOT NULL PRIMARY KEY (invoice_id) KEY payee (payee) ); # 插入数据 INSERT INTO invoice VALUES ( Company AB ); INSERT INTO invoice VALUES ( Company CD ); INSERT INTO invoice VALUES ( Company EF ); < 因为 MySQL 没有办法使用记录号 所以我们必须建立主键 就有了以下的目录结构 /mountpoint/database/table/primary_key/field 这样 每个列出现在不同的文件行之中 文件树的结构如下 /mnt/mysql/mydata/invoice/ /invoice_id /mnt/mysql/mydata/invoice/ /invoice_no /mnt/mysql/mydata/invoice/ /payee /mnt/mysql/mydata/invoice/ /invoice_id /mnt/mysql/mydata/invoice/ /invoice_no /mnt/mysql/mydata/invoice/ /payee /mnt/mysql/mydata/invoice/ /invoice_id /mnt/mysql/mydata/invoice/ /invoice_no /mnt/mysql/mydata/invoice/ /payee 另外 还有第二个办法可以使用 /mountpoint/database/table/ table 和 /mountpoint/database/table/primary_key/ record /mnt/mysql/mydata/invoice/ table /mnt/mysql/mydata/invoice/ / record /mnt/mysql/mydata/invoice/ /invoice_id /mnt/mysql/mydata/invoice/ /invoice_no /mnt/mysql/mydata/invoice/ /payee /mnt/mysql/mydata/invoice/ / record /mnt/mysql/mydata/invoice/ /invoice_id /mnt/mysql/mydata/invoice/ /invoice_no /mnt/mysql/mydata/invoice/ /payee /mnt/mysql/mydata/invoice/ / record /mnt/mysql/mydata/invoice/ /invoice_id /mnt/mysql/mydata/invoice/ /invoice_no /mnt/mysql/mydata/invoice/ /payee 这些文件是隐含的 以防重复 主要用来方便地通过文本文件浏览器来查看 现在 在那些需要使用SQL 语句搜索最小 最大 最后等数据 可以通过符号连接来实现了 /mountpoint/database/table/primary_key/ max 或者 /mnt/mysql/mydata/invoice/invoice_id/ max 或者指向 /mountpoint/database/table/field 和 /mnt/mysql/mydata/invoice/ 同样的就可以返回一行的 min/max/sum/avg 等数值 这能很快并且很容易地实现 /mnt/mysql/mydata/ keys/ /mnt/mysql/mydata/ keys/invoice_id/ /mnt/mysql/mydata/ keys/payee/ 符号连接到主键 /mnt/mysql/mydata/ keys/ primary_key/　实际上指向 /mnt/mysql/mydata/ keys/invoice_id/ 还有一些隐藏文件提供键类型 /mnt/mysql/mydata/ keys/invoice_id/ type /mnt/mysql/mydata/ keys/payee/ type 第一个文内容为 ¨PRIMARY KEY¨ 第二个为 ¨KEY¨ 另外还可以用索引来排序记录 如果读取下面的目录 /mnt/mysql/mydata/ keys/payee/asc/ PHP 的readdir() 函数就以升序返回数据的符号连接 另外还有一些全局函数 /mountpoint/ version /mountpoint/ last_insert_id /mountpoint/ uptime /mountpoint/database/ raid ( / ) /mountpoint/database/ type (ISAM/MyISAM/HEAP/DBD) /mountpoint/database/ tables /mountpoint/database/table/ created /mountpoint/database/table/ last_updated /mountpoint/database/table/ last_checked /mountpoint/database/unt 六 写权限？ 在开发的第二阶段 会有措施执行SQL 语句 现在的思路是 采用目录 /mountpoint/mand/ 然后执行命令 把SQL 语句作为目录建立 或者建立目录把SQL 语句作为文件放入这个目录 两个方案都有优点 第一个方案可以重新使用SQL 语句 但是这样的目录名实在不敢令人苟同 第二个方案采用信号量文件 语句执行完毕就删除这个文件 没有任务使用时 目录也被删除 对于那些慢速的查询响应 可以选择timeout 的时间 七 权限管理 在权限管理方面 可以使用Unix 的权限管理方式 这样的方案看来是最好的 lishixinzhi/Article/program/MySQL/201311/29464

以上就是关于数据库进阶：数据库管理员日常工作中必备的sql列表[3]全部的内容，包括:数据库进阶：数据库管理员日常工作中必备的sql列表[3]、sql 语句中那些是聚合函数、MySQL 文件系统等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！