Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

Tuesday, January 25, 2022

What is RANK function in Oracle database ?

               Are you looking for an article on the Rank function in the Oracle database? Are you also interested in knowing how to use the RANK function in SQL? If so, then you reached the right place. In this article, we will learn the RANK function in detail Let's start.

A) What is the RANK function?

              The RANK is an analytic function in oracle we use the RANK function to get the rank of a value in a group of values. The RANK  function can be used both as an aggregate and as an analytic function.

B) Using RANK function as an aggregate function 

             We need to use the following syntax in order to use the RANK function as an aggregate function 

             RANK (Expression_1 , Expression _2 .......Expression_n)

                           WITHIN GROUP

                          (ORDER BY Expression_1 , Expression_2......Expression_n)

             In the above syntax, Expression _1 , Expression_2 are used to determine the unique row in the group.


C) Using RANK function as an Analytic function 

              We need to use the following syntax in order to use RANK function as Analytic function -

              RANK ( ) OVER ( [ query - partition _clause ]

                                             ORDER BY clause )

             In above syntax -

            Order By clause - is an optional parameter and is used to order the data within each group.

            query-partition_clause - it is an optional parameter and is used to partition results set into groups.

D) DENSE_RANK in oracle 

               The DENSE_RANK in oracle determines the rank of a row in an ordered group of rows and returns rank as Number Rank values are not skipped in the event of ties. If values are the same then the same rank number is given.

                  Learn more about oracle here - 

Tuesday, October 5, 2021

How to monitor Errors in the Alert logs in Oracle Database?

                Are you looking for details about monitoring Errors in the Alert log?  Would you also like to know about ORA  errors such as ORA-7445 , ORA-1653 ,ORA-1650 etc? If so, then you reached the right place. In this article, we will understand monitoring Errors in the Alert logs.

A) What are Alert logs?

                The important information about error messages and exceptions which occurs during various operation database is captured in the log file called Alert logs.

                 Each Oracle database for windows instance has one alert log.

B) What is the location of Alert logs? 

                 We set the path for DIAGNOSTIC_DEST initialization parameter. At this path location, the alert log file is created. Normally, the alert file name is alert _SID.log

C) Database crash errors 

                  These errors are associated with an error that can be severe enough to crash an oracle instance. To analyze the oracle instance crash issue we need to capture a trace file or a core dump file and sent it to oracle technical support.

D) ORA - 600 Errors 

                  The ORA-600 will not crash the oracle database. However, it may produce a core dump or trace file - 

           Example of trace file -

                   Errors in file /ora/home/dba/oracle/product/rdbms/log ora_ 123.trc

                   ORA-00600= internal error code , arguments : [12700],[12345],[61],[ ],[ ]....

E) ORA-1578

                 If a data block is read that appears corrupt in such case ORA-1578 is returned. This error message provides details of the file and block number.


                ORA-D1578 ORACLE data block corrupted (file#xyz, block#01)

F) ORA-1650 

                It is an error message related to the rollback segment. The error message 'ORA-1650 cannot extend rollback segment ' is produced when the rollback segment has become full. The oracle instance will not crash but the task will be terminated.

                e.g.  ORA-1650 is unable to extend rollback segment PQR by 64000 in tablespace ROLLBACK.

             Based on the above critical error messages we can build the monitoring system. 

Learn more about oracle here -

Sunday, August 29, 2021

What is difference between TRUNCATE and DELETE

         Are you working on databases or learning the database concepts, and want to know about the basic commands related to table like DELETE, TRUNCATE, then this is the right place. In this article, we are going to learn what is the meaning of these commands and the purpose of using these. TRUNCATE and DELETE command does the same job but slightly in a different manner. In this article, we will see what is the difference between the DELETE and TRUNCATE commands.

TRUNCATE command

- TRUNCATE command is DDL (Data Definition Language) command.

- If you use truncate command, then it will delete the data in a table and not a table itself.

- TRUNCATE locks the table but does not locks the rows, as it removes all the data from the table.

- It removes all the rows of the table, but the structure of the table remains as it is. It does not delete the   

   table structure, columns, indexes and constrains.

- This command does not require where clause.

- After truncate operation rollback process is not possible because this command does not maintain any log file from which we can rollback the data.

                                  Syntax –

                                TRUNCATE TABLE table_name;

                                 Example –

                                 TRUNCATE TABLE Customer;

DELETE command

-                           DELETE command is DML(Data Manipulation Language) command.

-                          DELETE command deletes all the records from a table.

-                          DELETE locks the rows, for deletion each row in the table is locked.

-                         If we want to delete a specific row/record from a table then we can use the WHERE clause.

-                       The table structure, indexes, attributes will not be deleted after the DELETE operation.

-                        Rollback operation can be possible after DELETE operation but we have to rollback the data before the COMMIT statement. After the COMMIT statement we can not rollback the data.


·                                                                     Syntax –

                         DELETE FROM table_name;

                                     Example -

                         DELETE FROM Customer;


                                    Syntax (Using WHERE clause)-

                        DELETE FROM table_name WHERE condition;


                                    DELETE FROM Customer WHERE id=1;

 The above delete operation will delete all the records from the ‘Customer’ table without deleting the ‘Customer’ table itself.

Difference between TRUNCATE and DELETE command

-                   DELETE command deletes the specific commands based on conditions defined by the WHERE clause,        but it does not free up the space.

-                 TRUNCATE command does not require a WHERE clause. After executing the TRUNCATE statement it releases all the memory along with the removal of data.

-                DELETE command maintains the log but the TRUNCATE command does not maintain the logs, hence the TRUNCATE command is faster than the DELETE command.

-               TRUNCATE command uses less Transaction space.



Sunday, July 11, 2021

Howto fix - "ORA-00245: control file backup failed" issue

 Are you looking for an article about fixing "ORA-00245: control file backup" error? If so, then you reached the right place. In this article, we will see what is root cause of "ORA-00245: control file backup" error message at the database side and how to fix it?

What is "ORA-00245: control file backup" error ?

This error message occurs when Archive logs backup fails. This is Oracle database level functionality which takes backup of archive logs so that you can restore your device quickly and seamlessly in the event of data loss.

Whar the error messages are associated with "ORA-00245: control file backup"?

Here is the list of error messages associated with "ORA-00245: control file backup" -

  • RMAN-03009: Failure of full resync command on default channel
  • RMAN-03002: Failure of configuring command
  • RMAN-03014: implicit resync of recovery catalog failed
  • ORA-00245: control file backup failed; in Oracle RAC, target might not be on shared storage


What is the root cause of "ORA-00245: control file backup" error message?

The root cause can be the failure of   SNAPSHOT CONTROLFILE due to local file system configuration e.g. /abc CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/<oracle home>/snapcf_pqr1.f'

However, you need to find out all possible other root causes.


How to fix  "ORA-00245: control file backup" error?

In order to fix this error message change SNAPSHOT CONTROLFILE NAME  RAMN parameter to shared storage e.g. +RECO_PROD

Learn more about Oracle here -


Wednesday, July 8, 2020

What are the Types of Data Model?

Are you looking for an article which explains different types of Data Model? Are you also interested in knowing what data model to choose for business? If so, then you reached the right place. In this article, we will understand the different types of data models that are used in Enterprise applications.

What is the Data Model?

The process in which the data is analyzed and defined based on the requirements in order to support the business processes is called Data Modeling and the output of data modeling process is a data model.

What are the Types of Data Model?

There are three types of data models. The list of data models is:

  1. Conceptual Data Models: It is a high-level data model and used for static business structures and concepts
  2. Logical Data Models:  This data model elaborates entity types, data attributes, and relationships between entities
  3. Physical Data Models: This type of data model provides detailed information such as data type, length, relationships, referential integrity etch.

Selection of data model-

Each data model has its own purpose. Depending on the purpose we need to decide which model is suitable for business use cases.

For example, if we are planning to explain to business users how business attributes are structured in the application then the Logical Data Model will be a better choice.

If we are planning to use integrate multiple systems and would like to explain how systems work as a whole then Conceptual Data Model will be helpful as it gives a high-level view of the underlying system.

If the use case is to develop a database based on data model then we need detailed information about each and every component of the data model. In this case, the Physical Data Model will be the best suited. Normally, developers use the Physical Data Model to configure database objects.

Detailed information is explained in the video below:

Tuesday, June 16, 2020

What are the differences between RAC and GRID?

Are you looking for the differences in RAC and GRID system in Oracle? Are you also would like to know how RAC and GRID systems related to each other? If yes, then you reached the right place. In this article, we will see what are the differences between RAC and Grid systems.

What is RAC system?

The database system comprises the configuration of multiple servers which are combined together with clustering software or program and accessing the shared disk storage structures is known as Oracle Real Application Clusters (RAC) system.

What is Grid System

The Grid system represents the pool of database hosts or servers, along with a pool of storage, and networks in an inter-related resource platform. Grid is used for effective workload management within the grid database.

What is the relationship between RAC and Grid systems?

The RAC is an integral part of Grid computing and RAC helps Grid computing for high availability information sharing.

What are differences between RAC and Grid?

Here is a list of the differences between RAC and Grid systems

User management in Cluster is centralized
User management in the Grid is decentralized
In Cluster, the inter-operability is VIA and it is proprietary
No standard is developed for inter-operability
Ownership is singe in case of Cluster
The multiple ownership exits in Grid
Throughput is medium
Throughput is high
The guaranteed capacity
Capacity varies for grid implementation
Centralized resource management
Decentralized resource management
Can be used with commodity computer
Can be used with commodity and high end computers
The scheduling is centralized
The scheduling is decentralized
Single system image is possible
Single system image is not possible

Tuesday, July 2, 2019

Important stats related SQL queries with details of determining Tablespace

This article on Oracle database provides details about how can we monitor tablespace during sql execution. In this article we will also see how to what are the sqls are currently executing. During this process we will understand, how to get SQL ID associated with each sql statement.

Tablespaces in the Oracle database

The tablespace is one or more logical storage units in the Oracle database. Each database table belongs to some tablespace in the oracle database. Normally, Oracle DBA has alerts setup to monitor tablespace, so that SQL or jobs which are based on SQL statements will not fail due to tablespace issue. However, as a developer, we can also monitor tablespace using the SQL statement below -


To determine used and free tablespace, use the sql below

select tablespace_name, SUM(bytes_used/1024/1024/1024) "Temp_Used", sum(bytes_free/1024/1024/1024) "Temp_Free"

from v$temp_space_header where tablespace_name like '%TEMP%' group by tablespace_name;

Determine currently running Active DB Sessions

As Oracle developer or application developer, we can use the query below to determine the active DB sessions in the Oracle database -

This query will return important details e.g. USERNAME, STATUS, database MACHINE, SERVICE_NAME, SQL_ID etc.

Determine which SQL statement is running in Database

Before determining SQL statement, we need to determine what are the active session associated with SQL statement which can be determined with help of above section - 'Determine currently running Active DB Sessions'.

By using the result of above query, get the SQL_ID associated with active session. Once we have SQL_ID then execute the SQL statement below to determine the query which is running in the database -

SELECT * FROM GV$SQL WHERE SQL_ID = '61f38skxkw8hc'

e.g. Here '61f38skxkw8hc' is value for SQL_ID

Get Explain Plan
In order to get explain plan execute the statement below -

select * from TABLE(dbms_xplan.display_awr('61f38skxkw8hc'));

Get Start and End time of SQL Query

select sql_id, first_load_time, last_load_time, elapsed_time, cpu_time from   v$sql where  sql_text like 'with /* slow */ rws as (%';

select sql_id, elapsed_time_delta/executions_delta avg_elapsed
from   sys.dba_hist_sqlstat
where  snap_id = :snap;

Getting SQL Event details

A.SQL_TIME BETWEEN TO_DATE('03/10/2018 09:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND TO_DATE('03/11/2018 6:00:00', 'MM/DD/YYYY HH24:MI:SS')

Monday, December 10, 2018

Issues noticed during Materialized View Creation - ORA-23413: table "CMX_ORS"."C_B_CUST" does not have a materialized view log

Did you encounter with ORA-23413 the Oracle database error while creating a Materialized view? Are you also noticing ORA-01031: insufficient privileges error? If yes, then refer below article about explanation and solution to these database errors?

SQL Error: ORA-23413: table "CMX_ORS"."C_B_CUST" does not have a materialized view log

Explanation: This type of error occurs if you try to create a Materialized view on table C_BO_CUST without creating the Materialized View Log. It is mandatory to create log definition first before creating actual Materialized View.

Error Message:
SQL Error: ORA-23413: table "CMX_ORS"."C_B_CUST" does not have a materialized view log
23413. 00000 -  "table \"%s\".\"%s\" does not have a materialized view log"
*Cause:    The fast refresh cannot be performed because the master table does not contain a materialized view log.

Solution: Use the sample below to create the Materialized View Log -

SQL Error: ORA-01031: insufficient privileges

Explanation: ORA-01031 - insufficient privileges is the generic message which normally occurs if required privileges are not available for given operation. In the case of Materialized view, if user does not have "CREATE MATERIALIZED VIEW" privileges then 'ORA-01031: insufficient privileges'  error message will be reported.

Error Message:
SQL Error: ORA-01031: insufficient privileges 01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password 
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.

Solution: In order to get required privileges we need to reach out Oracle DBA. In the current case, we need to ask DBA to provide "CREATE MATERIALIZED VIEW" privileges to given user. Assume that you are using TEST_USER to create Materialized view then ask DBA to provide privileges below -

The video below provides a detailed explanation and prerequisites for Material views : 

How does secure data sharing work in snowflake ?

               Are you looking for the details about how data sharing works in snowflake? Are you also interested in knowing what are things...