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.
- Open Windows Defender Firewall with Advanced Security
- Click Inbound Rules
- Click New Rule from the Actions list
- Select Port from the list to configure Oracle port
- Click TCP and then type the port number. (Usually, port number 5500, 1521)
- Choose Allow the Connection
- Choose the Network Profiles you want to enable the firewall rule.
- 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
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.
- Install Oracle Instant Client on Windows 10
- How to install Oracle Instant Client on Ubuntu
- Oracle Instant Client on macOS Big Sur
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
C:\Users\dhani>sqlplus / as sysdba SQL*Plus: Release 188.8.131.52.0 - Production on Wed May 26 14:50:30 2021 Version 184.108.40.206.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 220.127.116.11.0 - Production Version 18.104.22.168.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;
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