How to resolve ORA-14552: Cannot Perform a DDL Commit or Rollback error?

ORA-14552 is a commonly encountered error message in Oracle databases that can occur when attempting to perform a DDL (Data Definition Language) commit or rollback within a query or DML (Data Manipulation Language) statement. This error can cause frustration for database administrators and developers, as it can lead to unexpected behavior and potentially compromise data integrity.

DDL statements are used to define the database structure, such as creating, altering, or dropping tables, indexes, or views. These statements are typically executed by a database administrator or a developer with sufficient privileges. In contrast, DML statements are used to manipulate the data stored within the database, such as inserting, updating, or deleting rows from tables. DML statements are typically executed by applications or end-users.

The ORA-14552 error message occurs when attempting to perform a DDL commit or rollback within a query or DML statement. This can happen, for example, when executing a SELECT statement that includes a DDL statement, such as CREATE TABLE or DROP TABLE. It can also occur when executing a DML statement that triggers a DDL statement, such as a trigger that creates or drops a table.

When this error occurs, the transaction is typically rolled back, and the changes made up to that point are discarded. This can result in data inconsistencies and potential loss of data.

To avoid this error, it is important to separate DDL and DML statements into separate transactions. For example, if a DDL statement needs to be executed during a query or DML statement, it should be executed in a separate transaction before or after the query or DML statement.

It is also important to ensure that DDL statements are executed with sufficient privileges and that they do not conflict with other transactions that may be running concurrently. This can be achieved by using locking mechanisms and transaction isolation levels to ensure that transactions do not interfere with each other.

The ORA-14552 error message can be a frustrating and potentially dangerous issue for database administrators and developers. However, it can be avoided by separating DDL and DML statements into separate transactions and ensuring that DDL statements are executed with sufficient privileges and do not conflict with other transactions. By following these best practices, database administrators and developers can ensure the integrity and consistency of their data and avoid potential data loss. 

What is error - ORA-12801: error signaled in parallel query server P00D in Oracle.

 Are you looking for how to fix the error - "ORA-12801: error signaled in parallel query server P00D" in Oracle? Are you also interested in knowing what are the causes of the "ORA-12801: error signaled in parallel query server P00D" error? If so, then you reached the right place. In this article, we will learn more about ORA-12801 error and how to fix it.

Introduction:

Oracle is a powerful database management system used by many organizations for their data storage and retrieval needs. When dealing with large datasets, Oracle can utilize parallel processing to speed up queries. However, sometimes an error can occur during a parallel query execution, and one such error is ORA-12801: error signaled in parallel query server P00D. In this article, we will discuss the meaning, causes, and solutions for this error.

Meaning of ORA-12801 Error:

The ORA-12801 error indicates that an error has occurred in a parallel query execution. The P00D identifier in the error message refers to the specific parallel query server that encountered the error. The error message can have different variations, including:

ORA-12801: error signaled in parallel query server P00D

ORA-12801: error signaled in parallel query server P00D, instance INSTANCE_NUMBER

ORA-12801: error signaled in parallel query server P00D, SID SERIAL_NUM

The variations indicate different instances of the error, but the meaning and causes remain the same.

Causes of ORA-12801 Error:

There can be several causes of the ORA-12801 error, including:

Insufficient Resources: Parallel queries require more resources than regular queries. If the system does not have sufficient resources, such as CPU, memory, or disk I/O, the query may fail with this error.

Configuration Issues: Incorrect configuration of the parallel query parameters, such as parallel degree or query block size, can cause the ORA-12801 error.

Hardware Failures: Hardware failures, such as disk or network failures, can cause the parallel query to fail.

Software Bugs: Bugs in the Oracle software can also cause the ORA-12801 error.

Solutions for ORA-12801 Error:

Here are some possible solutions for the ORA-12801 error:

Increase Resources: If the error is due to insufficient resources, you can try increasing the system resources such as CPU, memory, or disk I/O. You can also consider reducing the parallel degree of the query to consume fewer resources.

Check Configuration: Verify that the parallel query parameters, such as parallel degree and query block size, are correctly set. Incorrect configuration can cause the ORA-12801 error.

Monitor System: Keep track of system performance during parallel query execution. This can help identify performance bottlenecks and resource constraints that may be causing the error.

Verify Hardware: Check the hardware components, such as disks and network, for any failures. Fix any issues that are found.

Update Software: If the error is due to a software bug, updating Oracle software to the latest patch level or version can help resolve the issue.

Contact Support: If none of the above solutions work, you can contact Oracle support for assistance. They can help diagnose the issue and provide guidance on how to resolve it.

In conclusion, the ORA-12801: error signaled in parallel query server P00D error can occur due to various reasons, such as insufficient resources, configuration issues, hardware failures, or software bugs. To resolve the issue, you can increase system resources, verify configuration settings, monitor system performance, verify hardware, update software, or contact Oracle support. By understanding the causes and taking appropriate measures, you can resolve the ORA-12801 error and ensure smooth execution of parallel queries in Oracle.

Learn more about Oracle here

What is ORA-12154: TNS could not resolve service name error in Oracle database

 Would you be interested in knowing what causes ORA-12154 error and how to resolve it? Are you also interested in knowing important tips in order to resolve ORA-12154 error? If so, then you reached the right place. In this article, we will understand how to fix this error. Let's start.

What is ORA-12154 error in Oracle database?

ORA-12154: TNS could not resolve service name is a common error encountered while connecting to an Oracle database. This error occurs when the TNS (Transparent Network Substrate) service is unable to find the service name specified in the connection string.

Here are some tips to resolve the ORA-12154 error:

• Check the TNSNAMES.ORA file: This file is used by TNS to resolve the service name to an actual database connection. Check the file for any spelling or syntax errors in the service name.

• Verify the service name: Make sure that the service name specified in the connection string matches the service name defined in the TNSNAMES.ORA file.

• Update the TNS_ADMIN environment variable: If you are using a different TNSNAMES.ORA file, make sure that the TNS_ADMIN environment variable points to the correct location.

• Check the listener status: Ensure that the listener is running and able to accept incoming connections. You can check the listener status by using the “lsnrctl status” command.

• Restart the listener: If the listener is not running, restart it using the “lsnrctl start” command.

• Check the network connectivity: Verify that the server hosting the database is reachable and there are no network issues preventing the connection.

• Reinstall the Oracle client: If all other steps fail, reinstalling the Oracle client may resolve the ORA-12154 error.

• Verify the Oracle Home environment variable: Make sure that the Oracle Home environment variable is set correctly to the location of the Oracle client software.

• Check the SQLNET.ORA file: This file is used to configure the Oracle Net Services that provide the communication between the client and the server. Verify that the correct settings are configured in the SQLNET.ORA file.

• Use the TNS Ping utility: The TNS Ping utility is used to test the connectivity to the database by checking the availability of the listener. You can use the “tnsping” command to run this utility.

• Check the firewall settings: If the server hosting the database is located behind a firewall, verify that the firewall is configured to allow incoming connections on the specified port.

• Disable the Windows Firewall: If the Windows firewall is enabled, it may be blocking the connection to the database. Try disabling the Windows firewall temporarily to see if it resolves the ORA-12154 error.

• Check the port number: Make sure that the port number specified in the connection string matches the port number used by the listener.

• Try a different connection method: If the error persists, try connecting to the database using a different method such as SQL*Plus or SQL Developer.

• Check for multiple TNSNAMES.ORA files: If you have multiple Oracle client installations on the same machine, there may be multiple TNSNAMES.ORA files. Make sure you are using the correct TNSNAMES.ORA file for your current Oracle client installation.

• Check the service name format: The service name can be specified in different formats such as a simple string, an easy connect string, or a connect descriptor. Make sure that you are using the correct format for your particular scenario.

• Upgrade the Oracle client software: If you are using an outdated version of the Oracle client software, upgrading to the latest version may resolve the ORA-12154 error.

• Check for incorrect hostname or IP address: Verify that the hostname or IP address specified in the connection string is correct and matches the actual hostname or IP address of the database server.

• Verify the SERVICE_NAME parameter in the database: If you are connecting to a database that uses the SERVICE_NAME parameter instead of the SID, make sure that the service name specified in the connection string matches the actual service name in the database.

• Check the network configuration: If you are using a complex network configuration such as a VPN, make sure that the network is configured correctly and that the database server is accessible from the client machine.

• Verify that LDAP is listed as one of the values of the names.directory_path parameter in the sqlnet.ora Oracle Net profile.

Verify that the LDAP directory server is up and that it is accessible.

 

Verify that the net service name or database name used as the connect identifier is configured in the directory.

 

Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier

By following these tips, you should be able to resolve the ORA-12154 error and successfully connect to your Oracle database. If the error persists, it is important to seek the help of a qualified Oracle database administrator or support specialist.

Learn more about Oracle here

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 - 

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.

                  e.g. 

                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 -

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;

                                    Example-

                                    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.

 

 

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 -

 

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:

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

Cluster	Grid
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

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 -

SELECT B.TABLESPACE_NAME, TBS_SIZE SIZE_MB, A.FREE_SPACE FREE_MB FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024 ,2) AS FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS TBS_SIZE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME UNION SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TBS_SIZE FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME ) B WHERE A.TABLESPACE_NAME(+)=B.TABLESPACE_NAME;

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 -

SELECT * FROM GV$SESSION WHERE STATUS = 'ACTIVE' AND TYPE <> 'BACKGROUND' AND USERNAME = 'TEST_SCHEMA_ID';

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

SELECT SNAP_ID, SQL_TIME, SESSION_ID, USER_ID, SQL_ID, SQL_OPNAME,
TO_CHAR(A.SQL_EXEC_START, 'MM/DD/YYYY HH24:MI:SS'), SESSION_STATE,
EVENT, TIME_WAITED, PROGRAM, A.MACHINE FROM DBA_HIST_ACTIVE_SESS_HISTORY A
WHERE
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')
AND SQL_ID = 'XXX'
AND EVENT IS NOT NULL
AND EVENT <> 'CELL SINGLE BLOCK PHYSICAL READ'
ORDER BY SQL_TIME ASC

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 -

CREATE MATERIALIZED VIEW LOG ON C_B_CUST

   PCTFREE 5

   TABLESPACE CMX_TEMP

   STORAGE (INITIAL 5K NEXT 5K);

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 -

GRANT CREATE MATERIALIZED VIEW TO TEST_USER

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