Instance Details

Oracle Database and Instance: A Brief Introduction

Oracle Database is one of the most popular and widely used relational database management systems (RDBMS) in the world. It is designed to store, manage, and manipulate data in a structured and organized way. Oracle Database can handle large amounts of data and support various applications, such as enterprise resource planning (ERP), customer relationship management (CRM), e-commerce, data warehousing, and more.

An Oracle Database consists of two main components: the database and the instance. The database is a set of physical files on disk that store the data and metadata of the database. The instance is a set of memory structures and processes that run on a server and access the database files. The instance is responsible for managing the database operations, such as executing SQL statements, performing transactions, enforcing security, and maintaining data integrity.

A single database can have multiple instances associated with it. This is called a Real Application Cluster (RAC) configuration, which allows for high availability, scalability, and performance of the database. A RAC configuration consists of two or more nodes, each running an instance of the same database. The nodes are connected by a network and share the same storage system. The instances can communicate and coordinate with each other through a mechanism called cache fusion, which ensures that the data in the database is consistent and synchronized across all the nodes.

Useful Queries for Oracle Database and Instance

As a database administrator (DBA) or a developer, you may need to query the Oracle Database and instance to obtain various information, such as the database name, version, size, status, parameters, sessions, users, objects, and more. Oracle Database provides a rich set of data dictionary views that store the metadata and statistics of the database and instance. You can use SQL queries to access these views and retrieve the information you need.

Here are some examples of useful queries for Oracle Database and instance:

• To get the name and version of the database and instance, you can use the following query:

SELECT * FROM V$VERSION;

• To get the size of the database in megabytes, you can use the following query:

SELECT SUM(BYTES)/1024/1024 AS "Database Size (MB)" FROM DBA_DATA_FILES;

find database size in total 

  select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual;

• To get the status of the database and instance, such as open, mounted, or shutdown, you can use the following query:

SELECT STATUS FROM V$INSTANCE;

• To get the list of initialization parameters and their values for the instance, you can use the following query:

SELECT NAME, VALUE FROM V$PARAMETER;

• To get the list of active sessions and their details, such as username, program, status, and SQL statement, you can use the following query:

SELECT S.USERNAME, S.PROGRAM, S.STATUS, Q.SQL_TEXT FROM V$SESSION S JOIN V$SQL Q ON S.SQL_ID = Q.SQL_ID WHERE S.STATUS = 'ACTIVE';

• To get the list of users and their privileges, you can use the following query:

SELECT U.USERNAME, P.PRIVILEGE FROM DBA_USERS U JOIN DBA_SYS_PRIVS P ON U.USERNAME = P.GRANTEE;

• To get the list of tables and their properties, such as owner, tablespace, number of rows, and size, you can use the following query:

SELECT T.OWNER, T.TABLE_NAME, T.TABLESPACE_NAME, T.NUM_ROWS, S.BYTES/1024/1024 AS "Table Size (MB)" FROM DBA_TABLES T JOIN DBA_SEGMENTS S ON T.OWNER = S.OWNER AND T.TABLE_NAME = S.SEGMENT_NAME;

To find the startup time of the database:

SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.v_$instance;

To find the uptime of the database:

SELECT host_name, instance_name, TO_CHAR (startup_time, 'DD-MM-YYYY HH24:MI:SS') startup_time, FLOOR (sysdate-startup_time) days FROM  sys.v_$instance;

Thank you for reading. 😊