小知识点总结

1、正在连接的用户不能删除，确实要删除的话，如下
 select sid,serial#,username from v$session where user='USERNAME';
 alter system kill session 'sid,serial#';
 drop user username cascade;

 --删除表空间及数据文件
drop tablespace ha_soar_data including contents and datafiles;



2、在一台机器中想分析下执行计划，但是在sqlplus中输入set autotrace on 报cannot set autotrace 错误
解决方法如下：
首先必须采用用Oracle的sqlplus登陆sys账号
sqlplus " sys/sys@XXX as sysdba "
然后执行如下脚本：
@?\sqlplus\admin\plustrce.sql; （创建plustrace角色并授权）   
@?\rdbms\admin\utlxplan.sql; （创建执行计划的表） 
然后执行： grant all on plan_table to public; （也可以授权给某一个单独的用户）
           grant plustrace to public; 
然后就可以进行 set autotrace了  但是只能在sqlplus中运行相关命令，在pl/sql developer等工具中仍然报错
关于Autotrace几个常用选项的说明:
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出

 
3、赋予创建dblink的权限有三种: sys 用户下
grant create database link to user ; --只有user用户能使用的dblink
grant create public database link to user ;--所有用户都可以使用的dblink
grant drop public database link to user; --删除dblink的权限

--创建
create (public) database link DB133
  connect to gzf_common identified by "gzf_common"
    using '  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.242.133)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = fgora1)
    )
  )';

--删除dblink 
DROP (public) DATABASE LINK DB133;
--查询该用户的dblink
select * from user_db_links;
--查询表
select * from scott.emp@DB133;



4、根据日期查星期
select to_char(sysdate,'day') from dual;
select sqbbh,sqsj,to_char(sqsj,'day') from tpg_gzfspb
select to_char(to_date('2014-09-07','yyyy-mm-dd'),'day') from dual;




5、取随机数据
select * from emp sample(50) 
--Sample值在[0.000001,99.999999]之间。定义结果集中包含记录占总记录数量的百分比。sample只对单表生效，不能用于表连接和远程表
select dbms_random.value from dual; 


6、创建触发器使表在插入数据时id自增长
create table ttt (id number primary key ,name varchar2(20),age number(2))
create or replace trigger gger_tt 
before insert on ttt
for each row
  when (new.id is null)
    begin
      select ttt_sequence.nextval into :new.id from dual;
      end;

--插入数据
insert into ttt(name) values('明')



7、这样查出来是本周一 、相似度函数
select trunc(sysdate,'iw') from dual; 
select UTL_MATCH.EDIT_DISTANCE_SIMILARITy('啊','啊吗')  from dual; 

8、使用序列作为插入值，初始值不是 1 
---创建序列
create sequence SEQ_TEST
minvalue 1
maxvalue 99999999999
start with 21
increment by 1
cache 20;

创建表时 
CREATE TABLE tbl_test(
    test_id NUMBER PRIMARY KEY, 
    test_name VARCHAR2(20)
)
 SEGMENT CREATION IMMEDIATE; --加上这句 插入数据使用序列时可使初始值为1 

或执行ALTER SYSTEM SET deferred_segment_creation=FALSE; dba 权限
insert into tbl_test values(seq_test.nextval,'abc')
select * from tbl_test



9、在cmd 命令中 通过sqlplus 登录时，提示不是内部或外部命令，也不是可运行的程序或批处理文件
查看 path环境变量里有没有配置oracle，没的话 将 bin 路径添加进去 （F:\oracle\product\10.2.0\db_1\BIN）


10、oracle远程连接服务器出现 ORA-12170 TNS连接超时 解决办法
    关闭防火墙即可


11、SQL Server 2000/2005中可以快速压缩日志log文件  PropertyManagementCenterIMS 为数据库名 
DUMP TRANSACTION PropertyManagementCenterIMS WITH NO_LOG
BACKUP LOG PropertyManagementCenterIMS WITH NO_LOG
DBCC SHRINKDATABASE(PropertyManagementCenterIMS )


sql server 2008 压缩方法
--查询当前数据库的名称和日志名称 注意 这个和物理文件名可以是不同的 注意
select * from sys.database_files   

USE master
ALTER DATABASE PropertyManagementCenterIMS SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE PropertyManagementCenterIMS SET RECOVERY SIMPLE --简单模式
USE PropertyManagementCenterIMS
DBCC SHRINKFILE ('PropertyManagementCenterIMS_log' , 1, TRUNCATEONLY)  --压缩成1M
USE master
ALTER DATABASE PropertyManagementCenterIMS SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE PropertyManagementCenterIMS SET RECOVERY FULL --还原为完全模式




12、删除重复数据
delete  from  emp1 e  where  e.rowid > (select min(x.rowid) from  emp1 x  where  x.empno = e.empno);

DELETE FROM  TEST1  A WHERE EXISTS(SELECT 1 FROM TEST1 B WHERE A.EMPNO=B.EMPNO AND A.ROWID>B.ROWID)



13、 创建一个job 

var job_num number;
 begin
   dbms_job.submit(:job_num,'test_proc;',SYSDATE,'sysdate+2/24/60');  --第2分钟执行一次
 end;


删除一个job 
exec dbms_job.remove(81);
这里的数字81是对应DBA_JOBS表中当前要删除的JOB记录所在行的JOB字段的值；


SELECT TRUNC(sysdate) +1 +2/(24)FROM dual;         --每天的凌晨2点执行
SELECT TRUNC(next_day(sysdate,2))+2/24 FROM dual;   --每周一凌晨2点执行
SELECT TRUNC(LAST_DAY(SYSDATE))+1+2/24 FROM dual;  --每月1号凌晨2点执行
SELECT TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24 FROM dual;  --每季度的第一天凌晨2点执行
SELECT ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24 FROM dual; --每年7月1日和1月1日凌晨2点
SELECT ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24 FROM dual; --每年1月1日凌晨2点
SELECT Sysdate + 5 / (24*60)  FROM dual;  --每5分钟执行一次



14、查询锁表，kill 进程
SELECT object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;

alter system kill session 'sid,serial#';


15、 case when exists 

SELECT CASE
         WHEN EXISTS (SELECT 1 FROM tpr_dwxx WHERE ID=10000016) THEN
          1 ELSE 0 END
  FROM DUAL;




16、查询死锁及kill 
SELECT l.session_id sid,  
       s.serial#,  
       l.locked_mode 锁模式,  
       l.oracle_username 登录用户,  
       l.os_user_name 登录机器用户名,  
       s.machine 机器名,  
       s.terminal 终端用户名,  
       o.object_name 被锁对象名,  
       s.logon_time 登录数据库时间  
FROM v$locked_object l, all_objects o, v$session s  
WHERE l.object_id = o.object_id  
   AND l.session_id = s.sid  
ORDER BY sid, s.serial#;  

模板：alter system kill session 'sid,serial#' immediate;
实例：alter system kill session '145,761' immediate;



17、like 查询下线线开头的字符
SELECT * FROM sky.test2  WHERE NAME LIKE '\_%' ESCAPE '\';