Oracle database is a relational database management system. It is also called OracleDB, or simply Oracle. Oracle Database is a multi-model database management system produced and marketed by Oracle Corporation. It is a database commonly used for running online transaction processing, data warehousing and mixed database workloads.
It was created in 1977 by Lawrence Ellison and other engineers. It is one of the most popular relational database engines in the IT market for storing, organizing, and retrieving data.
Latest stable version is oracle 19c which is a multi-model database that provides full support for relational data and non- relational data, such as JSON, XML, text, spatial, and graph data.
SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.
The SQL*Plus executable is usually installed in $ORACLE_HOME/bin, which is usually included in your operating system PATH environment variable. You may need to change directory to the $ORACLE_HOME/bin directory to start SQL*Plus.
Just open terminal in Linux or CMD in windows and type
sqlplus /nolog
The "/nolog" parameter means "start sqlplus, but do not log into a database
connect database using sqlplus using following command
SQL > connect sys as sysdba SQL > Enter password:
Use the following command to clear the screen in sqlplus.
SQL > clear scr
To exit sqlplus, just type
SQL > exit
And press enter
To retrieve all version information from Oracle, you could execute the following SQL statement:
SQL> SELECT * FROM v$version;
This query would output something like this:
Banner -------------------------------------------------------------------------------------- Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production
If you only wanted the Oracle version information, you execute the following SQL statement:
SELECT * FROM v$version WHERE banner LIKE 'Oracle%';
It should return something like this:
Banner -------------------------------------------------------------------------------------- Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
To start database following syntax is used
STARTUP [NOMOUNT | MOUNT | RESTRICT | UPGRADE | FORCE | OPEN READ ONLY]
Parameter inside [] are optional
Example:
STARTUP FORCE; STARTUP OPEN READ WRITE RESTRICT; STARTUP;
startup pfile=d:\ora901\database\initORA901.ora
To stop database following syntax is used
SHUTDOWN [IMMEDIATE | ABORT]
SHUTDOWN IMMEDIATE; SHUTDOWN;
SQL -> show con_name
SQL -> show pdbs;
SQL -> Alter session set container = PDB_NAME
To open pluggable database, following command is used
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN READ ONLY FORCE; ALTER PLUGGABLE DATABASE ALL OPEN; ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;
To close pluggable database, following command is used
ALTER PLUGGABLE DATABASE <pdb-name-clause> CLOSE [IMMEDIATE];
Example
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE IMMEDIATE; ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE; ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE IMMEDIATE;
SELECT name, open_mode FROM v$pdbs;
SQL> select name from v$database
To retrieve all users from Oracle, you could execute the following SQL statement:
SELECT * FROM all_users; SELECT username FROM all_users;
If you only wanted dba users, you execute the following SQL statement:
SELECT username FROM dba_users;
Syntax:
SELECT ANY DICTIONARY (SELECT | INSERT | UPDATE | DELETE) ANY TABLE
SELECT DISTINCT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER = 'APEX_200200'
DROP USER APEX_200200 CASCADE
create user user_name identified by password;
create user testdb identified by Admin#12345; CREATE USER saidul IDENTIFIED BY Admin#12345;
ALTER USER user_name IDENTIFIED BY new_password;
ALTER USER saidul IDENTIFIED BY Saidul#123;
Oracle Net Listener is a separate process that runs on the database server. It receives incoming client connection requests and manages the traffic of these requests to the database server.
Run the following command to restart the Oracle Net listener:
$ $ORACLE_HOME/bin/lsnrctl start
For specific listener
$ $ORACLE_HOME/bin/lsnrctl start [listenername]
check all listener name
$ more $ORACLE_HOME/network/admin/listener.ora
You must specify the listener name only if it is different from the default listener name, LISTENER. The listener name is mentioned in the listener.ora file. To display the contents of this file, run the following command:
How to check the status of the current listener in your database server?
lsnrctl status
The System Global Area (SGA) is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. Following command is use to show parameter of System Global Area (SGA),
show parameter sga;
A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. Following command is use to show parameter of Program Global Area (PGA)
show parameter pga;
show parameters area_size
We can also see the SGA RAM region by issuing the show sga command.
show sga;
Simply, PFILE and SPFILE are the initialization parameter file which contains some parameters like data_transfer_cache_size, Java_pool_size, sga_target, large_pool_size etc. When we start the machine, the initialization parameter file is read and the characteristics of the Instance are established by parameters specified within the initialization parameter file.
There are two types of initialization parameters files:
Server Parameter File (SPFILE)
Text Initialization Parameter File(PFILE)
SPFILEs are available in Oracle 9i and above. All prior releases of Oracle are using PFILEs. By default, Oracle database initializes with spfile, if not exit spfile, then it initializes with PFILE.
The “SHOW PARAMETERS” command from SQL*Plus (i.e.: SHOW PARAMETERS timed_statistics)
V$PARAMETER view – it displays the currently in effect parameter values
V$PARAMETER2 view – it displays the currently in effect parameter values, but “List Values” are shown in multiple rows
V$SPPARAMETER view -it displays the current contents of the server parameter file.
Now we are going to have to look at How the Oracle Instance is initialized?
Whenever the Oracle instance starts, first it looks to the $ORACLE_HOME/dbs (UNIX, Linux) or $ORACLE_HOME/database (Windows) directory for the following files (in this order):
spfileSID.ora (SPFILE)
Default SPFILE (SPFILE)
initSID.ora (PFILE)
Default PFILE (PFILE)
SPFILE have its own advantages which are mention below:-
No need to restart the database in order to have a parameter changed and the new value stored in the initialization file
Reduce human errors: Parameters are checked before changes are accepted
An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)
How could we switch from SPFILE to PFILE and vice-versa?
Switch from SPFILE to PFILE:
CREATE PFILE FROM SPFILE;
Backup and delete SPFILE
Restart the instance
Switch from PFILE to SPFILE:
CREATE SPFILE FROM PFILE=’Location of the PFILE’;
Restart the instance (the PFILE will be in the same directory but will not be used. SPFILE will be used instead)
Converting SPFILE to PFILE and vice-versa
This could be done in order to have a backup in the other format or to change the initialization file for the database instance.
ALTER SYSTEM SET MEMORY_TARGET = 5000M SCOPE=SPFILE;