【Oracle】实用脚本归纳(3)

文中使用的Oracle版本为10g。

系列的第三弹,依然是使用得比较多的脚本

  1. 查看正在运行的存储过程
select b.sid,b.SERIAL#
from SYS.V_$ACCESS a, SYS.V_$session b
where a.type = 'PROCEDURE'
and (a.OBJECT like upper('%ZZ_Z%') or a.OBJECT like lower('%ZZ_Z%'))
and a.sid = b.sid
and b.status = 'ACTIVE';
  1. Unix下数据备份
# 先转成oracle用户
su - oracle
#导出命令
$ exp <username>/<password> file=<dmp_file_path> tables=<tables_you_need>
  1. Job(定时器)正确写法
-- 每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/ (24*60);

-- 每天定时执行(每天的凌晨1点执行)
Interval => TRUNC(sysdate) + 1 +1/ (24);

-- 每周定时执行(每周一凌晨1点执行)
Interval => TRUNC(NEXT_DAY(sysdate,'星期一'))+1/24;

-- 每月定时执行(每月1日凌晨1点执行)
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24;

-- 每季度定时执行(每季度的第一天凌晨1点执行)
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24;

-- 每半年定时执行(每年7月1日和1月1日凌晨1点)
Interval => ADD_MONTHS(TRUNC(sysdate,'yyyy'),6)+1/24;

-- 每年定时执行(每年1月1日凌晨1点执行)
Interval =>ADD_MONTHS(TRUNC(sysdate,'yyyy'),12)+1/24;
  1. 增加临时表空间
alter tablespace TEMP add tempfile '<dbf_file_path>' size 2046M;
alter database tempfile '<dbf_file_path>' autoextend on next 3M maxsize unlimited;

5. 横表变纵表(行转列)

加入现在有一张表名为“tb”,且tb表中字段为Name(varchar2(12)),Subject(varchar2(128))和Result(int),如下:

Name        Subject      Result
---------- ----------- -----------
张三 语文   74
张三 数学   83
张三 物理   93
李四 语文   74
李四 数学   84
李四 物理   94

现在需要对其进行行转列,我们先看静态SQL的转换

select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name;

如果列字段并不固定,同时又想行转列,这种情况下需要使用函数处理

declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql);

结果输出为

姓名         语文        数学        物理          
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93