ORACLE常用傻瓜??1000?(之四)-数据库专栏,ORACLE-数据库其他-idc中文资讯站-客观公证的IDC产业权威媒体,关注域名、虚拟主机、服务器、网络、开源技术资讯，整合运营商资源，客观评价各地机房带宽线路质量、IDC企业名录等信息

ORACLE常用傻瓜??1000?(之四)-数据库专栏,ORACLE

来源：作者：发布时间:2007-12-26 01:59:55

              oracle常用傻瓜??1000?(之四)

作者: ccbzzp

        大家在?用oracle的?候可能?碰到很多看起?不?的??, 特??新手??, 今天我??把他??一下, ?布?大家, 希望?大家有?助! 和大家一起探?, 一起?步!

        ?oracle高手??是不用看的.

   虚?字段
133. currval 和 nextval
   为表创建序列
   create sequence empseq ... ;
   select empseq.currval from dual ;
   自动插入序列的数值
   insert into emp
        values (empseq.nextval, lewis, clerk,
                7902, sysdate, 1200, null, 20) ;

134. rownum
   按设定排序的行的序号
   select * from emp where rownum < 10 ;

135. rowid
   返回行的物理地址
   select rowid, ename from emp where deptno = 20 ;

136. 将n秒转换为时分秒格式？
   set serverout on
   declare
   n number := 1000000;
   ret varchar2(100);
   begin
   ret := trunc(n/3600) || 小时 || to_char(to_date(mod(n,3600),sssss),fmmi"分   "ss"秒") ;
   dbms_output.put_line(ret);
   end;

137. 怎样查询做比较大的排序的进程？
   select b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
   a.username, a.osuser, a.status
   from v$session a,v$sort_usage b
   where a.saddr = b.session_addr
   order by b.tablespace, b.segfile#, b.segblk#, b.blocks ;

138. 怎样查询做比较大的排序的进程的sql语句？
   select /*+ ordered */ sql_text from v$sqltext a
   where a.hash_value = (
   select sql_hash_value from v$session b
   where b.sid = &sid and b.serial# = &serial)
   order by piece asc ;

139. 怎样查找重复记录？
   select * from table_name
   where rowid!=(select max(rowid) from table_name d
   where table_name.col1=d.col1 and table_name.col2=d.col2);

140. 怎样删除重复记录？
   delete from table_name
   where rowid!=(select max(rowid) from table_name d
   where table_name.col1=d.col1 and table_name.col2=d.col2);

141. 怎样快速编译任何视图？
   sql >spool view1.sql
   sql >select ‘alter view ‘||tname||’
   compile;’ from tab;
   sql >spool off
   然后执行view1.sql即可。
   sql >@view1.sql;

142. ora-01555 snapshot too old的解决办法
   增加minextents的值，增加区的大小，配置一个高的optimal值。

143. 事务需要的回滚段空间不够，表现为表空间用满（ora-01560错误），回滚段扩展到达参数      maxextents的值（ora-01628）的解决办法.
   向回滚段表空间添加文档或使已有的文档变大；增加maxextents的值。

144. 怎样加密oracle的存储过程？
    下列存储过程内容放在aa.sql文档中
    create or replace procedure testccb(i in number) as
    begin
    dbms_output.put_line(输入参数是||to_char(i));
    end;

    sql>wrap iname=a.sql;
    pl/sql wrapper: release 8.1.7.0.0 - production on tue nov 27 22:26:48 2001
    copyright (c) oracle corporation 1993, 2000. all rights reserved.
    processing aa.sql to aa.plb
    运行aa.plb
    sql> @aa.plb ;

145. 怎样监控事例的等待？
   select event,sum(decode(wait_time,0,0,1)) "prev",
   sum(decode(wait_time,0,1,0)) "curr",count(*) "tot"
   from v$session_wait
   group by event order by 4;

146. 怎样回滚段的争用情况？
   select name, waits, gets, waits/gets "ratio"
   from v$rollstat c, v$rollname d
   where c.usn = d.usn;

147. 怎样监控表空间的 i/o 比例？
   select b.tablespace_name name,b.file_name "file",a.phyrds pyr,
   a.phyblkrd pbr,a.phywrts pyw, a.phyblkwrt pbw
   from v$filestat a, dba_data_files b
   where a.file# = b.file_id
   order by b.tablespace_name;

148. 怎样监控文档系统的 i/o 比例？
   select substr(c.file#,1,2) "#", substr(c.name,1,30) "name",
   c.status, c.bytes, d.phyrds, d.phywrts
   from v$datafile c, v$filestat d
   where c.file# = d.file#;

149. 怎样在某个用户下找任何的索引？
   select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
   from user_ind_columns, user_indexes
   where user_ind_columns.index_name = user_indexes.index_name
   and user_ind_columns.table_name = user_indexes.table_name
   order by user_indexes.table_type, user_indexes.table_name,
   user_indexes.index_name, column_position;

150. 怎样监控 sga 的命中率？
   select a.value + b.value "logical_reads", c.value "phys_reads",
   round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "buffer hit ratio"
   from v$sysstat a, v$sysstat b, v$sysstat c
   where a.statistic# = 38 and b.statistic# = 39
   and c.statistic# = 40;

151. 怎样监控 sga 中字典缓冲区的命中率？
   select parameter, gets,getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
   (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "hit ratio"
   from v$rowcache
   where gets+getmisses <>0
   group by parameter, gets, getmisses;

152. 怎样监控 sga 中共享缓存区的命中率，应该小于1% ？
   select sum(pins) "total pins", sum(reloads) "total reloads",
   sum(reloads)/sum(pins) *100 libcache
   from v$librarycache;

   select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins)    "reload percent"
   from v$librarycache;

153. 怎样显示任何数据库对象的类别和大小？
   select count(name) num_instances ,type ,sum(source_size) source_size ,
   sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size)    error_size,
   sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size)    size_required
   from dba_object_size
   group by type order by 2;

154. 监控 sga 中重做日志缓存区的命中率，应该小于1%
   select name, gets, misses, immediate_gets, immediate_misses,
   decode(gets,0,0,misses/gets*100) ratio1,
   decode(immediate_gets+immediate_misses,0,0,
   immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
   from v$latch where name in (redo allocation, redo copy);

155. 监控内存和硬盘的排序比率，最好使他小于 .10，增加 sort_area_size
   select name, value from v$sysstat where name in (sorts (memory), sorts      (disk));

156. 怎样监控当前数据库谁在运行什么sql语句？
   select osuser, username, sql_text from v$session a, v$sqltext b
   where a.sql_address =b.address order by address, piece;

157. 怎样监控字典缓冲区？
   select (sum(pins - reloads)) / sum(pins) "lib cache" from v$librarycache;
   select (sum(gets - getmisses - usage - fixed)) / sum(gets) "row cache" from    v$rowcache;
   select sum(pins) "executions", sum(reloads) "cache misses while executing" from    v$librarycache;
   后者除以前者,此比率小于1%,接近0%为好。

   select sum(gets) "dictionary gets",sum(getmisses) "dictionary cache get misses"
   from v$rowcache

158. 监控 mts
   select busy/(busy+idle) "shared servers busy" from v$dispatcher;
   此值大于0.5时，参数需加大
   select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where    type=dispatcher;
   select count(*) from v$dispatcher;
   select servers_highwater from v$mts;
   servers_highwater接近mts_max_servers时，参数需加大

159. 怎样知道当前用户的id号?
    sql>show user;
    or
    sql>select user from dual;

160. 怎样查看碎片程度高的表?
   select segment_name table_name , count(*) extents
   from dba_segments where owner not in (sys, system) group by segment_name
   having count(*) = (select max( count(*) ) from dba_segments group by       segment_name);

162. 怎样知道表在表空间中的存储情况?
   select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
   tablespace_name=&tablespace_name and segment_type=table group by       tablespace_name,segment_name;

163. 怎样知道索引在表空间中的存储情况?
   select segment_name,count(*) from dba_extents where segment_type=index and       owner=&owner
   group by segment_name;

164、怎样知道使用cpu多的用户session?
   11是cpu used by this session

   select a.sid,spid,status,substr(a.program,1,40)       prog,a.terminal,osuser,value/60/100 value
   from v$session a,v$process b,v$sesstat c
   where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc;

165. 怎样知道监听器日志文档?
   以8i为例
   $oracle_home/network/log/listener.log

166. 怎样知道监听器参数文档?
   以8i为例
   $oracle_home/network/admin/listener.ora

167. 怎样知道tns 连接文档?
   以8i为例
   $oracle_home/network/admin/tnsnames.ora

168. 怎样知道sql*net 环境文档?
   以8i为例
   $oracle_home/network/admin/sqlnet.ora

169. 怎样知道警告日志文档?
   以8i为例
   $oracle_home/admin/sid/bdump/sidalrt.log

170. 怎样知道基本结构?
   以8i为例
   $oracle_home/rdbms/admin/standard.sql

171. 怎样知道建立数据字典视图?
   以8i为例
   $oracle_home/rdbms/admin/catalog.sql

172. 怎样知道建立审计用数据字典视图?
   以8i为例
   $oracle_home/rdbms/admin/cataudit.sql

173. 怎样知道建立快照用数据字典视图?
   以8i为例
   $oracle_home/rdbms/admin/catsnap.sql

   待续...

　　

喜欢本文，那就收藏到：

上一篇: 数据库关闭的学问-数据库专栏,SQL Server

下一篇: ORACLE9i 的透明网关的配置-数据库专栏,ORACLE

相关推荐

相关资讯