11g OCM备考11——第三场，数据管理

最新推荐文章于 2021-04-04 00:51:49 发布

cijiayuan5672

最新推荐文章于 2021-04-04 00:51:49 发布

阅读量263

点赞数

文章标签：数据库

题目

1. DBLINK

Create DBLINK, should query on the woods database all the table under the system user in the woods3 Database.

2. Materialized View

2.1 create a Fast Refreshable Materialized View use script

Execute the createmv.sql script to create a materialized view PROD_MV in the SH schema in the PROD1 database. Ensure that the PROD_MV materialized view is fast refreshed and allows rewrites.

2.2 fast refreshable materialized view

Using the query‘Select a,b, count(*) cnt from t1 group by a,b' , create fast refreshable materialized view

2.3 create updateable materialized view

Using the HR.EMPLOYEES table in the PROD1 database. Create updateable materialized view EMP_UPD_MV consisting of following columns: EMPLOYEE_ID, FIRST_NAME, LAST_NAME

3. Manage Transport of tablespaces across platforms

场景：给1个dmp文件和1个其它平台【Solaris[tm] OE (32-bit)】的datfile文件，让其导入到prod库中

环境准备

3.1 模拟生成dmp文件

woods4库：

create directory dd1 as '/u01/app/oracle/oradata/dd1'

SQL> create user swz identified by swz default tablespace wood01;

SQL> grant dba to swz;

SQL> conn swz/swz;

SQL> create table test01 as select * from dba_objects where rownum<100;

SQL> select count(*) from test01;

COUNT(*)

----------

把表空间设置为只读

alter tablespace wood01 read only;

[oracle@ocm2 ~]$ expdp system/oracle dumpfile=wood01.dmp directory=dd1 transport_tablespaces=wood01

3.2

模拟生成其它平台【Solaris[tm] OE (32-bit)】的datafile文件

RMAN> convert tablespace wood01 to platform 'Solaris[tm] OE (32-bit)' db_file_name_convert='/u01/app/oracle/oradata/woods4','/u01/app/oracle/oradata/dd1';

把数据文件和表空间定义文件移动到目标端

mv wood* ../dd2

4. Configure a schema to support a star transformation query

(/home/oracle/scripts/star.sql)

(According to the provided SQL script using the star query at run time)

5. Administer external tables

5.1 ORACLE_LOADER external table

(1)Using the scripts in the in the scripts directories. You will found prod.dat, prod.ctl create external table PROD_MASTER in the HR schema

(2) append the data of table admin_ext_employees to the table employees

prod.dat:

1001,mary,alice,a@oracle.com,09/01/2004,IT_PROG,5500,0.1,101,10
2001,lisa,marie,b@oracle.com,01/01/2002,HR_REP,8000,0.2,102,20

prod.ctl

create TABLE PROD_MASTER

(

employee_id number(6),

first_name VARCHAR2(20),

last_name VARCHAR2(25),

email VARCHAR2(25),

hire_date DATE,

job_id VARCHAR2(10),

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

manager_id NUMBER(6),

department_id NUMBER(4) )

ORGANIZATION EXTERNAL

( TYPE ORACLE_LOADER

DEFAULT DIRECTORY D3

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

FIELDS TERMINATED BY ','

MISSING FIELD VALUES ARE NULL

(

employee_id ,first_name ,last_name ,email ,

hire_date CHAR date_format DATE mask "mm/dd/yyyy",

job_id ,salary ,commission_pct ,manager_id ,department_id )

)

LOCATION ('prod.dat')

);

（

5.2 ORACLE_DATAPUMP external table

(1)create an external table COUNTRIES_EXT in the PROD database owned by SH contains COUNTRY_ID, COUNTRY_NAME and COUNTRY_REGION columns of the SH.COUNTRIES table

(2)create another external table called COUNTRIES_EXT2 in the ORCL database owned by system the external files created in previous step

6. parallel execution

(1) 用户发出并行查询以后，能够根据实际并行资源情况，动态调整用户实际拿到的并行资源

(2) Requirements for maximum parallel slaves is 100, Permanent slave of minimum number is 10%.

(3) Set the table sh.sales parallel degree of 8, to ensure that the entire scene with the parallel degree.

(4) The running time of less than 5 seconds, should not use parallel.

(5) Make necessary changes to parall.sql, ensure it can parallel execution.

Do not drop or truncate the plan table after executing the script.

/home/oracle/scripts/

parall.sql: explain plan for SELECT /*+ PARALLEL 4 */ * from sh.sales;

7. data pump

7.1 Implement Data Pump export and import jobs for data transfer

从hr用户导出，导入到hr_test 用户

[oracle@ocm dd1]$ expdp system/oracle schemas=hr directory=dd1 dumpfile=hr.dmp

SQL> create tablespace hr_test datafile '/u01/app/oracle/oradata/woods/hr_test.dbf' size 50m;

SQL> create user hr_test identified by hr_test default tablespace hr_test quota unlimited on hr_test;

SQL> grant connect,resource to hr_test;

[oracle@ocm dd1]$ impdp system/oracle remap_schema=hr:hr_test directory=dd1 dumpfile=hr.dmp

SQL> select count(*) from hr_test.jobs;

COUNT(*)

----------

7.2 Implement Data Pump to and from remote databases

dblink就用上面的DBLINK，在woods端远程导出woods3上的swz schema

[oracle@ocm dd1]$ expdp system/oracle schemas=swz network_link=DBLINK directory=dd1 dumpfile=test_dblink.dmp

在woods端远程导出woods3上的swz.test01，并导入到woods上的表hr.test01

[oracle@ocm dd1]$ impdp system/oracle remap_schema=swz:hr network_link=DBLINK directory=dd1 tables=swz.test01

SQL> select count(*) from hr.test01;

COUNT(*)

----------

答案

1.

在woods端创建

create public database link DBLINK connect to system identified by oracle using 'woods3';

2.

2.1

快速刷新必须有物化视图的日志，这个日志名字无需指定，系统自己命名

SQL> CREATE MATERIALIZED VIEW LOG ON sh.products with rowid,sequence(PROD_LIST_PRICE,PROD_MIN_PRICE,PROD_CATEGORY) including new values;

CREATE MATERIALIZED VIEW prod_mv REFRESH FAST

ENABLE QUERY REWRITE AS

SELECT SUM(prod_list_price-prod_min_price),count( prod_category),prod_category

FROM sh.products GROUP BY prod_category;

2.2

create table sh.t1 ( a varchar2(20), b varchar2(20));

CREATE MATERIALIZED VIEW LOG ON sh.t1 WITH ROWID(a,b) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW ma_t1 REFRESH FAST AS Select a,b, count(*) cnt from sh.t1 group by a,b;

2.3

CREATE MATERIALIZED VIEW LOG ON HR.EMPLOYEES WITH ROWID

("EMPLOYEE_ID', "FIRST_NAME", "LAST_NAME") , SEQUENCE INCLUDING NEW VALUES

CREATE MATERIALIZED VIEW "HR"."EMP_UPD_MV"

USING INDEX REFRESH COMPLETE WITH ROWID ON DEMAND FOR UPDATsE

ENABLE QUERY REWRITE AS select EMPLOYEE_ID, FIRST_NAME, LAST_NAME from EMPLOYEES

3.

wood3端

3.3

转换生成目标平台格式的数据文件

RMAN> convert datafile '/u01/app/oracle/oradata/dd2/woods01.dbf' from platform 'Solaris[tm] OE (32-bit)' db_file_name_convert='/u01/app/oracle/oradata/dd2','/u01/app/oracle/oradata';

3.4

导入表空间

SQL> create user swz identified by swz;

[oracle@ocm2 oradata]$ impdp system/oracle directory=dd2 dumpfile=wood01.dmp transport_datafiles='/u01/app/oracle/oradata/woods01.dbf'

SQL> select count(*) from swz.test01;

COUNT(*)

----------

SQL> alter database wood01 read write;