目录
Oracle 定时任务(dbms_jobs)
1、可以直接在 Oracle 数据库中创建定时任务用于执行存储函数或者存储过程，类似 windows 系统的定时任务。
2、建好后的定时任务可以在 pl/sql 工具中的左侧 DBMS_Jobs 菜单下看到，就如同查看表、视图、存储过程、序列等一样方便，显示的是任务ID(jobId)。
3、定时任务的查询、创建、禁用、停用、修改、删除 操作演示如下：
select t.*,ROWID from EMP t;
-- 1、创建存储过程：用于被定时任务调用(往 emp 插入数据)
create or replace procedure auto_insert_emp_job as
Begin
INSERT INTO C##SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ((select max(EMPNO)+1 from EMP), '李四', 'SALESMAN', 7698, TO_DATE('1983-04-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 1650.00, 350.00, 30);
commit;
End;
-- 2、创建定时器任务：调用存储函数或者存储过程
-- 建好后可以在 pl/sql 工具中的左侧 DBMS_Jobs 菜单下看到定时任务(job)，显示的是任务ID(jobId)
-- 因为任务ID(唯一标识符)是自动生成的，所以重复创建会产生重复的任务)
declare
job_id number; -- job 主键id，不用设置值让其自动生成
begin
sys.dbms_job.submit(job_id, -- job 主键id，不用传，会自动生成
'auto_insert_emp_job;', -- 需要执行的存储函数或者存储过程名称(结尾的分号不能少)
sysdate, -- 任务启动后第一次执行的时间
'trunc(sysdate,''mi'') + 1/ (24*60)'); --执行间隔时间，每1分钟执行一次
commit ;
end;
-- 3、查询数据库中的所有定时任务：根据定时任务执行的存储过程/函数进行查询
-- JOB：每个任务的唯一标识符
-- LOG_USER：提交作业时登录的用户
-- LAST_DATE、LAST_SEC：上次成功执行此作业的日期
-- THIS_DATE、THIS_SEC：当前正在运行的作业的开始时间，不在运行时为 null.
-- NEXT_DATE、NEXT_SEC：下一次执行此作业的日期
-- TOTAL_TIME：系统在此作业上花费的总挂钟时间，以秒为单位
-- BROKEN：如果为Y，则表示此作业已停止运行
-- failures：执行失败的次数
select a.* from dba_jobs a where what in ('auto_insert_emp_job;');
-- 没有 dba 权限时，只能查询当前用户自己的任务
select a.* from USER_JOBS a where WHAT in ('auto_insert_emp_job;');
-- 4、停止任务：1 是任务的唯一标识符，true 表示停止, sysdate 表示立即停止
begin
dbms_job.broken(1, true, sysdate);
commit ;
end;
-- 5、重新开始被停止了的任务：1 是任务的唯一标识符
begin
dbms_job.run(1);
commit ;
end;
-- 6、修改任务下次执行的时间，如果任务之前被禁用，执行之后会被重新启用.
begin
dbms_job.next_date(1,to_date('2021-09-04 16:00:00','yyyy-mm-dd hh24:mi:ss'));
commit ;
end;
-- 7、修改任务执行的间隔时间，会当任务执行完下一次之后才会生效，1 是任务的唯一标识符
begin
dbms_job.interval(1,'trunc(sysdate,''mi'') + 1/ (24*60)');
commit ;
end;
-- 8、删除定时任务, 1 是任务的唯一标识符
begin
dbms_job.remove(1);
commit ;
end;
| 每分钟执行 ： trunc(sysdate,'mi') + 1/(24*60) 每天的凌晨1点执行 ： trunc(sysdate) + 1 +1/(24) 每周一凌晨1点执行 ： trunc(next_day(sysdate,'星期一'))+1/24 每月1日凌晨1点执行： trunc(last_day(sysdate))+1+1/24 每季度的第一天凌晨1点执行 ： trunc(add_months(sysdate,3),'Q') + 1/24 每年7月1日和1月1日凌晨1点 ： add_months(trunc(sysdate,'yyyy'),6)+1/24 每年1月1日凌晨1点执行 ： add_months(trunc(sysdate,'yyyy'), 12)+1/24 |
-- 系统参数：job_queue_process(作业队列进程) 表示 oracle 并发的 job 数量，当值为0时表示停止全部job.
-- 需要管理员权限的账号才能修改
select * from v$parameter where name = 'job_queue_processes' ;
-- 修改 job_queue_processes 参数
alter system set job_queue_processes = 1000;trunc 函数不熟悉的可以参考：trunc 截断数字和日期
数据备份 与 导入导出
1、Oracle 的备份是 Oracle 操作中常见的工作，常见的备份方案有: 逻辑备份（IMP&EXP命令进行备份)、物理文件备份(脱机及联机备份)、利用 RMAN(Recovery Manager)的增量物理文件系统备份。
2、ORACLE 数据库的逻辑备份分为四种模式: 表空间备份（tablespace)、表备份(table)、用户备份(user)和完全备份(full)。
3、Oracle 的逻辑备份是使用 IMP&EXP 命令进行数据导入导出的操作。使用 EXP 命令导出或者使用IMP 命令导入时，需要 Create Session 系统权限，但是如果要导出其他的表，必须拥有权限: EXP_FULL_DATABASE。
4、EXP 命令导出的文件是二进制文件（*.dmp)，只能由对应的 IMP 命令进行读取恢复。
exp 与 imp 导入导出数据
1、在安装了 Oracle 后，在 bin 目录下提供了很多工具，其中就有 exp 与 imp. 可以在任意目录下的 cmd 中执行这些命令。exp、imp 命令的完整参数，可以通过 -help 进行查看：
| 命令 | 描述 |
|---|---|
| exp 用户名/密码@数据库地址 参数 | 默认表中数据都会一并导出。 BUFFER： 数据缓冲区大小。以字节为单位，一般在64000 以上。 COMPRESS：是否压缩导出(默认Y) 。 rows=n|y：如果只想导出表结构，不需要导出数据，则命令结尾加上 rows=n 参数（默认为 y）； file：指定输出文件的路径和文件名称(以 .dmp 为后缀名)，路径必须完整且存在，文件会自动生成。 GRANTS：是否导出权限 (默认Y) INDEXES：是否导出索引 (默认Y) CONSTRAINTS：是否导出的约束条件 (Y) TRIGGERS：是否导出触发器 (Y) PARFILE：可以把各种参数配置为一个文本键值形式的文件，该参数可以指定参 TABLES：导出指定的表名列表，导出多个表可以使用逗号隔开。 TABLESPACES ：导出指定表空间的数据 OWNER：导出指定用户的数据 FULL：导出数据库的所有数据(默认N) |
| imp 用户名/密码@数据库地址 参数 | 默认会导入 .dmp 文件中的表结构和数据。其他大部分参数与上面 exp 命名一致。 file：指定需要导入的 .dmp 文件路径。 log：记录导入的日志文件，不指定时不会记录，只会输出到屏幕上。 data_only=y|n：如果只想导入数据而不导入表结构，则加上 参数（默认为 n）; FROMUSER: 指定导出时 .dmp 文件中记载的用户信息。 TOUSER：指定 .dmp 文件要导入到什么目标用户中。 IGNORE：在恢复数据的过程中，当恢复的某个表已经存在，就要根据 ignore 参数的设置来决定如何操作。 COMMIT：默认N, 在缓冲区满时是否需要 commit, 如果设为 N, 需要较大的回滚段 。 |
--导出数据库
-- cmd 中执行如下命令，hnbs 为目标 Oracle 数据库中的账号，1 是密码，...240/1521 是目标数据库 ip 与端口，file 后面跟保存的 .dmp 文件路径
-- log 用于指定日志文件，不需要记录日志时可以省略不写
exp hnbs/1@192.168.2.240:1521/orcl file=d:/hnbs.dmp log=d:/hnbs.log
--在新数据库中创建同名的用户。在 plsql 等工具中执行命令。如果导出的数据库有表空间，则新数据库中的用户也建议设置同名的表空间，表空间需要提前创建。
--如果导出的数据库没有设置表空间，则这里也可以省略
select * from dba_tablespaces; -- 查询数据库所有表空间
create user hnbs identified by 1 default tablespace grp
--为用户授予权限
grant dba to hnbs;
--在 cmd 中执行命令，将导出的数据库文件导入到新数据库中,不需要记录日志时，可以省略 log 不写
imp hnbs/1@192.168.2.240:1521/orcl file=d:/hnbs.dmp log=d:/hnbs.log
--拓展：如果不想和导出的数据库使用一样的用户名称，则也可以创建不同的用户名，使用 fromuser 指定导出的数据库用户名称，touser 指定导入数据库用户名称
imp hnbs_3/1@192.168.2.240:1521/orcl file=C:\Users\Lenovo\Desktop\20190909yselement.dmp fromuser=hnbs touser=hnbs_3
2、除了导出、导入整个数据库，也可以单独导出、导入某张表：
--导出数据库下的指定表
--导出本机（127.0.0.1）用户 ptframe_1 下的 gap_sys_uidetail 表到 c:/wmx/gap_sys_uidetail.dmp
exp ptframe_1/1@127.0.0.1:1521/orcl file=c:/wmx/gap_sys_uidetail.dmp tables=(gap_sys_uidetail)
--导入数据下的指定表
--导入 c:/wmx/gap_sys_uidetail.dmp 表脚本到 192.168.2.240 机器用户 ptframe_1 下。
--注意默认情况下 240 上 ptframe_1 用户事先不能有表 gap_sys_uidetail 存在，否则导入失败.
--tables 指定的表名必须和.dmp文件中的一致，不能随便写，逗号分割,Linux 中括号需要转义，如 tables=\(gap_sys_uidetail,bas_agency_info\)
imp ptframe_1/1@192.168.2.240:1521/orcl file=c:/wmx/gap_sys_uidetail.dmp tables=(gap_sys_uidetail,bas_agency_info)
3、 对于 Linux 系统，必须先使用 su - oracle 切换到 oralce 用户，然后再使用 exp 、imp 命令。
[root@localhost ~]# su - oracle
上一次登录：四 10月 21 17:07:26 CST 2021pts/1 上
[oracle@localhost ~]$ exp hn_yszx0312/1@10.104.65.180:1521/orcl file=/home/oracle/hn_yszx0312.dmp log=/home/oracle/hn_yszx0312.log
.......
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@localhost ~]$ ll
-rw-r--r--. 1 oracle oinstall 2935046144 10月 21 17:13 hn_yszx0312.dmp
-rw-r--r--. 1 oracle oinstall 59088 10月 21 17:13 hn_yszx0312.log
[oracle@localhost ~]$ 
IMP-00010: 不是有效的导出文件, 标头验证失败解决办法
常见问题
| 问题 | 描述 |
|---|---|
| 数据库对象已经 存在 | 一般情况, 导入数据前应该彻底删除目标数据下的表、序列、函数、过程、触发器等。 数据库对象已经存在, 按缺省的 imp 参数 则会导入失败。 |
| 数据库对象有主外键约束 | 不符合主外键约束时数据会导入失败 。 解决办法: 先导入主表 再导入依存表 。 disable 目标导入对象的主外键约束，导入数据后 再 enable 它们。 |
| 权限不够 | 如果要把 A 用 户的数据导入 B 用户下 A 用户需要有 imp_full_database 权限 。 |
| 导入大表 ( 大于 80M ) 时 存储分配失败 | 默认的 EXP 时 compress = Y 也就是把所有的数据压缩在一个数据块上。 导入时如果不存在连续一个大数据块 则会导入失败。 导出80M 以上的大表时 记得 compress= N 则不会引起这种错误 。 |
| Imp 和 Exp 使用的字符集不同 | 如果字符集不同，导入会失败，可以改变 unix 环境变量或者 NT 注册表里 NLS_LANG 相关信息。 |
| Imp 和 Exp 版本不能往上兼容 | Imp 可以成功 导入低版本 E xp 生成的文件 , 不能导入高版本 E xp 生成的文件根据情况我 |
oracle 11g exp 空表导出处理
1、oralce 11g 中增加了一个新特性 "deferred_segment_creation-段延迟创建分片"，默认是 true 延迟分片。
2、deferred_segment_creation=true 时，如果 create 了一个 table，并且没有 insert 数据，那么这个表不会立即分配 extend，也就不占数据空间，即表不分配 segment 以节省空间，所以这些表不能 exp 导出来。
3、系统表 user_tables 中的 segment_treated 字段如果为"NO"，表示此表未分配 segment，exp 无法导出，否则为 true 表示此表已经分配了 segment exp 可以导出.
select * from user_tables where segment_created='NO' ;-- 查询当前登陆用户下所有未分片、exp 无法导出的空表
4、解决办法一：对空表主动分片（segment）：
select 'alter table ' ||table_name ||' allocate extent;' from user_tables where segment_created='NO';
运行后生成每张空表的分片 SQL 语句，将其生成的所有 sql 代码复制出来然后全部执行，就可以给每一张已经存在的表来分配 segment，然后就可以利用 exp 导出了。
5、解决办法二：设置系统参数，让后续 create 的新表自动分配 segment：
--查询系统参数 deferred_segment_creation，TRUE 表示延迟分片，FALSE 表示 create 时都分配 segment.
show parameter deferred_segment_creation;
alter system set deferred_segment_creation=false; --修改参数值为 FALSE，默认自动分片
show parameter deferred_segment_creation; -- 再次查询确认参数是否修改成功
expdp 与 impdp 数据泵导入导出
创建数据泵目录
1、数据泵目录用于存放 *.dmp 文件，expdp 导出成功之后，*.dmp 文件默认就会放到它下面，同理导入的时候，也需要先将 *.dmp 文件放入到它下面。
2、数据泵目录不需要重复创建，存在之后，直接将 *.dmp 文件放到对应的目录下，然后操作即可。
3、以下命令在 sqlplus 或者 PL/SQL Developer 等客户端工具执行即可。其中 directory 是关键字，与表空间有点类似，都有名称和路径。
-- 查询 expdp/impdp 数据泵目录信息(含名称、路径)
select * from dba_directories;
-- 创建数据泵目录(DATA_PUMP_DIR_ME 表示定义的目录名称，后面的表示目录路径，必须事先创建好，全部自定义即可，如/u01/app
-- /oracle)
create or replace directory DATA_PUMP_DIR_ME as 'E:\wmx\dpdump';
-- 将数据泵目录的读写权限授权给指定的用户(cs_element)，可以是多个用户，用逗号隔开,如 a,b,c
grant read,write on directory DATA_PUMP_DIR_ME to cs_element;4、目录路径必须是已经存在的目录，如果不存在，虽然显示创建成功，但是导入导出时会报错。
expdp 导出库
1、如果是 linux 系统，则必须先切换到 oracle 用户后再执行命令，如下所示是 CentOS 7.2 上的 Oracle 19c 导出。
[root@localhost ~]# su - oracle
上一次登录：四 10月 21 17:07:26 CST 2021pts/1 上
[oracle@localhost ~]$ expdp cs_element/1@10.104.65.181:1521/orcl directory=DATA_PUMP_DIR dumpfile=cs_element.dmp logfile=cs_element.log schemas=cs_element EXCLUDE=TABLE:\" in \(\'BAS_CALC_INFO_INSERT_SP\',\'BAS_CALC_COLS\'\)\" version=11.2.0.1.0cs_element/1：导出的用户名和密码
directory：指定的数据泵名称
dumpfile：指定导出的数据库文件名称(文件会放到数据泵名称对应的路径下)
logfile：日志文件名称(文件会放到数据泵名称对应的路径下)
schemas：指定导入导出的用户名称，可以是多个，用逗号隔开
exclude：排除某些表不导出(需要进行转义，不需要排除时，可以省略)
version：指定导出文件的版本(比如导出库是19c，导入库是11g，不需要指定时，可以省略)
compression=all：表示对数据进行压缩
select * from product_component_version ; -- 查看数据库版本
impdp 导入库
1、如下所示是本机 win10 系统 Oracle 11g 导入，语法与 expdp 是对应的。
-- 新建表空间，存在时忽略，datafile 路径自定义即可
create tablespace grp
datafile 'E:\wmx\oracle_tablesapce\grp.dbf'
size 500M
autoextend on
next 50M;
-- 创建用户并授权
create user cs_element identified by 1 default tablespace grp;
grant dba to cs_element;
-- 导入数据库
-- 先将准备好的 *.dmp 文件放到'数据泵目录'下，比如上面的 'E:\wmx\dpdump'
-- 然后 cmd 命令行中执行如下命令
-- 如果提示 'ORA-28040: 没有匹配的验证协议' ，则不用理会，继续输入 用户名和口令 回车即可正常导入
-- table_exists_action=replace 表示表存在时则替换，否则默认插不进去
E:\wmx\dpdump> impdp cs_element/1@localhost:1521/orcl directory=DATA_PUMP_DIR_ME dumpfile=cs_element.dmp logfile=cs_element.log schemas=cs_element table_exists_action=replace
高版本导出到低版本存在的问题
1、高版本导出时，虽然可以通过 version 指定低版本的版本号，导出时会自动转换，但是 Oracle 12c 以前的表名、列名是不允许超过 30 个字符的，所以如果高版本上面的某个表名、列名，视图名以及对应的列，或者索引名称等等，只要超过 30 个字符串，则导出会失败。
2、如下所示为从 19C 导出时指定 11g 版本号的失败情况。
ORA-39373: cannot export TABLE:"CS_ELEMENT"."GFM_TB_CHANGE_ELEMENT_DATA_CACHE" to version 11.2.0.1.0 due to long identifiers
ORA-39373: cannot export VIEW:"CS_ELEMENT"."REPORT_V_BUDGET_MESSAGE_431081000" to version 11.2.0.1.0 due to long identifiers
ORA-39373: cannot export INDEX:"CS_ELEMENT"."IDX_BAS_CALC_INFO_INSERT_SP_MULTI_1" to version 11.2.0.1.0 due to long identifiers
ORA-39373: cannot export CONSTRAINT:"CS_ELEMENT"."UK_AGENCY_CODE_MOF_DIV_CODE_IS_DELETED" to version 11.2.0.1.0 due to long identifiersexclude 与 include 参数详解
1、exclude(排除)、include(包含) 参数能够对特定的对象或对象类型进行过滤，比如导出/不导出特定的表、视图以及存储过程、索引、约束、授权统计信息等等。
| EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause] | 排出特定对象 |
| INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause] | 包含特定对象 |
| 1、object_type：用于指定对象的类型，如 table,sequence,view,procedure,function,package 等等。 2、name_clause：过滤子句，由SQL操作符以及对象名(可使用通配符)来过滤指定对象类型中的特定对象。 3、当未指定 name_clause 时，则对该object_type类型的对象全部进行过滤。 4、多个 [object_type]:[name_clause] 中间以逗号分割。 | |
2、 常用的过滤 SQL 表达式演示（注意Oracle的对象名称都是大写的）。
-- 所有序列不导出，emp、dept 表不导出，其它全部导出
expdp ...... SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:"IN ('EMP','DEPT')"
-- 导出所有函数、存储过程，以及 emp 表，其它全部不导出
impdp ...... SCHEMAS=scott INCLUDE=FUNCTION,PROCEDURE,TABLE:"='EMP'"| EXCLUDE=SEQUENCE,VIEW | 过滤所有的 序列，视图 |
| EXCLUDE=TABLE:"IN ('EMP','DEPT')" | 过滤 EMP,DEPT 表 |
| EXCLUDE=SEQUENCE,VIEW,TABLE:"IN ('EMP','DEPT')" | 过滤所有序列、视图、以及 EMP,DEPT 表 |
| EXCLUDE=INDEX:"= 'INDEX_NAME'" | 过滤索引对象 INDEX_NAME |
| INCLUDE=PROCEDURE:"LIKE 'PROC_B%'" | 包含以 PROC_B 开头的所有存储过程 |
| INCLUDE=TABLE:"!= 'EMP' " | 包含不等于 emp 表的其它全部内容 |
| 其它常用操作符 NOT IN, NOT LIKE, <, != 等等都可以使用 | |
3、命令行下如果执行报错，则需要对 单引号、双引号、括号进行转义，如：
Windows 下：INCLUDE=TABLE:\"IN ('EMP', 'DEPT')\"
Linux 下：EXCLUDE=TABLE:\" in \(\'BAS_CALC_INFO_INSERT_SP\',\'BAS_CALC_COLS\'\)\"expdp、impdp 与 exp、imp 的区别
| exp、imp | expdp、impdp |
|---|---|
| Oracle 10g 之前和之后都有 | Oracle 10g 及以后版本才有 |
| 属于客户端工具程序，既可以在可以客户端使用，也可以在服务端使用 | 属于服务端工具程序，只能在 Oracle 服务端使用，客户端只是用来连接服务器启动导入导出操作，导出文件只存储在服务端。 |
| IMP 只适用于 EXP 导出的文件 | IMPDP 只适用于 EXPDP 导出的文件 |
| EXPDP、IMPDP 在备份和恢复时间上要比 EXP、IMP 有优势，而且文件也要小的多 | |
| 使用 EXPDP、IMPDP 管理更加灵活，支持并行、可中断、可修改元数据文件名字与表空间名、支持元数据导入导出、估算导出数据所需空间、指定导入数据库版本等。 | |
更多细节与注意事项可以参考：Oracle 数据库 expdp 用法及注意事项 .pdf
扫一扫
913
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
