【Oracle】实用脚本归纳（3）

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

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

查看正在运行的存储过程

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';

Unix下数据备份​

# 先转成oracle用户
su - oracle
#导出命令
$ exp <username>/<password> file=<dmp_file_path> tables=<tables_you_need>

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;

增加临时表空间​

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