In the link Installing Sample Schemas say that you need create the schemas manually (
SH) before run the hr_main.sql.
I will try explain you why you got error:
CREATE USER hr IDENTIFIED BY hr
ERROR at line 1:
ORA-65096: invalid common user or role name
You got error because you are trying create HR as a common user. In 12c, if you are connected to CDB and you don't include "CONTAINER" it will create as Common User. If you are connected to PDB and you don't include "CONTAINER" it will create as LOCAL User.
To create Common User the username need start with "C##", so "HR" doesn't start with "C##" for that you got "ORA-65096: invalid common user or role name"
alter pluggable database pdborcl open;
alter session set container=PDBORCL;
GRANT execute ON sys.dbms_stats TO hr
ERROR at line 1:
ORA-01917: user or role 'HR' does not exist
you got error because HR is not created in PDB. You need create
SH Users manually in PDB. After that you need connected to PDB as SYSDBA.
Please note: Setting the TWO_TASK environment variable as mentioned further down below seems to be the more elegant solution. I therefore modified the instructions below:
Thank you for the response and trying to help, but the solution was different.
I don't know whether or not the following is the right way, but it worked:
1. Edit /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora to include the PDB name, e.g. pdborcl.
(ADDRESS = (PROTOCOL = TCP)(HOST = vm21)(PORT = 1521))
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
2. Modify the hr_main.sql script to connect to the PDB:
$ cd /u01/app/oracle/product/12.1.0/dbhome_1/demo/schema/human_resources
$ cp hr_main.sql hr_main.sql_orig
$ a='CONNECT sys/\&pass_sys'
$ b='CONNECT hr/\&pass'
$ sed -i "s|^$a|$a@pdborcl|g" hr_main.sql
$ sed -i "s|^$b|$b@pdborcl|g" hr_main.sql
3. Set the shell environment to the SID of CBD (orcl) and connect to the PDB (pdborcl):
$ . oraenv
$ sqlplus sys@pdborcl as sysdba
2. Configure the shell environment for the CDB (orcl) and set the TWO_TASK variable to the PDB (pdborcl)
$ . oraenv
$ export TWO_TASK=pdborcl
$ sqlplus sys/password as sysdba
SQL*Plus: Release 184.108.40.206.0 Production on Sat Feb 15 01:17:59 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> sho con_name
3. Follow the documentation and execute the script:
PL/SQL procedure successfully completed.
Don't forget to unset the TWO_TASK variable.
$ unset TWO_TASK
Your solution was just what i was looking for.
I was trying to connect Autodesk Map 3D to Oracle. It automatically creates the spatial user and it wasn't designed for containers.
I added the PDB (pdborcl) to Service Naming in Net Manager. After this i just connected using a common user and the PDB as Service Name. It created its users with no problem.
You can just set TWO_TASK environment variable before setting up your sample schema.
sqlplus sys/passwd@PDBORCL as sysdba
Indeed, when TWO_TASK is set, the client connections by default will use TNS and alias in your tnsnames.ora instead of local connection and ORACLE_SID.
"The TWO_TASK (on UNIX) or LOCAL (on Windows) environment variable can be set to a connection identifier. This removes the need to explicitly enter the connection identifier whenever a connection is made in SQL*Plus "
Ok - except that was NOT the problem OP had at all.
OP did an install that INCLUDED the sample schema; there was no 'before setting up your sample schema' option. OP just didn't realize that the sample schema was in the PDB (PDBORCL) and that you have to connect to a PDB using a SERVICE_NAME, not a SID. OP didn't have an entry in their TNS file for the PDB.
As far as I remember the sample schema was not included when I did a default installation. The user "hr" for instance, did not exist, and I wanted to run the demo sql scripts to create it.
The problem with the demo scripts are that they connect using "sys/password as sysdba", and do not prompt for the PDB name. Connections will therefore be made to the CDB relying on the ORACLE_SID, which causes an error because creating non-common users in CDB$ROOT is not allowed.
As far as I can tell setting the TWO_TASK variable is a very elegant solution and it works fine - I just tested it. Of course, it is still necessary to have the PDB service name in tnsnames.ora, but there is no more need to modify the demo scripts.
As far as I remember the sample schema was not included when I did a default installation
Well I don't know since I wasn't there. But this is what you said:
I installed a simple starter database during the installation of Oracle Database 12cR1 with the PDBORCL option selected (default)
That 'starter' dabase would include the sample schemas and data.
The important thing is that you got your problem resolved.
The dbca app in 12c actually does not mention starter database anymore, like it did in 11g.
When you create a database in advanced mode, there is a checkbox "Sample Schemas", which is not selected by default. But I just used dbca to create another cdb database using the default (not advanced mode) and the pdb does also not include the hr user.