oracle sql 备忘

oracle sql查询一对多的表数据查询，多表显示对应的最后一条数据

oracle 怎么查看数据被锁
select * from v$locked_object;
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

alter system kill session'1025,41';-- 杀掉对应进程  其中1025为sid,41为serial#.

查出wid出现2次以上的wid

select wid from T_YQSBGL_SBKPXXB group by wid having count(*) >=2;

增加一个字段
alter table test1

add (name varchar2(30) default ‘无名氏’ not null);

查询表字段

select * from user_tab_columns where table_name='T_JJYJGL_CFDDBGSQB';

查询表字段注释

select * from user_col_comments where table_name='T_JJYJKL_JJYJKPXXGLB';

加主键 表student， 主键名pk_student，主键studentid

 alter table student add constraint pk_student primary key(studentid);

日期与时间戳互转

SELECT UNIX_TIMESTAMP('2016-08-01 13:00:00'),FROM_UNIXTIME(created, '%Y-%m-%d %H:%i:%S') FROM `customers`  

link字段不含‘-c’

SELECT `name`, concat(`link`,'-c-',`id`) as link, `on_menu` FROM `catalogs` where `visibility`=1 and locate('-c-',link)=0

locate() 只要找到返回的结果都大于0(即使是查询的内容就是最开始部分)，没有查找到才返回0；

2014年购买3+以上用户

select b.customer_id from `customers` a left join orders b on a.id=b.customer_id 
where b.created>=UNIX_TIMESTAMP('2014-01-01 00:00:00') and b.created<UNIX_TIMESTAMP('2015-01-01 00:00:00')

group by b.customer_id having count(b.customer_id)>=3

利用DBLink+JOB实现两个Oracle数据库之间的数据同步

-- Create database link   

create public database link usr_zc_app_LINK  
  connect to usr_zc_app identified by wisedu001  
  using '10.5.7.5:1521/ywkdb';  
  
  
  --查询其中一个表， 测试是否连接成功
  select * from T_TEST_XL@usr_zc_app_LINK; 
  
  
  
-- 数据同步的存储过程  执行完之后，点编辑看下是否有报错。确保编译不报错
CREATE OR REPLACE PROCEDURE TEST_JOB_SCOTT  
AS SCOTT_ID   NUMBER;  
BEGIN  
   SELECT   NVL (MAX (WID), 0) INTO SCOTT_ID FROM T_TEST_XL@usr_zc_app_LINK;  
  
    INSERT INTO T_TEST_XL@usr_zc_app_LINK  
      SELECT   *  
          FROM   T_TEST_XL  
        WHERE   T_TEST_XL.WID > SCOTT_ID;  
  
  
     COMMIT;  
EXCEPTION  
    WHEN OTHERS  
    THEN  
      DBMS_OUTPUT.PUT_LINE ('Exception happened,data was rollback');  
      ROLLBACK;  
END;  




--建立JOB任务,定时同步数据  这段需要在命令窗口执行
declare
  job number;
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job,  /*自动生成JOB_ID*/  
        WHAT => 'TEST_JOB_SCOTT;',  /*需要执行的存储过程名称或SQL语句*/  
        NEXT_DATE => sysdate+3/(24*60),  /*初次执行时间-下一个3分钟*/  
        INTERVAL => 'trunc(sysdate,''mi'')+5/(24*60)' /*每隔5分钟执行一次*/
      );  
  commit;
end;

-- 查询定时任务

SELECT * FROM USER_JOBS