第七课 数据管理（1-6）

一 传输表空间，将JCH1库的tsport表空间迁移到JCH2库中

场合：1.适用于OLAP数据仓库应用 2.数据迁移 比exp/imp速度快，不仅迁移数据同时迁移元数据

原理：只导出表空间的元数据（即结构信息），导入目标数据库，把表空间设置为只读，把对应的数据文件复制到目标系统的目标目录，挂载上导入的表空间，在把表空间设置为读写。

官方文档：Administrator’s Guide-> 8 Managing Tablespace -> Transport Tablespace Between databases

SQL Reference -> CREATE DIRECTORY

清理环境

drop user tsport cascade;

drop tablespace tsport including contents and datafiles;

1.初始化实验环境

sqlplus / as sysdba;

create tablespace tsport datafile '/oracle/oracle/oradata/JCH1/tsport01.dbf' size 20m autoextend off;

create user tsport identified by tsport default tablespace tsport;

grant connect,resource to tsport;

conn tsport/tsport

create table t1 (a int) tablespace tsport;

insert into t1 values (100);

commit;

create index idx_t1 on t1 (a) tablespace tsport;

select index_name,table_name,tablespace_name from user_indexes where table_name='T1';

conn / as sysdba

select tablespace_name,segment_name,segment_type from dba_segments where tablespace_name='TSPORT'; 查看TSPORT表空间有哪些对象

2.检查TSPORT表空间是否违反约束条件

exec dbms_tts.transport_set_check('TSPORT',true);

例如表在A表空间，索引在B表空间，如果只传输A的话，就会违反自包含约束条件，表上的索引就会失效，传输不完整，解决方法：同时传输A和B两个表空间。

select * from transport_set_violations; 查看违反约束列表，没有记录为正常

3.设置TSPORT为只读表空间，即把所有数据文件都设置成只读状态。

alter tablespace tsport read only;

4.使用exp或expdp的transport_tablespace=y参数导出表空间的元数据 即结构信息

exp -help 打开帮助文档可以查看其选项说明

exp userid=\'/ as sysdba\' transport_tablespace=y tablespaces=tsport file=/oracle/oracle/exp_tsport.dmp

使用SYS用户只导出tsport表空间的元数据（结构信息），而不是真实数据，因此容量比较小

scp exp_tsport.dmp ocm2:~ 传输到第二台机器上

数据泵导出方式选做

【expdp -help

create directory dir_home as '/home/oracle';

grant read,write on directory dir_home to public;

expdp system/oracle directory=dir_home dumpfile=expdp_tsport.dmp transport_tablespaces=tsport transport_full_check=y

】

5.拷贝数据文件(表空间真正的数据)只有数据文件为只读状态才可以复制，而且不用停库

scp tsport01.dbf ocm2:/u01/app/oracle/oradata/JCH2/

6.imp或impdp的传输表空间导入

JCH2库的准备工作

create user tsport identified by tsport;

grant connect,resource to tsport;

使用imp导入方式 默认以追加方式插入表空间元数据+数据文件

imp userid=\'/ as sysdba\' file=/oracle/exp_tsport.dmp fromuser=tsport touser=tsport transport_tablespace=y tablespaces=tsport datafiles=/u01/app/oracle/oradata/JCH2/tsport01.dbf

【使用impdp导入方法 选做】

create directory dir_home as '/home/oracle';

grant read,write on directory dir_home to public;

impdp system/oracle directory=dir_home dumpfile=expdp_tsport.dmp remap_schema=(tsport:tsport) 对象从一个schema加载到另一个schema

transport_datafiles=/u01/app/oracle/oradata/LEO2/tsport01.dbf 导入哪个数据文件

检查表空间是否导入成功

col tablespace_name for a15

col segment_name for a15

col segment_type for a15

select tablespace_name,segment_name,segment_type from dba_segments where tablespace_name='TSPORT';

conn tsport/tsport

select * from t1;

7.将JCH1实例和JCH2实例表空间调整为可读写状态

sqlplus sys/oracle@JCH112 as sysdba

conn / as sysdba

select tablespace_name,status from dba_tablespaces;

alter tablespace tsport read write;

select tablespace_name,status from dba_tablespaces;

sqlplus sys/oracle@JCH111 as sysdba

select tablespace_name,status from dba_tablespaces;

alter tablespace tsport read write;

select tablespace_name,status from dba_tablespaces;

二 创建分区表和分区索引

官方文档：

Administrator’s Guide –> 17 Managing Partitioned Tables and Indexs

Data Warehousing Guide -> 5 Partitioning in Data Warehouses

场合：数据量很大，要求检索范围小，效率高

优点：DBA管理灵活性高，基于分区删除、插入

缺点：跨分区检索效率很低,但可创建一个全局索引global来改善性能

全局索引global：默认删除分区，则全局索引失效，一个分区表只有一个全局索引

本地索引local：一个分区一个索引，有几个分区就有几个索引

要求：我们创建一个分区表，共有4个分区，每个分区独立使用一个表空间

使用非标准块，块大小16k

1.设置非标准块

JCH1

alter system set db_16k_cache_size=80M; 设置非标准块16K缓冲区，用于存放非标准块

show parameter db_16k_cache_size

作用：用于减少物理I/O读写次数，原本读100次可以完成的数据，现在读50次就完成了

2.创建4个表空间，一个分区对应一个表空间

create user jch1 identified by jch1;

grant dba to jch1

drop table leo1.t2_part;

drop index leo1.idx_t2_part;

drop tablespace part1 including contents and datafiles;

drop tablespace part2 including contents and datafiles;

drop tablespace part3 including contents and datafiles;

drop tablespace part4 including contents and datafiles;

create tablespace part1 datafile '/oracle/oracle/oradata/JCH1/disk1/part1_01.dbf' size 50M

extent management local

blocksize 16k;

create tablespace part2 datafile '/oracle/oracle/oradata/JCH1/disk2/part2_01.dbf' size 50M

extent management local blocksize 16k;

create tablespace part3 datafile '/oracle/oracle/oradata/JCH1/disk3/part3_01.dbf' size 50M

extent management local blocksize 16k;

create tablespace part4 datafile '/oracle/oracle/oradata/JCH1/disk4/part4_01.dbf' size 50M

extent management local blocksize 16k;

select * from v$tablespace;

2.构造分区表数据

conn tsport/tsport

drop table t2 purge;

create table t2 (itemid number(10),name varchar2(10),itemdate date);

create index idx_t2 on t2(itemid);

insert into t2 values (1,'apple1',to_date('2000-02-01','yyyy-mm-dd'));

insert into t2 values (2,'apple2',to_date('2000-03-01','yyyy-mm-dd'));

insert into t2 values (3,'apple3',to_date('2002-04-01','yyyy-mm-dd'));

insert into t2 values (4,'apple4',to_date('2002-05-01','yyyy-mm-dd'));

insert into t2 values (5,'apple5',to_date('2002-06-01','yyyy-mm-dd'));

insert into t2 values (6,'apple6',to_date('2010-07-01','yyyy-mm-dd'));

insert into t2 values (7,'apple7',to_date('2010-08-01','yyyy-mm-dd'));

insert into t2 values (8,'apple8',to_date('2012-09-01','yyyy-mm-dd'));

insert into t2 values (9,'apple9',to_date('2012-10-01','yyyy-mm-dd'));

insert into t2 values (10,'apple10',to_date('2013-11-01','yyyy-mm-dd'));

commit;

select * from t2; 插入10条记录，显示出来

3.JCH1用户下创建分区表

conn jch1/jch1

CREATE TABLE t2_part

PARTITION BY RANGE (itemdate)

( PARTITION p1 VALUES LESS THAN (to_date('2002-01-01','yyyy-mm-dd'))

TABLESPACE part1,

PARTITION p2 VALUES LESS THAN (to_date('2010-01-01','yyyy-mm-dd'))

TABLESPACE part2,

PARTITION p3 VALUES LESS THAN (to_date('2012-01-01','yyyy-mm-dd'))

TABLESPACE part3,

PARTITION p4 VALUES LESS THAN (to_date('2013-01-01','yyyy-mm-dd'))

TABLESPACE part4,

PARTITION other VALUES LESS THAN (maxvalue)

TABLESPACE part4)

as select * from tsport.t2;

查看分区表数据

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select * from jch1.t2_part;

查看某一个分区

select * from jch1.t2_part partition (p1);

4.创建hash全局分区索引

hash分区索引：均匀打散后存放数据，拿一列作hash打散，均匀分布在4个分区上，每个分区在不同表空间上的记录数都差不多，并发读并发写

官方文档：SQL Reference -> CREATE TABLE and CREATE INDEX

conn jch1/jch1

注：把全局分区索引(按照name列做hash打散)均匀分成4份，每份都保存在LEOINDEX表空间里

create unique index idx_t2_part on t2_part (name,itemid)global partition by hash (name) partitions 4 tablespace JCHINDEX parallel 4;

select index_name,index_type,table_name from user_indexes where table_name='T2_PART';

截断一个分区，需要保证全局索引可用

1.默认情况下，增加、删除分区>全局索引失效

2.truncate 操作会影响全局索引 delete 操作不会影响全局索引

alter table t2_part truncate partition p1 update global indexes;

select * from t2_part partition (p1);

【update global indexes 这个关键字可以在操作之后重建全局索引】

检查全局索引是否有效

select index_name,status,partitioned from dba_indexes where table_name='T2_PART';

三 FGA细粒度审计

场合：可以查出谁 什么时候 使用什么语句 删除的表

Oracle 细粒度审计是安全领域的一个分支，它可以追溯数据库的历史操作，从而保证所有的操作都是安全可靠可控，FGA是基于包来实现的

官方文档：Security Guide -> 12 Configuring and Administering Auditing -> Fine-Grained Auditing

PL/SQL Packages and Types Reference –> 40 DBMS_FGA

要求：使用FGA技术对表进行审计

1.清理环境

只有管理员才可以删除审计

DBMS_FGA.DROP_POLICY(

object_schema VARCHAR2, --用户名（如果为空默认当前登陆用户）

object_name VARCHAR2, --待审计的对象名（表名字）

policy_name VARCHAR2 ); --审计名（必须是唯一值）

execute DBMS_FGA.DROP_POLICY(object_schema=>'leo1',object_name=>'t',policy_name=>'audit_t');

conn jch1/jch1

drop table t purge; 删除待审计的表

2.创建待审计的T表

conn jch1/jch1

create table t (x number(10),y varchar2(20)); 创建待审计的表t

3.创建审计策略

conn / as sysdba

begin

dbms_fga.add_policy (

object_schema => 'jch1', 审计谁

object_name => 't', 审计谁的表

policy_name => 'audit_t', 审计策略的名字

audit_condition => 'x >= 100', 触发审计的条件 x>=100

audit_column => 'x', 审计表中的哪个列‘x,y’

enable => TRUE, 审计立刻生效

statement_types => 'INSERT,UPDATE,DELETE,SELECT'); 触发审计的语句对这些语句都启动审计

end;

/

4.查询确认FGA策略是否生效

col object_schema for a20

col object_name for a15

col policy_name for a13

col enabled for a3

select object_schema,object_name,policy_name,enabled from dba_audit_policies;

5.插入测试记录符合触发审计的条件

conn jch1/jch1

insert into t values (10,'first');

insert into t values (100,'leo');

insert into t values (200,'leonarding');

insert into t values (300,'andy');

insert into t values (400,'anlan');

insert into t values (500,'tigerfish');

insert into t values (600,'666666666');

select * from t; 查看审计表的内容

6.查看审计结果，默认会把审计结果放在SYS.FGA_LOG$基表中

注：审计会对INSERT,UPDATE,DELETE,SELECT这四种语句都做检查，并且记录谁 操作的哪个表 执行的什么语句

col OBJ$SCHEMA for a10;

col OBJ$NAME for a10;

col POLICYNAME for a15;

col LSQLTEXT for a50;

select OBJ$SCHEMA,OBJ$NAME,POLICYNAME,LSQLTEXT from SYS.FGA_LOG$;

set lines 200

col sql_text for a55;

col object_schema for a10;

col object_name for a10;

col policy_name for a15;

select object_schema,object_name,policy_name,sql_text from dba_common_audit_trail;

显示所有审计结果

四 监控索引使用情况

场合：监控表中无用索引删除之

官方文档：Administrator’s Guide -> 16 Managing Indexes -> Monitoring Index Usage

conn jch1/jch1

drop table t4;

create table t4 as select * from dba_objects;

create index idx_t4 on t4(object_id);

开启JCH1下idx_t4索引的监控

alter index jch1.idx_t4 monitoring usage;

停止JCH1下idx_t4索引的监控

alter index jch1.idx_t4 nomonitoring usage;

select * from t4 where object_id=5000;

查看v$object_usage视图获得索引被使用情况

set linesize 400 设置环境

col index_name for a10

col table_name for a10

col start_monitoring for a20

col end_monitoring for a20

select * from v$object_usage;

说明： monitoring字段为YES 表示此索引已经被监控，NO未被监控

used字段为YES 表示此索引已经被使用，NO未被使用

start_monitoring与end_monitoring 表示上次监控区间

五 创建含特殊字段类型的表

创建具有ROWID及时间戳类型字段的表并插入数据

官方文档：SQL Reference -> 2 Basic Elements of Oracle SQL -> Datatypes ->搜索“ROWID”和“TIMESTAMP” WITH LOCAL TIME ZONE Datatype

1.创建JCH_R表并初始化数据

conn jch1/jch1

构造数据环境

create table jch_text

(

text1 varchar2(10),

text2 varchar2(10),

text3 date,

text4 varchar2(50)

);

插入含有‘Hmilyjch’关键字的记录

insert into jch_text values ('jch1','name',sysdate,'Hmilyjch');

insert into jch_text values ('jch2','name',sysdate,'HmilyjchHmilyjch');

insert into jch_text values ('jch3','name',sysdate,'HmilyjchHmilyjchHmilyjch');

insert into jch_text values ('jch4','name',sysdate,'HmilyjchHmilyjchHmilyjchHmilyjch');

commit;

select * from jch_text;

create table jch1.jch_r (text rowid,insert_time timestamp with local time zone) tablespace users;

rowid 字段类型

timestamp with local time zone 时间戳和本地时区字段类型

2.向JCH_R表插入记录

在jch_text表中检索记录，如果1条记录中有包含3个或者以上的“Hmilyjch”关键字，就把这条记录rowid和时间戳插入leonarding_r表

insert into jch1.jch_r (text,insert_time) select rowid,current_timestamp from jch1.jch_text

where length(text4)>=3*10;

注：current_timestamp函数：返回根据时区转换过的“日期”和“时间”，返回的秒是系统的

sysdate 函数：返回操作系统的日期和时间

length 字符串长度函数：取字段长度

commit;

select * from jch1.jch_r;

drop table jch1.jch_r;

六 Flashback闪回技术

场景：当误删除时如何恢复数据

官方文档：Application Developer’s Guide - Fundamentals -> 10 Developing Flashback Application -> Using Flashback Query (SELECT … AS OF)

1.Flashback Query闪回查询数据

原理：闪回查询使用的是undo表空间里存放的前映像

构造环境

drop table t5 purge;

create table t5 (x int);

insert into t5 values(1);

insert into t5 values(2);

insert into t5 values(3);

commit;

select * from t5;

2.为构造后续的闪回查询查询当前的时间和scn号

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

查询当前系统时间

select sysdate from dual;

查询当前系统SCN号

select dbms_flashback.get_system_change_number from dual; 473966

3.删除数据

delete from t5 where x=1;

commit;

select * from t5;

4.两种方法创建视图构造闪回查询删除之前的数据

1）第一种方法：使用时间戳来构造闪回查询视图

create view v_t5_timestamp as select * from t5

as of timestamp to_timestamp('2014-01-24 03:27:11','yyyy-mm-dd hh24:mi:ss');

2）第二种方法：使用SCN构造闪回查询视图

create view v_t5_scn as select * from t5 as of scn 473966;

注：scn 比 timestamp 更精确

查询视图闪回内容

select * from v_t5_timestamp;

select * from v_t5_scn;

drop view v_t5_timestamp;

drop view v_t5_scn;

到此，两种构造视图的方法都顺利的获得了闪回查询的数据

2.一张表被反复多次删除，要求恢复到指定的数据版本

原理：清空回收站

purge recyclebin;

create table t6 (x int);

insert into t6 values (1);

commit;

select * from t6;

drop table t6;

create table t6 (x int);

insert into t6 values (1);

insert into t6 values (2);

commit;

select * from t6;

drop table t6;

查询回收站数据字典

SQL> col OBJECT_NAME for a40;

SQL> col ORIGINAL_NAME for a20;

SQL> col TYPE for a10;

SQL> select object_name,original_name,type from recyclebin;

show recyclebin

获得t6表被drop的两个版本中哪个是我们需要恢复的对象，恢复有1条记录的t6表

select * from "BIN$8LXMoaS95DTgQKjAb5kW+A==$0";

select * from "BIN$8LXMoaS85DTgQKjAb5kW+A==$0";

闪回指定的版本->闪回同时重命名

flashback table "BIN$8LXMoaS95DTgQKjAb5kW+A==$0" to before drop rename to t6_new;

drop table t6_new;