Oracle文摘2

Oracle常见服务

几个主要的：

OracleOraHome81TNSListener     监听服务
OracleServiceSID               ORACLE服务
OracleOraHome81Agent           智能代理服务
OracleOraHome81CMan            连接管理服务
OracleOraHome81HTTPServer      APACHE WEB 服务
OracleOraHome81ManagementServer ORACLE 企业管理器服务
OracleOraHome81Names            ORACLE命名服务

数据库进程

(1).查看相关进程在数据库中的会话
Select a.sid,a.serial#,a.program, a.status ,
substr(a.machine,1,20), a.terminal,b.spid
from v$session a, v$process b
where a.paddr=b.addr
and b.spid = &spid;

(2).查看数据库中被锁住的对象和相关会话
select a.sid,a.serial#,a.username,a.program,
c.owner, c.object_name
from v$session a, v$locked_object b, all_objects c
where a.sid=b.session_id and
c.object_id = b.object_id;

(3).查看相关会话正在执行的SQL
select sql_text from v$sqlarea where address =
( select sql_address from v$session where sid = &sid );

查看IP地址
select SYS_CONTEXT('USERENV','IP_ADDRESS')  from dual;

删除重复列的方法

(1) DELETE FROM table_name A WHERE ROWID > (
       SELECT min(rowid) FROM table_name B
        WHERE A.key_values = B.key_values);
(2) create table table2 as select distinct * from table1;
     drop table1;
     rename table2 to table1;
(3) Delete from mytable where rowid not in(
       select max(rowid) from mytable
       group by column_name );
(4) delete from mytable t1
      where  exists (select 'x' from my_table t2
                   where t2.key_value1 = t1.key_value1
                     and t2.key_value2 = t1.key_value2
                     ...
                     and t2.rowid > t1.rowid);

JOB中日期的使用

每个月1号:
last_day(sysdate)+1
每个季度的第一天:
to_date(decode(to_char(sysdate,'q'),'1',to_char(sysdate,'yyyy')||'0101',
    '2',to_char(sysdate,'yyyy')||'0401','3',to_char(sysdate,'yyyy')||'0701',
    '4',to_char(sysdate,'yyyy')||'1001'),'yyyymmdd')
每天:
sysdate+1
每个星期几:
decode(to_char(sysdate,'w'),'1',sysdate+7,
to_char(sysdate,'w'),'2',sysdate+6,to_char(sysdate,'w'),'3',sysdate+5,
to_char(sysdate,'w'),'4',sysdate+4,to_char(sysdate,'w'),'5',sysdate+3,
to_char(sysdate,'w'),'6',sysdate+2,to_char(sysdate,'w'),'7',sysdate+1)

每个星期x下午三点：interval(21, 'next_day(trunc(sysdate),x+1)+15/24');

每个季度的第一个星期x：
interval(21, 'next_day(trunc(sysdate,'Q'),3),5)');