场景4 Data Warehouse Management 数据仓库

场景4 Data Warehouse Management 数据仓库

 

parallel 4 100% —> 必须获得指定的4个并行度，如果获得的进程个数小于设置的并行度个数，则操作失败

 

parallel_min_percent : 若设为100，则如上

 

ILM ：信息生命周期管理

将休眠的数据高压缩放在低成本通道上（如磁带机）

将低访问量的数据低压缩放在访问效率低的设备上

将高访问数据不作压缩放在访问效率高的设备上

 

分区 （官方文档VLDB and Partioning Guide)

单张表容量大于2G, 则建议分区

1. 每个分区为一个独立的segment, 单独存储在tablespace(tbs防灾一个独立存储上) —> I/O

2. 管理  备份 增加新分区，删除，truncate, split, merge, 表交换数据

 

 

安全口令支持

1. 区分大小写

 

eg :

alter user scott identified by scott;

alter user scott identified by 1;

alter user scott identified by tiger;

 

eg:

desc dba_profiles

select * from dba_profiles;

select * from dba_profiles where profile=“DEFAULT’;

 

eg :

cd /u01/app/oracle/rdbms/admin

ls

 

!sql

@?/rdbms/admin/utlpwdmg.sql

select * from dba_profiles where profile=“DEFAULT’;

 

alter profile default limit password_verify_function verify_function_11g;

alter profile default limit password_verify_function null; (取消口令复杂性验证）

show paramter sec

 

eg :

conn /as sysdba

alter profile default limit failed_login_attempts3;

select * from dba_profiles where profile=“DEFAULT’;

show parameter sec

conn scott/t1

conn scott/t2

conn scott/t3

 

eg : 手工解锁scott用户

conn /as sysdba

desc dba_users;

select username, account_status from dba_users;

alter user scott identified by tiger account unlock; 

 

 

透明数据加密(TDE)

1. 表空间加密

2. LogMiner日志挖掘

3. 支持逻辑备库

4. 支持流

5. 支持异步数据改变抓取

6.

 

3DES : 3 * 56 = 168

AES : 128

 

eg : 启用TDE

cd $ORACLE_HOME/network/admin (ENCRYPTION_WALLET_LOCATION : u01/app/oracle/product/11.2.0/db_1/network/admin)

ls -l

cat sqlnet.ora

 

u01/app/oracle/admin/prod/wallet

alter system set encryption key identified by “<password>”;

 

select tablespace_name, encrypted from dba_tablespace;

 

LOB Implementation大对象植入

 

LOB : 大对象 (大对象单独存放在另外一个表空间里，和表不在同一个表空间）

CLOB ：大的文本，如日志 （存储在数据库里）

BLOB ：非结构化（二进制）如图片，视频，音乐 （存储在数据库里）

BFILE ：以文件方式存储在OS里

 

varchar2 4000字符

 

书 ：深入理解oracle 12c数据库管理

 

eg :

create table lob1(id number, desc_comm clob) tablespace users lob(desc_comm) store as basic file;(创建一个表，内含大对象）

select segment_name, segment_type, tablespace_name from user_segments;

 

show parameter secur

 

eg :

desc dba_tablespaces;

select tablespace_name, extent_management, segment_space_management from dba_tablespaces;

 

默认不对大对象进行缓存，造成很大I/O开销

SecureFile可开启缓存

 

SecureFile Compression

low : 快速读写

medium : default

high

 

OCM考试评分 ：录频，后台采集数据

存放大对象的表空间必须是ASSM自动段空间管理

raw 裸设备无缓存

 

basicfile迁移到securefile : 在线重定义（不影响对表的访问）

insert into select * from clob；

 

Logminer : 日志挖掘

 

 

闪回数据归档

flashback query （只用于DML，undo段）

flashback table（只用于DML，undo段）

flashback version query（undo段）

flashback transaction query（undo段）

flashback drop

flashback archive （闪回数据归档，只用于DML，将对表的所有DML操作保存到一张表里archive, 可永久保存数据变化，是对闪回查询和闪回表的补充）

flashback database （imcomplete recovery不完全恢复，将数据库恢复到过去的一个时间点）

用户误操作恢复

物理备份 ：datafile, control file, redo log

介质恢复 ：

1. restore（备份恢复）

2. recover

 

TSPITR : 基于表空间的时间点不完全恢复

 

eg :

select tablespace_name, extent_management, segment_space_management from dba_tablespaces;

select name, flashback_on from v$databasel

show parameter recover

 

eg :

1. 建立flashback专用表空间

2. 建立flashback archive

3. 修改flashback archive属性

4. 查看flashback archive

eg :

select name from v$datafile;

create tablespace ftbs1 datafile ‘/u01/app/oracle/oradata/prod/ftbs1.dbf’ size 100m autoextend on maxsize 2g;

 

eg :

create flashback archive farch1 tablespace flash_tbs quota 20m retention 1 year;

 

eg : desc dba_flashback_archive;

select flashback_archive_name, retention_in_days, status from dba_flashback_archive;

col flashback_archive_name for a20

alter flashback archive farch1 set default;

 

 

在表上启用flashback archive ：

eg :

grant flashback archive on farch1to scott;

conn scott/tiger

select * from tab;

create table emp1 as select * from emp;

select * from emp1;

insert into emp1 select * from emp1;

commit;

insert into emp1 select * from emp1;

commit;insert into emp1 select * from emp1;

commit;

select count(*) from emp1;

alter table emp1 flashback archive;

select * from tab;

select * from emp;

delete from emp;

commit;

set autotrace on

select * from emp as of timestamp to_timestamp(‘2016-07-24 15:30:51’, ‘yyyy-mm-dd hh:mm:ss’);

 

eg :

show parameter undo

create undo tablespace undotbs2 datafile ‘/u01/app/oracle/oradata/prod/undotbs2.dbf’ size 100m;

show parameter undo

alter system set undo_tablespace=undotbs2;

drop tablespace undotbs1 including contents and datafiles;

select * from emp as of timestamp to_timestamp(‘2016-07-24 15:30:51’, ‘yyyy-mm-dd hh:mm:ss’);

ora01555 : 快照过旧，访问undo块时，数据已经不在

 

eg :

select count(*) from emp1;

delete from emp1;

commit;

select * from emp1;

select * from emp1 as of timestamp to_timestamp(‘2016-07-24 15:21:45’ yyyy-mm-dd hh:mm:ss);

 

 

select sysdate from 

select * from tab;

set autotrace on

select * from emp1 as of timestamp to_timestamp(‘2016-07-24 15:21:45’ yyyy-mm-dd hh:mm:ss);

 

 

note : quota 20m : 每个表最多20兆

 

eg :

select * from emp1 as of timestamp to_timestamp(‘2016-07-24 15:18:45’ yyyy-mm-dd hh:mm:ss);

 

eg :

insert into emp1select * from emp1 as of timestamp to_timestamp(‘2016-07-24 15:21:45’ yyyy-mm-dd hh:mm:ss);

commit;

insert into emp select * from emp1;

alter table emp disable novalidate constraint pk_emp;

insert into emp select * from emp1;

select * from emp;

alter table emp flashback archive;

insert into emp select * from emp where rownum < 11;

select count(*) from emp;

commit

set autotrace trace

select * from emp as of timestamp to_timestamp(‘2016-07-24 15:40:42’, ‘yyyy-mm-dd hh:mm:ss’);

 

eg : 晴空buffer cache

alter system flush buffer_cache;

/

create tablespace undotbs1 file ‘u01/app/oracle/oradata/prod/undotbs1.dbf’ size 100m autoextend on;

alter system set undo_

drop table emp1;

truncate table emp1;

select * from emp as of timestamp to_timestamp(‘2016-07-24 15:46:42’, ‘yyyy-mm-dd hh24:mi:ss’);

select * from emp as of timestamp to_timestamp(‘2016-07-24 15:45:42’, ‘yyyy-mm-dd hh24:mi:ss’);

insert into emp 1 select * from emp1 where rownum<4;

commit

select * from emp1;

insert into emp 1 select * from emp1;

drop table emp1;

truncate table emp1;

select * from emp1;

select * from emp as of timestamp to_timestamp(‘2016-07-24 15:53:42’, ‘yyyy-mm-dd hh24:mi:ss’);

 

 

 

 

 

 

 

间隔分区Interval partitioning

1. Range范围分区的扩展

2. 若插入的数据不属于已经分好的分区，可自动增加新分区

3. 至少有一个范围分区建好

 

eg :

select name from v$datafile;

create tablespace tbs1 datafile ‘/u01/app/oracle/oradata/prod/tbs01.dbf’ size 50m;

create tablespace tbs2 datafile ‘/u01/app/oracle/oradata/prod/tbs02.dbf’ size 50m;

create tablespace tbs3 datafile ‘/u01/app/oracle/oradata/prod/tbs03.dbf’ size 50m;

su -oracle

conn scot/tiger

alter user scott identified by tiger account unlock;

克隆回话

create table pt1 (sales_amt number, d_date date) partition by range (d_date) interval(numtoyminterval(1, ‘year’)) store in (tbs1, tbs2, tbs3) (partition1 values less than (to_date(/01-01-2013’, ‘dd-mm-yyyy’)) tablespace tbs1;

insert into table pt1 values (1, sysdate+1000);

commit;

desc user_segments

col segment_name for a20

select segment_name, partition_name, segment_type, tablespace_name, bytes/1024 from user_segments where segment_name=‘PT1’;

R

select * from pt1;

select segment_name, partition_name, segment_type, tablespace_name, bytes/1024 from user_segments where segment_name=‘PT1’;

select * from pt1 partition(sys_p41);

alter table pt1 rename partition SYSP41 to p2; (改变自动分配的分区sysp41名为p2）

若无间隔分区，则插入不属于分区的数据时会报错

 

 

系统分区

1. 对选择的表开启应用控制的分区

2. 有应用控制分区和数据位置

3. 不像其他分区方法应用分区键

4. 不支持分区修剪

5. 可以将数据插入到指定的分区

eg :

set autotrace on

select * from pt1 where SALES_AMt=1;

analyze table pt1 compute statistics;

select * from pt1 where SALES_AMt=2;

select * from pt1 where D_DATE=’01-oct-12’;

r

 

若经常访问的数据跨分区，则性能不好

 

eg :

crate table apps (app_id number, app_amnt number) partition by sytem (partition p1, partition p2, partition p3);

set autotrace on;

select segment_name, partition_name, segment_type, tablespace_name, bytes/1024 from user_segments where segment_name=‘APPS’;

insert into apps partiton(p3) values (10, 1000);

commit;

select segment_name, partition_name, segment_type, tablespace_name, bytes/1024 from user_segments where segment_name=‘APPS’;

set autotrace off

show parameter seg

alter system set deffered_segment_creation=false; (延迟段，空表也分配存储空间)

insert into apps partiton(p1) values (10, 1000);

commit;

select * from apps partition(p1);

select * from apps partition(p3);

 

 

 

组合分区

1. 范围到范围分区

2. 

3. 

4. 

 

 

 

 

虚拟列分区

1. 虚拟列值有函数或表达式生成

2. 可在创建或修改表时创建虚拟列

3. 不占用存储

4. 可为虚拟列创建索引

5. 虚拟列上可建立分区和收集统计信息

 

eg :

create table tmp1(emp_id number, sal number, comm_pct number, commission generated always as (sal*comm_pct)) partition by range(commission) (partition p1 values less than (1000), partition p2 values less than (2000), partition p3 values less than (maxvalue));

desc tmp2;

insert into tmp1 (EMP_ID, SAL, COMM_PCT) values (10, 5000, 10);

select * from tmp1;

set autotrace on

select * from tmp1 where emp_id = 10;

select * from tmp1 where commission = 50000;

insert into tmp1 (EMP_ID, SAL, COMM_PCT) values (10, 5000, 0.1);

commit;

select * from tmp1 where commission = 500;

 

参考性分区

1. 参考性约束

2. 分区键存在父表和子表之间

3. 在分区键上强制建立主外键约束

4. 子表继承父表上的分区

5. 分区可被自动维护（父表增加新的分区时，子表也增加新的分区）对父表进行的任何分区维护操作都会自动同步到子表

 

eg :

create table orders(ord_id number, ord_date date, constraint ord_pk primary key (ord_id) partition by range(ord_date) (partition p11 values less than (…), partition p12 values less than (), partition pmax values less than ());

create table ord_items (line_id number, ord_id number not null, sku number, quantity number, constraint ord_items_pk primary key(line_id, ord_id),constraint ord_items_fk1 foreign key(ord_id) references orders(ord_id) ) partition by reference (ord_items_fk1);

 

desc user_segments

select segment_name, partition_name, segment_type from user_segments where segment_name in (‘ORDERS’, ‘ORD_ITEMS’);

 

insert into ord_items values (10, 100, 1000, 100);

commit;

select * from ord_items;

set autotrace on

select a.ord_id, a.ord_date, b.sku from orders a, ord_items b where a.ord_id = b.ord_id;

 

analyze table orders compute statistics;

select a.ord_id, a.ord_date, b.sku from orders a, ord_items b where a.ord_id = b.ord_id and ord_date = ’15-JUN-12’;

create index item_ordid on ord_items(ord_id);

analyze index item_ordid compute statistics;

select a.ord_id, a.ord_date, b.sku from orders a, ord_items b where a.ord_id = b.ord_id;

set autotrace off

select * from orders partition(p12);

select * from ord_items partition(p12);

select * from ord_items partition(p11);