欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

10gocm-)session5-)数据库管理实验

程序员文章站 2022-05-07 09:05:28
...

Oracle数据库管理实验 一 传输表空间 二 创建分区表和分区索引 三 FGA细粒度审计 四 监控索引使用情况 五 创建含特殊字段类型的表 六 Flashback闪回技术 一 传输表空间,将ocmdb库的tstrans表空间迁移到ocmdb02库中 场合:1.适用于OLAP数据仓库应用 2.数据迁

Oracle数据库管理实验


一 传输表空间
二 创建分区表和分区索引
三 FGA细粒度审计
四 监控索引使用情况
五 创建含特殊字段类型的表
六 Flashback闪回技术


一 传输表空间,将ocmdb库的tstrans表空间迁移到ocmdb02库中
场合:1.适用于OLAP数据仓库应用 2.数据迁移 比exp/imp速度快,不仅迁移数据同时迁移元数据
原理:只导出表空间的元数据(即结构信息),导入目标数据库,把表空间设置为只读,把对应的数据文件复制到目标系统的目标目录,挂载上导入的表空间,在把表空间设置为读写。
官方文档:Administrator’s Guide-> 8 Managing Tablespace -> Transport Tablespace Between databases
SQL Reference -> CREATE DIRECTORY
清理环境
drop user tstrans cascade;
drop tablespace tstrans including contents and datafiles;
1.初始化实验环境
sqlplus sys/sys@ocmdb1521 as sysdba
create tablespace tstrans datafile '/u01/oracle/oradata/ocmdb/tsport01.dbf' size 20m autoextend off;
create user tstrans identified by tstrans default tablespace tstrans;
grant connect,resource to tstrans;
conn tstrans/tstrans
create table t1 (a int) tablespace tstrans;
insert into t1 values (100);
commit;
create index idx_t1 on t1 (a) tablespace tstrans;
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='TSTRANS'; 查看tstrans表空间有哪些对象
2.检查tstrans表空间是否违反约束条件
exec dbms_tts.transport_set_check('TSTRANS',true);
例如表在A表空间,索引在B表空间,如果只传输A的话,就会违反自包含约束条件,表上的索引就会失效,传输不完整,解决方法:同时传输A和B两个表空间。
select * from transport_set_violations; 查看违反约束列表,没有记录为正常
3.设置TSTRANS为只读表空间,即把所有数据文件都设置成只读状态。
alter tablespace tstrans read only;
4.使用exp或expdp的transport_tablespace=y参数导出表空间的元数据 即结构信息
exp -help 打开帮助文档可以查看其选项说明
exp userid=\'/ as sysdba\' transport_tablespace=y tablespaces=tstrans file=/home/oracle/exp_tstrans.dmp
使用SYS用户只导出tstrans表空间的元数据(结构信息),而不是真实数据,因此容量比较小
scp exp_tstrans.dmp ocm02:/home/oracle 传输到第二台机器上
数据泵导出方式选做
【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_tstrans.dmp transport_tablespaces=tstrans transport_full_check=y

5.拷贝数据文件(表空间真正的数据)只有数据文件为只读状态才可以复制,而且不用停库
scp tstrans01.dbf ocm02:/u01/oracle/oradata/ocmdb02/
6.imp或impdp的传输表空间导入
LEO2库的准备工作
create user tstrans identified by tstrans;
grant connect,resource to tstrans;
使用imp导入方式 默认以追加方式插入表空间元数据+数据文件
imp userid=\'/ as sysdba\' file=/home/oracle/exp_tstrans.dmp fromuser=tstrans touser=tstrans transport_tablespace=y tablespaces=tstrans datafiles=/u01/oracle/oradata/ocm02/tstrans01.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_tstrans.dmp remap_schema=(tstrans:tstrans) 对象从一个schema加载到另一个schema
transport_datafiles=/u01/oracle/oradata/ocm02/tstrans01.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='TSTRANS';
conn tstrans/tstrans
select * from t1;
7.将ocmdb实例和ocmdb02实例表空间调整为可读写状态
sqlplus sys/sys@ocmdb1521 as sysdba
conn / as sysdba
select tablespace_name,status from dba_tablespaces;
alter tablespace tstrans read write;
select tablespace_name,status from dba_tablespaces;
sqlplus sys/sys@ocmdb021521 as sysdba
conn / as sysdba
select tablespace_name,status from dba_tablespaces;
alter tablespace tstrans 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.设置非标准块
ocm01


alter system set db_16k_cache_size=80M; 设置非标准块16K缓冲区,用于存放非标准块
show parameter db_16k_cache_size
作用:用于减少物理I/O读写次数,原本读100次可以完成的数据,现在读50次就完成了
create user ocm01 identified by ocm01;
grant dba to ocm01;


2.创建4个表空间,一个分区对应一个表空间
conn ocm01/ocm02


drop table t2_part;
drop index 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 '/u01/oracle/oradata/ocmdb/disk1/part1_01.dbf' size 50M
extent management local
blocksize 16k;
create tablespace part2 datafile '/u01/oracle/oradata/ocmdb/disk2/part2_01.dbf' size 50M
extent management local
blocksize 16k;
create tablespace part3 datafile '/u01/oracle/oradata/ocmdb/disk3/part3_01.dbf' size 50M
extent management local
blocksize 16k;
create tablespace part4 datafile '/u01/oracle/oradata/ocmdb/disk4/part4_01.dbf' size 50M
extent management local
blocksize 16k;
select * from v$tablespace;
2.构造分区表数据
conn tstrans/tstrans
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.ocm01用户下创建分区表
conn ocm01/ocm01
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 tstrans.t2;
查看分区表数据
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from t2_part;
查看某一个分区
select * from t2_part partition (p1);
4.创建hash全局分区索引
hash分区索引:均匀打散后存放数据,拿一列作hash打散,均匀分布在4个分区上,每个分区在不同表空间上的记录数都差不多,并发读并发写
官方文档:SQL Reference -> CREATE TABLE and CREATE INDEX
conn ocm01/ocm01
注:把全局分区索引(按照name列做hash打散)均匀分成4份,每份都保存在LEOINDEX表空间里
create unique index idx_t2_part on t2_part (name,itemid)global partition by hash (name) partitions 4 tablespace LEOINDEX 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=>'ocm01',object_name=>'t',policy_name=>'audit_t');
conn ocm01/ocm01
drop table t purge; 删除待审计的表
2.创建待审计的T表
conn ocm01/ocm01
create table t (x number(10),y varchar2(20)); 创建待审计的表t
3.创建审计策略
conn / as sysdba
begin
dbms_fga.add_policy (
object_schema => 'ocm01', 审计谁
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 leo1/leo1
insert into t values (10,'first');
insert into t values (100,'dfs');
insert into t values (200,'dsf');
insert into t values (300,'sdf');
insert into t values (400,'sdgdg');
insert into t values (500,'sdg');
insert into t values (600,'sdgsdgs');
select * from t; 查看审计表的内容
6.查看审计结果,默认会把审计结果放在SYS.FGA_LOG$基表中
注:审计会对INSERT,UPDATE,DELETE,SELECT这四种语句都做检查,并且记录谁 操作的哪个表 执行的什么语句
select OBJ$SCHEMA,OBJ$NAME,POLICYNAME,LSQLTEXT from SYS.FGA_LOG$;
set lines 200
col sql_text for a35
col object_schema 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 ocm01/ocm01
drop table t4;
create table t4 as select * from dba_objects;
create index idx_t4 on t4(object_id);
开启LEO1下idx_t4索引的监控
alter index idx_t4 monitoring usage;
停止LEO1下idx_t4索引的监控
alter index 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.创建LEONARDING_R表并初始化数据
conn ocm01/ocm01
构造数据环境
create table ocm01_text
(
text1 varchar2(10),
text2 varchar2(10),
text3 date,
text4 varchar2(50)
);
插入含有‘hugh’关键字的记录
insert into ocm01_text values ('hugh','name',sysdate,'hugh');
insert into ocm01_text values ('hugh2','name',sysdate,'hugh');
insert into ocm01_text values ('hugh3','name',sysdate,'hugh');
insert into ocm01_text values ('hugh4','name',sysdate,'hugh');
commit;
select * from ocm01_text;
create table ocm01_r (text rowid,insert_time timestamp with local time zone) tablespace users;
rowid 字段类型
timestamp with local time zone 时间戳和本地时区字段类型


2.向LEONARDING_R表插入记录
在leonarding_text表中检索记录,如果1条记录中有包含3个或者以上的“Leonarding”关键字,就把这条记录rowid和时间戳插入leonarding_r表
insert into ocm01_r (text,insert_time) select rowid,current_timestamp from ocm01_text;


注:current_timestamp函数:返回根据时区转换过的“日期”和“时间”,返回的秒是系统的
sysdate 函数:返回操作系统的日期和时间
length 字符串长度函数:取字段长度
commit;
select * from ocm01_r;
drop table ocm01_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;
SYSDATE
-------------------
2014-01-14 21:27:23


查询当前系统SCN号
select dbms_flashback.get_system_change_number from dual; 331299
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-14 21:27:23','yyyy-mm-dd hh24:mi:ss');


2)第二种方法:使用SCN构造闪回查询视图
create view v_t5_scn as select * from t5 as of scn 331299;
注: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;
查询回收站数据字典
select object_name,original_name,type from recyclebin;
show recyclebin
获得t6表被drop的两个版本中哪个是我们需要恢复的对象,恢复有1条记录的t6表
select * from "BIN$7+8KsHBFHoPgQKjAZRUk9w==$0";
select * from "BIN$7+8KsHBEHoPgQKjAZRUk9w==$0";
闪回指定的版本->闪回同时重命名
flashback table "BIN$7+8KsHBEHoPgQKjAZRUk9w==$0" to before drop rename to t6_new;
drop table t6_new;

总结自ocm实验选讲