Scenario 4 Data Warehouse Management DW

Scenario 4 Data Warehouse Management DW

Parallel 4 100%-> must obtain a specified 4 degree of parallelism, if the number of processes obtained is less than the number of degrees of parallelism set, the operation fails

Parallel_min_percent: If set to 100, as above

ILM: Information Lifecycle Management

High compression of dormant data on low-cost channels (e.g. tape drives)

Low-volume data compression on low-access devices

High-access data is not compressed and placed on highly efficient devices

Partitions (official documents VLDB and Partioning Guide)

The single-sheet capacity is larger than 2G, it is recommended to partition

1. Each partition is a separate segment, stored separately in the Tablespace (TBS disaster prevention on one isolated storage) I/O

2. Manage backup Add new partition, delete, truncate, split, merge, table Exchange data

Secure Password Support

1. Case-sensitive

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; (Cancellation of password complexity verification)

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: Manually unlock Scott users

Conn/as SYSDBA

Desc dba_users;

Select Username, account_status from dba_users;

Alter user Scott identified by Tiger account unlock;

Transparent data encryption (TDE)

1. Table Space Encryption

2. Logminer Log Mining

3. Support the logical Standby library

4. Support Flow

5. Support Asynchronous data Change crawl

6.

3des:3 * 56 = 168

aes:128

Eg: Enable 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 Large Object implant

LOB: Large objects (large objects are stored separately in another table space, and tables are not in the same tablespace)

CLOB: Large text, such as logs (stored in a database)

BLOB: unstructured (binary) slices, video, music (stored in database)

BFILE: Stored in the OS in file mode

VARCHAR2 4000 Characters

Book: In-depth understanding of Oracle 12c database management

Eg:

CREATE TABLE Lob1 (ID number, Desc_comm clob) tablespace users LOB (DESC_COMM) store as basic file; (creating a table containing large objects)

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;

Large objects are not cached by default, resulting in large I/O overhead

Securefile to enable caching

Securefile Compression

Low: fast read/write

Medium:default

High

OCM Exam Score: Recording frequency, background data acquisition

Table space for large objects must be ASSM automatic segment space management

Raw Bare Device No cache

Basicfile migration to Securefile: online redefinition (does not affect access to tables)

INSERT INTO SELECT * from CLOB;

Logminer: Log Mining

Flash Back Data archive

Flashback query (only for Dml,undo segments)

Flashback table (for Dml,undo segment only)

Flashback version query (undo segment)

Flashback Transaction query (undo segment)

Flashback drop

Flashback Archive (Flashback Data Archive, for DML only, saves all DML operations on the table to a single table archive, which can be used to persist data changes, complementing the flashback query and flash back table)

Flashback database (Imcomplete recovery is not fully recovered, restores the databases to a point in time in the past)

User error Operation Recovery

Physical Backup: datafile, control file, redo log

Media Recovery:

1. Restore (Backup recovery)

2. Recover

TSPITR: Non-full recovery of time points based on table space

Eg:

Select Tablespace_name, Extent_management, segment_space_management from Dba_tablespaces;

Select Name, flashback_on from V$databasel

Show parameter recover

Eg:

1. Set up flashback dedicated table space

2. Establishment of Flashback archive

3. Modify Flashback Archive Properties

4. View 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;

Enable flashback archive on the table:

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 to 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: The snapshot is too old to access the undo block when the data is no longer in

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: Up to 20 MB per table

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: Clear Sky 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 Partition interval Partitioning

1. Extension of range partition

2. If the inserted data does not belong to the partition that is already divided, the new partition can be added automatically

3. At least one range partition is built

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;

Clone reply

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; (Change auto-assigned partition sysp41 named p2)

If there is no interval partition, you will get an error when inserting data that is not part of the partition

System partition

1. Open the application-controlled partition on the selected table

2. There is an application to control the partition and data location

3. Do not apply partition keys like other partitioning methods

4. Partition pruning is not supported

5. Data can be inserted into the specified partition

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

Poor performance if frequently accessed data is cross-partitioned

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; (Delay segment, empty table also allocates storage space)

Insert INTO Apps Partiton (p1) VALUES (10, 1000);

Commit

SELECT * from apps partition (P1);

SELECT * from apps partition (P3);

Combining partitions

1. Range-to-range partitioning

2.

3.

4.

Virtual Column Partitioning

1. Virtual column values are generated by a function or an expression

2. You can create a virtual column when you create or modify a table

3. Do not consume storage

4. You can create an index for a virtual column

5. Create partitions and collect statistics on virtual columns

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 (+), partition P2 values less than (+), partition p3 values less th An (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;

Reference partitioning

1. Referential constraints

2. partition key exists between parent table and child table

3. Forcing the primary foreign key constraint on the partition key

4. Child tables inherit partitions on the parent table

5. Partitions can be automatically maintained (when a parent table adds a new partition, the child table also adds a new partition) any partition maintenance operations on the parent table are automatically synchronized to the child table

Eg:

CREATE TABLE orders (ord_id number, ord_date date, constraint ORD_PK primary key (ORD_ID) partition by Range (ord_date) (par Tition 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 prima Ry Key (line_id, ord_id), Constraint ORD_ITEMS_FK1 foreign key (ord_id) references orders (ORD_ID)) partition by reference (O RD_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);

Scenario 4 Data Warehouse Management DW