oracle 学习

创建表空间

create tablespace waterboss
datafile 'c:\waterboss.dbf'
size 100m
autoextend on
next 10m

解释：

waterboss 为表空间名称

datafile 用于设置物理文件名称

size 用于设置表空间的初始大小

autoextend on 用于设置自动增长，如果存储量超过初始大小，则开始自动扩容

next 用于设置扩容的空间大小

创建用户

create user wateruser identified by itcast
default tablespace waterboss

wateruser 为创建的用户名

identified by 用于设置用户的密码

default tablesapce 用于指定默认表空间名称

用户赋权

grant dba to wateruser

给用户 wateruser 赋予 DBA 权限后即可登陆

grant dab,resource,connect to water

DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

对于普通用户：授予connect, resource权限。

对于DBA管理用户：授予connect，resource, dba权限。

Oracle常用函数

Lower	转换小写
upper（）	转换大写
substr（）	取子串
length（）	取长度
trim（）	去空格
to_date（）	将字符串转换成日期
to_char（）	将日期或数字转换成字符串
to_number（）	将字符串转换成数字
nvl（）	可以将null转换成一个具体值
case	分支语句
decode（）	同case
round（）	四舍五入

-- lower()  //jack
select lower('JACK') from dual;

-- upper() 
...
-- substr  //abc
select SUBSTR('abcdef', 1, 3) from dual;
-- length() //4
select length('name') from dual;

-- trim()  
-- 把X的两边截去trim_str字符串 // b    b   bb
select trim('a' from 'aaab' ),trim('a' from 'aabaa'),trim('a' from 'abba')from dual;

-- to_date() 将字符串转为日期  YYYY-MM-DD HH24:MI:SS 
select to_date('2021-12-26 16:07:57','YYYY-MM-DD HH24:MI:SS') from dual;

-- to_char() 将数据或日期转为字符串 yyyy-mm-dd hh:mm:ss
select id,name,to_char(adddate,'yyyy-mm-dd hh:mm:ss') from t_owers where adddate>to_date('2021-12-26 16:07:50','YYYY-MM-DD HH24:MI:SS');

-- to_number() 将字符转为数值
select id name from t_owers where id>to_number('1.0','99.0');

-- nvl(x,y)  x不为null 则为y ,x为null 则为null
select id ,nvl(name,0) from t_owers;
--nvl2(x,y,z) x不为null 则为y ,x为null 则为z

-- trunc(x,y)  截取
--x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。

 select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333)  from dual;
 
--add_months()  返回在日期d1基础上再加n1个月后新的日期
select sysdate,add_months(sysdate,3) from dual;
--localtimestamp 
select locltmestamp from dual

Oracle聚合函数

count	取得记录数
sum	求和
Avg	取平均
Max	取最大的数
min	取最小的数

分页Rounum用法：

oracle只支持rownum小于或小于等于的运算, 不支持大于或大于等于的运算。

Oracle提供了rownum，rownum是一个隐含的(查询结果集)字段，rownum是一个行号，从1开始。

select rownum,t.* from t_owers ;

前5 数据

select * from (select * from t_owers order by id) where rownum <=5

记录 开启~结束

select *
from 
(
 select rownum r, t.* 
 from 
  (
    任意的SQL语句
  )  t  
  where rownum <=结尾的行号 
)where r>开始的行号

先查出 rownum 再 使用where rownum 条件

select * from 
(select rownum r,t.* from t_owers t where rownum <=5 
) 
where r>2

Oracle建立索引

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次 数,从而提高数据访问性能。

索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于下图的一棵“树”，而树的节点存储的就是每条记录的物理地址，也就是我们提到的伪列（ROWID）

select rowid from owers;

索引的目的是提高查询数据的速度，索引一本书的目录一样，索引的建立原则，比较少的DML(insert、update、delete)，经常出现在where语句中的字段。

建立索引

create index 索引名称 on 表名(列名);

create index index_t_owers_name on t_owers(name)

删除索引

drop index 索引名

drop index index_towers_name

查看索引

user_indexes 系统视图存放是索引的名称以及该索引是否是唯一索引等信息，

user_ind_columns 统视图存放的是索引名称，对应的表和列等

查看所有索引

select * from user_indexes

查看表索引

select * from user_indexes where table_name =upper('t_owers')

查看索引

select * from user_ind_columns

select * from user_ind_columns where table_name=upper('t_owers')

创建唯一索引

create unique index 索引名称 on 表名(列名);

create unique index xx on xx

反向键索引

应用场景：当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子

树。这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变

得不规则，从而使索引树能够均匀分布。

create index 索引名称 on 表名(列名) reverse;

位图索引

使用场景：位图索引适合创建在低基数列上

位图索引不直接存储 ROWID，而是存储字节位到 ROWID 的映射

优点：减少响应时间，节省空间占用

语法：

create bitmap index 索引名称 on 表名(列名);

需求：我们在 T_owners 表的 ownertypeid 列上建立位图索引，语句：

create bitmap index index_owners_typeid

on T_OWNERS(ownertypeid)

Oracle创建视图

查看权限

select * from session_privs;

查看视图

select view_name from user_views;

创建视图：

create view 视图名 as (select 语句)

create view v_t_owers  as select * from t_owers 

删除视图

drop view 视图名

drop view V_t_owers

Oracle序列

序列是Oracle特有的，它可以维护一个自增的数字序列，通常从1开增长，但可以设置，例如：学生表t_student中的编号，可以采用Oracle的序列的方式来维护。

还有一种经常使用的生成策略是Identity，如：Mysql/MS SQL Server。

创建序列

CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默
认是 1
[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中

create sequence 序列名 start with 1 increment by 1

create sequence  seq_t_id start with 1 increment by 1;

查看序列

select sequence_name from user_sequences

使用序列:

通过序列的伪列来访问序列的值

NEXTVAL 返回序列的下一个值

select seq_t_owers_id.nextval from dual;

CURRVAL 返回序列的当前值

select seq_t_owers_id.currval from dual;

insert into 表 （id）values（序列名.nextval）

insert into t_owers(id ,name ...) values(seq_t_id.nextval,'joj',...)

删除序列

drop sequence 序列名

修改序列：使用 ALTER SEQUENCE 语句修改序列，不能更改序列的 START

WITH 参数

ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE

同义词:

create[public] synonym 名 for 名

私有同义

create synonym owers for t_owers

公有同义词

create public synonym owers2 for t_owers 

PL/SQL

PL/SQL（Procedure Language/SQL）是 Oracle 对 sql 语言的过程化扩展，指

在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有

过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起

来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。

基本语法结构

[declare 
-- 变量声明
] 
begin
 -- 代码逻辑
 [exception
 -- 异常处理
 ]
 end;

变量

声明变量的语法：

变量名 类型（长度）;

变量赋值的语法：

变量名：=变量值

注： ‘=’ 和双等号一样是判断相等

declare
v_price number(10,2); -- 
v_usernum number(10,2);
v_usernum2 number(10,2);
v_money number(10,2);
begin
	v_price :=2.45;
	v_usernum :=9213;
	v_usernum2 :=round(v_usernum/1000,2);
	v_money :=v_price*V_usernum2;
	DBMS_OUTPUT.put_line('金额:' || v_money);  --输出
end;

DBMS_OUTPUT.put_line() 输出

select into 变量赋值

select 列名 into 变量名 from 表名 where 条件

declare
v_price number(10,2); -- 
v_usernum number(10,2);
v_usernum2 number(10,2);
v_money number(10,2);
begin
	select OWNERRYPEID into V_price from owers where id=1;
	v_usernum :=9213;
	v_usernum2 :=round(v_usernum/1000,2);
	v_money :=v_price*V_usernum2;
	DBMS_OUTPUT.put_line('金额:' || v_money);  --输出
end;

属性类型

定义变量不确定数据类型，但是一定是表列的数据类型，所以引用

%TYPE 引用型

作用：引用 某表某列 的 字段类型

declare 
-- 变量  表名.列名%type
v_num0 owers.OWNERRYPEID%type; -- 
begin
-- 
select OWNERRYPEID into v_num0 from owers where id =1;
DBMS_OUTPUT.put_line('金额:' || v_num0);
end;

%ROWTYPE 记录型

上例中的例子可以用下面的代码代替

作用: 标识某个表的行记录类型

记录一行

declare 
-- 变量  表名.列名%type
v_num owers%rowtype; -- 一行记录
v_id  number;
begin
-- 
select * into v_num from owers where id =1;

-- 把 v_num里属性赋给v_id

v_id :=v_num.id;

DBMS_OUTPUT.put_line('金额:' || v_num ||' ID:'||v_id);
end;

v_num %rowtype 一行记录

使用 时 v_num.id id就是表的属性

异常

在运行程序时出现的错误叫做异常

发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分

异常有两种类型：

预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式

引发

用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的

异常通过 RAISE 语句显式引发

预定义异常

Oracle 预定义异常 21 个

命名的系统异常

产生原因

ACCESS_INTO_NULL

未定义对象

CASE_NOT_FOUND

CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时

COLLECTION_IS_NULL

集合元素未初始化

CURSER_ALREADY_OPEN

游标已经打开

DUP_VAL_ON_INDEX

唯一索引对应的列上有重复的值

INVALID_CURSOR

在不合法的游标上进行操作

INVALID_NUMBER

内嵌的 SQL 语句不能将字符转换为数字

NO_DATA_FOUND

使用 select into 未返回行

TOO_MANY_ROWS

执行 select into 时，结果集超过一行

ZERO_DIVIDE

除数为 0

SUBSCRIPT_BEYOND_COUNT

元素下标超过嵌套表或 VARRAY 的最大值

SUBSCRIPT_OUTSIDE_LIMIT

使用嵌套表或 VARRAY 时，将下标指定为负数

VALUE_ERROR

赋值时，变量长度不足以容纳实际数据

LOGIN_DENIED

PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码

NOT_LOGGED_ON

PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据

PROGRAM_ERROR

PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包

ROWTYPE_MISMATCH

宿主游标变量与 PL/SQL 游标变量的返回类型不兼容

SELF_IS_NULL

使用对象类型时，在 null 对象上调用对象方法

STORAGE_ERROR

运行 PL/SQL 时，超出内存空间

SYS_INVALID_ID

无效的 ROWID 字符串

TIMEOUT_ON_RESOURCE

Oracle 在等待资源时超时

语法结构：

exception

 when 异常类型 then

 异常处理逻辑

declare 
-- 变量  表名.列名%type
v_num owers%rowtype; -- 一行记录
v_id  number;
begin
-- 
select * into v_num from owers where id =100;
-- 把 v_num里属性赋给v_id
v_id :=v_num.id;
DBMS_OUTPUT.put_line('金额:' || v_num.id ||' ID:'||v_id);

exception
	when NO_DATA_FOUND then
		dbms_output.put_line('数据为找到！');
	when TOO_MANY_ROWS then
		dbms_output.put_line('查询条件有误！');
end;

条件判断 if

基本语法 1

if 条件 then
 业务逻辑
end if;

基本语法 2

if 条件 then
 业务逻辑
else
 业务逻辑
end if;

基本语法 3

if 条件 then
 业务逻辑
elsif 条件 then
 业务逻辑
else
 业务逻辑 
end if;

代码：

-- if
declare
v_len number;
begin
	if length('abc')>0 then
	v_len:=length('abc');
	else
	v_len:=-1;
	end if;
	dbms_output.put_line(v_len);
end;

循环

1．无条件循环

语法结构

loop
 *--**循环语句*
end loop;

declare
vnum number:=1;
begin
loop

dbms_output.put_line(vnum);
vnum:=vnum+1;

exit when vnum>10;  -- 条件退出

end loop;
end;

2.条件循环 while

语法结构

while 条件
loop
-- 循环
end loop;

declare
vnum number:=1;
begin
while vnum<10  -- while 循环
loop
dbms_output.put_line(vnum);
vnum:=vnum+1;
end loop;
end;

3、for循环

基本语法

for 变量 in 起始值 … 终止值

for 变量 in 起始值..终止值
loop
end loop;

范例：输出从1开始的100个数

begin
 for vnum in 1..100
 loop
 dbms_output.put_line(vnum); 
 end loop;
end;

Oracle游标

**1.**什么是游标

游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。我们

可以把游标理解为 PL/SQL 中的结果集

2语法结构及示例

在声明区声明游标，语法如下：

cursor 游标名称 is SQL 语句;

使用游标

open 游标名称 --打开游标
loop
	fetch 游标名称 into 变量 --提取游标到变量
	exit when 游标名称%notfond; --当游标到最后一行下面退
出循环
end loop;
close 游标名称

代码：

定义行记录

定义游标

开启游标

遍历游标

关闭游标

declare 
v_num owers%rowtype;  -- 定义行记录
cursor c_num is select * from owers; --定义游标 是一个结果集
begin
open c_num;
loop
fetch c_num into v_num;  -- 遍历
exit when c_num%notfound;
dbms_output.put_line(v_num.id||v_num.adddate);
end loop;
close c_num;
end;

带参数的游标

我们的查询语句的条件值有可能是在运行时才能决定的，比如性业主类型，

可能是运行时才可以决定，那如何实现呢？我们接下来学习带参数的游标，修改

declare 
v_num owers%rowtype;
cursor c_num(v_table number) is select * from owers where OWNERRYPEID=v_table; --定义游标
begin
open c_num(4);
loop
fetch c_num into v_num;
exit when c_num%notfound;
dbms_output.put_line(v_num.id||v_num.adddate);
end loop;
close c_num;
end;

for 循环提取游标值

我们每次提取游标，需要打开游标 关闭游标 循环游标 提取游标 控制循环的

退出等等，好麻烦！有没有更简单的写法呢？有！用 for 循环一切都那么简单，

上例的代码可以改造为下列形式

declare 
v_num owers%rowtype;  -- 定义行记录
cursor c_num(v_table number) is select * from owers where OWNERRYPEID=v_table; --定义游标
begin
for v_num in c_num(4)   -- for循环
loop
dbms_output.put_line(v_num.id||v_num.adddate);
end loop;
end;

Oracel 函数

存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。

创建函数

CREATE [ OR REPLACE ] FUNCTION 函数名称
（参数名称 参数类型, 参数名称 参数类型, ...）
RETURN 结果变量数据类型

IS   -- 这里和declare差不多

 变量声明部分;
BEGIN
 逻辑部分;
 RETURN 结果变量;
[EXCEPTION 
 异常处理部分]
END;

创建存储函数，根据地址 ID 查询地址名称。

create function f_id(vid number)
return varchar
is
	vaddress varchar(30);
begin
	select addressid into vaddress from owers where id=vid;
	return vaddress;
end;

-- 结果
select f_id(1) from dual;
	
	

代码2：

create or replace function f1(p1 in number,p2 in number)
retrun number
as
begin
if p1>p2 then
	return p1;
else 
	return p2;
end if;
end f1;

select f1(11,22) from dual;

dual 为虚拟表

查看

select * from user_procedures

删除

drop function 函数名

Oracle存储过程与触发器

存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：

1、存储函数中有一个返回值，且必须返回；而存储过程没有返回值，可以通过 传出参数返回多个值。

2、存储函数可以在 select 语句中直接使用，而存储过程不能。存储过程多数是被应用程序所调用。

3、存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码

存储过程:

存储过程最直接的理解：就是保存了批量的sql(select,insert,if for)，以后可以通过一个名字把这些批量的sql执行，使用存储过程在大批量数据查询或计算时会带来高性能，存储过程编写和调试比较复杂，不同数据库产品存储过程差异非常大，很难实现平滑一致。

CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
（参数名 类型, 参数名 类型, 参数名 类型）
IS|AS
 变量声明部分;
 BEGIN
 逻辑部分
[EXCEPTION 
 异常处理部分]
END;

异常处理

exception
 when NO_DATA_FOUND then
 v_ownersuuid:=-1;
 rollback;
end;

参数只指定类型，不指定长度

过程参数的三种模式：

IN 传入参数（默认）

OUT 传出参数 ，主要用于返回程序运行结果

IN OUT 传入传出参数

● 建立存储过程

create sequence seq_t_id start with 1 increment by 1; -- 创建序列
-- 创建存储过程
create or replace procedure pro_owers_add(vname varchar2,vaddress varchar,vadddate date)
is 
begin
	insert into owers(id,name,addressid,adddate) values(seq_t_owers_id.nextval,vname,vaddress,vadddate);
	commit;
end;

-- 无参数时，调用存储过程
call pro_owers_add('z','沪',sysdate);
-- 还有一种调用存储过程，带参数必须使用第二种
begin
pro_owers_add('z','沪',sysdate);
end;

/**
* 增加
* @param owners
*/
public static void add(Owners owners){
java.sql.Connection conn=null;
java.sql.CallableStatement stmt=null;
try {
conn=BaseDao.getConnection();
    
stmt=conn.prepareCall("{call 
pro_owners_add(?,?,?,?,?)}");
                      
stmt.setString(1, owners.getName());
stmt.setLong(2, owners.getAddressid());
stmt.setString(3, owners.getHousenumber());
stmt.setString(4, owners.getWatermeter());
stmt.setLong(5, owners.getOwnertypeid());
stmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {
BaseDao.closeAll(null, stmt, conn);
} }

创建带传出参数的存储过程

create or replace procedure pro_owers_add1(vname varchar2,vaddress varchar,vadddate date,vid out number)
is 
begin
-- 对传出参数赋值
	select seq_t_owers_id.nextval into vid from dual;
	insert into owers(id,name,addressid,adddate) values(vid,vname,vaddress,vadddate);
	commit;
end;

--定义传出参数的变量
declare
	vid number;
begin	pro_owers_add('zz','沪',sysdate，vid);
	DBMS_OUTPUT.put_line('增加成功,ID:'||vid); -- 输出变量
end;

/**
* 增加
* @param owners
*/
public static long add(Owners owners){
long id=0;
java.sql.Connection conn=null;
java.sql.CallableStatement stmt=null;
try {
conn=BaseDao.getConnection();
stmt=conn.prepareCall("{call 
pro_owners_add(?,?,?,?,?,?)}");
stmt.setString(1, owners.getName());
stmt.setLong(2, owners.getAddressid());
stmt.setString(3, owners.getHousenumber());
stmt.setString(4, owners.getWatermeter());
stmt.setLong(5, owners.getOwnertypeid());
stmt.registerOutParameter(6, OracleTypes.NUMBER);//注
册传出参数类型
stmt.execute();
id=stmt.getLong(6);//提取传出参数
} catch (SQLException e) {
e.printStackTrace();
}finally {
BaseDao.closeAll(null, stmt, conn);
}
return id; }

触发器

触发器是特殊的存储过程，它与数据库的insert、update和delete相关联，如定义完成触发器之后，会在insert、update或delete语句执行前或执行后自动执行触发器中的内容。

触发器示例，向emp表中加入数据，采用触发器自动再向t_log表里加入一条数据。

CREATE [or REPLACE] TRIGGER 触发器名
 BEFORE | AFTER
 [DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]
 ON 表名
 [FOR EACH ROW ][WHEN(条件) ]
declare
 ……
begin
 PLSQL 块
End ；

FOR EACH ROW 作用是标注此触发器是行级触发器 语句级触发器

在触发器中触发语句与伪记录变量的值

触发语句	：old	:new
insert	null	将要插入的数据
Update	更新以前该行的值	更新后的值
delete	删除以前该行的值	null

前置触发器

需求：当用户输入本月累计表数后，自动计算出本月使用数 。

代码：

create or replace trigger tri_account_updatenum1
before
update of num1
on t_account
for each row --每一行
declare
begin
-- 修改伪记录变量 修改usernum值
 :new.usenum := :new.num1-:new.num0;
end; 

后置触发器

需求：当用户修改了业主信息表的数据时记录修改前与修改后的值

*–创建业主名称修改日志表😗用于记录业主更改前后的名称

create table t_owners_log
(
updatetime date,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
);

*--**创建后置触发器，自动记录业主更改前后日志*
create trigger tri_owners_log
after
update of name  -- 修改列名触发
on t_owners 
for each row -- 
declare
begin
 insert into t_owners_log 
values(sysdate,:old.id,:old.name,:new.name);
end;

测试

--更新数据
update t_owners set name='杨小花' where id=3;
commit;
--查询日志表
select * from t_owners_log;

● 首先建立t_log表

create table t_log (
	log_id number(10) primary key,
	log_time date
) 

● 为建立t_log的主键建立sequence

create sequence seq_log_id start with 1 increment by 1;

● 建立触发器

向emp表中加入数据,采用触发器自动再向t_log表里加入一条数据。

create or replace trigger tri_t_owers 
  after insert on t_owers 
declare

begin
   insert into t_owers_bak(id, adddate) values(11, sysdate);
end; 

删除触发器
drop trigger tri_t_owers;

查看触发器

select trigger_name from user_triggers

Oracle分区

http://blog.itpub.net/31401608/viewspace-2147665/

https://www.cnblogs.com/lijiaman/p/6495465.html

1.表空间及分区表的概念

**表空间：**是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。

**分区表：**当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间（物理文件上），这样查询数据时，不至于每次都扫描整张表。

2.表分区的具体作用

oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外，分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。

分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库 管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但 是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。

什么时候使用分区表，官方给的建议是：

a. 表的大小超过2GB。

b. 表中包含历史数据，新的数据被增加到新的分区中

3.表分区的优缺点

优点：

a.改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

b.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用。

c.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可。

d.均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

缺点：

分区表相关，已经存在的表没有方法可以直接转化为分区表。不过oracle提供了在线重定义表的功能。

4.表分区的几种类型及操作方法

4.1范围分区(range) maxvalue

a.每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。

b.所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。

c.如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。

create tablespace p1 datafile '/home/oracle/p1.dbf' size 32m extent management local autoallocate;
create tablespace p2 datafile '/home/oracle/p2.dbf' size 32m extent management local autoallocate;
create tablespace p3 datafile '/home/oracle/p3.dbf' size 32m extent management local autoallocate;
...

创建range 分区

create table pdba (id number, time date) partition by range (time)
(
  partition p1 values less than (to_date('2013-02-1', 'yyyy-mm-dd')),
  partition p2 values less than (to_date('2013-03-1', 'yyyy-mm-dd')),
  partition p3 values less than (to_date('2013-04-1', 'yyyy-mm-dd')),
  partition p4 values less than (to_date('2013-05-1', 'yyyy-mm-dd')),
  partition p5 values less than (to_date('2013-06-1', 'yyyy-mm-dd')),
  partition p6 values less than (to_date('2013-07-1', 'yyyy-mm-dd')),
  --partition p13 values less than (maxvalue)    --不建议使用
)
讲一讲分区表
select * from pdba partition(p12)  --十二月份

4.2列表分区(list) default

List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。

在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。

在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。

create table t_list_partition(a int,b int) partition by list(a)
(
  partition p1 values (1,2,3,4,5),
  partition p2 values (6,7,8,9,10),
  partition p3 values(default)
)

4.3散列分区(hash)

对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。

create table t_hash_partition(a int,b int) partition by hash(a)
(
   partition p1 tablespace users,
   partition p2 tablespace system
);
hash partition都有一个常识，就是partition的数量最好是2的次方

4.4组合分区

如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。

在10g中组合分区主要有两种：range-hash，range-list。11g中又增加了range-range，list-range，list-list，list-hash，并且 11g里面还支持Interval分区和虚拟列分区。 注意顺序，根分区只能是range分区，子分区可以是hash分区或list分区。

4.4.1RANGE-LIST

create table my_range_list(id number,time date) partition by range(time) subpartition by list(id)
(
	partition time_1 values less than(to_date('2021-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss'))
	(
		subpartition data_01 values(1,2,3,4,5),
		subpartition data_02 values(6,7,8,9,10),
		subpartition data_03 values(default)
	),
	partition time_2 values less than(to_date('2021-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss'))
	(
		subpartition data_11 values(1,2,3,4,5),
		subpartition data_12 values(6,7,8,9,10),
		subpartition data_13 values(default)
	),
	partition time_3 values less than(MAXVALUE)
	(
		subpartition data_21 values(1,2,3,4,5),
		subpartition data_22 values(6,7,8,9,10),
		subpartition data_23 values(default)
	)
)

select * from dba_tab_partitions where table_name like 'MY%';

4.4.2 LIST-LIST

create table  my_list_list(id number,name varchar2(20))
partition by list(id) subpartition by list(name)
(
	partition my_01 values(1,2,3,4,5)
	(
	subpartition my_11 values('tom','jery'),
	subpartition my_12 values(default)
	),
	partition my_02 values(6,7,8,9,10)
	(
	subpartition my_21 values('tom','jery'),
	subpartition my_22 values(default)
	)
)

5.间隔分区

https://www.cnblogs.com/lijiaman/p/6495465.html

间隔分区是Oracle 11.1引入的新功能，通过该功能，可以在输入相应分区的数据时自动创建相应的分区。在没有间隔分区技术之前，DBA通常会创建一个maxvalue分区以避免ORA-14400：插入的分区键值不能映射到任何分区（“inserted partition key does not map to any partition”) 错误。

作为范围分区（range partition）的扩展，间隔分区命令数据库在插入表中的数据超过所有现有范围分区时自动创建指定间隔的分区。DBA必须至少指定一个范围分区的较高值，称为过渡点，数据库会自动的为超出该过渡点的数据创建间隔分区，每个间隔分区的下边界是先前范围或间隔分区的上边界。

create table my_interval(ts date,data varchar2(30))
partition by range(ts) interval(numtoyminterval(1,'month'))
(
	partition p0 values less than(to_date('2021-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss'))
)
-- 测试
insert into my_interval values
(to_date('2021-02-02 00:00:00','yyyy-mm-dd hh24:mi:ss'),'1')
insert into my_interval values
(to_date('2021-03-03 00:00:00','yyyy-mm-dd hh24:mi:ss'),'1')

select * from dba_tab_partitions;

select * from user_tab_partitions where table_name='T_LIST_PARTITION_DEF'

NUMTOYMINTERVAL(x,c)

用法：x是一个数据，c是一个字符串，该函数是将x转为interval year to month类型。常用单位有：”year“、”month“。

NUMTODSINTERVAL(x,c)

用法：x是一个数据，c是一个字符串，该函数是将x转为interval day to second类型。常用单位有：”day“、”hour“、”minute“、”second“。

分区增删

查看分区

select * from 表名 partition(分区名)

添加分区

alter table 表名 add partition ...

添加子分区

alter table 表名 modify partition add subpartition ...

删除分区

alter table 表名 drop partition 分区名

alter table 表名 drop subpartition 子分区名

调度

https://www.cnblogs.com/fistao/p/4221179.html

https://www.iteye.com/blog/mybutterfly-1998036

在Oracle中任务调度指某一执行程序在特定的时间被周期性的执行。Oracle把任务调度称为job。而一个基本的job由两方面组成program和schedule。其中program指周期性执行的可执行程序，其中可包括PL\SQL匿名块，存储过程、函数、包以及操作系统的可执行脚本和外部程序等等。schedule指的是，调度program执行的周期。该周期既可以基于特定时间被调度，也可以基于某一事件被调度。因而JOB总体上可分为两大类，基于时间的JOB和基于事件的JOB。在Oracle 10g之前，采用dbms_job程序包来完成任务调度的相关工作。在Oracle 10g之后，Oracle推出了功能更加强大的dbms_schedule来完成任务调度工作，主要是由于dbms_schedule能够基于事件进行任务调度而dbms_job无法基于事件进行调度（笔者个人浅见，待验证）！

调度的组成

调度的话包含着3个部分，用一句话来描述那就是：程序+调度计划+作业=调度

如果用一张图片来描述的话那就是：

创建Program的SQL如下

BEGIN 
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name=>'HR.INSERT_SYSDATE', -- 名字
program_action=> 'insert into hr.job_test values(sysdate);commit;',
program_type=>'PLSQL_BLOCK',  --类型
number_of_arguments=>0, --指定该JOB执行时需要附带的参数的数量
comments=>'insert sysdate into table',   --描述
enabled=>TRUE); 
END;

其中program_type类型里有3个选项分别代表

PLSQL_BLOCK:表示该程序为可执行的PL\SQL匿名块
STORED_PORCEDURE:表示程序为存储过程、函数或程序包
EXECUTABLE：表示程序为位于操作系统上的可执行脚本或程序。

创建Schedule的SQL如下

BEGIN
sys.dbms_scheduler.create_schedule( 
repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',  --频率
start_date => systimestamp at time zone '+8:00', --指定任务初次执行的时间
comments => 'Start Every 5 minutes', --描述
schedule_name => '"HR"."INSERT_SCHE"');--名字
END;

在Repeating部分，我们指定启动的频率，在指定启动频率时应采用日历表达式。起格式由3部分组成：频率、间隔、特定时间点。

FREQ 关键字用来指定间隔的时间周期，可选参数有：YEARLY（年）, MONTHLY（月）, WEEKLY（周）, DAILY（日）, HOURLY（时）, MINUTELY（分）, SECONDLY（秒）等单位。

INTERVAL 关键字用来指定间隔的频繁，可指定的值的范围从1-999。

BYHOUR 指定一天中的小时。可指定的值的范围从1-24。16，17，18就表示每天下午的4、5、6点。

BYDAY 关键字用来指定每周的哪天运行。

BYMONTHDAY 关键字用来指定每月中的哪一天。-1 表示每月最后一天。

BYMONTH 关键字用来指定每年的月份。

BYDATE 指定日期。0310就表示3月10日。

下面是一些简单例子

每隔2天：FREQ=DAYLY；INTERVAL=2

每隔30妙：FREQ=SECONDLY;INTERVAL=30

每周的第一天：FREQ=WEEKLY;BYMONTHDAY=1

每月的最后一天：FREQ=MONTHLY;BYMONTHDAY=-1

每周日的凌晨1点：FREQ=WEEKLY;BYDAY=SUN;BYHOUR=1

创建job的SQL如下

DBMS_SCHEDULER.create_job(
                     job_name      => 'TEST_JOB',     
                     program_name  => 'TEST_PROG',
                     schedule_name => 'TEST_SCH',
                     job_class     => 'DEFAULT_JOB_CLASS',     --指定任务关联的CLASS，默认值为DEFAULT_JOB_CLASS       
                     comments      => 'arg test',--设置任务的注释信息，默认值为NULL。
                     enabled       => true);
end;

创建以后的监控：
查看调度

SELECT * FROM User_Scheduler_Schedules;

查看作业

SELECT * FROM User_Scheduler_Jobs;

查看程序

SELECT * FROM User_Scheduler_Programs;

例子：

-- 表
create table MY_RANGE_LIST( id number,time date);

-- 存储过程
create or replace procedure mypro
is
begin
insert into MY_RANGE_LIST VALUES (seq_t_owers_id.nextval,SYSDATE);
commit;
end;

-- 	 调度
--program
begin 
DBMS_SCHEDULER.CREATE_PROGRAM(
	program_name=>'insert_pro',
	program_action=> 
-- 	'mypro',  --存储过程
	'insert into MY_RANGE_LIST VALUES (seq_t_owers_id.nextval,SYSDATE);', --代码块
program_type=> 
-- 'STORED_PROCEDURE',--存储过程类型
'PLSQL_BLOCK',  --代码块类型
number_of_arguments=>0, --指定该JOB执行时需要附带的参数的数量
comments=>'insert sysdate into table',   --描述
enabled=>TRUE); 
end;
-- schedle
begin 
dbms_scheduler.create_schedule(
	repeat_interval => 'FREQ=MINUTELY;INTERVAL=2', --每 2 分钟执行一次
	start_date => sysdate, -- 当前时间
	comments => 'Start Every 2 minutes', --
	schedule_name=>'insert_sch'--名称
);
end;
-- job
begin
dbms_scheduler.create_job(
job_name => 'insert_job', --名称
program_name => 'insert_pro', --程序名称
schedule_name => 'insert_sch', -- 计划名称
job_class => 'DEFAULT_JOB_CLASS', -- 作业类
comments => 'insert job', --描述
enabled => true -- 自动
);
end;


-- 删除
begin
dbms_scheduler.drop_job('insert_job');
end;
begin
dbms_scheduler.drop_program('insert_pro');
end;
begin
dbms_scheduler.drop_schedule('insert_sch');
end;
-- 查看
SELECT * FROM User_Scheduler_Schedules;

SELECT * FROM User_Scheduler_Jobs;

SELECT * FROM User_Scheduler_Programs;

DBlink

数据库连接，就是指不同数据库之间的访问

01，查询

　　查询DBLINK信息
　　select * from dba_db_links;
　　select owner,object_name from dba_objects where object_type='DATABASE LINK';
　　select * from ALL_DB_LINKS;

02，创建

[](javascript:void(0)😉

Oracle创建dblink报错:ORA-01017、ORA-02063解决：
根据ORA-01017的提示是连接到另一方的用户密码错误，当9i或10g的版本的Oracle数据库连接11g的版本时，会自动将密码转化为大写。
**密码最好用双引号引起来，否则可能在连接的时候报错

create database link dblink名
  connect to 数据库用户名 identified by "数据库密码"
 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';  

[](javascript:void(0)😉

-- 如果创建全局 dblink，必须使用 systm 或 sys 用户，在 database 前加 public。  
create public database link dblink名
　　connect to 数据库用户名 identified by "数据库密码"
　　using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';  

[](javascript:void(0)😉

03，删除

--删除DBLINK
DROP DATABASE LINK [name];  
--或  
DROP PUBLIC DATABASE LINK [name];  

nsert_pro’, --程序名称
schedule_name => ‘insert_sch’, – 计划名称
job_class => ‘DEFAULT_JOB_CLASS’, – 作业类
comments => ‘insert job’, --描述
enabled => true – 自动
);
end;

– 删除
begin
dbms_scheduler.drop_job(‘insert_job’);
end;
begin
dbms_scheduler.drop_program(‘insert_pro’);
end;
begin
dbms_scheduler.drop_schedule(‘insert_sch’);
end;
– 查看
SELECT * FROM User_Scheduler_Schedules;

SELECT * FROM User_Scheduler_Jobs;

SELECT * FROM User_Scheduler_Programs;

# DBlink

数据库连接，就是指不同数据库之间的访问

## 01，查询

查询DBLINK信息
　　select * from dba_db_links;
　　select owner,object_name from dba_objects where object_type=‘DATABASE LINK’;
　　select * from ALL_DB_LINKS;

## 02，创建

[[外链图片转存中...(img-MbNJxEVa-1641372490045)]](javascript:void(0);)

Oracle创建dblink报错:ORA-01017、ORA-02063解决：
根据ORA-01017的提示是连接到另一方的用户密码错误，当9i或10g的版本的Oracle数据库连接11g的版本时，会自动将密码转化为大写。
**密码最好用双引号引起来，否则可能在连接的时候报错

create database link dblink名
connect to 数据库用户名 identified by “数据库密码”
using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))’;

[[外链图片转存中...(img-crAM6Buw-1641372490049)]](javascript:void(0);)

– 如果创建全局 dblink，必须使用 systm 或 sys 用户，在 database 前加 public。
create public database link dblink名
　　connect to 数据库用户名 identified by “数据库密码”
　　using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))’;

[[外链图片转存中...(img-mb3aju8g-1641372490051)]](javascript:void(0);)

## 03，删除

–删除DBLINK
DROP DATABASE LINK [name];
–或
DROP PUBLIC DATABASE LINK [name];