{当你试图解决一个你不理解的问题时,复杂化就产生了。}
1.ORACLE用户解锁+查看是否被锁
select * from USER_TABLES where table_name='CRM_BPM_MENU' --查看是否被锁
alter user ireport account unlock; --解锁
2.Delete 表之后后悔?想恢复数据?(看这里)
1、查询数据库当前时间(目的是为了检查数据库时间是否与你电脑时间相近,避免时间不同而将数据恢复到错误时间点)
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
2、查询删除数据时间点之前的数据
select * from 表名 as of timestamp to_timestamp('2019-04-30 16:12:11','yyyy-mm-dd hh24:mi:ss');
(若没有数据 ,将时间继续提前)
3.-开启用行移动功能
alter table 表名 enable row movement;
4、恢复数据
flashback table 表名 to timestamp to_timestamp('2019-04-30 16:12:11','yyyy-mm-dd hh24:mi:ss');
3.查询重复数据
select *
from tmp_lyz_prod20190821 a where (a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE) in (
select a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE from tmp_lyz_prod20190821 a group by
a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE having count(*) > 1) and rowid not in (
select min(rowid) from tmp_lyz_prod20190821 a group by a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE having count(*)>1)
3.1 删除重复数据,保留一条
delete
--select *
from tmp_lyz_prod20190821 a where (a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE) in (
select a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE from tmp_lyz_prod20190821 a group by
a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE having count(*) > 1) and rowid not in (
select min(rowid) from tmp_lyz_prod20190821 a group by a.bk_portfolio, a.bk_product, a.record_code, a.LIQUIDATION_DATE having count(*)>1)
4.数据库表空间使用情况
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
5.循环+LOOP的使用
BEGIN
FOR tmp_cur IN(select 1 as sk_date ,2 as idx from dual)
LOOP gv_filedate(tmp_cur.idx)=tmp_cur.sk_date
END LOOP;
FOR tmp_cur IN(select 2 as sk_date ,3 as idx from dual)
LOOP gv_filedate(tmp_cur.idx)=tmp_cur.sk_date
END LOOP;
END;
–取前几
row_number() over(PARTITION BY b.sk_issue ORDER BY a.opdate DESC) AS row_no
6.生成测试脚本
select 'select '''||t.table_name||''','''||u.comments||''', COUNT(1) from '||t.table_name ||' union' from user_tables t
LEFT JOIN user_tab_comments u ON t.TABLE_NAME=u.TABLE_NAME
where t.table_name like 'WDB_AM_%' and t.num_rows>0
7.错误小汇总
ORA-00918: column ambiguously defined 未明确定义列
异常原因:select 查询的字段在from的两张表中都存在,导致数据库无法区别需要查询的字段来自于哪张表
ORA-0131:Insufficient privileges.调用存储过程测试。
Note:Debugging requires the DEBUG CONNECT SESSION system privileges.
解决办法:以SYS用户登录数据库,执行赋权操作:
SQL> grant DEBUG CONNECT SESSION to user_name;
有网友指出还需赋予DEBUG ANY PROCEDURE的权限,经测试,该权限可不用赋予!
select * from role_sys_privs where privilege like ‘DEBUG%’ order by 2;–可以从数据字典role_sys_privs表查看该权限相关信息:
8.权限问题汇总
赋予权限
grant dba to xxx;
–删除权限
revoke dba from xxx;
–赋予用户登录数据库的权限
grant create session to xxx;
–授予用户操作表的权限
grant unlimited tablespace to xxx;
grant create tablespace to xxx;
grant alter tablespace to xxx;
grant drop tablespace to xxx;
grant manage tablespace to xxx;
–撤销用户操作表空间的权限
revoke unlimited tablespace from xxx;
revoke create tablespace from xxx;
revoke alter tablespace from xxx;
revoke drop tablespace from xxx;
revoke manage tablespace from xxx;
grant create session to zhangsan;--授予zhangsan用户创建session的权限,即登陆权限
grant unlimited session to zhangsan;--授予zhangsan用户使用表空间的权限
grant create table to zhangsan;--授予创建表的权限
grant drop table to zhangsan;--授予删除表的权限
grant insert table to zhangsan;--插入表的权限
grant update table to zhangsan;--修改表的权限
grant select on tablename to zhangsan;--授予zhangsan用户查看指定表的权限
grant drop on tablename to zhangsan--授予删除表的权限
grant insert on tablename to zhangsan;--授予插入的权限
grant update on tablename to zhangsan;--授予修改表的权限
select * from user_sys_privs;--查看当前用户所有权限
select * from user_tab_privs;--查看所用用户对表的权限
9.存储过程中缓冲过小
问题现象:ORA-06512:字符串缓冲区太小
所以在存储过程或函数里定义变量时最好使用【表名.字段名%type】的方式去定义,可以避免以后表字段变大时出现的异常问题。
10.截取固定字符前面的字符串
select substr('678910.SHD',1,instr('678910.SHD','.')-1) from dual --截取.前面的字段
select substr('678910.SHD',1,instr('678910.SHD','.')-1),instr('678910.SHD','.')-1,length(substr('678910.SHD',1,instr('678910.SHD','.')-1)) from dual
11.oracle数据库备份
grant CREATE ANY DIRECTORY to infa; --赋权
select * from dba_directories; --查找全部
select * from all_directories where DIRECTORY_NAME = 'DIR_TEST'; --查找
create directory dumpdir as 'E:\temp';--创建
create directory dir_test as '/home/oracle/secooler';--创建
create or replace directory dumpdir as '/home/dumpfiles'; --修改
grant read,write on directory DUMPDIR to system;--赋权文件
expdp infa/INFA@ORCL directory=dir_test dumpfile=infa%date:~0,4%%date:~5,2%%date:~8,2%.DUMP schemas=infa
grant read,write on directory dir_test to system;
select * from dba_directories;
create or replace directory dumpdir as 'E:\temp'; --修改
expdp infa/INFA@ORCL directory=dumpdir full=y dumpfile=infa.dmp logfile=fullexp.log;
expdp 'sys/system as sysdba' directory=dumpdir full=y dumpfile=fullexp.dmp logfile=fullexp.log;
WIND中快捷键:compmgmt.msc 定时计划CMD
12.想取表中部分数据中加条件限制+不在这部分的数据
解决方案:使用or,就按照意思来写
select * from table_name
where id>0 AND name like '%小_%' or id<=0
13.问题描述
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效 发生异常
原因:其他Session已经对目标表做了操作,且未提交操作,导致锁表,新的Session无法再对表进行DDL操作。
解决方法
Plan-A:等待原session执行完对表的操作,或commit对表的操作。
Plan-B:关闭原会话。
--查询被锁的会话ID
select session_id from v$locked_object;
--查询结果:SESSION_ID-------9
--查询上面会话的详细信息:
SELECT sid, serial#, username, osuser FROM v$session where sid = 9;
--查询结果:serial#------99
--将上面锁定的会话关闭:
ALTER SYSTEM KILL SESSION '9,99';
14.查看执行的进度
SELECT SE.SID,
OPNAME,
TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,
ELAPSED_SECONDS ELAPSED,
ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
SQL_TEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID
AND SOFAR != TOTALWORK
ORDER BY START_TIME;
15.查看表在那个存储过程中使用
--在进行字符串比较的时候,注意去空格
SELECT DISTINCT * FROM user_source
WHERE TYPE = 'PACKAGE BODY'
AND upper(text) LIKE '%FACT_PORT_ANALISIS%';
15.取表中按照某个字段排序并输出前几个TOP150
select * from (select * from ods.ctl_etlrun_log c order by c.log_serialno desc) where rownum<=150;
SELECT al.*
,rank() over(PARTITION BY al.cpdm ORDER BY nvl(al.cyje, 0) DESC) rn
FROM (SELECT cpdm,cyje from dual) al)
WHERE rn <= 200;
select * from issu_mkt_issue --投资品种
select * from prod_portfolio --套帐信息
select * from dw.mid_voucher --凭证中间表
select * from dw.dim_product --产品信息维
16.LEF JOIN 造成数据重复
Oracle中的left join查询可能出现重复数据 (主表中一条变多条)一对多的情况
左表与右表LEFT JOIN,关联条件时,关联条件中右表数据是1:多的时候,造成数据重复
解决方案:将关联条件放在GROUP BY 里面,分组就可以了
17.Oracle substr()与substrb()
【参数】c1是字符串,n1是开始位置,n2是字符/节数
一个汉字两个字节,一个字符
SUBSTR(c1, n1) 从字符串中指定的开始位置,取得后面的字符串
SUBSTR(c1, n1, n2 ) 从字符串中指定的开始位置,取得指定字符数的字符串
SUBSTRB(c1 ,n1,n2 ) 从字符串中指定的开始位置,取得指定字节数的字符串
从上面的就是可以看出substr()和substrb()的不同之处,在于substr()是取得字符串的字符数,substrb()是取得字符串的字节数。
例如:
1)取得字符串“今天天气很不错”,第3个字符开始的字符串
select substr( '今天天气很不错' ,3) from dual;
输出的结果为:天气很不错
2)取得字符串“今天天气很不错”,第3个字节开始的字符串
select substrb( '今天天气很不错' ,3) from dual;
输出的结果为:天天气很不错
3)取得字符串“今天天气很不错”,第3个字符开始2个字符的字符串
select substr( '今天天气很不错' ,3,2) from dual;
输出的结果为:天气
4)取得字符串“今天天气很不错”,第3个字节开始2个字节的字符串
select substrb( '今天天气很不错' ,3,2) from dual;
输出结果为:天
注:当开始位置设定为负数时,是从字符串最后开始检索的。
与这两个函数类似的还有:
length与lengthb 长度计算函数
select length('天气') from dual;
输出:2
select lengthb('你好') from dual;
输出:4
18.oracle截取某一个字符之前或之后的值;substr();instr()
函数介绍:
截取的函数:
substr(?,?); --要截取的字符串,开始的位置(包含),默认截取到末尾
substr(?,?,?); --要截取的字符串,开始的位置(包含),结束的位置(包含)
获取目标字符出现的位置:
instr(? , ? , ? ); --要检索的字符串,目标字符串,参数开始的位置
instr( ? , ? , ? , ? ) --要检索的字符串,目标字符串,参数开始的位置,第几次出现
select substr('AAA-BBB',1,instr('AAA-BBB','-',-1)-1) 值 from dual;--结果:AAA
select substr('AAA-BBB',instr('AAA-BBB','-',-1)+1) 值 from dual; --结果:BBB
19.创建同义词
create synonym table_name for user.table_name;
grant select/delete/update on user2;--在user用户上执行,将赋予user2用户操作user表的权限
select * from dba_synonyms; --查看同义词的情况。
20.Oracle COALESCE函数
Oracle COALESCE函数语法为COALESCE(表达式1,表达式2,…,表达式n),n>=2,此表达式的功能为返回第一个不为空的表达式,如果都为空则返回空值
注意:所有表达式必须为同一类型或者能转换成同一类型。
21.Delete 表,恢复数据
一:根据时间来恢复:
1、查询数据库当前时间(目的是为了检查数据库时间是否与你电脑时间相近,避免时间不同而将数据恢复到错误时间点)
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual;
2、查询删除数据时间点之前的数据
select * from 表名 as of timestamp to_timestamp(‘2019-04-30 16:12:11’,‘yyyy-mm-dd hh24:mi:ss’);
(若没有数据 ,将时间继续提前)
3、恢复数据(激动人心的时刻)
flashback table 表名 to timestamp to_timestamp(‘2019-04-30 16:12:11’,‘yyyy-mm-dd hh24:mi:ss’);
大功告成,数据恢复成功;
但是也可能会出现问题,比如报错:ORA-08189:未启用行移动功能,不能闪回表;
不要怕,这个很简单
alter table 表名 enable row movement;
然后再次执行上面SQL即可;
22.row_number()
现在需要按照课程对学生的成绩进行排序:
--row_number() 顺序排序(1,2,3,4,5,6)
select name,course,row_number() over(partition by course order by score desc) rank from student;
--rank() 跳跃排序,如果有两个第一级别时,接下来是第三级别(1,2,2,4,5,6)
select name,course,rank() over(partition by course order by score desc) rank from student;
--dense_rank() 连续排序,如果有两个第一级别时,接下来是第二级别 (1,2,2,3,4,5)
select name,course,dense_rank() over(partition by course order by score desc) rank from student;
TIPS:
使用rank over()的时候,空值是最大的,如果排序字段为null, 可能造成null字段排在最前面,影响排序结果。
可以这样: rank over(partition by course order by score desc nulls last)
总结:
在使用排名函数的时候需要注意以下三点:
1、排名函数必须有 OVER 子句。
2、排名函数必须有包含 ORDER BY 的 OVER 子句。
3、分组内从1开始排序。
23. ora-01537
添加表空间文件,添加的文件名重复
ALTER TABLESPACE TBS_ODS ADD DATAFILE
--'D:\app\Administrator\product\11.2.0\oradata\dctest\tbs_ods_02.dbf'
'/oradata/dctest/tbs_ods_05.dbf'
SIZE 50M
AUTOEXTEND ON;-- NEXT 5M MAXSIZE 100M;
select tablespace_name from dba_tablespaces;
select * from dba_data_files;
23.1 查看表空间使用情况
select b.file_id 文件ID,
b.tablespace_name 表空间,
b.file_name 物理文件名,
b.bytes 总字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name
24.DECODE函数的用途
sign(value)函数会根据value的值为0,正数,负数,分别返回0,1,-1
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
DEcode的用途:
1.翻译值、
decode(t.sex, '1', '男生', '2', '女生', '其他')
2.decode比较大小(利用sign)、
3.decode分段(利用sign)、
decode(sign(sal - 3000), 1, '中等', 0, '中等', -1, '低薪')
4.搜索字符串(利用instr(t.name, '三'))
decode(instr(t.name, '三'), 0, '姓名不含有三', '姓名含有三')
5.判断是否为空 decode(t.sex,NULL,'暂无数据',t.sex)
25.触发器
触发器的定义就是说某个条件成立的时候,触发器里面所定义的语句就会被自动的执行。
因此触发器不需要人为的去调用,也不能调用。
在一个表中定义的语句级的触发器,当这个表被删除时,程序就会自动执行触发器里面定义的操作过程。这个就是删除表的操作就是触发器执行的条件了。
在一个表中定义了行级的触发器,那当这个表中一行数据发生变化的时候,比如删除了一行记录,那触发器也会被自动执行了。
触发器的语法:
create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
pl/sql语句
end
触发器名:触发器对象的名称。由于触发器是数据库自动执行的,因此该名称只是一个名称,没有实质的用途。
触发时间:指明触发器何时执行,该值可取:
before:表示在数据库动作之前触发器执行;
after:表示在数据库动作之后触发器执行。
触发事件:指明哪些数据库动作会触发此触发器:
insert:数据库插入会触发此触发器;
update:数据库修改会触发此触发器;
delete:数据库删除会触发此触发器。
表 名:数据库触发器所在的表。
for each row:对表的每一行触发器执行一次。如果没有这一选项,则只对整个表执行一次。
实现功能:
1、 允许/限制对表的修改
2、 自动生成派生列,比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑
下面的触发器在更新表tb_emp之前触发,目的是不允许在周末修改表:
create or replace trigger auth_secure before insert or update or DELETE
on tb_emp
begin
IF(to_char(sysdate,'DY')='星期日') THEN
RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表tb_emp');
END IF;
END;
/
使用触发器实现序号自增
1.创建一个测试表:
create table tab_user(
id number(11) primary key,
username varchar(50),
password varchar(50)
);
2.创建一个序列
create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20;
3.创建一个触发器:
CREATE OR REPLACE TRIGGER MY_TGR
BEFORE INSERT ON TAB_USER
FOR EACH ROW--对表的每一行触发器执行一次
DECLARE
NEXT_ID NUMBER;
BEGIN
SELECT MY_SEQ.NEXTVAL INTO NEXT_ID FROM DUAL;
:NEW.ID := NEXT_ID; --:NEW表示新插入的那条记录
END;
4.向表插入数据:
insert into tab_user(username,password) values('admin','admin');
insert into tab_user(username,password) values('fgz','fgz');
insert into tab_user(username,password) values('test','test');
COMMIT;
5.测试-查询表结果:SELECT * FROM TAB_USER;
当用户对test表执行DML语句时,将相关信息记录到日志表
–创建测试表
CREATE TABLE test(
t_id NUMBER(4),
t_name VARCHAR2(20),
t_age NUMBER(2),
t_sex CHAR
);
–创建记录测试表
CREATE TABLE test_log(
l_user VARCHAR2(15),
l_type VARCHAR2(15),
l_date VARCHAR2(30)
);
–创建触发器
CREATE OR REPLACE TRIGGER TEST_TRIGGER
AFTER DELETE OR INSERT OR UPDATE ON TEST
DECLARE
V_TYPE TEST_LOG.L_TYPE%TYPE;
BEGIN
IF INSERTING THEN
--INSERT触发
V_TYPE := 'INSERT';
DBMS_OUTPUT.PUT_LINE('记录已经成功插入,并已记录到日志');
ELSIF UPDATING THEN
--UPDATE触发
V_TYPE := 'UPDATE';
DBMS_OUTPUT.PUT_LINE('记录已经成功更新,并已记录到日志');
ELSIF DELETING THEN
--DELETE触发
V_TYPE := 'DELETE';
DBMS_OUTPUT.PUT_LINE('记录已经成功删除,并已记录到日志');
END IF;
INSERT INTO TEST_LOG
VALUES
(USER, V_TYPE, TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')); --USER表示当前用户名
END;
/
–下面我们来分别执行DML语句
INSERT INTO test VALUES(101,'zhao',22,'M');
UPDATE test SET t_age = 30 WHERE t_id = 101;
DELETE test WHERE t_id = 101;
–然后查看效果
SELECT * FROM test;
SELECT * FROM test_log;
创建触发器,它将映射emp表中每个部门的总人数和总工资
–创建映射表
CREATE TABLE dept_sal AS
SELECT deptno, COUNT(empno) total_emp, SUM(sal) total_sal
FROM scott.emp
GROUP BY deptno;
–创建触发器
CREATE OR REPLACE TRIGGER EMP_INFO
AFTER INSERT OR UPDATE OR DELETE ON scott.EMP
DECLARE
CURSOR CUR_EMP IS
SELECT DEPTNO, COUNT(EMPNO) AS TOTAL_EMP, SUM(SAL) AS TOTAL_SAL FROM scott.EMP GROUP BY DEPTNO;
BEGIN
DELETE DEPT_SAL; --触发时首先删除映射表信息
FOR V_EMP IN CUR_EMP LOOP
--DBMS_OUTPUT.PUT_LINE(v_emp.deptno || v_emp.total_emp || v_emp.total_sal);
–插入数据
INSERT INTO DEPT_SAL
VALUES
(V_EMP.DEPTNO, V_EMP.TOTAL_EMP, V_EMP.TOTAL_SAL);
END LOOP;
END;
–对emp表进行DML操作
INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000);
SELECT * FROM dept_sal;
DELETE EMP WHERE empno=123;
SELECT * FROM dept_sal;
创建触发器,用来记录表的删除数据
–创建表
CREATE TABLE employee(
id VARCHAR2(4) NOT NULL,
name VARCHAR2(15) NOT NULL,
age NUMBER(2) NOT NULL,
sex CHAR NOT NULL
);
–插入数据
INSERT INTO employee VALUES('e101','zhao',23,'M');
INSERT INTO employee VALUES('e102','jian',21,'F');
--创建记录表(包含数据记录)
CREATE TABLE old_employee AS SELECT * FROM employee;
–创建触发器
CREATE OR REPLACE TRIGGER TIG_OLD_EMP
AFTER DELETE ON EMPLOYEE
FOR EACH ROW --语句级触发,即每一行触发一次
BEGIN
INSERT INTO OLD_EMPLOYEE VALUES (:OLD.ID, :OLD.NAME, :OLD.AGE, :OLD.SEX); --:old代表旧值
END;
/
–下面进行测试
DELETE employee;
SELECT * FROM old_employee;
创建触发器,利用视图插入数据
–创建表
CREATE TABLE tab1 (tid NUMBER(4) PRIMARY KEY,tname VARCHAR2(20),tage NUMBER(2));
CREATE TABLE tab2 (tid NUMBER(4),ttel VARCHAR2(15),tadr VARCHAR2(30));
–插入数据
INSERT INTO tab1 VALUES(101,'zhao',22);
INSERT INTO tab1 VALUES(102,'yang',20);
INSERT INTO tab2 VALUES(101,'13761512841','AnHuiSuZhou');
INSERT INTO tab2 VALUES(102,'13563258514','AnHuiSuZhou');
–创建视图连接两张表
CREATE OR REPLACE VIEW tab_view AS SELECT tab1.tid,tname,ttel,tadr FROM tab1,tab2 WHERE tab1.tid = tab2.tid;
–创建触发器
CREATE OR REPLACE TRIGGER TAB_TRIGGER
INSTEAD OF INSERT ON TAB_VIEW
BEGIN
INSERT INTO TAB1 (TID, TNAME) VALUES (:NEW.TID, :NEW.TNAME);
INSERT INTO TAB2 (TTEL, TADR) VALUES (:NEW.TTEL, :NEW.TADR);
END;
/
–现在就可以利用视图插入数据
INSERT INTO tab_view VALUES(106,'ljq','13886681288','beijing');
–查询
SELECT * FROM tab_view;
SELECT * FROM tab1;
SELECT * FROM tab2;
创建触发器,比较emp表中更新的工资
–创建触发器
set serveroutput on;
CREATE OR REPLACE TRIGGER SAL_EMP
BEFORE UPDATE ON EMP
FOR EACH ROW
BEGIN
IF :OLD.SAL > :NEW.SAL THEN
DBMS_OUTPUT.PUT_LINE('工资减少');
ELSIF :OLD.SAL < :NEW.SAL THEN
DBMS_OUTPUT.PUT_LINE('工资增加');
ELSE
DBMS_OUTPUT.PUT_LINE('工资未作任何变动');
END IF;
DBMS_OUTPUT.PUT_LINE('更新前工资 :' || :OLD.SAL);
DBMS_OUTPUT.PUT_LINE('更新后工资 :' || :NEW.SAL);
END;
/
–执行UPDATE查看效果
UPDATE emp SET sal = 3000 WHERE empno = '7788';
创建触发器,将操作CREATE、DROP存储在log_info表
–创建表
CREATE TABLE log_info(
manager_user VARCHAR2(15),
manager_date VARCHAR2(15),
manager_type VARCHAR2(15),
obj_name VARCHAR2(15),
obj_type VARCHAR2(15)
);
–创建触发器
set serveroutput on;
CREATE OR REPLACE TRIGGER TRIG_LOG_INFO
AFTER CREATE OR DROP ON SCHEMA
BEGIN
INSERT INTO LOG_INFO
VALUES
(USER,
SYSDATE,
SYS.DICTIONARY_OBJ_NAME,
SYS.DICTIONARY_OBJ_OWNER,
SYS.DICTIONARY_OBJ_TYPE);
END;
/
–测试语句
CREATE TABLE a(id NUMBER);
CREATE TYPE aa AS OBJECT(id NUMBER);
DROP TABLE a;
DROP TYPE aa;
–查看效果
SELECT * FROM log_info;
–相关数据字典
SELECT * FROM USER_TRIGGERS;
–必须以DBA身份登陆才能使用此数据字典
SELECT * FROM ALL_TRIGGERS;SELECT * FROM DBA_TRIGGERS;
–启用和禁用
ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER trigger_name ENABLE;
26.根据存储过程名称导出
SET echo off ;
SET heading off ;
SET feedback off ;
SPOOL 'D:/DC_REOPRT/PRC_REPORT_NEW_CISP.SQL' replace
SELECT CASE
WHEN LINE = 1 THEN
'CREATE OR REPLACE ' || TEXT
WHEN LINE = MAX_LINE THEN
TEXT || CHR(10 ) || '/'
ELSE
TEXT
END
FROM USER_SOURCE A
LEFT JOIN (SELECT A.NAME,A.TYPE, MAX(LINE) MAX_LINE
FROM USER_SOURCE A
WHERE TYPE in ( 'PROCEDURE', 'PACKAGE' ,'PACKAGE BODY' )
GROUP BY A.NAME,A.TYPE ) B ON A.NAME||A.TYPE = B.NAME||B.TYPE
WHERE A.TYPE in ('PROCEDURE' , 'PACKAGE', 'PACKAGE BODY' ) AND A.NAME IN
(
'PKG_WDB_AM_PROD_BASEINFO'
)
ORDER BY a.NAME||a.TYPE , LINE;
SPOOL OFF
上面内容命名为export_cisp.sql,
注意:
文件名:D:/DC_REOPRT/PRC_REPORT_NEW_CISP.SQL
存储过程名: 'PKG_WDB_AM_PROD_BASEINFO'
使用:在wind下CMD小窗体执行下面命令:
1.登陆
sqlplus 用户名/密码@库地址
2.执行
@D:\DC_REOPRT\export_cisp.sql
27.exp命令导入导出
1.plsql developer导出工具那自己选对应的表
2.exp命令指定对应的表
3.dbms_metadata.get_ddl
自己写sql从user_tab_columns这些系统字典表去拼
1.略
2.使用EXP
导出表数据以及表结构(以Oracle11g为例)命令行:
exp 用户名/密码@数据库名字 owner=用户名?file=dmp文件存储位置(即导出文件为.dmp文件)
导入表数据以及表结构(以Oracle11g为例)命令行:
imp 用户名/密码@数据库名称?fromuser(文件的指定用户,即从那个用户导入数据)=用户名 touser(目标用户,即导入到当前登录的那个用户)=用户名 file=要导入的dmp文件位置 ignore=y(忽略错误创建)
3.使用dbms_metadata.get_ddl
select dbms_metadata.get_ddl('TABLE','TMP_TRD_FA_VCH_TRAN','ODS') from dual
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;
28.base64编码与解码 JAVA
Java 8的java.util套件中,新增了Base64的类别,可以用来处理Base64的编码与解码,用法如下:
final Base64.Decoder decoder = Base64.getDecoder();
final Base64.Encoder encoder = Base64.getEncoder();
final String text = "字串文字";
final byte[] textByte = text.getBytes("UTF-8");
//编码
final String encodedText = encoder.encodeToString(textByte);
System.out.println(encodedText);
//解码
System.out.println(new String(decoder.decode(encodedText), "UTF-8"));
【记录时间】:2019-09-25 18:17
【记录者】:菜萌