ORACLE使用备忘录

1.将一个用户SCHEMA下的表的增删查改权限赋予另一个用户

--假设现在要将test_u用户的SCHEMA下的所有表操作权限赋予test_user
GRANT ALL ON  test_tab  TO test_user ;--将test_tab这表的操作权限赋予 test_user 这个用户
REVOKE ALL ON test_tab  FROM test_user ;--将test_tab这表的操作权限从 test_user 这个用户收回
--执行语句的用户必须是test_tab表的主人,即此表在其schema下(schema可以理解为用户空间 每个用户有唯一一个),或sys用户
--批量语句生成方法
--表
SELECT 'GRANT ALL ON test_u.'||table_name||' to test_user ;' from all_tables where owner= upper('test_u');
--视图
SELECT 'GRANT ALL ON test_u.'||view_name||' to test_user ;' from all_views where owner= upper('test_u');
--序列
grant create sequence,select any sequence to test_u;

--执行生成的语句即可
--收回同理
--表
SELECT 'REVOKE ALL ON test_u.'||table_name||' from test_user ;' from all_tables where owner= upper('test_u');
--视图
SELECT 'REVOKE ALL ON test_u.'||view_name||' from test_user ;' from all_views where owner= upper('test_u');
--序列
REVOKE create sequence,select any sequence from test_u;

--循环执行脚本
begin
for grant_sql in ( 
select 'grant ALL on scott.' || table_name || ' to test_user '  exe_sql
 from all_tables t
where t.owner = 'test_u'
)
loop 
  execute immediate grant_sql.exe_sql;
end loop;
end;

--查看用户的表对象权限
select * from user_tab_privs where GRANTEE='CWY';

--查看当前用户的系统权限
SELECT * from user_sys_privs;
--查询当前用户的所有权限
select * from session_privs;
--查看所有被分配DBA权限的用户 必须DBA去执行
select * from dba_role_privs where granted_role='DBA';

--授予收回DBA权限
GRANT dba to CWY;
revoke dba from CWY;

--同义词  访问其他用户的表不必加前缀 如user2访问user1的testTable表 
CREATE SYNONYM testTable FOR User1.testTable

2.触发器相关

--查看触发器
select *  from all_triggers where table_owner='test_u' and trigger_name='TRI_TEST';
--查看触发器创建语句
select * from all_source where type='TRIGGER' AND name='TRI_TEST';
--自动插入时间  id触发器   :new 代表将插入的数据  :old代表要更新的数据或者删除的数据
CREATE or REPLACE TRIGGER TRI_TEST INSERT on TEST FOR EACH ROW
  BEGIN
    select SEQ_TEST.nextval INTO :new.ID from dual;
    select sysdate  INTO :new.CTIME from dual;
  end;

3.创建用户

--这一步是创建用户并指定存储的表空间 和运行时的临时表空间
create user test_u identified by "a123456" default tablespace USERS  temporary tablespace TEMP;
grant connect,resource to CWY;

--如果是新的环境 表空间还得创建
create temporary tablespace tsp_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m  MAXSIZE UNLIMITED extent management local;
create tablespace tsp1  datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;

--删除用户以及表空间
drop user yujkdev2 cascade;--删除用户
drop tablespace YUJKDATA including contents and datafiles;--删除表空间和数据文件

--oracle组织结构  ORACLE软件>>orcl(实例)>>表空间(直接关联物理文件)>>用户(schema)>>各表各视图
--orcl相当于大楼 表空间是各层   用户是每层的各个房间   房间内的工位才是各表各视图

从一个用户导出表到另一个用户

select 'create table newuser.'|| a.table_name || ' as select * from '|| 'olduser.'||a.table_name||';' from dba_tables a where owner='olduser';
--生成sql语句  执行完就行,只是序列和触发器等没有过去 要注意

4.分区

CREATE TABLE TEST
(
    ID NUMBER(18) PRIMARY KEY,
    CTIME TIMESTAMP NOT NULL
)partition by range (CTIME) interval (numtoyMinterval (1,'MONTH'))
(
partition TEST_DEF_PART values less than (to_date('2019-06-01', 'yyyy-mm-dd'))
);

5.高级队列 AQ

可以在java端实时接收数据库数据变动的消息
一个很棒的教程
其中orai18n.jar特别注意 很重要 java端少了这个jar包并不会有异常 但是接收到的会是乱码,第二,不知道为什么使用consumer.setMessageListener的方法老是会接收不及时,但使用consumer.receive() 方法反而很正常,所以得自己写while循环去实现监听

--前提是sys用户对其赋予了权限  即使本身是dba用户了 也得sys用户赋权过,dba本身不完全包含这权限,其他都行 但存储过程创建就报错

--消息载体
create OR REPLACE  type aqmsg as object (message varchar2(4000));

--队列表
BEGIN
  dbms_aqadm.create_queue_table(
    queue_table   => 'aqmsg_queue_table',
    queue_payload_type => 'aqmsg',
    multiple_consumers => false
  );
end;


  --创建队列并启动队列：
    --可以分开的 两个函数
begin
  dbms_aqadm.create_queue (
    queue_name  => 'aqmsg_queue',
    queue_table => 'aqmsg_queue_table'
  );

  dbms_aqadm.start_queue(
    queue_name  =>  'aqmsg_queue'
  );
end;
--查看以下系统创建了哪些相关的对象
-- SELECT object_name, object_type FROM user_objects WHERE object_name like  '%SYSTASK%';


--消息入列 存储过程 CWY必须加 不然其他地方调用不了
CREATE or REPLACE PROCEDURE sendAq(msg VARCHAR2)
  AS
   r_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
  r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  v_message_handle RAW(16);
  o_payload aqmsg;
  BEGIN
    o_payload := aqmsg(msg);

  dbms_aq.enqueue(
    queue_name  => 'aqmsg_queue',
    enqueue_options => r_enqueue_options,
    message_properties => r_message_properties,
    payload => o_payload,
    msgid => v_message_handle
  );
  END;


--===============================备忘
--发送消息 发送消息要commit
--   DECLARE
--     r_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
--   r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
--   v_message_handle RAW(16);
--   o_payload aqmsg;
-- begin
--   o_payload := aqmsg('你好');
-- 
--   dbms_aq.enqueue(
--     queue_name  => 'aqmsg_queue',
--     enqueue_options => r_enqueue_options,
--     message_properties => r_message_properties,
--     payload => o_payload,
--     msgid => v_message_handle
--   );
--   commit;
-- end ;

--停止 删除队列
-- BEGIN
--    DBMS_AQADM.STOP_QUEUE(
--       queue_name => 'aqmsg_queue'
--       );
--    DBMS_AQADM.DROP_QUEUE(
--       queue_name => 'aqmsg_queue'
--       );
--    DBMS_AQADM.DROP_QUEUE_TABLE(
--       queue_table => 'aqmsg_queue_table'
--       );
-- END;

--通过SQL语句查看消息是否正常入队：
select * from AQ$AQMSG_QUEUE_TABLE;
select user_data from AQ$AQMSG_QUEUE_TABLE;

6.插入和更新并成一句,存在就更新,不存在就插入,同步表很好用

--解释:合并关于表1的语句
MERGE INTO table1 t1
--拼接要插入或更新的新数据集
--这里也可以是查询其他表,或者查询自己这张表的数据,反正能出结果集怎么都行 同步两张表数据时候很好用
--这里并不是同步表,所以直接拼接死结果集
USING (SELECT 'zhangsan' as KEY,'monitor' as VAL_STR,23 as VAL_NUMBER from dual) t2
--拿出(...)内上一步拼接或查询出的结果集去按条件对t1连接
--这里直接用别名和字段进行匹配
ON (t1.key=t2.key) 
WHEN MATCHED THEN
--如果匹配的上说明已经存在,这里会隐藏了update的目标对象  其实就是t1被匹配到的那条数据 所以update 后面直接是set
UPDATE SET VAL_NUMBER = b.VAL_NUMBER,VAL_STR = b.VAL_STR
--如果没匹配到, 这里的目标也隐藏了  也是t1 所以insert 后面直接是字段
WHEN NOT MATCHED THEN
INSERT (KEY,VAL_STR,VAL_NUMBER) VALUES (b.key,b.VAL_STR,b.VAL_NUMBER)

制作批量执行sql脚本的bat(例子)

--a.sql 文件
CREATE TABLE TEST1
(
    IID NUMBER(10) PRIMARY KEY,
    NAME VARCHAR2(50)
);
COMMENT ON COLUMN TEST1.IID IS 'adads';

--b.sql 文件
insert into TEST1(IID,NAME) values(1,'aaa');
insert into TEST1(IID,NAME) values(2,'bbb');

--all.sql 文件
--@ 后面加路径 不知道是否可以用相对路径
--设置回显sql语句 debug用
SET ECHO ON
@sub\a.sql
@sub\b.sql
exit

sqlplus 用户名/密码@orcl @all.sql>log.txt

sql脚本本身没什么好说的,按顺序就好,包括all.sql在内的所有sql文件时候默认路径就是从bat的路径开始算起的,如果all.bat和all.sql不在同一目录可能命令就不是@all.sql>log.txt了 可以是@sub/all.sql>log.txt [备注 sub文件夹和all.bat在同一级别] @…/all.sql>log.txt [备注 all.bat在子目录] 等 或者直接写绝对路径

7.hibernate内获取 oracle触发器自增主键 的id

hibernate 感觉有点奇怪 oracle触发器去自增序列的时候,hibernate怎么配都不行,要么获取不到序列值,要么获取错,要么直接保存异常.没办法 只能用原生的JDBC去保存,后来发现普通的执行sql语句 再怎么设置 Statement.RETURN_GENERATED_KEYS 也无效,反正是返回不了就是了.想了很久 决定另辟蹊径 用匿名存储过程去返回插入后自增的序列值.如下

--sql写法 前面加 BEGIN
BEGIN
--正常insert语句  
insert into table1 values('aa','bb');
--后面加返回的字段 多个用逗号分隔 如RETURNING ID,NAME INTO ?,?;  注意要;结尾 因为是存储过程
RETURNING ID INTO ?;
--加end
END;

hibernate里获取原生的jdbc连接 如果不是hibernate 直接拿原生连接即可

 		Session session = sessionFactory.getCurrentSession();
        try {
            Connection conn = ((SessionImplementor) session).getJdbcConnectionAccess().obtainConnection();
            return conn;
        } catch (SQLException e) {
            // e.printStackTrace();
            return null;
        }

执行匿名存储过程 并返回设置的值

 	CallableStatement call = getConn().prepareCall(sql);
 	//按sql中?的顺序注册返回值类型  从1开始
    call.registerOutParameter(1, OracleTypes.NUMBER);
    call.execute();
    //返回对象 可以get具体类型 但要和注册的一致
    return call.getObject(1);