使用链路实现数据实时自动远程传输

SQL> select * from global_name; #查看全局数据名

SQL> grant create database link to hr10; #授权HR10用户建立链路权限

SQL〉create database link shilei connect to hr10 identified by hr10 using 'https://www.360docs.net/doc/234341419.html,'; 在HR10用户下建立远程链路

SQL> select * from tab@https://www.360docs.net/doc/234341419.html,;

链路远程查询

SQL〉create table y_dept as select * from dept@https://www.360docs.net/doc/234341419.html,; 远程复制一张表

SQL> select * from user_db_links;

查询链路数据字典

SQL> drop database link 数据库链路名#####删除链路

在SYS/SYSTEM用户下授权建立公共链路：SQL〉CREATE PUBLIC DATABASE LINK https://www.360docs.net/doc/234341419.html, CONNECT TO hr10 IDENTIFIED BY hr10 USING

'shilei';

另一种写法：SQL〉CREATE PUBLIC DATABASE LINK https://www.360docs.net/doc/234341419.html, USING 'shilei'; #操作两个同名用户中的任意实体

查询公共链路信息：SYSTEM

SQL〉SELECT * FROM DBA_DB_LINKS；与SQL〉SELECT * FROM USER_DB_LINKS；

删除公共链路：

SQL〉DROP PUBLIC DATABASE LINK 公共链路名；

二、使用链路实现数据实时自动远程传输

创建数据触发器：INSERDEPT(触发器名称)、EACH ROW（行级）

1、数据插入触发器：

SQL〉CREATE OR REPLACE TRIGGER INSERDEPT AFTER INSERT ON DEPT FOR EACH ROW BEGIN INSERT INTO DEPT@https://www.360docs.net/doc/234341419.html, VALUES

(:NEW.DEPTNO,:NEW.DNAME,:NEW.LOC);END INSERDEPT;

插入数据：

SQL> INSERT INTO DEPT VALUES(60,'WWWWW','SONGYUAN');

提交完成：

SQL> COMMIT;

显示错误：

SQL〉SHOW ERRORS

查询触发器数据字典：SQL〉select triggter_name from user_triggters;

查询触发器代码：SQL>select triggter_body from user_triggters where triggter_name='inserdept' ;

2、创建更新触发器：

SQL>create or replace triggterupddept after update on dept for each row begin update dept@https://www.360docs.net/doc/234341419.html, set

deptno=:new.deptno,dname=:new.dname,loc=:new.loc where deptno=:old.deptno and dname=:old.dname and loc=:old.loc; end updept;

SQL>/ #触发生成执行

3、删除触发器：

SQL〉DROP TRIGGER触发器名字；

4、数据删除触发器：

SQL>create or replace triggterdeldept

after delete on dept for each row

begin delete from dept@https://www.360docs.net/doc/234341419.html,

wheredeptno=:old.deptno;

enddeldept;

sql>delete from dept where deptno=40;

sql>commit;

5、触发器三合一：

SQL>create or replace triggterchangdept

after insert or update or delete on dept for each row

begin

ifinserting then insert into dept@https://www.360docs.net/doc/234341419.html,

VALUES(:NEW.DEPTNO,:NEW.DNAME,:NEW.LOC);

update dept@https://www.360docs.net/doc/234341419.html,

setdeptno=:new.deptno,dname=:new.dname,loc=:new.loc where deptno=:old.deptno and dname=:old.dname and loc=:old.loc;

elsif deleting then

delete from dept@https://www.360docs.net/doc/234341419.html,

wheredeptno=:old.deptno;

end if;

endchangdept;

SQL〉/

6、查看触发器有效状态

SQL〉Selecttrigger_name,table_name,status from user_triggers;

7、用户审计：

建立表：SQL>create table audit_info (name varchar2(30),op varchar(30),ttime date,

s1int,

s2int,

s3 varchar2(30),

s4 varchar2(30));

本机创建触发器：（必须在sys/sysem下建立链路并生成触发器）

SQL>create or replace trigger sys_a1

after logon on database

begin

insert into audit_info@https://www.360docs.net/doc/234341419.html,(name,op,ttime) values(user,'进入系统'，sysdate)；

end；

SQL〉/

SQL>create or replace trigger sys_a2

after logoff on database

begin

insert into audit_info@https://www.360docs.net/doc/234341419.html,(name,op,ttime) values(user,'离开系统'，sysdate)；

end；

SQL〉/

监控操作：

create or replace triggeraudit_op

after insert or updata or delete on dept

for each row

begin

if inserting then

insert into audit_info@https://www.360docs.net/doc/234341419.html, values(user,'插入数据'，sysdate,:new.deptno,:new.dname,:new.loc,null)；

insert into audit_info@@https://www.360docs.net/doc/234341419.html, values(user,'删除数据'，sysdate,:old.deptno,:old.dname,:old.loc,null)；

end;

三、定时自动传输

快照（snapshots）

主节点（master）辅节点（snapshots）

语法：

1、授权（辅节点）

Sys/system

SQL〉Grant create snapshots to 用户名；

2、创建快照（辅节点）

SQL〉create snapshot 快照名；

Refresh 刷新方式—complete，fast，force

Next 时间间隔

With primary key（表有主键）

As select 字句

语法说明：

Complete—完全刷新

Fast—增量刷新

Force—

Sysdata+1（每天传一次）、sysdata+1/24(每小时传一次)、sysdata+1/1440（每分钟传一次）固定时间刷新：next_day(trunk(sysdata),’星期日’)+22/24

3、创建快照日志（主节点）

SQL〉create snapshot log on 表名；

Refresh 刷新方式—complete，fast，force

Next 时间间隔

With primary key（表有主键）

As select 字句

##############Drop table dept cascade constraints 删除表和关联

SQL〉create snapshot dept

Refresh force

Next sysdate+1/1440

With primary key（表有主键）

As select * from dept@https://www.360docs.net/doc/234341419.html,;

创建快照日志：SQL>CREATE SNAPSHOT LOG ON DEPT;

查询快照信息：

SQL〉select name,master_owner,master,type,next,master_link from user_snapshots;

Name :快照名

Master_owner:主节点用户名

Master：主节点表名

Type：刷新方式

Next：时间间隔

Master_link:链路名

删除快照：

SQL〉drop snapshots 快照名；

三、数据库闪回

概念：

数据闪回（flashback）可以实现对于过去某指定时间点数据库中数据的浏览、可以使用基表返回到过去某一时间点，实现逻辑操作错误后，数据库的自我恢复。

（一）查询闪回（查询闪回）

查询过去某指定时间点节点基表中数据，恢复错误的删除，更新等

SQL〉select * from emp as of timestamp to_timestamp(‘2011.09.22 09:40:00’,’yyyy.mm.ddhh24:mi:ss’);

查询到结果后执行数据插入操作：

SQL>Insert into emp

SQL〉select * from emp as of timestamp to_timestamp('2011.09.22 09:40:00','yyyy.mm.dd hh24:mi:ss');

SQL〉commit;

（二）基表返回

指部分数据改动

案例：一不小心把工资清零了。

Update emp

基表返回

A、设置基表允许进行行移动

SQL>alter table emp enable row movement;

B、实施基表返回

SQL〉flashback table emp enable row timestamp to_timestamp('2011.09.22 09:40:00','yyyy.mm.dd hh24:mi:ss');

（三）删除闪回

显示回收站信息：showrecyclebin

闪回drop删除的表：

SQL〉flashback table emp to before drop;

或SQL〉flashback table “回收站实体名”to before drop;

或SQL〉flashback table emp to before drop rename to emp11;

注意：表结构更改不能闪回

建立与emp一样的emp1表：SQL〉create table emp1 as select * from emp;

SQL〉Delete from emp where comm is null；---删除部分数据，不会受自由空间、可以回退

SANPSHOT TOO OLD---说明回滚段太小，要删除的数据太多

Set autocommit 3

SQL>TRUNCATE TABLE EMP;---将删除全部数据保留表结构，回收自由空间、不可回退SQL〉drop table emp；---删除数据及表结构、回收全部空间，不可回退

彻底删除表，不在保留：

SQL〉dorp table emp1 purge；

清除回收站：SQl〉purge recyclebin；

关于数据闪回的几点说明：

●系统表空间数据不能闪回（管理员所做的系统操作不能闪回）

●实体在回收站保留时间不可保证，保留时间取决于系统运行状况，可能保留几

秒、也可能保留几个月—---undo表空间里

●闪回时建立在基表上的约束不可保证

四、数据分区存储(partition);

（一）数据分区：把一个表划分成多个存储区域，在创建表结构是考虑分区存储方案，在基表中选取一个或多个列作为分区关键字，该关键字决定哪些数据存储在哪个分区。数据插入时，所插入的数据自动存储到相应分区。

（二）创建分区表

SQL〉CREATE table employee

(Name varchar2(20),sal number(7,2),hdate date) partition by range(sal)

(partition p1 values less than(1000) tablespace users,

partition p2 values less than(3000) tablespace sysaux,

partition p3 values less than(8000) tablespace example,

partition p4 values less than(20000) tablespace users);

查询可使用表空间

SQL>select tablespace_name from dba_tablespaces;

分区表查询：

SQL〉select * from employee partition(p1);

减少磁盘访问i/o、提高海量数据表的访问效率

查询是否创建了分区表：

SQL〉select table_name,partitioned from user_table;

查询分区表结构：

SQL〉select partition_name,high_value,tablespace_name from user_tab_partitions where table_name=’EMPLOYEE’;

(三)修改分区

1、增加分区

SQL〉alter table employee add partition p5 values less than(30000) tablespace users;

2、删除分区

SQL〉alter table employee truncate partition p5; ## truncate删除数据，保留结构

3、分区拆分

SQL〉alter table employee split partition p4 at(10000) into (partition p34,partition p4);

4、修改分区名称

SQL〉Artle table employee rename partition p5 to p6；

5、分区合并（merges）

SQL〉Artle table employee merge partitions p5,p6 into partition p6；

6、移动表分区:从一个表空间移动到另一个表空间

SQL〉ALTER TABLE EMPLOYEE MOVE PARTITION P6 TABLESPACE USERS;

四、创建LIST分区表

应用区分不了大小的数据分区表

SQL〉create table product(

P_namevarchar2(20),

P_idnumber(5),

P_date date,

P_locvarchar2(50)) partition by list(p_loc)

(partition p1 values(‘北京’) tablespace users,

partition p2 values(‘上海’) tablespacesysaux,

partition p3 values(‘河南’,’陕西’) tablespace example,

partition p4 values(null) tablespace users,

partition p1 values(default) tablespace users);

分区合并：

SQL〉Artle table employee merge partitions p1,p2 into partition p1；

分区拆分：

SQL>Alter table product split partition p3 values(‘陕西’) into (partition p3,partition p32);

To_char(sysdate,’yyyy’)从系统日期提取年

Extract(year from sysdate)

五、存储过程

（一）存储过程

把一个PL/SQL块存储在数据库中，作为数据库实体来存储，可以调用

语法：

SQL〉CREATE or replace procedure 过程名（变量in|out|in out数据类型）is|as

变量定义；

Begin

…;

End 过程名；

例子：

In：调用者向过程传递参数

Create or replace procedure delemp(v1 in number) as

Begin

Delete from emp where empno=v1;

If SQL%NOFOUND then dbms_output.put_line(‘删除失败！系统中无编码为’||v1||’员工’);

Else dbms_output.put_line (‘编码为’||v1||’员工已被除名’);

End if;

Enddelemp;

SQL>set serveroutput on

存储过程使用方法：

在sql*plus中调用

SQL>exec delemp(7788);

在pl*sql中调用

Begin

Delemp(7934)

Delemp(2345)

End;

查询以创建的存储过程

SQL>select distinct name from user_source;

查询存储过程代码：

SQL>select text from user_source where name=’delemp’;

加密存储过程：

将存储过程代码先存成*.Sql文件，在系统盘符下执行wrap iname=*.sql ==>*.plb命令生成*.plp文件，在执行此文件。

查询基表中代码：

SQL>select dbms_metadata.get_ddl(‘TABLE’,’EMP’) from dual;

----输出sql格式

SQL>select dbms_metadata.get_xml(‘TABLE’,’EMP’) from dual;

---输出XML格式

SQL>set long 1000

Out:由过程向调用者返回结果

Create or replace procedure queremp（v1 in emp.empno%type,

V2 out emp.ename%type,

V3 out emp.sal%type) is

Begin

Select ename,sal into v2,v3 from emp where empno=v1;

Exception

When NO_DATA_FOUND then dbms_output.put(‘系统无编码为’||v1||’人员’)；

End queryemp；

调用方法

Declare

V1 varchar2（20）；

V2 number（7，2）；

Begin

Queryemp（7654，v1，v2）；

Dbms_output.put_line(v1||’***’||v2);

End;

IN OUT(双向参数)

Create or replace procedure changename(

V in out varchar2)

Is

Begin

V:=substr(v,1,3);

End changename;

调用方法：

Declare

Vvarchar2(20):=’SMITH’;

Begin

Changename(v);

Dbns_output.put_line(v);

End;

(二)、函数

创建函数的语法：

Create or replace function 函数名（变量IN|OUT|IN OUT 数据类型）return 数据类型Is|as

变量定义；

Begin

…;

Return 值；

End 函数名；

例子：

Create or replace functiongetsal(v1 in number) return number

Is v_salemp.sal%type;###相当于局部变量在下面执行语句中应用

Begin

Select sal into v_sal from emp where empno=v1;

Return v_sal;

End getsal;

函数调用：

SQL>Select getsal(7369) from dual;####dual是公共同义词并不代表是表

SQL>EXEC dbms_output.put_line(getsal(7369));

题目：计算n!函数

Create or replace function jcn(n int) return int

Is

Begin

If n=1 then return 1;

Else return n*jcn(n-1);

End if;

End jcn;

（三）包（package）

1、什么包？

把一些逻辑相关的过程、函数、变量、例外等组合在一起形成的实体叫包。包由两个部分组成，即包的说明部分及包体部分。定义在包的说明部分的元素叫公共元素，定义在包体部分的元素叫私有元素。

2、包的优点

规范程序设计

方便过程与函数管理

改善系统性能

3、创建包的语法

Create or replace package body 包名

调用

Exec 包名.包体过程名（变动数值）；