|
|
Oracle理论基础要素
--$理论知识$--
--$$、三大范式
第一范式（1NF）：字段是原子性的，不可分;
第二范式（2NF）：有主键，非主键字段依赖主键。确保一个表只说明一个事物
第三范式（3NF）：非主键字段不能相互依赖。 每列都与主键有直接关系，不存在传递的依赖
--$$--理论知识汇总
1.数据模型组成三要素：数据结构，数据操作和完整性约束条件
2.数据库系统的三级模式结构：模式,外模式和内模式
3.数据库是长期存储在计算机内，有组织的，可共享的大量数据的集合
4.数据库的基本特征：数据按一定的数据模型组织、描述和储存，可为各种用户共享，冗余度较小，数据独立性较高，易扩展
5.数据库系统的构成：数据库，数据库管理系统（及其开发工具），应用系统，数据库管理员
6.SQL语句的基本特点：综合统一，高度非过程化，面向集合的操作方式，以同一种语法结构提供多种使用方式，语言简洁。
7.SQL的功能：数据定义、数据操纵、数据控制
8.自主授权控制实现机制：SQL语言，GRANT和REVOKE语句实现
9.数据库安全性控制的方法：用户标识与鉴别，存取控制，授权与回收
10.数据库保护涉及的问题：安全性，完整性，数据库恢复和并发控制(死锁问题)
11.加密的方法：替换、置换、两种结合
12.数据库完整性的方法：实体完整性、参照完整性和自定义完整性
13.数据的完整性和安全性是两个不同概念：
1.数据的完整性：防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据，防范对象：不合语义的、不正确的数据
2.数据的安全性：保护数据库防止恶意的破坏和非法的存取，防范对象：非法用户和非法操作
14.数据库设计的步骤：需求分析（用户需求），概念结构设计（E-R图），逻辑结构设计（视图），物理结构设计（索引）数据库实施，数据库运行和维护
15.数据字典的内容：数据项，数据结构，数据流，数据存储，处理过程
16.E-R图合并的冲突：属性冲突，命名冲突，结构冲突
17.优化数据模型的方法：确定数据依赖，消除冗余的联系，确定所属范式
18.引入嵌入式编程的原因：SQL是非过程的语言，事务处理应用需要高级语言
19.高级语言和SQL语言的作用：前者是负责控制数据流程，后者负责操纵数据库
20.SQL通信区的作用：向主语言传递SQL语句的执行状态信息，使主语言能够据此控制程序流程
21.主变量的作用：向SQL语句提供参数，将SQL语句查询数据库的结果交主语言进一步处理
22.游标：解决集合性操作语言与过程性操作语言的不匹配。(数据缓冲区，存放SQL语句的执行结果)
23.为什么使用游标：SQL语言与主语言具有不同数据处理方式。SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录。主语言是面向记录的，一组主变量一次只能存放一条记录。仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求。嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式
24.常见的存储介质及其特征：高速缓存存储器：容量小，速度快，最昂贵。主存储器：断电时，贮存内容丢失。快闪存储器：非易失。磁盘存储器：长期联机数据存储。光学存储器：光学存储到光盘上。磁带存储器：顺序访问数据
25.磁盘的请求模式：顺序访问模式：连续的请求会请求处于相同磁道或相邻磁道上连续的块。只需要一次寻道时间。随机访问模式：连续的请求会请求随机位于磁盘上的块，效率较低。
26.优化方法：缓冲：从磁盘读取的数据暂时存在内存缓冲区里。预读：当一个磁盘块被访问时，相邻磁道的连续快也被读入内存缓冲区中。调度：按块经过读写头的顺序发出访问请求。电梯算法。
27.定长记录：多个文件，在任意一个文件只存储一个固定长度的记录。变长记录：构造自己的文件，使之容纳多种长度的记录。
28.文件组织记录的几种方法：堆文件：任意存放。顺序文件组织方式：记录根据其 搜索码 的值顺序存储。散列文件组织方式：在每条记录的某些属性计算一个散列函数，根据其值计算。搜索码是一个属性或多个属性的集合，不一定是主码。在物理上，尽可能接近搜索码的顺序存储记录。插入新纪录时，若位置空闲，则直接插入，否则插入溢出块。记录过多，则需要文件重组，保证搜索码和物理存储顺序的一致性。
29.缓冲区机制：当数据库系统中的程序需要磁盘上的块时，它向缓冲区管理器发出请求（即调用）。如果这个块在缓冲区中，缓冲区管理器将这个块在主存中的地址传给请求者。如果这个块不在缓冲区中，缓冲区首先为这个块分配空间（必要时，移出一些块，这个块必须已经被写回磁盘），然后把请求的块从磁盘读入缓冲区中，并将这个块在主存中的地址传给请求者。
30.应用的技术：缓冲区替换策略:移除最近最少使用的写回磁盘并从缓冲区移走。被钉住的块：使数据库系统从崩溃中回复，限制一个块写回磁盘的时间。块的强制写出：把块强制写回磁盘。防止数据丢失
31.查询处理优化：查询-通过语法分析器与翻译器转化为关系代数表达式-通过优化器优化执行计划-执行引擎得到查询结果。
32.事物的特性：原子性、一致性、持续性、隔离性
33.事务是一个数据库操作序列；一个不可分割的工作单位；恢复和并发控制的基本单位
34.数据库常见故障种类及其恢复策略：故障种类：事物内部的故障：撤销事务(UNDO)。系统故障(软故障):若事务未提交，强行撤销所有未完成事务。事务提交：但缓冲区中的信息尚未完全写回到磁盘上：重做(REDO)所有已提交的事务。介质故障(硬故障)：装入数据库发生介质故障前某个时刻的数据副本；重做自此开始时的所有成功事务，将这些事务已提交的结果重新计入数据库。计算机病毒：杀毒！
-------------1.用户管理/权限------------------
-------1.1 用户管理
-- 创建
create user checker --用户
identified by 1qaz@wsx --密码
default tablespace users --默认表空间
temporary tablespace temp --临时表空间
quota 10M on users --赋予默认表空间赋予checker用户10M空间（超过则无法使用，除非再次申请）
-- 修改
alter user checker quota 20M on users;--若在建表时没有指定，可以通过该方法执行
alter user checker identified by 123456;--修改密码
alter user checker account unlock;--若checker用户被锁，可登入system、scott、sys等具有dba权限的用户下解锁
-- 删除
drop user checker cascade;--删除用户、该用户下所有表都会被删掉
-------1.2 用户权限
---- 授权
-- 系统权限
grant connect,resource to checker;--访问资源权限
grant create session,create table to user2 with admin option; --再次授权
-- 对象授权
grant select,insert,delete on scott.emp to user2; --某用户下的某张表赋予增删改权限
--回收权限
revoke resource to user2;--收回user2的权限
revoke delete on scott.emp from user2;--收回user2的删除权限
--查询
DBA_USERS --用户基本信息表
DBA_SYS_PRIVS --系统权限
DBA_TAB_PRIVS --对象权限
USER_SYS_PRIVS --用户系统权限
ROLE_SYS_PRIVS --用户角色
ALL_TABLES --可查询的所有表
USER_TAB_PRIVS --用户将权限授予哪些用户
ALL_TAB_PRIVS --哪些用户给自己授权
-------1.3 用户角色
----预定义
--connect/resource/dba/exp_full_database/imp_full_database
--创建
create role designer identified by 123456;
grant create view,create table to designer;
--授予
grant designer to user2;
--管理
select * from role_sys_privs where role='DESIGNER';
alter role designer not identified;
alter role designer identified by 9ol.0p;/; --修改role的密码
-- set role designer;--置为生效
--删除
drop role designer;
-------1.4 资源配置PROFILE
--管理密码
create profile lock_account limit
failed_login_attempts 5
password_lock_time 7;
--password_life_time/password_grace_time
--password_reuse_time/password_reuse_max
--password_verify_function
alter user user profile lock_account;
--管理资源
alter system set resource_limit=true;
alter profile password_lift_time limit
cpu_per_session 20000
session_per_user 10
cpu_per_call 500
passeord_life_time 180
failed_login_attempts 10;
--删除资源管理
drop profile password_life_time cascade;
--查询
select profile from dba_users where username='SCOTT';
select resource_name,resource_type,limit from dba_profiles where profile='DEFAULT';
-------------2.数据对象-----------------
----2.1 数据表
---------2.1.1 创建普通表
create table students(
stuno number(10) not null, --非空约束
stuname varchar2(8),
id varchar2(18) constraint ID_UK unique, --唯一性约束
sex char(2),
age int constraint AGE_CK check(age > 0 and age < 120) disable, --禁用
departno varchar2(2) not null,
classno varchar2(4) not null,
regdate date default sysdate,
---blob/clob/bfile
constraint STU_PK primary key(stuno) ---主键约束
)tablespace users --表空间
storage(initial 256k) --存储参数next/minextents(AUTOALLOCATE)
pctfree 20 --数据块最小空闲空间比例，达到后标记不可用
pctused 40 --数据库是否可用界限
initrans 10 --允许并发事务数目
nologging; --DDL操作不产生日志
--------2.1.2创建临时表
----------2.1.2.1 会话级别临时表
--会话级临时表是指临时表中的数据只在会话生命周期之中存在，
--当用户退出会话结束的时候，Oracle自动清除临时表中数据。
create global temporary table
aaa
(
id number
) on commit oreserve rows;
insert into aaa values(100);
select * from aaa;
---------2.2.2.2事务级别的临时表
create global temporary table
bbb
(id number
) on commit delete rows;
insert into bbb values(200);
select * from bbb;
--这时当你执行了commit和rollback操作的话，再次查询表内的数据就查不到了。
----维护
--字段
alter table students add(province varchar2(10));--添加字段
alter table students drop column province;--删除列
alter table students drop (sex,age); --删除多列
alter table students modify departno varchar2(4);--修改字段长度
--重命名
alter table students rename to students_bak;--重命名表名
alter table students rename column A to A_id;--重命名列名
--表空间
alter table students move tablespace tbsp_2;
--存储参数
alter table students pctfree 25 pctused 45;
--删除
drop table students cascade constraints; --同时删除视图，约束或触发器等
flashback table students to before drop; --闪回
--状态
alter table students read only; --设置表只读
--约束
alter table students modify stuno not null; --修改约束
alter table students add constraint STUD_PK(stuno);--添加主键
alter table students drop constraint STUD_PK;--删除主键
alter table students add constraint IDs_UK unique(id);--添加唯一性约束
alter table students drop constraint IDs_UK;--删除唯一性约束
alter table students add constraint DEPART_FK foreign key(department_id)
reference departments(department_id); --外键约束
alter table students drop constraint depart_FK;--删除外键约束
alter table students enable validate constraint depart_FK; --novalidate
alter table students disable constraint depart_FK;
----2.2 索引(Index)
---创建
create index emp_deptno_index
on emp(deptno)
pctfree 25 --reverse
tablespace users;
--合并
alter index emp_deptno_index coalesce deallocate unused;
--重建
alter index emp_deptno_index rebuild;
--删除
drop index emp_deptno_index;
--查询
select table_name.index_name,index_type from dba_indexes where owner='HR'; --表索引sys
select column_name,column_length from user_ind_columns where index_name='EMP_DEPTNO_INDEX';--索引列scott
select tablespace_name,segment_type,bytes from user_segments where segment_name='EMP_DEPTNO_INDEX';--索引段scott
select column_expression from user_ind_expressions where index_name = 'EMP_JOB_FUN'; --函数索引
----2.3 视图(View)
--创建
create or replace view emp_view as
select d.dname,d.loc,e.empno,e.ename
from emp e, dept d
where e.deptno = d.deptno and d.deptno = 20
with read only;
---查看
select * from emp_view;
desc emp_view;
---重新编译
alter view emp_view compile;
---删除
drop view emp_view;
----2.4 同义词(Synonym)
---创建
create public synonym public_dept for scott.dept;
create synonym private_dept for dept;
---删除
drop public synonym public_dept;
drop synonym private_dept;
----2.5 序列(Sequence)
---创建
alter sequence empno_seq
start with 100 --从多少开始
maxvalue 100000 --minvalue/nominvalue/nomaxvalue --最大值（最小值/无最小值/无最大值）
increment by 200 --（增量）步长
cache 100 --缓存
cycle --nocycle 循环
order --noorder 顺序
---管理
alter sequence empno_seq
maxvalue 500000
increment by 200;
---删除
drop sequence empno_seq;
----2.6 物化视图
---基本理论知识
1、物化视图的类型：ON DEMAND、ON COMMIT
二者的区别在于刷新方法的不同，ON DEMAND 顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性 ；
而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。
2、ON DEMAND物化视图
物化视图的创建本身是很复杂和需要优化参数设置的，特别是针对大型生产 数据库 系统而言。但Oracle允许以这种最简单的，类似于普通视图的方式来做，所以不可避免的会涉及到默认值问题。也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。
物化视图的特点：
(1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user_tables查询出来，而得到佐证；
(2) 物化视图也是一种段(segment)，所以其有自己的物理存储属性；
(3) 物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证；
3、物化视图的数据怎么随着基表而更新？
Oracle提供了两种方式，手工刷新和自动刷新，默认为手工刷新 。也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。这是最基本的刷新办法了。自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包，加以实现。
4、ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别
"ON DEMAND"不刷新(手工或自动)就不更新物化视图，而"ON COMMIT"不刷新也会更新物化视图，——只要基表发生了COMMIT。
创建定时刷新的物化视图：
---创建语法
create materialized view mv_name refresh force on demand start with sysdate
next sysdate+1 （指定物化视图每天刷新一次）
create materialized view mv_name refresh force on commit as select * from table_name
--更新
alter materialized view mv_name
refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')
---删除
drop materialized view mv_name
-----------------3.表/索引分区--------------------------
----3.1 表分区
---创建
--范围
create table ware_retail_part
(
id integer primary key,
retail_date date,
ware_name varchar2(50)
)
partition by range(retail_date)
(
partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TBSP_1,
partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TBSP_1,
partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TBSP_2,
partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TBSP_2
);
--散列
create table goods
(
id number,
goodname varchar2(50)
)
storage(initial 2048k)
partition by hash(id)
(
partition par1 tablespace tbsp_1,
partition par2 tablespace tbsp_2
);
--列表
create table clients
(
id integer primary key,
name varchar2(50),
province varchar2(20)
)
partition by list(province)
(
partition shandong values('山东省'),
partition guangdong values('广东省'),
partition yunnan values('云南省')
);
--组合分区
create table person2
(
id number primary key,
name varchar2(20),
sex varchar2(2)
)
partition by range(id) --范围分区
subpartition by hash(name) --hash子分区
subpartitions 2 store in(tbsp_1,tbsp_2) --存储在两个不同的命名空间中
(
partition par1 values less than(5000),
partition par2 values less than(10000),
partition par3 values less than(maxvalue)
);
--间隔分区
create table saleRecord
(
id number primary key,
goodsname varchar2(50),
saledate date,
quantity number
)
partition by range(saledate)
interval (numtoyminterval(1,'year'))
(
partition par_fist values less than (to_date('2012-01-01','yyyy-mm-dd'))
);
--应用
insert into ware_retail_part values(1,to_date('2011-01-20','yyyy-mm-dd'),'PC');
insert into ware_retail_part values(2,to_date('2011-04-15','yyyy-mm-dd'),'TV');
select * from ware_retail_part partition(par_02);
---管理
--添加
alter table clients
add partition hebei values('河北省')
storage(initial 10K next 20k) tablespace tbsp_1
nologging;
--合并
alter table person coalesce partition;
alter table person2 modify partition par3 coalesce subpartition;
--删除
--disable constraint/drop/enable constraint
delete from ware_retail_part where retail_date>=to_date('2011-10-01','yyyy-mm-dd'); --数据
alter table ware_retail_part drop partition par_04; --表分区
alter index ware_index rebuild; --重建索引
--并入
alter table sales merge partitions part_sea3,part_sea4 into partition part_sea4;
alter table sales modify partition part_sea4 rebuild unusable local indexes; --重建局部索引
----3.2 索引分区
---创建
--本地
--create tablespace ts_1/ts_2/ts_3;
--create table studentgrade partition by range(grade);
create index grade_index on studentgrade(grade)
local
(
partition p1 tablespace ts_1,
partition p2 tablespace ts_2,
partition p3 tablespace ts_3
); --dba_ind_partitions
--全局
create index index_SalePrice on Books(SalePrice)
global partition by range(SalePrice)
(
partition p1 values less than (30),
partition p2 values less than (50),
partition p3 values less than (maxvalue)
);
---管理
--删除
alter index index_saleprice drop partition p2;
alter index index_saleprice drop partition p1;
alter index index_saleprice rebulid partition p3;
--重命名
alter index index_saleprice rename partition p3 to p_new;
---------------4.数据库管理--------------------
------4.1 数据文件/表空间
---查看
select tablespace_name,file_name,bytes from dba_data_files order by tablespace_name;
---默认
select segment_type,segment_name,owner from dba_segments where tablespace_name='USERS';
--SYSTEM/SYSAUT/UNDOTBS1/USERS/EXAMPLE/TEMP
---创建
--本地化管理方式
create tablespace tbs_test_1 datafile 'D:\OracleFiles\OracleData\datafile1.dbf' --创建表空间文件
size 10m
extent management local uniform size 256K; --autoallocate
--段空间管理方式
create tablespace tbs_test_3 datafile 'D:\OracleFiles\OracleData\datafile3.dbf'
size 20m
extent management local autoallocate
segment space management manual; --auto
--非标准块
alter system set db_16k_cache_size = 16M scope=both;
create tablespace tbs_test_5 datafile 'D:\OracleFiles\OracleData\datafile5.dbf'
size 64m reuse
autoextend on next 4m maxsize unlimited
blocksize 16k
extent management local autoallocate
segment space management auto;
--大文件
create bigfile tablespace tbs_test_big datafile 'D:\OracleFiles\OracleData\datafilebig.dbf'
size 2g;
---维护
--默认
alter database default temporary tablespace temp_1;
alter database default tablespace tbs_example;
--状态
alter tablespace tbs_test_3 read only; --read write
--重命名
alter tablespace tbs_test_3 rename to tbs_test_3_new;
--删除
drop tablespace tbs_test_1 including contents cascade constraint;
--文件
alter tablespace users add datafile 'e:\app\Administrator\oradata\orcl\users02.dbf' size 10M autoextend on next 5M maxsize unlimited; --添加
alter tablespace users drop datafile 'e:\app\Administrator\oradata\orcl\users02.dbf'; --删除
alter database datafile 'D:\OracleFiles\OracleData\datafile2.dbf'
autoextend on next 10m maxsize unlimited; --自动扩展
---撤销表空间
--创建
create undo tablespace undo_tbs_1
datafile 'D:\OracleFiles\OracleData\undotbs1.dbf'
size100M;
--修改
alter tablespace undo_tbs_1
add datafile 'D:\OracleFiles\OracleData\undotbs_add.dbf'
size 2g;
--切换
alter system set undo_tablespace=undo_tbs_1;
--删除
alter system set undo_tablespace=undotbs1;
drop tablespace undo_tbs_1;
--查询
show parameter undo_tablespace; --undo_management/undo_retention
select tablespace_name from dba_tablespaces where contents = 'UNDO';
select to_char(begin_time,'hh24:mi:ss'), to_char(end_time,'hh24:mi:ss'), undoblks
from v$undostat order by begin_time; --表空间统计信息
select rn.name,rs.xacts,rs.writes,rs.extents
from v$rollname rn,v$rollstat rs where rn.usn = rs.usn; --段统计信息
select name,status from v$transaction; --活动事务
select segment_name, extent_id,bytes,status from dba_undo_extents
where segment_name='_SYSSMU3_991555123$'; --UNDO区信息
---临时表空间
--创建
create temporary tablespace temp_01 tempfile 'D:\OracleFiles\tempfiles\temp_01.tpf' size 300m;
alter database default temporary tablespace temp_01;
--重命名/删除同上
--查询
select file_name,bytes,tablespace_name from dba_temp_files;
---临时表空间组
create temporary tablespace tp1 tempfile 'D:\OracleFiles\tempfiles\tp1.tpf'
size 10m tablespace group group1;
create temporary tablespace tp2 tempfile 'D:\OracleFiles\tempfiles\tp2.tpf'
size 20m tablespace group group1; --创建
create temporary tablespace tp3 tempfile 'D:\OracleFiles\tempfiles\tp3.tpf'
size 10m tablespace group group3;
alter tablespace tp1 tablespace group group3; --转移
alter user hr temporary tablespace group3; --分配
alter database orcl default temporary tablespace group3; --默认
drop tablespace tp1 including contents and datafiles;
------4.2 控制文件
---多路复用
alter system set control_file=
'D:\PROGRAM\ORACLE\ORADATA\ORCL\CONTROL01.CTL',
'D:\PROGRAM\ORACLE\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL',
'D:\OracleFiles\ControlFiles\CONTROL03.CTL'
scope=spfile; --参数设置后复制文件
select name from v$controlfile; --查看
---创建
select member from v$logfile; --查看日志文件
select name from v$datafile; --查看数据文件
select name from v$controlfile; --查看控制文件
shutdown immediate; --关闭数据库，然后备份文件
startup nomount; --启动数据库实例，不加载数据库
create controlfile reused --创建新控制文件
database "orcl"
logfile
group 1 'D:\PROGRAM\ORACLE\ORADATA\ORCL\REDO01.LOG',
group 2 'D:\PROGRAM\ORACLE\ORADATA\ORCL\REDO02.LOG',
group 3 'D:\PROGRAM\ORACLE\ORADATA\ORCL\REDO03.LOG'
datafile
'D:\PROGRAM\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
'D:\PROGRAM\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',
'D:\PROGRAM\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
'D:\PROGRAM\ORACLE\ORADATA\ORCL\USERS01.DBF',
'D:\PROGRAM\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
'D:\PROGRAM\ORACLE\PRODUCT\11.2.0\DBHOME_1\ORADATA\KPLAYER\KPLAYER.DBF'
maxlogfiles 50
maxlogmembers 3
maxinstances 6
maxdatafiles 200
noresetlogs
noarchivelog;
alter system set control_files= --编辑参数
'E:\PROG\ADMIN\ORADATA\ORCL\CONTROL01.CTL',
'E:\PROG\ADMIN\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL'
scope=spfile;
alter database open; --打开数据库 [resetlogs]
---备份
alter database backup controlfile
to 'D:\OracleFiles\ControlFiles\ctf.bak'; --二进制文件
alter database backup controlfile to trace; --脚本文件
show parameter user_dump_dest;
---恢复
--关闭->复制覆盖->重启
--关闭->编辑CONTROL_FILES->重启
---删除
--关闭->编辑CONTROL_FILES->重启
---查询
--v$controlfile 所有控制文件名称和状态
--v$controlfile_record_section 控制文件各记录文档段信息
--v$parameter 系统所有初始化参数
------4.3 重做日志文件
---增加
alter database add logfile group 5
('D:\OracleFiles\LogFiles\REDO4_A.LOG',
'E:\OracleFiles\LogFiles\REDO4_B.LOG')
size 20M; --添加新的重做日志文件组
alter database add logfile member
'E:\OracleFiles\LogFiles\REDO4_C.LOG' to group 4; --创建日志成员文件
alter database add logfile member
'D:\OracleFiles\LogFiles\REDO1_new.LOG' to
('E:\app\Administrator\oradata\orcl\REDO01.LOG') ; --指定成员名称
---删除
--日志成员
alter database drop logfile member 'E:\OracleFiles\LogFiles\REDO4_C.LOG';
--日志文件组
alter database drop logfile group 5;
--清空
alter database clear logfile group 4;
---更改
--关闭->复制源文件到目标位置->启动加载但不打开mount
alter database rename file
'D:\OracleFiles\LogFiles\REDO1_new.LOG',
'D:\OracleFiles\LogFiles\REDO4_A.LOG'
to
'E:\OracleFiles\LogFiles\REDO1_new.LOG',
'E:\OracleFiles\LogFiles\REDO4_A.LOG';
--打开数据库
--查看
--v$log v$logfile v$log_history
------4.4 归档日志文件
---切换
select log_mode from v$database; --noarchivelog/archivelog
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
---进程
alter system set log_archive_max_processes = 3;
---位置
--本地
alter system set log_archive_dest_1='location=D:\OracleFiles\archive1 optional';
alter system set log_archive_dest_2='location=D:\OracleFiles\archive2 mandatory';
alter system set log_archive_dest_3='location=D:\OracleFiles\archive3 mandatory reopen=400';
alter system set log_archive_min_succeed_dest=3; --最小归档数
alter system set log_archive_dest_state_4=defer; --禁用位置
--远程
alter system set log_archive_dest_1='service=MRKJ';
---查看
--v$database v$archived_log v$archive_dest v$archive_processes v$backup_redolog
archive log list;
----------5. 数据库维护-------------
------5.1 数据库控制
---事务
set transaction read only; --read write
exec dbms_transaction.read_only;
set transaction use rollback segment system; --分配回滚段
savepoint sp01; --设置保存点
insert into jobs_temp values('DESIGN','DS',3000,5000);
rollback to sp01; --回滚
---锁
lock table dept_temp in row share mode;
--row exclusive mode/share mode/share row exclusive mode/exclusive mode
------5.2 数据导入/导出
---导出
create directory dump_dir as 'd:\dump';
grant read,write on directory dump_dir to scott;
--表
expdp scott/1qaz2wsx directory = dump_dir dumpfile=tab.dmp tables=emp,dept
--模式
expdp system/1qaz2wsx directory = dump_dir dumpfile=schema.dmp schemas=scott,hr
--表空间
expdp system/1qaz2wsx directory = dump_dir dumpfile = tablespace.dmp tablespaces=tbsp_1
--数据库
expdp system/1qaz2wsx directory=dump_dir dumpfile=fulldatabase.dmp full=y
--content/query/logfile/status
---导入
impdp system/1qaz2wsx directory=dump_dir dumpfile=tab.dmp tables=scott.dept,scott.emp remap_schema=scott:system --表
impdp system/1qaz2wsx directory=dump_dir dumpfile=schema.dmp schemas=scott remap_schema=scott:system; --模式
impdp system/1qaz2wsx directory=dump_dir dumpfile=tablespace.dmp tablespaces=tbsp_1 --表空间
impdp system/1qaz2wsx directory=dump_dir dumpfile=fulldatabase.dmp full=y --数据库
--remap_schema/remap_tablesapce/sqlfile/table_exists_action/transport_datafiles
------SQL Loader
sqlldr --用法
---自由格式
/* --student.ctl
load data
infile 'd:\data\student.txt'
into table student
(stuno position(01:04) integer external,
stuname position(11:14) char,
sex position(21:22) char,
old position(29:30) integer external
)
*/
sqlldr system/1qaz2wsx control=d:\data\student.ctl log=d:\data\stu_log
---固定格式
/* --persons.ctl
load data
infile 'd:\data\persons.csv'
append into table persons
fields terminated by ','
(code,name,sex,old)
*/
sqlldr system/1qaz2wsx control=d:\data\persons.ctl
------6.0 数据库对象
---6.1 游标
一、游标概述
游标（cursor）是数据库系统在内存中开设的一个数据缓冲区，存放SQL语句的执行结果。
每个游标都有一个名字，用户可以用SQL语句逐一从游标中获取记录，并赋给变量做进一步处理。
作用：用于定位结果集的行 和 遍历结果集。
游标产生时间：当执行DML SQL语句时；
游标用于存放：结果集
游标有名字吗：有，SQL或用户给他取名
游标如何操作：用FETCH语句逐一从游标中提取记录，并赋给变量进一步处理
同时可打开几个游标：多个，具体数量由数据库初始化参数OPEN_CURSOR决定。
二、游标分类
游标分为：静态游标（隐式和显式）和REF游标（+游标变量）
1. 游标的属性
--显式游标
游标的属性 返回值类型 意 义
%ROWCOUNT 整型 获得FETCH语句返回的数据行数
%FOUND 布尔型 最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND 布尔型 与%FOUND属性返回值相反
%ISOPEN 布尔型 游标已经打开时值为真，否则为假
2. 静态游标
静态游标是指结果集已经确实（静态定义）的游标。
静态游标分：隐式游标 和 显示游标。
2.1 隐式游标
在PL/SQL中隐式游标在执行DML SQL(Insert/Delete/Update/Select)语句时自动创建、自动声明、打开和关闭，其名SQL（注：所有的隐式游标名都叫“SQL”）；
隐式游标的属性 返回值类型 意 义
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假
示例：
使用隐式游标的属性。
BEGIN
UPDATE emp SET sal=5000 WHERE empno=7369;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('表已更新');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE('更新失败');
END IF;
END;
------------------------------------------
declare
cursor c_name(cust_name varchar) is
select * from YXD.ZJZB_N_20180131_03 where 客户名 = cust_name;
Cust_Nm YXD.ZJZB_N_20180131_03%ROWTYPE;
BEGIN
FOR i in c_name('王东生') LOOP
DBMS_OUTPUT.put_line(i.客户名||','||i.行业板块);
END LOOP;
END;
------------------------------------------
--1.不带参的游标
--查询每个人的薪水
declare
--定义光标（游标）
cursor cemp is select ename,sal from emp;
pename emp.ename%type;
psal emp.sal%type;
begin
--打开游标
open cemp;
loop
--取当前记录
fetch cemp into pename,psal;
--exit when 没有取到记录;
exit when cemp%notfound;
dbms_output.put_line(pename||'的薪水是'||psal);
end loop;
--关闭游标
close cemp;
end;
/
--2. 带参的游标
--查询某个部门的员工姓名
declare
--形参（定义）
cursor cemp(dno number) is select ename from emp where deptno=dno;
pename emp.ename%type;
begin
--实参
open cemp(20);--传入参数，打开游标部门号为20
dbms_output.put_line('该部门的员工有：');
loop
fetch cemp into pename;
exit when cemp%notfound;
dbms_output.put_line(pename);
end loop;
close cemp;
end;
/
----------------------------------------------------------------------
--6.2 事务（TRANSACTION）
---6.2.1 什么是事务?
在数据库中事务是工作的逻辑单元，一个事务是由一个或多个完成一组的相关行为的SQL语句组成，通过事务机制确保这一组SQL语句所作的操作要么都成功执行，完成整个工作单元操作，要么一个也不执行。
如：网上转帐就是典型的要用事务来处理，用以保证数据的一致性。
--6.2.2 事务特性
原子性(Atomicity)：一个事务里面所有包含的SQL语句是一个执行整体，不可分割，要么都做，要么都不做。
一致性(Consistency)：事务开始时，数据库中的数据是一致的，事务结束时，数据库的数据也应该是一致的。
隔离性(Isolation)：是指数据库允许多个并发事务同时对其中的数据进行读写和修改的能力，隔离性可以防止事务的并发执行时，由于他们的操作命令交叉执行而导致的数据不一致状态。
持久性 (Durability) : 是指当事务结束后，它对数据库中的影响是永久的，即便系统遇到故障的情况下，数据也不会丢失。
--6.2.3 数据异常
因为Oracle中支持多个事务并发执行，所以会出现下面的数据异常。
--6.2.3.1 脏读
当一个事务修改数据时，另一事务读取了该数据，但是第一个事务由于某种原因取消对数据修改，使数据返回了原状态，这是第二个事务读取的数据与数据库中数据不一致，这就叫脏读。
如：事务T1修改了一条数据，但是还未提交，事务T2恰好读取到了这条修改后了的数据，此时T1将事务回滚，这个时候T2读取到的数据就是脏数据。
--6.2.3.2 不可重复读
是指一个事务读取数据库中的数据后，另一个事务则更新了数据，当第一个事务再次读取其中的数据时，就会发现数据已经发生了改变，这就是不可重复读取。不可重复读取所导致的结果就是一个事务前后两次读取的数据不相同。
如：事务T1读取一行记录，紧接着事务T2修改了T1刚刚读取的记录，然后T1再次查询，发现与第一次读取的记录不同。
--6.2.3.3 幻读
如果一个事务基于某个条件读取数据后，另一个事务则更新了同一个表中的数据，这时第一个事务再次读取数据时，根据搜索的条件返回了不同的行，这就是幻读。
如：事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一行新记录，恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这条新纪录就是幻读。
事务中遇到的这些异常与事务的隔离性设置有关，事务的隔离性设置越多，异常就出现的越少，但并发效果就越低，事务的隔离性设置越少，异常出现的越多，并发效果越高。
--6.2.4 事务隔离级别
针对读取数据时可能产生的不一致现象，在SQL92标准中定义了4个事务的隔离级别：
隔离级别 脏读 不可重复读 幻读
Read uncommitted(读未提交) 是 是 是
Read committed（读已提交） 否 是 是
Repeatable read（可重复读） 否 否 是
Serializable（串行读） 否 否 否
Oracle默认的隔离级别是read committed。
Oracle支持上述四种隔离级别中的两种:read committed 和serializable。除此之外，Oralce中还定义Read only和Read write隔离级别。
Read only：事务中不能有任何修改数据库中数据的操作语句，是Serializable的一个子集。
Read write：它是默认设置，该选项表示在事务中可以有访问语句、修改语句，但不经常使用。
设置隔离级别
设置一个事务的隔离级别：
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;
SET TRANSACTION READ WRITE;
set transaction read write;
注意：这些语句是互斥的，不能同时设置两个或两个以上的选项。
设置单个会话的隔离级别：
ALTER SESSION SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
ALTER SESSION SET TRANSACTION ISOLATION SERIALIZABLE;
-------------------------------------------------------------------------------------
--------------------------------银行转账业务流程事务---------------------------------
-------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE BANK_TRANSFER_TRANSACTION(
I_MONEY IN NUMBER,--输入参数，转账金额
I_OUT_ACCOUNT IN INTEGER,--输入参数，转出账户
I_IN_ACCOUNT IN INTEGER, --输入参数，转入账号
V_BALANCE OUT account.balance%TYPE--输出参数，返回当前账户余额
) IS
--从一个账户向另一个账户转账
BEGIN
--从转出账户扣钱
UPDATE account set balance = balance - I_MONEY WHERE ID=I_OUT_ACCOUNT;
select balance into V_BALANCE from account where ID=I_OUT_ACCOUNT;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20001,'对不起，没有该账户，可能未开户:'||40000000000);
END IF;
IF V_BALANCE<0 THEN
RAISE_APPLICATION_ERROR(-20002,'账户余额不足！');
END IF;
--向转入账户加钱
UPDATE account set balance=balance + I_MONEY WHERE ID=I_IN_ACCOUNT;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20001,'对不起，没有该账户:'||I_IN_ACCOUNT);
END IF;
-- 如果没有异常，则提交事务
COMMIT;
DBMS_OUTPUT.PUT_LINE('转账成功!'||'当前可用余额为：'||V_BALANCE);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- 出现异常则回滚事务
DBMS_OUTPUT.PUT_LINE('转账失败：');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
--6.3 存储过程中的自治事务（PRAGMA AUTONOMOUS_TRANSACTION）
定义：PRAGMA AUTONOMOUS_TRANSACTION中文翻译过来叫“自治事务”（翻译的还算好理解），
对于定义成自治事务的Procedure，实际上相当于一段独立运行的程序段，这段程序不依赖于主程序，也不干涉主程序。
特点：
第一,这段程序不依赖于原有Main程序，比如Main程序中有未提交的数据，那么在自治事务中是查找不到的。
第二,在自治事务中，commit或者rollback只会提交或回滚当前自治事务中的DML，不会影响到Main程序中的DML。
------------------------------------------------------
------------------专题-ORACLE连续值问题 ----------------------
--常见于并列排名等情况--
-----------------------------------------------------
--现有需求，找出这组数据中的连续中断后的最小值和最大值
name num
A 1
A 2
A 4
B 5
B 7
B 8
C 9
C 11
----------------------------------------------------
创建测试表：
create table test
(name varchar2(1),
num int);
insert into test values ('A',1);
insert into test values ('A',2);
insert into test values ('A',4);
insert into test values ('B',5);
insert into test values ('B',7);
insert into test values ('B',8);
insert into test values ('C',9);
insert into test values ('C',11);
commit;
执行结果：
with t as
(select name,
num,
row_number() over(partition by name order by num desc) rn
from test)
select s.name, s.num
from (select t2.*
from t t1, t t2
where t1.rn = t2.rn - 1
and t1.num = t2.num + 1
and t1.rn = 1
union all
select * from t where rn = 1) s
order by name, num
------------------------------------------------------------------
---------------------正则表达式-----------------------------------
------------------------------------------------------------------
SQL语句还可以搭配正则表达式作为查询条件，很是有用。
REGEXP_LIKE(匹配)
REGEXP_INSTR (包含)
REGEXP_REPLACE(替换)
REGEXP_SUBSTR(提取)
--＊*************＊--------------------
-- 表 1：定位元字符(常在notepad++中用到)
元字符 说明
^ 使表达式定位至一行的开头
$ 使表达式定位至一行的末尾
-- 表 2：量词或重复操作符
量词 说明
* 匹配 0 次或更多次
? 匹配 0 次或 1 次
+ 匹配 1 次或更多次
{m} 正好匹配 m 次
{m,} 至少匹配 m 次
{m, n} 至少匹配 m 次但不超过 n 次
-- 表 3：预定义的 POSIX 字符类
字符类 说明
[:alpha:] 字母字符
[:lower:] 小写字母字符
[:upper:] 大写字母字符
[:digit:] 数字
[:alnum:] 字母数字字符
[:space:] 空白字符（禁止打印），如回车符、换行符、竖直制表符和换页符
[:punct:] 标点字符
[:cntrl:] 控制字符（禁止打印）
[:print:] 可打印字符
-- 表 4：表达式的替换匹配和分组
元字符 说明
|替换 分隔替换选项，通常与分组操作符 () 一起使用
( )分组 将子表达式分组为一个替换单元、量词单元或后向引用单元（参见“后向引用”部分）
[char]字符列表 表示一个字符列表；一个字符列表中的大多数元字符（除字符类、^ 和 - 元字符之外）被理解为文字
-- 表 5：REGEXP_LIKE 操作符
语法:REGEXP_LIKE(source_string, pattern[, match_parameter])
说明：source_string 支持字符数据类型（CHAR、VARCHAR2、CLOB、NCHAR、NVARCHAR2 和 NCLOB，但不包括 LONG）。
pattern 参数是正则表达式的另一个名称。
match_parameter 允许可选的参数（如处理换行符、保留多行格式化以及提供对区分大小写的控制）。
-- 表 6：REGEXP_SUBSTR 操作符
语法：regexp_substr(source_string,pattern[,position[,occurrence[,match_parameter]]])
说明： source_string：源串，可以是常量，也可以是某个值类型为串的列
position：从源串开始搜索的位置。默认为1。
occurrence：指定源串中的第几次出现。默认值1.
match_parameter：文本量，进一步订制搜索，取值如下：
'i' 用于不区分大小写的匹配。
'c' 用于区分大小写的匹配。
'n' 允许将句点“.”作为通配符来匹配换行符。如果省略改参数，句点将不匹配换行符。
'm' 将源串视为多行。即将“^”和“$”分别看做源串中任意位置任意行的开始和结束，而不是看作整个源串的开始或结束。如果省略该参数，源串将被看作一行来处理。
如果取值不属于上述中的某个，将会报错。如果指定了多个互相矛盾的值，将使用最后一个值。如'ic'会被当做'c'处理。
省略该参数时：默认区分大小写、句点不匹配换行符、源串被看作一行。
案例：
select regexp_substr('MY INFO: Anxpp,22，and boy','my',1,1,'i') from users;
将返回MY，如果将match_parameter改为'c'将不反悔任何内容（null）||
-- 表 7：REGEXP_INSTR 操作符
语法：REGEXP_INSTR(source_string, pattern[, start_position[, occurrence[, return_option[, match_parameter]]]])
说明：该函数查找 pattern ，并返回该模式的第一个位置。您可以随意指定您想要开始搜索的 start_position。 occurrence 参数默认为 1，除非您指定您要查找接下来出现的一个模式。
return_option 的默认值为 0，它返回该模式的起始位置；值为 1 则返回符合匹配条件的下一个字符的起始位置。
-- 表 8：REGEXP_REPLACE 操作符
语法：REGEXP_REPLACE(source_string, pattern[, replace_string [, position[,occurrence, [match_parameter]]]])
说明：该函数用一个指定的 replace_string 来替换匹配的模式，从而允许复杂的“搜索并替换”操作。
--表 9：后向引用元字符
元字符 说明
\digit 反斜线 紧跟着一个 1 到 9 之间的数字，反斜线匹配之前的用括号括起来的第 digit 个子表达式。
（注意：反斜线在正则表达式中有另一种意义，取决于上下文，它还可能表示 Escape 字符。
2.32万/s *60*60=8352*24=2.00448亿
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
用户名正则(--4到16位（字母，数字，下划线，减号--)
var uPattern = /^[a-zA-Z0-9_-]{4,16}$/;
console.log(uPattern.test("iFat3"));
密码强度正则(--//密码强度正则，最少6位，包括至少1个大写字母，1个小写字母，1个数字，1个特殊字符--)
var pPattern = /^.*(?=.{6,})(?=.*\d)(?=.*[A-Z])(?=.*[a-z])(?=.*[!@#$%^&*?]).*$/;
console.log("该密码符合要求"+pPattern.test("iFat3#"));
整数正则
//正整数正则
var posPattern = /^\d+$/;
//负整数正则
var negPattern = /^-\d+$/;
//整数正则
var intPattern = /^-?\d+$/;
数字正则(整数或浮点)
//正数正则
var posPattern = /^\d*\.?\d+$/;
//负数正则
var negPattern = /^-\d*\.?\d+$/;
//数字正则
var numPattern = /^-?\d*\.?\d+$/;
Email正则
var ePattern = /^([A-Za-z0-9_\-\.])+\@([A-Za-z0-9_\-\.])+\.([A-Za-z]{2,4})$/;
console.log(ePattern.test("65974040@qq.com"));
手机号正则
var mPattern = /^1[3456789]\d{9}$/;
console.log(mPattern.test("18516370162"));
身份证正则
var card = /^[1-9]\d{5}[1-9]\d{3}((0\d)|(1[0-2]))(([0|1|2]\d)|3[0-1])\d{4}$/;
console.log(card.test("4123445565656657565"));
--------------------------------------------------------------------
------------------------专题-ORACLE行转列的几种方式 ----------------
--------------------------------------------------------------------
--行转列的几种方式
--1. pivot
with temp as(
select '四川省' nation ,'成都市' city,'第一' ranking from dual union all
select '四川省' nation ,'绵阳市' city,'第二' ranking from dual union all
select '四川省' nation ,'德阳市' city,'第三' ranking from dual union all
select '四川省' nation ,'宜宾市' city,'第四' ranking from dual union all
select '湖北省' nation ,'武汉市' city,'第一' ranking from dual union all
select '湖北省' nation ,'宜昌市' city,'第二' ranking from dual union all
select '湖北省' nation ,'襄阳市' city,'第三' ranking from dual
)
select * from （select nation,city,ranking from temp）pivot (max(city) for ranking in ('第一' as 第一,'第二' AS 第二,'第三' AS 第三,'第四' AS 第四));
*说明：pivot（聚合函数 for 列名 in（类型）），其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 select distinct ranking from temp*
常用的分析函数如下所列：
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)
------------------------------------------------------
------------DBMS_JOB 作业（定时器） ------------
------------------------------------------------------
一、dbms_job涉及到的知识点
1、创建job：（需在命令行窗口执行）
variable jobno number;
dbms_job.submit(:jobno, --job号,系统默认随机赋值
'过程名（parameter）;',--执行的存储过程, ';'不能省略 ,如果入参传有字符串则需用双单引号（''parameter''）
next_date, --下次执行时间
'interval' --每次间隔时间，interval以天为单位
);
2、删除job: dbms_job.remove(jobno);
3、修改要执行的操作: job:dbms_job.what(jobno, what);
4、修改下次执行时间：dbms_job.next_date(jobno, next_date);
5、修改间隔时间：dbms_job.interval(jobno, interval);
6、启动job: dbms_job.run(jobno);
7、停止job: dbms.broken(jobno, broken, nextdate); --broken为boolean值
二、初始化相关参数job_queue_processes
1、job_queue_process表示oracle能够并发的job的数量，当job_queue_process值为0时表示全部停止oracle的job。
2、查看job_queue_processes参数
方法一：
show parameter job_queue_process;
方法二：
select * from v$parameter where name='job_queue_processes';
3、修改job_queue_processes参数
alter system set job_queue_processes = 10;
三、user_jobs表结构
字段（列） 类型 描述
job number 任务的唯一标示号
log_user varchar2(30) 提交任务的用户
priv_user varchar2(30) 赋予任务权限的用户
schema_user varchar2(30) 对任务作语法分析的用户模式
last_date date 最后一次成功运行任务的时间
last_sec varchar2(8) 如hh24:mm:ss格式的last_date日期的小时，分钟和秒
this_date date 正在运行任务的开始时间，如果没有运行任务则为null
this_sec varchar2(8) 如hh24:mm:ss格式的this_date日期的小时，分钟和秒
next_date date 下一次定时运行任务的时间
以下使用一个案例来演示dbms_job的使用
一、在plsql中创建表：
create table t(
id varchar2(30),
name varchar2(30)
);
二、在plsql中创建存储过程：
create or replace procedure proce_t is
begin
insert into t(id, name) values('1', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
commit;
end proce_t;
/
三、创建job任务(1分钟执行一次)：
在sql>后执行：
variable jobno number;
begin
dbms_job.submit(:jobno,'proce_t;', sysdate, 'sysdate+1/24/60');
commit;
end;
/
提交后提示：
pl/sql procedure successfully completed
jobno
---------
25
四、跟踪任务的情况(查看任务队列)：
sql> select job, next_date, next_sec, failures, broken from user_jobs;
job next_date next_sec failures broken
---------- ----------- ---------------- ---------- --------
25 2012/9/14 1 10:59:46 0 n
说明任务已创建成功。
执行select * from t;查看定时任务的结果。可以看出定时任务是正常执行了的。
五、停止定时任务
1、查看定时任务的job号。
sql> select job, next_date, next_sec, failures, broken from user_jobs;
job next_date next_sec failures broken
---------- ----------- ---------------- ---------- ------
25 2012/9/14 1 11:01:48 0 n
2、停止一个已启动的定时任务：
begin
dbms_job.broken(25, true, sysdate);
commit;
end;
/
表示停止job为25的任务。
执行后显示如下：
pl/sql procedure successfully completed
3、查看定时任务是否已停止成功
sql> select job, next_date, next_sec, failures, broken from user_jobs;
job next_date next_sec failures broken
---------- ----------- ---------------- ---------- ------
25 4000/1/1 00:00:00 0 y
broken值为y，表示定时任务已停止。
六、启动定时任务
1、查看停止定时任务
sql> select job, next_date, next_sec, failures, broken from user_jobs;
job next_date next_sec failures broken
---------- ----------- ---------------- ---------- ------
25 4000/1/1 00:00:00 0 y
broken值为y，表示定时任务已停止。
2、启动定时任务
begin
dbms_job.run(25);
commit;
end;
/
3、查看定时任务是否已启动
sql> select job, next_date, next_sec, failures, broken from user_jobs;
job next_date next_sec failures broken
---------- ----------- ---------------- ---------- ------
25 2012/9/14 1 11:06:17 0 n
broken值为n，表示定时任务启动成功。
七、查看进程数
show parameter job_queue_processes;
必须大于0，否则执行下面的命令修改：
alter system set job_queue_processes=10;
八、再创建一个任务(每5分钟执行一次)：
variable jobno number;
begin
dbms_job.submit(:jobno, 'proce_t;', sysdate, 'sysdate+1/24/12'); --interval是以天为单位的
commit;
end;
/
九、 执行
select job,next_date,next_sec,failures,broken from user_jobs;
结果：
sql> select job,next_date,next_sec,failures,broken from user_jobs;
job next_date next_sec failures broken
---------- ----------- ---------------- ---------- ------
26 2012/9/14 1 11:12:08 0 n
25 2012/9/14 1 11:07:18 0 n
十、总结
关于job运行时间
1:每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/(24*60)
2:每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/(24)
3:每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24
4:每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
5:每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
6:每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
7:每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'), 12)+1/24
job的运行频率设置
1.每天固定时间运行，比如早上8:10分钟：Trunc(Sysdate+1) + (8*60+10)/24*60
2.Toad中提供的：
每天：trunc(sysdate+1)
每周：trunc(sysdate+7)
每月：trunc(sysdate+30)
每个星期日：next_day(trunc(sysdate),'星期日')
每天6点：trunc(sysdate+1)+6/24
半个小时：sysdate+30/(24*60)
3.每个小时的第15分钟运行，比如：8:15，9:15，10:15…：trunc(sysdate,'hh')+(60+15)/(24*60) 。
--------------------------------------------------