Oracle 锁表解除

首先执行下面的查询语句,查询出锁表信息。

select * from v$session t1
inner join v$locked_object t2
on t1.sid = t2.session_id

将查出的SID和serial#字段的值赋予下面语句,逗号分割。

alter system kill session '【SID】,【serial#】';

Oracle数据库的内置定时任务

想要使用Oracle自带的定时计划,来固定每过一段时间执行一次固定的SQL语句,按照如下操作进行设置。

1. 首先创建一个存储过程(必须是存储过程,在设置定时任务时直接写SQL语句会不执行)

create or replace procedure proc_changesettings
as
begin
delete from tb_f_record where id = 0;
commit;
exception
when others then
rollback;
end;

2. 执行创建定时任务SQL语句,dbms_job.submit第二个参数就是存储过程名称

declare
  JOB number;
begin
  dbms_job.submit(JOB, 'proc_changesettings;', sysdate ,'TRUNC(sysdate,''mi'') + 1/ (24*60)');    --1分钟一次
  commit;
  DBMS_JOB.RUN(JOB); 
end;

以上就创建好了,可以使用下面的SQL语句进行查询

select job,broken,what,interval,t.* from user_jobs t; 

另外,当定时任务不再使用时,通过如下SQL语句来删除,参数job是表user_jobs的job字段

begin dbms_job.remove(job); end;

Oracle 查询时将结果为负数的转换为0 取最大值、最小值[Greatest、least]函数

我们在做数据库统计时往往会遇到查询列是计算出来的,例如查询两公司销售差额等。最近有一个这样的需求:将计算后的结果为负数的改为0,一般来讲我们会直接使用【case when then else end】这样的操作,但是当你计算的公式很长很长的时候,你肯定不想弄两遍公式吧,一个是sql显得臃肿不美观,另外一个数据库处理速度相对也会影响。

这时推荐Oracle的两个函数:取最大值(Greatest)最小值(Least)

这里举个例子,有表t1、t2都有result字段,先将result为空的转换为0,再t2-t1得出结果,如果为负数取0。
一般我们这么写:

select (case when 
(
  (case when t2.result is null then 0 else t2.result end) - 
  (case when t1.result is null then 0 else t1.result end)
) < 0 then 0 else
(
  (case when t2.result is null then 0 else t2.result end) - 
  (case when t1.result is null then 0 else t1.result end)
) end) result from t1, t2

用函数的话就是这样子:

select greatest(
  (case when t2.result is null then 0 else t2.result end) - 
  (case when t1.result is null then 0 else t1.result end)
,0) result from t1, t2

示例是用的取最大值函数,还有least取最小值函数用法是一样的就不写例子了。

Oracle 错误 ORA-01450:超出最大的关键字长度(6398)

有时我们在【创建数据表】时、【创建物化视图】时、亦或者在【给数据表创建索引】时,提示这个错误:
ORA-01450:超出最大的关键字长度(6398)

这个错误网络上中文解释很少,后在国外网站找,找到了问题原因。其实它的提示意思就是”单个表中,建立索引的字段长度总和不能超过6398字节“。然后我看了一下我的表,我是在做物化视图时遇到的这个错误,我检查了一下有两个varchar2(4000)的字段在我写的语句内,那就相当于8000字节长度大于oracle规定的6398,这个错就是这么出现的。

解决方法也很简单,如果是新建索引的话那就尽量控制字段的长度;如果是物化视图时提示,并且原表不想修改字段类型且确实没有某条数据占用那么多长度的话,可以在select语句中使用Translate(colname USING NCHAR_CS) 的形式,转换查询出来的数据为nchar类型即可成功创建。

create materialized view mv_report_emplfee 
refresh force on demand start with sysdate next
to_date(concat(to_char( sysdate+1,'dd-mm-yyyy'),'09:00:00'),'dd-mm-yyyy hh24:mi:ss') 
as
select Translate(detail.emp_name USING NCHAR_CS) emp_name,
       Translate(detail.emp_no USING NCHAR_CS) emp_no,
       sum(detail.bill_total) bill_total
  from tb_pay_detail detail
 group by detail.emp_name, detail.emp_no

SQL Server 删除所有表内数据

我们有时需要将数据库内的所有表数据删除,例如调整账套,恢复损坏数据库时等,然而不能直接把表删了,因为要保留表结构以及一些约束。这里使用如下sql命令:
先将所有表的约束取消,然后执行删除所有表,最后将约束启用。非常简单直接.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'

参考链接:http://www.jb51.net/article/42239.htm

SQL Server 数据库(数据表)损坏修复 dbcc checkdb、dbcc checktable

一般数据库损坏可以尝试使用dbcc checkdb(‘dbname’)直接处理即可,但有时直接使用此方法不会显示任何的错误信息,且不会有任何的修复动作。
然后尝试使用checktable命令则有反应且可以恢复,但数据表多的时候不可能自己手动一个个复制表名执行吧,这时候遍历所有数据表依次修复数据表是不错的解决方案。
首先需要将数据库设置为单用户连接,恢复完毕后取消单用户设置即可。

设置单用户模式:
declare @dbname varchar(255)      
set @dbname='UFDATA_002_2016'      
exec sp_dboption @dbname,'single user','true'    

取消单用户模式:
exec sp_dboption @dbname,'single user','false'    

修复的sql代码如下:
该代码效果是使遍历当前数据库的表,将所有表依次执行checktable操作。

use ufdata_001_2016
go
declare @i int, @val nvarchar(100)
set @i = 4700
while @i <= 5397
begin
	select @val = name from 
	(select ROW_NUMBER() over(order by name) rowid, name from sys.tables) a
	where rowid = @i
	
	if object_id(@val,N'U') is not null	
	begin
	dbcc checktable(@val,REPAIR_ALLOW_DATA_LOSS)      
	dbcc checktable(@val,REPAIR_REBUILD)  
	end
	 set @i = @i + 1
end

另损坏的数据表直接select查询时,有可能会报这个错误:
消息 605,级别 21,状态 3,第 2 行
尝试在数据库 9 中提取逻辑页 (1:18488) 失败。该逻辑页属于分配单元 281474979397632,而非 281474980642816。
以下为查询对应表名的语句,将查询出来的表名手动进行checktable修复以解决问题。

SELECT au.allocation_unit_id, OBJECT_NAME(p.object_id) AS table_name, fg.name AS filegroup_name,
au.type_desc AS allocation_type, au.data_pages, partition_number
FROM sys.allocation_units AS au
JOIN sys.partitions AS p ON au.container_id = p.partition_id
JOIN sys.filegroups AS fg ON fg.data_space_id = au.data_space_id
WHERE au.allocation_unit_id = 281474979397632 OR au.allocation_unit_id = 281474980642816
ORDER BY au.allocation_unit_id;

通过SQLCMD执行SQLServer的定时备份

  1.  首先创建SQL文件
    declare @device varchar(30);
    declare @filename varchar(50);
    declare @datetime varchar(50);
    
    set @datetime =  convert(varchar(20),getdate(),112);
    set @device= @datetime;
    set @device= 'NWdevice' + @datetime;
    set @filename='D:\备份\'+@datetime+'.bak'
    
    execute sp_addumpdevice 'disk',@device,@filename;
    backup database developrdb to @device
    
    
  2. 创建bat批处理文件
    @sqlcmd -i backup.sql -d developrdb -s 127.0.0.1
  3. 在Windows控制面板下的任务计划定时调用bat文件即可。(此步骤详细操作略)

Excel 将含有上下午日期格式转换为24小时一般日期格式

带有上下午的日期格式有时直接通过改变单元格格式是不会发生变化的,所以这里提供一个转换函数,转换后可以通过设置单元格格式转换为自己想要的效果:

=REPLACE(D2,FIND(“午”,D2&”上午”)-1,3,)+COUNTIF(D2,”*下*”)/2*ISERR(FIND(“下午 12”,D2))

 

 

参考链接:http://club.excelhome.net/thread-859395-1-1.html