View video tutorial
ORACLE Connect To DB
ORACLE
To Query from the database you need to connect to the Oracle Container database(CDB) or Plugable Database(PDB).
Update in progress and will be completed shortly.
Thanks for being with us.
Connect to Plugable Database
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)
)
)
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 * from departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
Database Query
Next time login and query is easy.