How To Unlock HR User In Oracle Database 21c by Manish Sharma

What’s up Internet! I am Manish from RebellionRider.com. Today I will show you how to unlock HR user in Oracle Database 21c. So, without wasting much of your time let’s start! Step 1: Download the sample schema In previous versions, the sample schemas were placed inside the default pluggable database but in Oracle Database 21c we have to download the sample schemas. And you can do that from this GitHub repository. I will leave the link of this GitHub Repo in the description. Anyways, from this Oracle database 21c sample schema section, click on this Source Code link and download the zip file. https://github.com/oracle/db-sample-schemas/releases/latest We have to extract the content of this zip file. For that first copy this ZIP file and then go to your Oracle Home The location that is flashing on your screen is my Oracle home. It could be different in your case. I will leave the link of the video demonstrating how to find out your Oracle Home in just 32 seconds in the description. Go ahead and check that out- Step 1: Download the sample schema Step 2: Edit the hr_main script Step 3: Prepare the database. Unlock The HR user How To Unlock HR user 2 Anyways from Oracle Home go inside the directory named DEMO. Inside the DEMO directory, I already have a folder named schemas. If you have one, then it’s ok. If you don’t then create one. Inside this Schemas folder paste the Sample Schemas zip file that we just downloaded. Now extract it’s content. Done. Step 2: Edit the hr_main script To install and unlock the HR user in Oracle Database 21c we have to edit the hr_main.sql script.. First let’s locate this script. You can find it inside the folder named “human_resources”. Get inside it. Here is the script Now open it up!. Now come to the end of this script. Here you will see that we have some reference links to other scripts starting with the phrase SUB__CWD. We have to change this reference link with the actual location path of the scripts. Let’s do that - All these scripts are placed inside same “Human Resources” folder. So go inside the folder and copy it’s path. Done! Now come to the script and change all the reference links with this path. Just make sure all these paths must start with @ signs. Otherwise the script won’t execute. Ok, all the reference paths have been updated. Now replace all these forward slashes with the backward slashes so that we have one uniform path. Done. Ok, the hr_main.sql script has been modified successfully. Save it and close it Step 3: Prepare the database. Before we start installing or unlocking the HR schema first we need to prepare our database. Let’s quickly do that Login to your database using sys user SQLPLUS / as SYSDBA How To Unlock HR user 3 Right now I’m connected with the root container using sys user. SQL> SHOW con_name; Next we will switch the container from root to pluggable but first let’s clear the screen now let’s switch the container ALTER SESSION SET CONTAINER = orclpdb; Here ORCLPDB is the name of my pluggable database. it could be different in your case. Session Altered Before we perform any action we need to make sure our pluggable database is open for read write operations. Let’s check SQL> COLUMN name FORMAT a15; SQL> SELECT name, open_mode FROM v$pdbs; Since the pluggable database is mounted we need to open it. SQL> ALTER PLUGGABLE DATABASE OPEN; This statement will open the pluggable database. Pluggable database altered As I said that alter pluggable database DDL will open the pluggable database BUT only for a single session [Oh No!]. As soon as you log out from the session, it will close again. The solution to this problem is to save the current state of the pluggable database so that it will stay open across the sessions. And we can save this state like this - SQL> ALTER PLUGGABLE DATABASE ORCLPDB SAVE STATE; This statement will save the current state of the pluggable database and the PDB will remain open across the sessions. Pluggable database altered If you want to know how to save the state of all the pluggable databases just by using 1 single statement, then watch the YouTube shorts I uploaded earlier. Link is on your screen and in the description. How To Unlock HR user 4 Unlock The HR user Anyways, now that we have opened the pluggable database, it’s time to execute our hr_main.sql script and Unlock the HR user. Let’s do that - Let’s first clear the screen SQL>cl scr Now execute the script @C:\Users\Manish\Downloads\db-sample-schemas-21.1\db-sample-schemas-21.1\human_resources\hr_main.sql First write @ sign followed by the location path of the script and then the name of the script with the extension .sql. Do not execute, now we have to specify 6 parameters. Let’s do that Give space and specify the password that you want to set for your HR user. I will set the password to HR. Again give space and specify the name of the default tablespace for the HR user. In my case it will be USERS. Again give space and specify the name of the temporary tablespace. For me it will be temp. The 4th parameter will be the password of your sys user. In my case the password for my sys user is ORACLE. 5th parameter will be the path for the logs. I will be saving my logs in the log directory that I have already created inside the demo folder. You can save your logs anywhere you want. Just make sure to end the path with a forward slash. The 6th parameter is connect string- It is the string formed by adding hostname followed by colon and then the port number followed by forward slash and then the service name for the pluggable database into which you want to place your hr user. You can get all this information from your TNSNames.ora file. In my case this connect string will look something like this localhost:1521/orclpdb Now we are all set to execute our script. Let’s do that - User HR installed and unlocked successfully. That’s how we unlock HR user in Oracle Database 21c. Hope you enjoyed watching this video if so then give it a thumbs up. Subscribe to the channel if you haven’t already. Also press the bell icon to get notified. Thanks for watching. Will see you soon. Till then take care. This is Manish from RebellionRider.com

Channel: Manish Sharma

Views: 17,512

Keywords: manish sharma, rebellion rider, How To Unlock HR User In Oracle Database 21c by Manish Sharma, Install HR User in Oracle 21c, HR sample schema, sample schema, sample data for oracle database, oracle hr user, oracle 21c xe, manish sharma dba, oracle 21c, unlock hr user, unlock hr user and, unlock hr user and password, Install HR Schema in Oracle Database 21c Express Edition, Unlock HR User, how to unlock hr schema in oracle database 21c, install hr schema in oracle database 21c

Id: wnYi2RqiDvk

Length: 9min 33sec (573 seconds)

Published: Thu May 12 2022