当前位置：Gxlcms > 数据库问题 > Oracle 12c 多租户家族（12c 18c 19c）如何在 PDB 中添加 HR 模式

Oracle 12c 多租户家族（12c 18c 19c）如何在 PDB 中添加 HR 模式

时间：2021-07-01 10:21:17 帮助过：3人阅读

（12c [12.2.0.1]、18c [12.2.0.2]、19c [12.2.0.3]）如何在 PDB 中添加模式：19c (19.3) 手工添加示例 HR 用户

Oracle 12c 如何在 PDB 中添加 SCOTT 模式（手工方式）

Oracle 12c 如何在 PDB 中添加 SCOTT 模式（数据泵方式）

目录

1. 配置TNSNAMES.ora

2. 连接PDB

3. 执行脚本

 

1. 配置TNSNAMES.ora

略，请参考 12c 手工方式和 12c 数据泵方式

2. 连接PDB

[oracle@henry ~]$ sqlplus sys/oracle@pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 7 23:45:27 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME 　OPEN MODE  RESTRICTED
---------- --------- ---------- ----------
     　　3  PDB1 　　  READ WRITE NO

3. 执行脚本

SQL> @/home/oracle/hr.sql

脚本明细（我很长）

技术图片 
DROP USER hr CASCADE;
CREATE USER hr IDENTIFIED BY hr;
ALTER USER hr DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
ALTER USER hr TEMPORARY TABLESPACE temp;
 
GRANT create session
     , create table
     , create procedure
     , create sequence
     , create trigger
     , create view
     , create synonym
     , alter session
TO hr;

GRANT execute ON sys.dbms_stats TO hr;

conn hr/hr@pdb1;
CREATE TABLE regions
    ( region_id      NUMBER 
       CONSTRAINT  region_id_nn NOT NULL
    , region_name    VARCHAR2(25) 
    );
 
CREATE UNIQUE INDEX reg_id_pk
ON regions (region_id);
 
ALTER TABLE regions
ADD ( CONSTRAINT reg_id_pk
             PRIMARY KEY (region_id)
    ) ;
 

CREATE TABLE countries 
    ( country_id      CHAR(2) 
       CONSTRAINT  country_id_nn NOT NULL
    , country_name    VARCHAR2(40) 
    , region_id       NUMBER 
    , CONSTRAINT     country_c_id_pk 
                 PRIMARY KEY (country_id) 
    ) 
    ORGANIZATION INDEX; 
 
ALTER TABLE countries
ADD ( CONSTRAINT countr_reg_fk
             FOREIGN KEY (region_id)
              REFERENCES regions(region_id) 
    ) ;
 

CREATE TABLE locations
    ( location_id    NUMBER(4)
    , street_address VARCHAR2(40)
    , postal_code    VARCHAR2(12)
    , city       VARCHAR2(30)
    CONSTRAINT     loc_city_nn  NOT NULL
    , state_province VARCHAR2(25)
    , country_id     CHAR(2)
    ) ;
 
CREATE UNIQUE INDEX loc_id_pk
ON locations (location_id) ;
 
ALTER TABLE locations
ADD ( CONSTRAINT loc_id_pk
             PRIMARY KEY (location_id)
    , CONSTRAINT loc_c_id_fk
             FOREIGN KEY (country_id)
              REFERENCES countries(country_id) 
    ) ;
 
 
CREATE SEQUENCE locations_seq
 START WITH     3300
 INCREMENT BY   100
 MAXVALUE       9900
 NOCACHE
 NOCYCLE;

CREATE TABLE departments
    ( department_id    NUMBER(4)
    , department_name  VARCHAR2(30)
    CONSTRAINT  dept_name_nn  NOT NULL
    , manager_id       NUMBER(6)
    , location_id      NUMBER(4)
    ) ;
 
CREATE UNIQUE INDEX dept_id_pk
ON departments (department_id) ;
 
ALTER TABLE departments
ADD ( CONSTRAINT dept_id_pk
             PRIMARY KEY (department_id)
    , CONSTRAINT dept_loc_fk
             FOREIGN KEY (location_id)
              REFERENCES locations (location_id)
     ) ;
 
CREATE SEQUENCE departments_seq
 START WITH     280
 INCREMENT BY   10
 MAXVALUE       9990
 NOCACHE
 NOCYCLE;
 
CREATE TABLE jobs
    ( job_id         VARCHAR2(10)
    , job_title      VARCHAR2(35)
    CONSTRAINT     job_title_nn  NOT NULL
    , min_salary     NUMBER(6)
    , max_salary     NUMBER(6)
    ) ;
 
CREATE UNIQUE INDEX job_id_pk 
ON jobs (job_id) ;
 
ALTER TABLE jobs
ADD ( CONSTRAINT job_id_pk
             PRIMARY KEY(job_id)
    ) ;
 
CREATE TABLE employees
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
     CONSTRAINT     emp_last_name_nn  NOT NULL
    , email          VARCHAR2(25)
    CONSTRAINT     emp_email_nn  NOT NULL
    , phone_number   VARCHAR2(20)
    , hire_date      DATE
    CONSTRAINT     emp_hire_date_nn  NOT NULL
    , job_id         VARCHAR2(10)
    CONSTRAINT     emp_job_nn  NOT NULL
    , salary         NUMBER(8,2)
    , commission_pct NUMBER(2,2)
    , manager_id     NUMBER(6)
    , department_id  NUMBER(4)
    , CONSTRAINT     emp_salary_min
                     CHECK (salary > 0) 
    , CONSTRAINT     emp_email_uk
                     UNIQUE (email)
    ) ;
 
CREATE UNIQUE INDEX emp_emp_id_pk
ON employees (employee_id) ;
 
 
ALTER TABLE employees
ADD ( CONSTRAINT     emp_emp_id_pk
                     PRIMARY KEY (employee_id)
    , CONSTRAINT     emp_dept_fk
                     FOREIGN KEY (department_id)
                      REFERENCES departments
    , CONSTRAINT     emp_job_fk
                     FOREIGN KEY (job_id)
                      REFERENCES jobs (job_id)
    , CONSTRAINT     emp_manager_fk
                     FOREIGN KEY (manager_id)
                      REFERENCES employees
    ) ;
 
ALTER TABLE departments
ADD ( CONSTRAINT dept_mgr_fk
             FOREIGN KEY (manager_id)
              REFERENCES employees (employee_id)
    ) ;

CREATE SEQUENCE employees_seq
 START WITH     207
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

CREATE TABLE job_history
    ( employee_id   NUMBER(6)
     CONSTRAINT    jhist_employee_nn  NOT NULL
    , start_date    DATE
    CONSTRAINT    jhist_start_date_nn  NOT NULL
    , end_date      DATE
    CONSTRAINT    jhist_end_date_nn  NOT NULL
    , job_id        VARCHAR2(10)
    CONSTRAINT    jhist_job_nn  NOT NULL
    , department_id NUMBER(4)
    , CONSTRAINT    jhist_date_interval
                    CHECK (end_date > start_date)
    ) ;
 
CREATE UNIQUE INDEX jhist_emp_id_st_date_pk 
ON job_history (employee_id, start_date) ;
 
ALTER TABLE job_history
ADD ( CONSTRAINT jhist_emp_id_st_date_pk
      PRIMARY KEY (employee_id, start_date)
    , CONSTRAINT     jhist_job_fk
                     FOREIGN KEY (job_id)
                     REFERENCES jobs
    , CONSTRAINT     jhist_emp_fk
                     FOREIGN KEY (employee_id)
                     REFERENCES employees
    , CONSTRAINT     jhist_dept_fk
                     FOREIGN KEY (department_id)
                     REFERENCES departments
    ) ;
 

CREATE OR REPLACE VIEW emp_details_view
  (employee_id,
   job_id,
   manager_id,
   department_id,
   location_id,
   country_id,
   first_name,
   last_name,
   salary,
   commission_pct,
   department_name,
   job_title,
   city,
   state_province,
   country_name,
   region_name)
AS SELECT
  e.employee_id, 
  e.job_id, 
  e.manager_id, 
  e.department_id,
  d.location_id,
  l.country_id,
  e.first_name,
  e.last_name,
  e.salary,
  e.commission_pct,
  d.department_name,
  j.job_title,
  l.city,
  l.state_province,
  c.country_name,
  r.region_name
FROM
  employees e,
  departments d,
  jobs j,
  locations l,
  countries c,
  regions r
WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id
  AND j.job_id = e.job_id 
WITH READ ONLY;
 
COMMIT;

INSERT INTO regions VALUES 
        ( 1
        , ‘Europe‘ 
        );
 
INSERT INTO regions VALUES 
        ( 2
        , ‘Americas‘ 
        );
 
INSERT INTO regions VALUES 
        ( 3
        , ‘Asia‘ 
        );
 
INSERT INTO regions VALUES 
        ( 4
        , ‘Middle East and Africa‘ 
        );
 
INSERT INTO countries VALUES 
        ( ‘IT‘
        , ‘Italy‘
        , 1 
        );
 
INSERT INTO countries VALUES 
        ( ‘JP‘
        , ‘Japan‘
    , 3 
        );
 
INSERT INTO countries VALUES 
        ( ‘US‘
        , ‘United States of America‘
        , 2 
        );
 
INSERT INTO countries VALUES 
        ( ‘CA‘
        , ‘Canada‘
        , 2 
        );
 
INSERT INTO countries VALUES 
        ( ‘CN‘
        , ‘China‘
        , 3 
        );
 
INSERT INTO countries VALUES 
        ( ‘IN‘
        , ‘India‘
        , 3 
        );
 
INSERT INTO countries VALUES 
        ( ‘AU‘
        , ‘Australia‘
        , 3 
        );
 
INSERT INTO countries VALUES 
        ( ‘ZW‘
        , ‘Zimbabwe‘
        , 4 
        );
 
INSERT INTO countries VALUES 
        ( ‘SG‘
        , ‘Singapore‘
        , 3 
        );
 
INSERT INTO countries VALUES 
        ( ‘UK‘
        , ‘United Kingdom‘
        , 1 
        );
 
INSERT INTO countries VALUES 
        ( ‘FR‘
        , ‘France‘
        , 1 
        );
 
INSERT INTO countries VALUES 
        ( ‘DE‘
        , ‘Germany‘
        , 1 
        );
 
INSERT INTO countries VALUES 
        ( ‘ZM‘
        , ‘Zambia‘
        , 4 
        );
 
INSERT INTO countries VALUES 
        ( ‘EG‘
        , ‘Egypt‘
        , 4 
        );
 
INSERT INTO countries VALUES 
        ( ‘BR‘
        , ‘Brazil‘
        , 2 
        );
 
INSERT INTO countries VALUES 
        ( ‘CH‘
        , ‘Switzerland‘
        , 1 
        );
 
INSERT INTO countries VALUES 
        ( ‘NL‘
        , ‘Netherlands‘
        , 1 
        );
 
INSERT INTO countries VALUES 
        ( ‘MX‘
        , ‘Mexico‘
        , 2 
        );
 
INSERT INTO countries VALUES 
        ( ‘KW‘
        , ‘Kuwait‘
        , 4 
        );
 
INSERT INTO countries VALUES 
        ( ‘IL‘
        , ‘Israel‘
        , 4 
        );
 
INSERT INTO countries VALUES 
        ( ‘DK‘
        , ‘Denmark‘
        , 1 
        );
 
INSERT INTO countries VALUES 
        ( ‘HK‘
        , ‘HongKong‘
        , 3 
        );
 
INSERT INTO countries VALUES 
        ( ‘NG‘
        , ‘Nigeria‘
        , 4 
        );
 
INSERT INTO countries VALUES 
        ( ‘AR‘
        , ‘Argentina‘
        , 2 
        );
 
INSERT INTO countries VALUES 
        ( ‘BE‘
        , ‘Belgium‘
        , 1 
        );
 
INSERT INTO locations VALUES 
        ( 1000 
        , ‘1297 Via Cola di Rie‘
        , ‘00989‘
        , ‘Roma‘
        , NULL
        , ‘IT‘
        );
 
INSERT INTO locations VALUES 
        ( 1100 
        , ‘93091 Calle della Testa‘
        , ‘10934‘
        , ‘Venice‘
        , NULL
        , ‘IT‘
        );
 
INSERT INTO locations VALUES 
        ( 1200 
        , ‘2017 Shinjuku-ku‘
        , ‘1689‘
        , ‘Tokyo‘
        , ‘Tokyo Prefecture‘
        , ‘JP‘
        );
 
INSERT INTO locations VALUES 
        ( 1300 
        , ‘9450 Kamiya-cho‘
        , ‘6823‘
        , ‘Hiroshima‘
        , NULL
        , ‘JP‘
        );
 
INSERT INTO locations VALUES 
        ( 1400 
        , ‘2014 Jabberwocky Rd‘
        , ‘26192‘
        , ‘Southlake‘
        , ‘Texas‘
        , ‘US‘
        );
 
INSERT INTO locations VALUES 
        ( 1500 
        , ‘2011 Interiors Blvd‘
        , ‘99236‘
        , ‘South San Francisco‘
        , ‘California‘
        , ‘US‘
        );
 
INSERT INTO locations VALUES 
        ( 1600 
        , ‘2007 Zagora St‘
        , ‘50090‘
        , ‘South Brunswick‘
        , ‘New Jersey‘
        , ‘US‘
        );
 
INSERT INTO locations VALUES 
        ( 1700 
        , ‘2004 Charade Rd‘
        , ‘98199‘
        , ‘Seattle‘
        , ‘Washington‘
        , ‘US‘
        );
 
INSERT INTO locations VALUES 
        ( 1800 
        , ‘147 Spadina Ave‘
        , ‘M5V 2L7‘
        , ‘Toronto‘
        , ‘Ontario‘
        , ‘CA‘
        );
 
INSERT INTO locations VALUES 
        ( 1900 
        , ‘6092 Boxwood St‘
        , ‘YSW 9T2‘
        , ‘Whitehorse‘
        , ‘Yukon‘
        , ‘CA‘
        );
 
INSERT INTO locations VALUES 
        ( 2000 
        , ‘40-5-12 Laogianggen‘
        , ‘190518‘
        , ‘Beijing‘
        , NULL
        , ‘CN‘
        );
 
INSERT INTO locations VALUES 
        ( 2100 
        , ‘1298 Vileparle (E)‘
        , ‘490231‘
        , ‘Bombay‘
        , ‘Maharashtra‘
        , ‘IN‘
        );
 
INSERT INTO locations VALUES 
        ( 2200 
        , ‘12-98 Victoria Street‘
        , ‘2901‘
        , ‘Sydney‘
        , ‘New South Wales‘
        , ‘AU‘
        );
 
INSERT INTO locations VALUES 
        ( 2300 
        , ‘198 Clementi North‘
        , ‘540198‘
        , ‘Singapore‘
        , NULL
        , ‘SG‘
        );
 
INSERT INTO locations VALUES 
        ( 2400 
        , ‘8204 Arthur St‘
        , NULL
        , ‘London‘
        , NULL
        , ‘UK‘
        );
 
INSERT INTO locations VALUES 
        ( 2500 
        , ‘Magdalen Centre, The Oxford Science Park‘
        , ‘OX9 9ZB‘
        , ‘Oxford‘
        , ‘Oxford‘
        , ‘UK‘
        );
 
INSERT INTO locations VALUES 
        ( 2600 
        , ‘9702 Chester Road‘
        , ‘09629850293‘
        , ‘Stretford‘
        , ‘Manchester‘
        , ‘UK‘
        );
 
INSERT INTO locations VALUES 
        (

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！