Introduction In an Oracle Database, privileges are used to control access to the database's objects and operations. A privilege is a permission to perform a specific action on a specific object, such as SELECTing data from a table or EXECUTing a stored procedure. When you create a user in the database, that user does not have any privileges by default. In order for the user to be able to perform any actions, you must grant them the necessary privileges. This can be done by using the GRANT command in SQL command line. When a user has been granted a privilege, ... Read More

Introduction In Oracle databases, a schema is a logical grouping of related objects, such as tables, views, and stored procedures. Each schema belongs to a specific database user and has a set of associated privileges. To list all tables in a schema in an Oracle database, you can use one of the views in the data dictionary. The `ALL_TABLES` view contains one row for each table in the current schema, while the `DBA_TABLES` view contains one row for each table in the entire database. To list the tables in a schema, you can use a SELECT statement to query the ... Read More

Introduction Oracle is a powerful, versatile relational database management system that is widely used in the enterprise. One of the most common tasks when working with Oracle is to check if a specific object, such as a table, view, trigger, or other types of object, exists in the database. This can be useful for a variety of purposes, such as validating input data, checking for dependencies, and more. In this article, we will explore several methods for checking if a table, view, trigger, or other object exists in Oracle. Using the "DBA_OBJECTS" View One of the easiest and most straightforward ... Read More

Problem:You want to display open cursors in Oracle.SolutionWe can query the data dictionary to determine the number of cursors that are open per session. "V$SESSION" provides a more accurate number of the cursors currently open than "V$OPEN_CURSOR".Exampleselect  a.value  , c.username  , c.machine  , c.sid  , c.serial# from v$sesstat a  , v$statname b  , v$session c where a.statistic# = b.statistic# and c.sid  = a.sid and b.name  = 'opened cursors current' and a.value  != 0 and c.username IS NOT NULL order by 1, 2;The OPEN_CURSORS initialization parameter determines the maximum number of cursors a session can have open.Read More

Problem:You want to identify the SQL statements consuming more resources in Oracle.Solution“V$SQLSTATS" view displays performance statistics for SQL statements that have recently executed. You can also use "V$SQL” and “V$SQLAREA" to report on SQL resource usage. "V$SQLSTATS” is faster and retains information for a longer period of time, but contains only a subset of the columns in “V$SQL" and "V$SQLAREA”.Exampleselect * from( select   sql_text  ,buffer_gets  ,disk_reads  ,sorts  ,cpu_time/1000000 cpu_sec  ,executions  ,rows_processed from v$sqlstats order by cpu_time DESC) where rownum < 20;

Problem:You want to view where Oracle SQL is taking time within a SQL execution plan.SolutionWith Oracle 11g version, we can view SQL execution plan progress while the SQL is running. The “V$SQL_PLAN_MONITOR” view contains a row for each step of a SQL statement’s execution plan. Below SQL will help to view the execution plan along with the progress.The “V$SQL_PLAN_MONITOR" provides you with information on the steps that are using the most resources. The statistics in "V$SQL_PLAN_MONITOR” are updated every second.We can also generate a real time text, HTML, or even a XML report of query progress within an execution plan ... Read More

Problem:You want to know how much longer a long running SQL might ake to finish.SolutionWe can use “V$SESSION_LONGOPS" view to know the approximate time of a query left to execute. "V$SESSION_LONGOPS” view displays the status of various database operations that have been running for longer than six seconds. Please note that this view give you only a rough estimate of when a SQL might complete.Exampleselect   a.username  , a.opname  , b.sql_text  , to_char(a.start_time, 'DD-MON-YY HH24:MI') start_time  , a.elapsed_seconds how_long  , a.time_remaining secs_left  , a.sofar  , a.totalwork  , round(a.sofar/a.totalwork*100, 2) percent from v$session_longops a     ,v$sql         ... Read More

Problem:You want to monitor currently executing SQL statistics in Oracle.SolutionIf your database is Oracle Database 11g, you can use the following query to select from the “V$SQL_MONITOR” to monitor the near real time resource consumption of SQL queries.The statistics in “V$SQL_MONITOR” are updated every second. This helps us to view the resource consumption as it updates. These statistics are gathered by default when a SQL statement runs in parallel or consumes more than 5 seconds of CPU or I/O time.The “V$SQL_MONITOR" view includes a subset of statistics contained in the "V$SQL”, “V$SQLAREA", and "V$SQLSTATS” views.The “V$SQL_MONITOR" view displays real-time statistics ... Read More

Problem:You want to limit the amount of resources a user can consume in your database.SolutionTo limit the resources, we can follow below steps.We can use below SQL statement to view the current setting of RESOURCE_LIMIT in our database.select name, value from v$parameter where name='resource_limit';Create a profile to limit the resources and assign it to a user. It won’t limit the CPU utilization though.ExampleCREATE PROFILE test_profile LIMIT    SESSIONS_PER_USER          2    CPU_PER_SESSION            UNLIMITED    CPU_PER_CALL               300000    CONNECT_TIME           ... Read More

Problem:You want to use Primary Key Constraints and Foreign Key Constraints to enforce database integrity in Oracle.SolutionAny DML statement (an INSERT, UPDATE, or DELETE) executed on tables defined with integrity, helps to ensures that the rows in the tables maintain their integrity.Let’s look at some examples that show the enforcement of a primary key constraint. The customers table’s primary key is the customer_id column, which means that every value stored in the customer_id column must be unique. If you try to insert a row with a duplicate value for a primary key, the database returns the error ORA-00001, as in ... Read More