oracle遇到问题总结

{当你试图解决一个你不理解的问题时,复杂化就产生了。}

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
【记录者】：菜萌