0

I've gone through lots of trial and errors in installing the sample schemata for Oracle 12c and still can't get it right.

I have a pluggable database named PDB01. I also have an instantclient installed for the use of PL/SQL developer, but not sure if this affects the installation.

Things I've done: Major Reference: https://docs.oracle.com/database/121/COMSC/installation.htm#COMSC00003

  1. Install Oracle Database Example
  2. Set TWO_TASK variable as indicated in this discussion: https://community.oracle.com/message/11234394#11234394 http://www.orafaq.com/wiki/TWO_TASK

    set two_task=pdb01
    
  3. Edit tnsnames.ora to include the PDB name as discussed here: https://community.oracle.com/message/13570347#13570347

    PDB01 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = vm21)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = pdb01)
        )
      )
    
  4. Connect to sys:

    connect sys/password@pdb01 as sysdba
    
  5. Run the hr_main.sql script:

    @?/demo/schema/human_resources/hr_main.sql
    specify password for HR as parameter 1:
    Enter value for 1: xxxx
    specify default tablespace for HR as parameter 2:
    Enter value for 2: users
    specify temporary tablespace for HR as parameter 3:
    Enter value for 3: temp
    specify password for SYS as parameter 4:
    Enter value for 4: xxxx (same as the password of SYS)
    specify log path as parameter 5:
    Enter value for 5: D:\app\OracleHomeUser\product\12.1.0\dbhome_1\demo\schema\log
    

    And then I got :

    User dropped.
    User created.
    User altered.
    User altered.
    Grant succeeded.
    Grant succeeded.
    
    Connected.
    GRANT execute ON sys.dbms_stats TO hr
                                       *
    ERROR at line 1:
    ORA-01917: user or role 'HR' does not exist
    
    ERROR:
    ORA-01017: invalid username/password; logon denied
    
    Warning: You are no longer connected to ORACLE.
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Creating REGIONS table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Creating COUNTRIES table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Creating LOCATIONS table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Creating DEPARTMENTS table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Creating JOBS table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Creating EMPLOYEES table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Creating JOB_HISTORY table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Creating EMP_DETAILS_VIEW view ...
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Populating REGIONS table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Populating COUNTIRES table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Populating LOCATIONS table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Populating DEPARTMENTS table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Populating JOBS table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Populating EMPLOYEES table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    ******  Populating JOB_HISTORY table ....
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0641: "EXECUTE" requires connection to server
    

hr_main.sql

    rem
    rem Header: hr_main.sql 09-jan-01
    rem
    rem Copyright (c) 2001, 2011, Oracle and/or its affiliates. 
    rem All rights reserved. 
    rem
    rem Owner  : ahunold
    rem
    rem NAME
    rem   hr_main.sql - Main script for HR schema
    rem
    rem DESCRIPTON
    rem   HR (Human Resources) is the smallest and most simple one 
    rem   of the Sample Schemas
    rem   
    rem NOTES
    rem   Run as SYS or SYSTEM
    rem
    rem MODIFIED   (MM/DD/YY)
    rem   jmadduku  02/18/11 - Grant Unlimited Tablespace priv with RESOURCE
    rem   celsbern  06/17/10 - fixing bug 9733839
    rem   pthornto  07/16/04 - obsolete 'connect' role 
    rem   hyeh      08/29/02 - hyeh_mv_comschema_to_rdbms
    rem   ahunold   08/28/01 - roles
    rem   ahunold   07/13/01 - NLS Territory
    rem   ahunold   04/13/01 - parameter 5, notes, spool
    rem   ahunold   03/29/01 - spool
    rem   ahunold   03/12/01 - prompts
    rem   ahunold   03/07/01 - hr_analz.sql
    rem   ahunold   03/03/01 - HR simplification, REGIONS table
    rem   ngreenbe  06/01/00 - created

    SET ECHO OFF
    SET VERIFY OFF

    PROMPT 
    PROMPT specify password for HR as parameter 1:
    DEFINE pass     = &1
    PROMPT 
    PROMPT specify default tablespeace for HR as parameter 2:
    DEFINE tbs      = &2
    PROMPT 
    PROMPT specify temporary tablespace for HR as parameter 3:
    DEFINE ttbs     = &3
    PROMPT 
    PROMPT specify password for SYS as parameter 4:
    DEFINE pass_sys = &4
    PROMPT 
    PROMPT specify log path as parameter 5:
    DEFINE log_path = &5
    PROMPT

    -- The first dot in the spool command below is 
    -- the SQL*Plus concatenation character

    DEFINE spool_file = &log_path.hr_main.log
    SPOOL &spool_file

    REM =======================================================
    REM cleanup section
    REM =======================================================

    DROP USER hr CASCADE;

    REM =======================================================
    REM create user
    REM three separate commands, so the create user command 
    REM will succeed regardless of the existence of the 
    REM DEMO and TEMP tablespaces 
    REM =======================================================

    CREATE USER hr IDENTIFIED BY &pass;

    ALTER USER hr DEFAULT TABLESPACE &tbs
          QUOTA UNLIMITED ON &tbs;

    ALTER USER hr TEMPORARY TABLESPACE &ttbs;

    GRANT CREATE SESSION, CREATE VIEW, ALTER SESSION, CREATE SEQUENCE TO hr;
    GRANT CREATE SYNONYM, CREATE DATABASE LINK, RESOURCE , UNLIMITED         TABLESPACE TO hr;

    REM =======================================================
    REM grants from sys schema
    REM =======================================================

    CONNECT sys/&pass_sys AS SYSDBA;
    GRANT execute ON sys.dbms_stats TO hr;

    REM =======================================================
    REM create hr schema objects
    REM =======================================================

    CONNECT hr/&pass
    ALTER SESSION SET NLS_LANGUAGE=American;
    ALTER SESSION SET NLS_TERRITORY=America;

    --
    -- create tables, sequences and constraint
    --

    @?/demo/schema/human_resources/hr_cre

    -- 
    -- populate tables
    --

    @?/demo/schema/human_resources/hr_popul

    --
    -- create indexes
    --

    @?/demo/schema/human_resources/hr_idx

    --
    -- create procedural objects
    --

    @?/demo/schema/human_resources/hr_code

    --
    -- add comments to tables and columns
    --

    @?/demo/schema/human_resources/hr_comnt

    --
    -- gather schema statistics
    --

    @?/demo/schema/human_resources/hr_analz

    spool off
1

When it comes to Windows, something is always different. TWO_TASK works on Linux/UNIX, but not on Windows. The Windows version of TWO_TASK is LOCAL.

SQL*Plus Environment Variables

LOCAL

Windows environment variable to specify a connection string. Performs the same function as TWO_TASK on UNIX.

...

TWO_TASK

UNIX environment variable to specify a connection string. Connections that do not specify a database will connect to the database specified in TWO_TASK.

Use LOCAL:

set LOCAL=PDB01

And try again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.