教你免费下载:del_data.sql,hr_cre.sql 和 hr_popul.sql

先上图:

找这几个文件时几经波折,百度在这种基础问题上也掉链子了,老铁们且看且珍惜。

有些文件可以在官网下载,但是老外的网站你捉摸不透,时隔一天我已经找不到了。

优快云现在不能免费分享文件,笔者贴上代码,老铁们可自行搬运:

 

一、新建文本文档命名为“del_data.sql”,后缀名别漏了改,分割线内的代码复制粘贴保存:

———————————————————“del_data.sql”的分割线———————————————————

ALTER TABLE departments
DISABLE CONSTRAINT DEPT_MGR_FK;
ALTER TABLE job_history
DISABLE CONSTRAINT JHIST_EMP_FK;
DROP TRIGGER secure_employees;
DROP TRIGGER update_job_history;
DROP PROCEDURE add_job_history;
DROP PROCEDURE secure_dml;
DELETE FROM employees
WHERE manager_id IN (108, 114, 120, 121, 122, 123, 145, 146, 147, 148);
DELETE FROM employees
WHERE employee_id IN (114, 120, 121, 122, 123, 145, 146, 147, 148,
                       196, 197, 198, 199, 105, 106, 108, 175, 177,
                       179, 203, 204);
DELETE FROM locations
WHERE location_id NOT IN
   (SELECT DISTINCT location_id
   FROM departments);


DELETE FROM countries
WHERE country_id NOT IN
  (SELECT country_id
   FROM locations);


DELETE FROM jobs
WHERE job_id NOT IN
  (SELECT job_id
   FROM employees);
DELETE FROM departments
WHERE department_id NOT IN
   (SELECT DISTINCT department_id
   FROM employees   WHERE department_id IS NOT NULL);


UPDATE departments
SET manager_id = 124
WHERE department_id = 50;
UPDATE departments
SET manager_id = 149
WHERE department_id = 80;
DELETE FROM locations
WHERE location_id IN (2700, 2400);
UPDATE locations
SET street_address = '460 Bloor St. W.',
     postal_code = 'ON M5S 1X8'
WHERE location_id = 1800;
ALTER TABLE departments
ENABLE CONSTRAINT DEPT_MGR_FK;
CREATE TABLE job_grades(grade_level VARCHAR2(3), lowest_sal  NUMBER, highest_sal NUMBER);


INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);
INSERT INTO departments VALUES
         ( 190
         , 'Contracting'
        , NULL
        , 1700
        );

COMMIT;

———————————————————“del_data.sql”的分割线———————————————————

 

二、“hr_cre.sql”,方法同上。

———————————————————“hr_cre.sql”的分割线———————————————————

Rem
Rem $Header: hr_cre.sql 29-aug-2002.11:44:03 hyeh Exp $
Rem
Rem hr_cre.sql
Rem
Rem Copyright (c) 2001, 2002, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      hr_cre.sql - Create data objects for HR schema
Rem
Rem    DESCRIPTION
Rem      This script creates six tables, associated constraints
Rem      and indexes in the human resources (HR) schema.
Rem
Rem    NOTES
Rem
Rem    CREATED by Nancy Greenberg, Nagavalli Pataballa - 06/01/00
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    hyeh        08/29/02 - hyeh_mv_comschema_to_rdbms
Rem    ahunold     09/14/00 - Added emp_details_view
Rem    ahunold     02/20/01 - New header
Rem    vpatabal 03/02/01 - Added regions table, modified regions
Rem            column in countries table to NUMBER.
Rem            Added foreign key from countries table
Rem            to regions table on region_id.
Rem                    Removed currency name, currency symbol 
Rem            columns from the countries table.
Rem                  Removed dn columns from employees and
Rem            departments tables.
Rem            Added sequences.
Rem            Removed not null constraint from 
Rem            salary column of the employees table.


SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET ECHO OFF 


REM ********************************************************************
REM Create the REGIONS table to hold region information for locations
REM HR.LOCATIONS table has a foreign key to this table.


Prompt ******  Creating REGIONS table ....


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)
    ) ;


REM ********************************************************************
REM Create the COUNTRIES table to hold country information for customers
REM and company locations. 
REM OE.CUSTOMERS table and HR.LOCATIONS have a foreign key to this table.


Prompt ******  Creating COUNTRIES table ....


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) 
    ) ;


REM ********************************************************************
REM Create the LOCATIONS table to hold address information for company departments.
REM HR.DEPARTMENTS has a foreign key to this table.


Prompt ******  Creating LOCATIONS table ....


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) 
    ) ;


Rem Useful for any subsequent addition of rows to locations table
Rem Starts with 3300


CREATE SEQUENCE locations_seq
 START WITH     3300
 INCREMENT BY   100
 MAXVALUE       9900
 NOCACHE
 NOCYCLE;


REM ********************************************************************
REM Create the DEPARTMENTS table to hold company department information.
REM HR.EMPLOYEES and HR.JOB_HISTORY have a foreign key to this table.


Prompt ******  Creating DEPARTMENTS table ....


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)
     ) ;


Rem Useful for any subsequent addition of rows to departments table
Rem Starts with 280 


CREATE SEQUENCE departments_seq
 START WITH     280
 INCREMENT BY   10
 MAXVALUE       9990
 NOCACHE
 NOCYCLE;


REM ********************************************************************
REM Create the JOBS table to hold the different names of job roles within the company.
REM HR.EMPLOYEES has a foreign key to this table.


Prompt ******  Creating JOBS table ....


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)
    ) ;


REM ********************************************************************
REM Create the EMPLOYEES table to hold the employee personnel 
REM information for the company.
REM HR.EMPLOYEES has a self referencing foreign key to this table.


Prompt ******  Creating EMPLOYEES table ....


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)
    ) ;




Rem Useful for any subsequent addition of rows to employees table
Rem Starts with 207 




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


REM ********************************************************************
REM Create the JOB_HISTORY table to hold the history of jobs that 
REM employees have held in the past.
REM HR.JOBS, HR_DEPARTMENTS, and HR.EMPLOYEES have a foreign key to this table.


Prompt ******  Creating JOB_HISTORY table ....


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
    ) ;


REM ********************************************************************
REM Create the EMP_DETAILS_VIEW that joins the employees, jobs, 
REM departments, jobs, countries, and locations table to provide details
REM about employees.


Prompt ******  Creating EMP_DETAILS_VIEW view ...


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;

———————————————————“hr_cre.sql”的分割线———————————————————

 

 

三、“hr_popul.sql”,方法同上。

———————————————————“hr_popul.sql ”的分割线———————————————————

rem
rem Header: hr_popul.sql 09-jan-01
rem
rem Copyright (c) 2001, 2002, Oracle Corporation.  All rights reserved.  
rem
rem Owner  : ahunold
rem
rem NAME
rem   hr_popul.sql - Populate script for HR schema
rem
rem DESCRIPTON
rem
rem
rem NOTES
rem   There is a circular foreign key reference between 
rem   EMPLOYESS and DEPARTMENTS. That's why we disable
rem   the FK constraints here
rem
rem CREATED
rem   Nancy Greenberg, Nagavalli Pataballa - 06/01/00
rem
rem MODIFIED   (MM/DD/YY)
rem   hyeh      08/29/02 - hyeh_mv_comschema_to_rdbms
rem   ahunold   03/07/01 - small data errors corrected
rem                      - Modified region values of countries table
rem                      - Replaced ID sequence values for employees
rem                        and departments tables with numbers
rem                      - Moved create sequence statements to hr_cre
rem                      - Removed dn values for employees and
rem                        departments tables
rem                      - Removed currency columns values from
rem                        countries table
rem   ngreenbe           - Updated employee 178 for no department
rem   pnathan            - Insert new rows to job_history table
rem   ahunold   02/20/01 - NLS_LANGUAGE, replacing non American
rem   ahunold   01/09/01 - checkin ADE


SET VERIFY OFF
ALTER SESSION SET NLS_LANGUAGE=American; 


REM ***************************insert data into the REGIONS table


Prompt ******  Populating REGIONS table ....


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' 
        );


REM ***************************insert data into the COUNTRIES table


Prompt ******  Populating COUNTIRES table ....


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'
        ,

<think>嗯,用户遇到了OracleHR用户创建失败的问题,运行hr_main.sql脚本后未成功创建。这确实是一个常见的痛点,尤其是在学习Oracle时需要用到HR样例schema时。用户引用了三份材料,提到了不同版本的脚本获取安装方式,这说明用户已经做过功课但问题仍未解决。用户可能正在学习Oracle数据库管理或开发,需要HR样例数据来练习SQL查询或测试功能。从语气看用户有些焦虑,毕竟程中经常用HR示例但自己却卡在安装这一步。深层需求可能不仅仅是解决错误,而是要彻底理解整个HRschema的构建机制。参考用户提供的线索,12C版本存在脚本缺失是个关键点(引用1),而11G需要额外安装(引用2),官网脚本获取也是方案之一(引用3)。但用户当前的问题是执行后未成功创建,需要区分是环境问题还是脚本问题。首先想到权限问题:Oracle安装目录权限不足可能导致脚本执行中断(方案1)。其次常见的是路径问题,用户可能没有在SQL*Plus正确设置当前路径(方案2)。另外特别注意到Oracle12c容器数据库特性,不指定PDB就会默认指向CDB$ROOT这个容器数据库(方案3),这点很关键但容易被忽略。对于环境变量问题(方案4),用户下载的第三方脚本路径是否在SQL>中正确指定了绝对路径?还有版本匹配性(方案5),比如12c里HR需要解锁而旧版不用。最后内存溢出(方案6)虽然少见但也要提及,毕竟大数据量时会发生。补充措施里建议用户检查脚本完整性很必要——第三方脚本可能有字符集问题(补充2)。后续操作验证部分特意加入了连接测试查看表结构,是为了让用户确认HR用户完全可用。生成的两个相关问题也都切中要害:权限CDB问题确实是用户接下来可能遇到的连锁问题。最后用表格小结根本原因,突出PDB未指定这个技术点。用户如果是新手,可能需要更基础的操作指引,比如"@"符号的使用方法,但当前回复已包含关键解决方案。如果这些方案都不行,可能需要指导用户按错误日志来具体分析。</think> 根据您的描述,执行`hr_main.sql`脚本后HR用户未成功创建,以下是可能的原因及解决方案: --- ### **常见原因与解决方案** #### 1. **权限不足** - **原因**:执行脚本的用户需具备`SYSDBA`或`DBA`权限。 - **解决方案**: ```sql CONN / AS SYSDBA; @$ORACLE_HOME/demo/schema/human_resources/hr_main.sql ``` #### 2. **路径错误** - **原因**:脚本路径未指定或`ORACLE_HOME`变量未生效。 - **解决方案**: - 检查路径是否存在: ```bash ls $ORACLE_HOME/demo/schema/human_resources/hr_main.sql ``` - 若路径正确但仍报错,改用绝对路径执行: ```sql @/u01/app/oracle/product/12.2.0/dbhome_1/demo/schema/human_resources/hr_main.sql ``` #### 3. **未指定PDB(12c及以上版本特有)** - **原因**:Oracle 12c+默认在容器数据库(CDB)中运行,需指定可插拔数据库(PDB)[^1][^2]。 - **解决方案**: 1. 切换到PDB(如`ORCLPDB1`): ```sql ALTER SESSION SET CONTAINER=ORCLPDB1; ``` 2. 重新执行脚本: ```sql @hr_main.sql ``` #### 4. **脚本依赖未满足** - **原因**:`hr_main.sql`依赖同目录下的子脚本(如`hr_cre.sql`, `hr_popul.sql`)。 - **验证步骤**: 检查目录是否包含以下文件: ``` hr_cre.sql -- 创建对象 hr_popul.sql -- 填充数据 hr_idx.sql -- 创建索引 ``` *若缺失,需从官网或第三方源补全文件*[^3]。 #### 5. **用户已存在但未解锁** - **解决方案**:手动解锁HR用户并重置密码: ```sql ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY hr; ``` #### 6. **脚本执行中断** - **原因**:表空间不足、内存溢出或语法兼容性问题。 - **排查方法**: 查看执行日志(如`SPOOL hr_install.log`),定位错误行。 --- ### **补充建议** 1. **使用官方脚本**: 从Oracle官网下载匹配版本的脚本: ```bash wget https://docs.oracle.com/database/121/SCPTG/hr_main.txt -O hr_main.sql ``` 2. **分步执行子脚本**: ```sql -- 手动执行子脚本 @hr_cre.sql -- 创建表 @hr_popul.sql -- 插入数据 @hr_idx.sql -- 创建索引 ``` --- ### **执行后验证** ```sql CONN hr/hr SELECT table_name FROM user_tables; -- 应显示EMPLOYEES、DEPARTMENTS等表 SELECT COUNT(*) FROM employees; -- 正常应返回107条数据 ``` --- ### **根本原因总结** | 原因 | 发生场景 | 解决方案 | |---------------------|---------------------------|--------------------------| | PDB未指定 | Oracle 12c+多租户环境 | `ALTER SESSION SET CONTAINER=PDB_NAME` | | 脚本依赖缺失 | 第三方脚本不完整 | 补全子脚本或下载官方版本 | | 权限不足 | 非SYSDBA用户执行 | 用`SYSDBA`权限重新执行 | > 若仍失败,请检查`alert.log`中的详细错误日志,或分享执行时的具体报错信息以便进一步分析[^1][^3]。 --- ###
评论 8
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值