Things You Should Know After Installing Oracle Database 19c

Welcome to another Oracle database tutorial for beginners. Oracle database is a powerful but relatively complex database. In my previous article, you’ve learned how to install Oracle 19c on Windows 10. Install Oracle on Windows 10 pretty much easier that in Linux. But it still relatively complex if we compare it to other database such as MySQL or PostgreSQL. Now let’s continue our lessons about Oracle database. In this article, I will show you some things that you should know after installing Oracle Database.

A. Configure Windows Firewall

When we install the Oracle database on a Windows machine, we need to configure the Windows Firewall to allow the Oracle services or ports. By doing this, we will be able to access the database from the network. To allow Oracle database through the firewall, do the following.

  1. Open Windows Defender Firewall with Advanced Security
  2. Click Inbound Rules
  3. Click New Rule from the Actions list
  4. Select Port from the list to configure Oracle port
  5. Click TCP and then type the port number. (Usually, port number 5500, 1521)
  6. Choose Allow the Connection
  7. Choose the Network Profiles you want to enable the firewall rule.
  8. Finally, give a name for this rule.

B. Connect to Oracle Enterprise Manager Database Express

If you install the Oracle database Enterprise Edition, you should have the Enterprise Manager (EM) by default. We can use this feature to monitor the Oracle database performance and so on. We can track the database activity, resource usage, status and many more. To open the EM Database Express, you can open the web browser and then type the server address using this format

https://192.168.100.50:5500/em

Change the IP address with your Oracle server host. Please note that if you connect from a remote computer, make sure you have configured the firewall correctly.

C. Install SQLPlus Utility

When we install Oracle Database on a Windows 10, it will also install the SQLPlus Utility. You can run SQLPlus from the Windows command line. But, if want to connect to the Oracle server using SQLPlus from a remote computer, you probably need to install the SQLPlus Utility first. You can read the following article to install Oracle Instant Client which includes sqlplus.

D. Basic SQLPlus Commands You Should Know

Here are some useful commands that you should know with some example. Please note that for some commands, you will need to change the database name or user name so it match with your database.

Connect to Container Database (CDB)

sqlplus / as sysdba

Output

C:\Users\dhani>sqlplus / as sysdba
 SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 26 14:50:30 2021
 Version 19.3.0.0.0
 Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 Connected to:
 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 Version 19.3.0.0.0
 SQL>

Connect to Pluggable Database (PDB)

SQL> ALTER SESSION SET CONTAINER=orclpdb;

Open the Pluggable Database

SQL> ALTER DATABASE OPEN;

Show or display available services from the database

The following command will display the PDB database

select pdb from v$services;

Output

SQL> select pdb from v$services;
 PDB
 CDB$ROOT
 CDB$ROOT
 CDB$ROOT
 CDB$ROOT
 ORCLPDB
 SQL>

Create a New Oracle database user

Let’s create a new database user called dhani with the password thePassword. We will create this new user inside the Pluggable database. So first, we need to alter the session to the pluggable database.

SQL> ALTER SESSION SET CONTAINER=orclpdb;
SQL> CREATE USER dhani IDENTIFIED BY thePassword;

Now let’s grant this new user with some privileges

SQL> GRANT CONNECT, DBA, RESOURCE TO manjaro;
 Grant succeeded.

So now this new user has CONNECT, DBA and RESOURCE privileges.

E. Install Oracle SQL Developer Tool

Oracle SQL Developer Tool is a GUI software that helps us to connect and manage the Oracle databases from a centralized location. It is a handy tool that we can use to create new schema, manage Oracle database users and many more.

You can download the latest Oracle SQL Developer from the link below

https://www.oracle.com/tools/downloads/sqldev-downloads.html

Be the first to comment

Leave a Reply

Your email address will not be published.


*