oracle知识点总结

一、体系结构

oracle
数据库
物理
配置文件
控制文件
数据
日志(DML)
逻辑
表空间:管理数据文件
多个数据文件
段
产生新表
区
段用完之后是区
块
数据库的基本单位2k,4k,8k,16k
实例
进程
DBWR
数据读写进程
LGWR
日志读写
SMON
实例恢复
磁盘碎片的处理
PMON:进程的监控器
把不用进程删除
CPKT:检查点
把内存中的数据保存到文件中

内存(SGA)
数据

日志
共享
编译后的sql
数据字典
user_tables
user_views
客户连接数据库的过程
客户端:
用户进程(N)
服务器:
监听器--->服务进程(1..5)--->DBWR LGWR
关系:
表空间(1)--数据文件(1..n)
表(1)---段(1)
段(1)---区(0..n)
段(区)(1)---块(n)
登陆
sqlplus 用户名/密码@网络服务名
在sqlplus中的切换:
connect 用户名/密码@网络服务名
net manger配置数据连接
二、sql查询和sql函数
数据库的数据类型:
字符
varchar2(4000)
char(2000)
long(2G)，只能有一个字段，不能有索引及主键
nvarchar(2)
日期
date
raw(2000)
long raw
数字
number(10,2)
lob(4G)
blob(二进制)
clob(字符)
bfile(引用)
pl/sql的数据类型:
字符
varchar2(32767)
char(32767)
long(32760)，只能有一个字段，不能有索引及主键
日期
date
timestamp:在秒上有小数
raw(32760)
long raw(32760)
数字
number(10,2)
整型
int
naturaln
natural
positive
positiven
浮点
real
float
double
lob(4G)
blob(二进制)
clob(字符)
bfile(引用)
rowid:唯一的标识每一行
rownum：分页
select * from customer where rownum<2;

select * from customer where rownum<100
minus
select * from customer where rownum<90

pageSize=2;
通用算法
select * from customer where rownum<=100(页数)*pagesize
minus
select * from customer where rownum<=(页数-1)*pageSize
第一页
select * from customer where rownum<=1*pagesize
minus
select * from customer where rownum<=0*pageSize
第二页
select * from customer where rownum<=2*pagesize
minus
select * from customer where rownum<=(2-1)*pageSize
第三页
select * from customer where rownum<=3*pagesize
minus
select * from customer where rownum<=(3-1)*pageSize
ddl:
create
drop
alert table customer add(business varchar2(20));
truncate
dml:
insert
insert into 表名(列) select customerName from customer;
delete
update
select
create table a1 as select * from customer 1=2;
select * from 表 where 条件 group by 字段 having 条件 order by
select c.*,b.* from customer c join buy b on c.customerName=b.customerName;
select c.*,b.* from customer c , buy b where c.customerName=b.customerName;
事务处理:
commit;
rollback to savepoint p1;
savepoint p1;
权限管理
grant 权力 to 用户
revoke 权力 from 用户
函数:
字符相连:||
substr(字符,开始位置,数量);
instr(字符，被查找的字符串，从第几个位置查找)
replace(字符，被换的，替换的)
to_date('2004-01-01','yyyy-mm-dd');
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
nvl(sex,'');
lpad('aaa',15,'*');
分析函数
row_number() over (partitin by column order by 列名)
100 1
100 2
200 3
产生一个名称序号,数值相同序号不重复
rank() over ((partitin by column) order by 列名)
100 1
100 1
200 3
产生一个名称序号,数值相同序号可以重复，但下面的面的序号不连续
dense_rank() over ((partitin by column) order by 列名)
产生一个名称序号,数值相同序号可以重复，下面的序号连续

100 1
100 1
200 2
select ename,deptno,sal,dense_rank() over (order by sal desc) from emp;--在scott用户下
三、锁和表分区
锁:
表级锁lock table in (参数) Mode

共享锁(share)
可以加多个,加完后，别的人只读不可写
排它锁(exclusive)
只能加1个,加完后，别的人只读不可写
共享更新锁(share update)
本身不起作用，由insert delete update select for update
行级锁(dml):
select * from 表名 for update wait 1
insert
delete
update
分区:
范围
create table customer_range
(
customerID number(10) primary key
)
patition by range(customerID)
(
partition p1 values less than(100), --<100
partition p2 values less than(200), --100<=customerId<200
partition p2 values less maxvalue --null 不在所有范围
)
散列
create table customer_hash
(
customerID number(10) primary key
)
partition by hash(cusotmerID)
(
partition p1 ,
partition p2
)
复合
create table customer_com
(
customerID number(10)
)
partiton by range(customerID)
subpartition by hash(customerID)
(
partition p1 values less than(100)
(
subpartition sp1,
subpartition sp2,
subpartition sp3,
subpartition sp4
)
partition p2 values less than(maxvalue)
(
subpartition sp5,
subpartition sp6,
subpartition sp7,
subpartition sp8
)
)
列表
create table customer_list
(
customerID number(10) primary key,
sex varchar2(20),
city varchar2(20)
)
partition by list(city)
(
partition p1 values('上海','北京','深圳'),
partition p2 values('银川'),
partition p3 values(default)
)
特点:
逻辑是一张表，物理上不在一起.
优点:
查询快
备份方便
故障出现在小的范围内。
分共的处理:
select * from customer partition(p1);
select * from customer subpartition(sp1);
合并
alter table customer merge partitons p1,p2 into partition p3;
拆分
alter table customer split partition p1 at(50) into (partition p3,partitin p2);
交换
alter table customer exchange patition p1 with table customer_temp;
增加
alter table customer add partition p1 values less than(200);
删除
alter table customer drop partition p2;
第四章数据库对象
同义词:别名
作用:简化操作
私有:
create synonym a1 as y17.customer;
公有:所有用户都可以用,但要有对应对象的访问权限。
create public synonym a1 as y17.customer;
序列:产生主键
语法:
create sequence aa
start with 10
increment by 2
maxvalue 1000
minvalue 10
cycle
--cache 30
当前的一个
select aa.currval from dual;
下一个:
select aa.nextval from dual;

create table cc
(
a1 varchar2(20) primary key
)

insert into cc values(lpad(aa.nextval,20,'0'));
视图:虑似表(不存数据，支sql)
可以修改的前题:
1、对行没有压缩或扩大(distinct group by union join)
2、对列没变异(upper(sex) 或 sex||'')
3、一次只能修改一个表的数据
customer
custoemrID
customerName
buy
buyID
buyName
customerID
create view customer_buy
as
select b.*,c.customerName from customer c join buy b
on c.customerID=b.customerID;
语法:
create view [force] as select * from 表名
[with check option] --通过视图修改时不能变的没有了
create view v_check as  
select * from customer where customerID in(1,2) with check option;
[with read only] --只读

create view force_view force as select aaaa1 from aaaa;
索引:排序(hash树)
优点:
查快,(改增、删)慢
三级范式:(查慢,(改增、删)快)
1、主键(employID projectID)
employID employName projectID projectName   小时工资 职务 工时
2、完全依赖于主键
employID employName   小时工资 职务

employID projectID(中间表) 工时

projectID projectName
3、没有传递依赖
employID employName   职务

小时工资 职务

employID projectID(中间表)

projectID projectName
e-r图:
找实体、拉关系、写属性
唯一的索引:
create unique index aa on custmer(customerID);
组合索引：
create index aa on custmer(customerID,sex);
select * from customer where customerID='' and sex=''
位图索引:(重复性高的)
create bitmap index aa on custmer(sex);

男 起始位置 000000000000000011110000000000

女 终止位置 111111111111111100001111111111
返向
create unique index aa on custmer(customerID) reverse;

11111111112
11111111113
11111111114
压缩
create unique index aa on custmer(customerID,buyID) compress;
函数
create unique index aa on custmer(customerID||'sssss') reverse;
索引的分区:
本地分区:索引的分区与基表的分区一样，基表的分区变了索引的分区也变了
create index a_1 on customer(customerID) local;
全局分区:索引的分区与基表的分区不一样，基表的分区不影响索引的分区
create index a_1 on customer(customerID)
partition by range(customerID)
(
p1 values less than(100),
p2 values less than(maxvalue)
)
第五章使用pl/sql
一、通用的语法
赋值 :=
一句话结否用;
pl/sql块的基本结构
declare
begin
exception
end;
二、条件
if 条件 then
end if;

if 条件 then
elsif 条件 then
end if;

case 变量
when 常数 then ;
when 常数 then ;
when 常数 then ;
else 语句;
end case;

case
when 变量>=常数 then ;
when 变量<常数 then ;
else 语句;
end case;
三、循环
while 条件 loop
end loop;

loop
exit when 条件
end loop;

for i in 1..100 loop
end loop;
四、异常
自定义异常
declare
myException Exception
begin
raise myException;
exception
--when myException then raise_application_error(-20001,'消息');
when myException then dbms_output.put_line('消息');
end;
预定义异常:数据库给定的异常
declare
v_customerName varchar2(200);
begin
select customerName into v_customerName from customer;
exception
when too_many_rows then raise_application_error(-20001,'行太多');
when no_data_found then raise_application_error(-20001,'没有数据');
when others then raise_aplication_error(-20001,'其他错误');
end;
五、动态sql
declare
v_sql varchar2(200);
v_customer customer%rowtype;
v_customerName customer.customerName%type;
begin
v_sql:='select * from customer where customerName=:1';
v_customerName='&a';
execute immediate v_sql into v_customer using v_customerName;
exception
end;

execute immediate sql [into 变量] [using 参数值]
六、游标管理
作用:一批行，可以一行一行的处理。
属性:
%rowcount
%found
%notfound
%isopen
类型:
隐式(insert delete update select into)
游标名:sql
%rowcount
%found
显式
--不带参数
declare
cursor c1 return customer%rowtype is select * from customer;
v_customer customer%rowtype;
begin
open c1;
fetch c1 into v_customer;
while(c1%found) loop
fetch c1 into v_customer;
end loop;
close c1;
end;
--带参数
declare
cursor c1(v_customerName varchar2) return customer%type is select * from customer where customerName=v_customerName;
v_customer customer%rowtype;
v_name customer.customerName%type;
begin
v_name='&ak';
open c1(v_name);
fetch c1 into v_customer;
while(c1%found) loop
fetch c1 into v_customer;
end loop;
//循环游标
for i in c1(v_name) loop
end loop;
end;
--循环游标
declare
cursor c1 return customer%type is select * from customer;
v_customer customer%rowtype;
begin
for v_customer in c1 loop
end loop;
end;
动态(指针)：先定义类型，再定义变量
--普通的使用方式
declare
type c_type is ref cursor return customer%rowtype;
c1 t_type;
begin
open c1 for select * from customer;
end;
--动态的指针游标
declare
type c_type is ref cursor return customer%rowtype;
c1 t_type;
v_sql varchar2(2000):='select * from customer where customerName=:1';
v_customerName customer.customerName%type;
begin
v_customerName:='&k';
open c1 for v_sql using v_customerName;
end;
七、子程序和程序包
一、过程
create or replace procedure aa (a1 varchar2,a2 out varchar2,a3 in out varchar2)
is/as
begin
end;

二、函数:
create or replace function aa(a1 varchar2,a2 varchar2) return varchar2
is
begin
return;
end;
三、函数与过程的区别
pl/sql 变量:=函数名(参数) 过程名(参数)
sql/plus select 函数名(参数) from exec 过程名(参数)
必须有return(1..n) return(0..n)
参数只能in 可以是out in out in
四、自主事务处理:主过程、从过程事务不影响。
create or replace procedure a1
is
begin
insert
a2();
commit;
end;

create or replace procedure a2
is
program autonomous_transaction --自主事务处理
begin
rollback;
end;
五、包
--包规格说明，是全局变量
create or replace package a1
is
v_customerName varchar2(200);
procedure aa(a1 varchar2);
function bb(a1 varchar2) return varchar2;
end;
--包主体，是局部变量
create or replace package body a1
is
procedure aa(a1 varchar2)
is
begin
end;
function bb(a1 varchar2) return varchar2
is
begin
return 'aa';
end;
end;
--使用包:
包名.过程名
--包中使用游标
--包规格说明
create or replace package a1
is
v_customerName varchar2(200);
cursor c1(v_customerName varchar2) return customer%rowtype;
procedure aa(a1 varchar2);
function bb(a1 varchar2) return varchar2;
end;
--包主体
create or replace package body a1
is
cursor c1(v_customerName varchar2) return customer%rowtype is select * from customer where customerName=v_customerName;
procedure aa(a1 varchar2)
is
begin
open c1(a1);
end;
function bb(a1 varchar2) return varchar2
is
begin
return 'aa';
end;
end;
八、触发器与内置程序包
--语法
create or replace trigger 名称
before|after|instead of   delete|update|insert
on 表名
for each row  
when(old.sex='男')
declare
begin
:old.列名
:new.列名
end;
--行级,语句级
--条件判断when(old.sex='男')
--新表、老表
:new.列名
:old.列名
--当有多个事件如何区分
create or replace trigger 名称
before delete or update or insert
on 表名
for each row
declare
begin
if(updating) then
dbms_output.put_line('updating');
elsif(deleting) then
dbms_output.put_line('deleting');
else
dbms_output.put_line('inserting');
end if;
end;
--instead of:在视图上用
create or replace trigger 名称
instead of delete or update or insert
on 表名
for each row
declare
begin
if(updating) then
dbms_output.put_line('updating');
elsif(deleting) then
dbms_output.put_line('deleting');
else
dbms_output.put_line('inserting');
end if;
end;
--模式级
create table temp1
(
obj_name varchar2(200),
obj_type varchar2(200),
sdate date
)
create table a
(
a1 varchar2(200)
)
create or replace trigger 名称
before drop
on schema
declare
begin
insert into temp1 values(ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;
--数据库级的触发器
create or replace trigger 名称
before startup
on database
declare

begin
end;
--触发器不起作用
alter trigger 名称 disable;
alter table a disable all triggers;
--内置程序包
dbms_output:打印
dbms_output.enable(2000);
dbms_output.put();
dbms_output.put_line();
dbms_lob:对lob的处理
dbms_lob.append('aaaaa');
instr
write
utl_file
v_file utl_file.file_type;
utl_file.fopen('目录','文件名','r');
utl_file.get_line(v_file,变量);
utl_file.put(v_file,变量);
utl_file.fclose(v_file);
dbms_random
(dbms_random.random() mod 99); --产生0--的随机数
dbms_queryXml:根据一个sql可以产生一个xml文件
v_clob:=dbms_queryXml.queryXml('select * from customer');
第九章:备份与恢复
故障类型
语句
进程:客户直接关闭
实例:非法关机
介质:硬盘坏了
备份类型
按组件:
物理:备份是物理文件
逻辑:备份的sql
增量备份
按备份方式:
热备份:数据库实例不关闭
逻辑备份属于热备份

也可进行物理备份
冷备份
物理备份

create table a
(
a1 varchar2(20),
a2 varchar2(100)
) tablespace t53;
insert into a values('aaaaaaaaaaa','bbbbbbbbbb');
insert into a values('bbbb','bbbbbbbbbb');
insert into a values('cccc','bbbbbbbbbb');
insert into a values('dddd','bbbbbbbbbb');
insert into a values('eeee','bbbbbbbbbb');
insert into a values('ffff','bbbbbbbbbb');
insert into a values('mmmm','bbbbbbbbbb');

create table b
(
a1 varchar2(20),
a2 varchar2(100)
) tablespace t53;
insert into b values('aaaaaaaaaaa','bbbbbbbbbb');
insert into b values('bbbb','bbbbbbbbbb');
insert into b values('cccc','bbbbbbbbbb');
insert into b values('dddd','bbbbbbbbbb');
insert into b values('eeee','bbbbbbbbbb');
insert into b values('ffff','bbbbbbbbbb');
insert into b values('mmmm','bbbbbbbbbb');

create table c
(
a1 varchar2(20),
a2 varchar2(100)
) tablespace t53;
insert into c values('aaaaaaaaaaa','bbbbbbbbbb');
insert into c values('bbbb','bbbbbbbbbb');
insert into c values('cccc','bbbbbbbbbb');
insert into c values('dddd','bbbbbbbbbb');
insert into c values('eeee','bbbbbbbbbb');
insert into c values('ffff','bbbbbbbbbb');
insert into c values('mmmm','bbbbbbbbbb');

drop table a;
drop table c;
drop table b;



一、导出(dos)
--导入/导出一个用户的所有内容
exp t49001/1234@aptech file=t49 owner=t49001
imp t49001/1234@aptech file=t49 full=y ignore=y
--导出/导入一表空间的tablespaces(一个用户)
exp t49001/1234@aptech file=space tablespaces=t53
--tablespaces代表是局部,不能出现ignore=y
imp t49001/1234@aptech file=space tablespaces=t53 ignore=y ;
--导入/导出几个表tables(一个用户)
exp t49001/1234@aptech file=d:/v_tables tables=(a,b)
imp t49001/1234@aptech file=v_tables tables=(b) ignore=y
--用参数文件
userid=t49001/1234@aptech
file=aa
owner=t49001

exp parfile=aa.txt

--将一个用户的数据导入到另一个用户，不能出现full fromuser touser(多个用户)
exp system/system@aptech file=scott owner=scott

imp system/system@aptech file=scott fromuser=scott touser=t49002

--查看当前数据库的模式
用sysdba或sysoper的身份进入
sqlplus system/system@aptech
connect system/system@aptech as sysdba
--登陆的另一种方式
sqlplus /nolog
connect system/system@aptech as sysdba

archive log list

--通过命令行改为归档模式
--关闭数据
shutdown immediate
--数据库装载数据
startup mount
--修改为归档模式或非归档模式
alter database archivelog/noarchiverlog
--打开数据库
alter database open
--自动归档
alter system archive log start
--手动归档非活动但没有归档的日志文件，一般是最后一个动作
alter system archive log all;
--通过企业管理器改为归档模式
修改为归档或非归档
应用的进修系统会自动
关
启动
加载数据
修改为对应的模式
打开
--归档的相关参数
log_archive_dest_1:归档文件存放的位置
log_archive_format:自动归档备份文件名，保证备份日志名不重复
log_archive_start:是否自动
--归档的相关系统文件
v$archive_dest:归档的日志存放的位置
v$archived_log:已归档的日志信息
--oem图形化的管理
--oem图形化的管理
配置oem
Enterprise Manager Configuration Assistant
在企业控制器，选择登陆到oms
用户名:sysman
默认密码:oem_temp
将所有的服务启动
agent是最重要的
查找节点
localhost
选择数据库以sys进入，其身份是sysdba
在控制面板-->管理工具-->安全策略-->本地策略-->用户权限分配-->作为批处理作业登录(加入administator用)
在oracle的首选项中加入本机的验证
导入或导入相关的数据