JAVA SpringMVC+mybatis（oracle 和 mysql） HTML5 全新高大尚后台框架 bootstrap
本文主要分享Oracle DBA 工作中常用的一些sql?
| 连接~ | ? |
| /*查看Oracle错误号信息*/ | [oracle@lottery?~]$?oerr?ora?600 |
| /*清屏~*/ | SQL>??clear?screen???? |
| /*注册oracle监听*/ | SQL>??alter?system?register; |
| /*查看OS连DB数*/ | [oracle@lottery?~]$?ps?-ef?|?grep?oracle$ORACLE_SID|?grep?LOCAL=NO|?wc?-l |
| /*查询数据库当前进程的连接数*/ | select?count(*)?from?v$process; |
| /*查看数据库当前会话的连接数*/ | select?count(*)?from?v$session; |
| /*查看数据库用户连接会话的总数*/ | select?username,count?(username)?from?gv$session?where?username?is?not?null?/*and?status=‘ACTIVE‘*/?group?by?username; |
| /*查询数据库最大连接/进程数*/ | select?name?,?value??from?v$parameter?where?name?in?(?‘processes‘?,‘sessions‘?);??==>?show?parameter?processes/sessions; |
| 优化~ | ? |
| /*通过SQL_ID查找执行计划*/ | select?*?from?table(dbms_xplan.display_cursor(‘br8d2xs44sga8‘));? |
| /*通过SQL_ID查找SQL文本*/ | select?*?from?gv$sqlarea?s?where?s.sql_id=?‘br8d2xs44sga8‘; |
| /*查看数据库的等待事件*/ | SELECT?*?FROM?gv$session_wait?where?sid?in?(SELECT?sid?FROM?gV$SESSION?WHERE?STATUS=‘ACTIVE‘??and?username?is?not?null?and?sid!=userenv(‘sid‘?)?); |
| /*查看表的统计信息是否正确*/ | SELECT?TABLE_NAME,NUM_ROWS,LAST_ANALYZED?FROM?USER_TABLES?T?WHERE?TABLE_NAME=‘表‘;??--#用于检查表最后一次统计和真实的数据是否差距很大；? |
| /*查看表所有字段信息*/ | select?*?from?user_tab_columns?where?table_name=?‘表‘?;?? |
| /*统计整个用户*/ | begin?DBMS_STATS.gather_schema_stats(‘用户‘?,?cascade=>?TRUE?,no_invalidate=>false?);?end;? |
| /*统计表*/ | begin?DBMS_STATS.GATHER_TABLE_STATS(‘用户‘?,?‘表‘,?cascade?=>TRUE?);?end?; |
| /*查看表最后一次DML时间*/ | select?max(ora_rowscn),?scn_to_timestamp(max(ora_rowscn))?from?表; |
| 基本信息~ | ? |
| /*查看表空间剩余情况*/????????? | select?a.TABLESPACE_NAME,sum?(round?(a.bytes/?1024/?1024?/1024?,?2))?from?dba_free_space?a?group?by?a.tablespace_name;? |
| /*查询内存分配情况*/ | select?component,current_size?/1024?/1024?MB,?user_specified_size?/1024?MB??from?v$memory_dynamic_components?where?current_size?!=?0; |
| /*查看用户大小*/?????????????? | SELECT?S.OWNER,SUM?(BYTES/1024/?1024/1024?)?FROM?DBA_SEGMENTS?S?/*WHERE?S.OWNER=‘‘*/?GROUP?BY?S.OWNER; |
| /*查看数据库默认表空间*/ | select?*?from?database_properties?s?where?s.description?like?‘%default%tablespace‘ |
| /*查看库中的临时表*/ | select?*?from?user_tables?u?where?u.temporary=‘Y‘; |
| /*查看11g?alert文件位置*/ | select?value?from?v$diag_info;??-->?show?parameter?diagnostic_dest |
| /*当前回话的进程号*/ | select?spid?from?v$process?where?addr?in?(select?paddr?from?v$session?where?sid?in?(select?sid?from?v$mystat?where?rownum=1)); |
| 权限~ | ? |
| /*查看resource角色的权限*/ | select?*?from?role_sys_privs?where?role=‘RESOURCE‘?; |
| /*查看数据库中授dba权限的用户*/ | SELECT?*?FROM?DBA_ROLE_PRIVS?S?WHERE?S.GRANTED_ROLE=?‘DBA‘; |
| dblink~ | ? |
| /*创建DBLINK语句*/ |
?create?public?database?link?dblink名?connect?to?用户?identified?by?密码 ?using?‘(DESCRIPTION?=(ADDRESS_LIST?=(ADDRESS?=?(PROTOCOL?=?TCP) ?(HOST?=?IP地址?)(PORT?=?1521)))(CONNECT_DATA?=(SERVER?=?DEDICATED)(SERVICE_NAME?=?实例名)))‘;?? |
| 其他~ | ? |
| /*查找快照SNAP_ID对应的时间*/ | select?*?from?sys.wrh$_active_session_history; |
| /*查看索引拥有者!=表的拥有者*/ | SELECT?owner,index_name,index_type,table_owner,table_name,table_type?FROM?dba_indexes?where?owner!=table_owner; |
| /*查看库中(只读)属性的表*/ | select?table_name,status,read_only?from?dba_tables?where?read_only=‘YES‘; |
| #更改表属性??alter?table?表?read?only(read?write);??(11g新特性) | |
| #注意：索引创建/修改对只读表【表空间】没有影响！因为索引修改的是数据字典，和表不相关 | |
| /*查看分区表基本信息查询*/ | SELECT?TABLE_NAME,?column_name?,?PARTITION_NAME?,?HIGH_VALUE?LESS_THAN值,?TABLESPACE_NAME?FROM?USER_TAB_PARTITIONS?tp?join?USER_PART_KEY_COLUMNS?tpc?on?tp.table_name=tpc.name; |
| /*查看某用户登录的所有会话*/????????? | SELECT?‘ALTER?SYSTEM?KILL?SESSION?‘‘‘?||?SID||‘,‘?||SERIAL#?||‘‘‘;‘,?S.*?FROM?V$SESSION?S?WHERE?USERNAME=?‘用户‘?AND?STATUS!=?‘KILLED‘?;??--#用于解决?ORA?-01940?:?无法删除当前连接的用户??? |
| /*查看command_type值对应类型*/ | SELECT?*?FROM?v$sqlcommand;??--【v$sqlarea.command_type、v$session.command?】 |
原文：http://201501120459.iteye.com/blog/2176616