选择schema和表以后点击这里
2.创建Materialized View
填写题目中给出的SQL语句
按照题目要求指定刷新方式
CREATE MATERIALIZED VIEW LOG ON costs
WITH SEQUENCE
(prod_id, unit_cost, time_id, channel_id, promo_id, unit_price) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON products WITH SEQUENCE, ROWID,PRIMARY (/*所有字段*/)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW PROD_MV PCTFREE 0 TABLESPACE demo
STORAGE (INITIAL 8k NEXT 8k PCTINCREASE 0) BUILD IMMEDIATE REFRESH FAST
ENABLE QUERY REWRITE
AS SELECT time_id,prod_subcategory,SUM(unit_cost), COUNT(unit_cost),COUNT(*) FROM costs c.products p where c.prod_id=p.prod_id
GROUP BY time_id,prod_subcategory;
2.Creating an Updatable Materialized View
2.1. Using the HR.EMPLOYEES table in the PROD database. create an updatable materialized view in the EMREP database named EMP_UPD_MV consisting of the following columns: EMPLOYEE_ID,FIRST_NAME,LAST_NAME,PHONE_NUMBER,SALARY.
1.创建database link
create database link lk_prod connect to hr identified by hr using 'prod'; 2.验证database link是否可用 select
EMPLOYEE_ID,FIRST_NAME,LAST_NAME,PHONE_NUMBER,SALARY
from
hr.employees@lk_prod; 3.创建Updatable Materialized View
按照题目要求指定刷新方式
3.Oracle_Loader External Tables
3.1. In the scripts directory. you will find prod_master.dat and prod_master.ctl.Using the information found in these files.create and external table named PROD_MASTER in the SH schema of the PROD database.
CREATE TABLE sh.prod_master
(根据prod_master.ctl定义表 )
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER DEFAULT DIRECTORY dir1 LOCATION ('prod_master.dat') )
PARALLEL
REJECT LIMIT UNLIMITED;
4. Oracle_Datapump External Table
4.1. Create an external table called COUNTRIES_EXT in the PROD database owned by SH. containing the data from the COUNTRY_ID,COUNTRY_NAME,and COUNTRY_REGION columns of the SH.COUNTRIES table.
1.创建目录
Create directory dir1 as '/home/oracle/'; Grant read,write on directory dr1 to sh; 2.创建外部表
CREATE TABLE sh.COUNTRIES_EXT ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP