ORACLE Connect to HR Schema.
ORACLE
To query the database, Oracle must have a connection to an Oracle Container Database (CDB) or Pluggable Database (PDB).
Connect to Plugable Database PDB (12c to 19c)
➔ An Oracle Pluggable Database (PDB) is a portable collection of database objects. Multiple PDBs are combined into a single container database (CDB) which creates isolation between PDBs.
Step 01: Edit TNS (Transparent Network Substrate)file, app\devi\product\12.2.0\dbhome_1\network \admin\tnsnames.ora, and add new pluggable database(same name as you give at installation time.)
Open in any editor, add the entry below, and finally save.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpdb)
)
)
Or use command line utility. As an administrator issue command tnsping orclpdb or command netca
Open Command Line Interface Window as a Administrator
Microsoft Windows [Version 10.0.19043.1288] (c) Microsoft Corporation. All rights reserved. As Administrator C:\WINDOWS\system32>tnsping orclpdb LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 30-MAR-2022 22:23:30 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) The command completed successfully C:\WINDOWS\system32>netca
Step 02: Reload listener for new pluggable database.
Open Command Line Interface Window as a Administrator
Microsoft Windows [Version 10.0.19043.1288] (c) Microsoft Corporation. All rights reserved. As Administrator C:\WINDOWS\system32>lsnrctl reload LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 30-MAR-2022 22:23:30 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) The command completed successfully C:\WINDOWS\system32>
Step 03: As Admin Set the container (from CDB$ROOT) to plugable database, Set writable, Unlock user.
Open Command Line Interface Window as a Administrator, now issue command sqlplus
Note**: As Administrator set the container from CDB$ROOT to pluggable database (orclpdb)
C:\Users\devi>sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 30 21:56:44 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter user-name: sys as sysdba
password:******
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
Note**: As Administrator Set database to pluggable database;
SQL> alter session set container=orclpdb;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
Note**: As admin change con_name mode from MOUNTED to READ/WRITE if not already;
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB MOUNTED
SQL> alter pluggable database open;
Pluggable database altered.
SQL> conn sys as sysdba
Enter password:******
Connected.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
Note**: As admin To unlock user HR and change password for hr(As a sys)
SQL> ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK;
Now Login to hr to work with
SQL> conn hr/hr@ORCLPDB
Connected.
SQL> select employee_id,last_name,first_name from employees where employee_id=100;
EMPLOYEE_ID LAST_NAME FIRST_NAME
----------- ------------------------- --------------------
100 King Steven
Note**: Change/switch login user by conn pass/user@db
SQL> conn sys/sys@orclpdb as sysdba;
Connected.
SQL> SELECT username FROM all_users;
OR
SQL> conn hr/hr@orclpdb as sysdba;
Connected.
OR
SQL>exit;
Enter user-name: hr/hr@orclpdb
OR
Enter user-name: hr@orclpdb
Enter password: hr
Last Successful login time: Wed Mar 30 2022 23:34:03 +06:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
SQL> SELECT table_name FROM user_tables; /* show all user tables */
SQL> select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
Query form PDB database tables.
As Admin Set the container to plugable database, Set writable, Login as user.
Open Command Line Interface Window as a Administrator, now issue command sqlplus
Note**: As Administrator set the container from CDB$ROOT to pluggable database (orclpdb)
C:\Users\devi>sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 30 21:56:44 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter user-name: sys as sysdba
password:******
Note**: As Administrator Set database to pluggable database;
SQL> alter session set container=orclpdb;
Session altered.
Note**: As admin change con_name mode from MOUNTED to READ/WRITE if not already;
SQL> alter pluggable database open;
Pluggable database altered.
Note**: Now Login to hr to work with
SQL> conn hr/hr@ORCLPDB
Connected.
SQL>exit;
Enter user-name: hr@orclpdb
Enter password: hr
Last Successful login time: Wed Mar 30 2022 23:34:03 +06:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700