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_LINKconnect 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