常用积累

1.Oracle 取本周第一天，最后一天
select sysdate,to_char(sysdate,'D'),trunc(sysdate,'DD')-to_char(sysdate,'D')+1,trunc(sysdate,'DD')-to_char(sysdate,'D')+7 from dual;
2.查看日志切换频率的语句
select b.recid,
        to_char(b.first_time,'dd-mon-yy hh:mi:ss') start_time,
        a.recid,
        to_char(a.first_time,'dd-mon-yy hh:mi:ss') end_time,
        round(((a.first_time - b.first_time)*24)*60,2) minutes
   from v$log_history a,v$log_history b
  where a.recid = b.recid+1
    order by a.first_time asc;
3.查看当前归档目录
    SELECT p1.value || '/' || p2.value || '_ora_' || p.spid || '.ora' filename
      FROM v$process p, v$session s, v$parameter p1, v$parameter p2
      WHERE p1.name = 'user_dump_dest'
     AND p2.name = 'instance_name'
     AND p.addr = s.paddr
     AND s.audsid = USERENV('SESSIONID')
     AND p.background is null
     AND instr(p.program, 'CJQ') = 0;
4.删除表空间的方法:
     Select Distinct  'Drop User ' ||  owner  || ' Cascade; '  From dba_segments Where  tablespace_name='TS_JZXGL';  删除用户后,再去删掉表空间"TS_JZXGL"
    DROP TABLESPACE TS_JZXGL INCLUDING CONTENTS AND DATAFILES;
    DROP TABLESPACE TS_JZXGL INCLUDING CONTENTS;
   5.删除某个用户下的对象
   prompt --Drop constraint
   select 'alter table '||table_name||' drop constraint '||constraint_name||' ;' from user_constraints where constraint_type='R';
   prompt --Drop tables
   select 'drop table '||table_name ||';' from user_tables;
   prompt --Drop view
   select 'drop view ' ||view_name||';' from user_views;
   prompt --Drop sequence
   select 'drop sequence ' ||sequence_name||';' from user_sequences;
   prompt --Drop function
   select 'drop function ' ||object_name||';'  from user_objects  where object_type='FUNCTION';
   prompt --Drop procedure
   select 'drop procedure '||object_name||';' from user_objects  where object_type='PROCEDURE';
   prompt --Drop package
   prompt --Drop package body
   select 'drop package '|| object_name||';' from user_objects  where object_type='PACKAGE';
   prompt --Drop database link
   select 'drop database link '|| object_name||';' from user_objects  where object_type='DATABASE LINK';
6.创建密码文件
  orapwd file='/u01/pwdtestdb.ora password=lijunyu entries=30;
7.更改动态注册及静态注册的初始化文件
  alter system set log_archive_dest_1='location=/u01/oradata/archtest/' scope=both;
8.死锁查询
   select   s.sid session_id, s.username, decode(lmode,  0 ,  ' none ' ,  1 ,  ' null ' ,  2 ,  ' row-s (ss) ' ,  3 ,  ' row-x (sx) ' ,   4 ,  ' share ' ,  5 ,  ' s/row-x (ssx) ' ,   6 ,  ' exclusive ' , to_char(lmode)) mode_held, decode(request,  0 ,  ' none ' ,  1 ,  ' null ' ,  2 ,  ' row-s (ss) ' ,  3 ,  ' row-x (sx) ' ,  4 ,  ' share ' ,  5 ,  ' s/row-x (ssx) ' ,  6 ,  ' exclusive ' , to_char(request)) mode_requested, o.owner || ' . ' || o. object_name || '  ( ' || o.object_type || ' ) ' , s.type lock_type, l.id1 lock_id1, l.id2 lock_id2
from  v$lock l, sys.dba_objects o, v$session s
where  l.sid  =  s.sid  and   l.id1  =  o. object_id

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15242702/viewspace-666013/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15242702/viewspace-666013/