Understanding Oracle DBMS_CLOUD Common Packages

Understanding Oracle DBMS_CLOUD Common Packages

Oracle Cloud provides the DBMS_CLOUD package to simplify the integration of cloud-based storage, data loading, and external data access. 

This package is useful for managing data in Oracle Autonomous Database and other cloud environments, enabling users to efficiently interact with external cloud services.

Overview of DBMS_CLOUD

The DBMS_CLOUD package is a collection of procedures and functions that help users perform operations such as loading data, managing credentials, and accessing external storage. It eliminates the need for complex scripting and provides secure access to cloud-based resources.

DBMS_CLOUD Packages and their usages.

1. DBMS_CLOUD.CREATE_CREDENTIAL

This procedure securely stores cloud service credentials, which are used to authenticate database access to object storage.

BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'my_credential',
        username => 'your_username',
        password => 'your_password'
    );
END;

2. DBMS_CLOUD.LIST_OBJECTS

Retrieves a list of objects from a specified cloud storage bucket.

SELECT * FROM TABLE(DBMS_CLOUD.LIST_OBJECTS(
    credential_name => 'my_credential',
    location => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/'
));

3. DBMS_CLOUD.GET_OBJECT

Downloads a file from cloud storage into the database directory.

BEGIN
    DBMS_CLOUD.GET_OBJECT(
        credential_name => 'my_credential',
        object_uri => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/sample.csv',
        directory_name => 'DATA_PUMP_DIR',
        file_name => 'sample.csv'
    );
END;

4. DBMS_CLOUD.COPY_DATA

Loads data from an external source (such as an object storage CSV file) into an Oracle database table.

BEGIN
    DBMS_CLOUD.COPY_DATA(
        table_name => 'employees',
        credential_name => 'my_credential',
        file_uri_list => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/employees.csv',
        format => JSON_OBJECT('delimiter' VALUE ',', 'skipheaders' VALUE 1)
    );
END;

5. DBMS_CLOUD.DELETE_CREDENTIAL

Removes a stored credential when it is no longer needed.

BEGIN
    DBMS_CLOUD.DELETE_CREDENTIAL('my_credential');
END;

Benefits of Using DBMS_CLOUD

• Secure Cloud Storage Integration – Simplifies connecting Oracle databases with cloud storage services.
• Efficient Data Loading – Enables quick and efficient bulk data imports from external sources.
• Flexible Querying – Allows querying external data sources without physically moving data.
• Improved Performance – Optimized for cloud environments, reducing manual data transfer overhead.

Understanding Oracle SQL ID and Its Role in Performance Issues

 Understanding Oracle SQL ID and Its Role in Performance Issues

In Oracle databases, the SQL ID is a fundamental identifier that helps database administrators (DBAs) track and analyze SQL statements. When performance issues arise, SQL IDs can provide critical insights into problematic queries, enabling optimization and troubleshooting.

What is an Oracle SQL ID?

An SQL ID is a unique identifier assigned to an SQL statement when it is first parsed and stored in the library cache. It remains constant for that particular SQL text, helping DBAs identify and analyze the query’s execution behavior over time.

Why is SQL ID Important in Performance Tuning?

SQL IDs are vital for performance troubleshooting because they allow DBAs to:

• Locate specific queries in dynamic performance views.
• Analyze execution plans to identify inefficiencies.
• Monitor query performance using AWR (Automatic Workload Repository) and SQL Monitoring reports.
• Compare query performance over different executions.

Identifying Performance Issues Using SQL ID

Once you have an SQL ID, you can investigate performance issues using several approaches:

1. Retrieve Execution Plan

Using the DBMS_XPLAN.DISPLAY_CURSOR function, you can analyze the execution plan to identify inefficiencies such as full table scans, missing indexes, or suboptimal join methods.

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('<Your SQL ID>', 0, 'ALL'));

2. Query SQL History from AWR

To see historical performance data related to an SQL ID, use:

SELECT * FROM dba_hist_sqlstat WHERE sql_id = <Your SQL ID>
;

This helps determine if performance degradation is recent or has been ongoing.

3. Check Active Sessions

If an SQL statement is currently running and causing performance issues, use:

SELECT sql_id, status, username, sql_text FROM v$session WHERE sql_id = <Your SQL ID>
;

This provides information on users executing the query and its current status.

Common Causes of SQL Performance Issues

1. Full Table Scans – Ensure proper indexing to avoid unnecessary full table scans.
2. Inefficient Joins – Optimize joins by using appropriate indexes and execution strategies.
3. Outdated Statistics – Regularly update optimizer statistics using:

   EXEC DBMS_STATS.GATHER_SCHEMA_STATS('your_schema');
   

4. High Parsing Overhead – Use bind variables to reduce excessive hard parsing.
5. Blocking Sessions – Check for locking issues using V$LOCK and V$SESSION.

Conclusion

SQL ID is a powerful tool for diagnosing and resolving performance issues in Oracle databases. By leveraging execution plans, historical data, and session monitoring, DBAs can pinpoint problematic queries and implement optimizations. Regular performance analysis ensures smooth database operations and improved efficiency.

 

How to Find the SQL ID in Oracle Developer

SQL ID is a unique identifier assigned by Oracle to each SQL statement executed in the database. It is essential for performance tuning, troubleshooting, and analyzing query execution plans. In this blog post, we will explore various methods to find the SQL ID in Oracle Developer and SQL*Plus.

1. Using V$SQL to Find SQL ID

Oracle provides the V$SQL view, which contains details about executed SQL statements, including the SQL ID. The following query retrieves the SQL ID for a specific SQL text:

SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text LIKE '%your_query_pattern%';

Replace %your_query_pattern% with a part of your SQL statement to filter the results.

2. Finding SQL ID for a Running Query in V$SESSION

If a query is currently executing, you can find its SQL ID from the V$SESSION view:

SELECT sid, serial#, sql_id, sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE';

This query lists active sessions and their corresponding SQL IDs.

3. Using SQL Developer’s Autotrace or Explain Plan

Steps to Find SQL ID in Oracle SQL Developer:

1. Open Oracle SQL Developer and execute your query.
2. Enable Autotrace:
   • Click on Query Builder > Enable Autotrace.
   • Run your SQL statement.
   • The SQL ID will be displayed in the execution plan output.
3. Alternatively, run:

   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALL'));
   

   This retrieves the SQL ID of the last executed statement in your session.

4. Using AWR to Retrieve SQL ID for Historical Queries

Oracle's Automatic Workload Repository (AWR) stores SQL execution history, which is useful when identifying SQL IDs for past queries:

SELECT sql_id, sql_text, elapsed_time_total
FROM dba_hist_sqlstat
ORDER BY elapsed_time_total DESC
FETCH FIRST 10 ROWS ONLY;

This retrieves the top 10 longest-running SQL queries along with their SQL IDs.

5. Finding SQL ID from an Active Session in ASH

Oracle’s Active Session History (ASH) provides detailed SQL execution information:

SELECT sql_id, session_id, sql_text
FROM v$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '10' MINUTE;

This helps track recent SQL statements executed in the database.

Conclusion

Knowing how to find the SQL ID in Oracle is crucial for diagnosing performance issues and analyzing execution plans. Whether using V$SQL, V$SESSION, ASH, or AWR reports, you can quickly locate the SQL ID and use it for further tuning or investigation.

Optimizing Long-Running SQL Queries

In this blog post, we will explore various methods to find, analyze, and optimize long-running SQL queries in Oracle.

Indexing for Performance Improvement

Indexes can significantly improve query performance. To check for missing indexes, you can analyze execution plans using the following:

SELECT * FROM v$sql_plan WHERE sql_id = '<SQL_ID>';

If the execution plan shows full table scans where indexes could be useful, consider creating indexes:

CREATE INDEX idx_column_name ON table_name (column_name);

Optimizing Execution Plans

Use EXPLAIN PLAN to understand how Oracle executes queries:

EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Review the execution plan and ensure the query is utilizing indexes efficiently.

Using Query Hints

SELECT /*+ INDEX(table_name index_name) */ column1, column2 FROM table_name WHERE column1 = 'value';

Partitioning Large Tables

Partitioning large tables can improve query performance by reducing the amount of data scanned:

CREATE TABLE sales_partitioned (

    sale_id NUMBER,

    sale_date DATE,

    amount NUMBER

)

PARTITION BY RANGE (sale_date) (

    PARTITION sales_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),

    PARTITION sales_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))

);

 

Identifying Long-Running SQL Queries in Oracle Database

In any Oracle database environment, long-running SQL queries can impact performance, causing bottlenecks, and degrade the overall user experience. 

Identifying and optimizing these SQL queries is crucial for maintaining database efficiency. In this blog post, we will see the various methods to find and analyze long-running SQL queries in Oracle.

1. Using V$SESSION and V$SQL

Oracle provides dynamic performance views such as V$SESSION and V$SQL to monitor active sessions and queries. To find long-running queries, execute the following SQL statement:

SELECT s.sid, s.serial#, s.username, s.status, s.schemaname, s.osuser,
       s.machine, s.program, q.sql_id, q.sql_text, s.logon_time,
       (sysdate - s.logon_time) * 24 * 60 AS minutes_running
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
ORDER BY minutes_running DESC;

This query will help you identify active sessions, their corresponding SQL queries, and how long they have been running.

2. Using V$SESSION_LONGOPS

For queries that take a significant amount of time, Oracle tracks their progress in V$SESSION_LONGOPS. The following SQL query helps you to monitor long-running operations:

SELECT sid, serial#, opname, target, sofar, totalwork,
       ROUND(sofar/totalwork*100,2) AS percent_done, elapsed_seconds
FROM v$session_longops
WHERE totalwork > 0 AND sofar <> totalwork
ORDER BY elapsed_seconds DESC;

This view provides insights into the execution progress of long-running queries.

3. Using Active Session History (ASH)

Oracle Active Session History (ASH) collects sampled session activity, which can be used to analyze performance bottlenecks. The following query retrieves details on long-running queries from ASH:

SELECT sql_id, session_id, session_serial#, event, wait_class,
       sample_time, time_waited
FROM v$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '10' MINUTE
ORDER BY sample_time DESC;

ASH provides granular insights into SQL execution patterns over a specific time frame.

4. Identifying Blocking Sessions

Long-running queries might be blocked by other sessions, leading to performance degradation. To identify blocking sessions, use the following query:

SELECT blocking_session, sid, serial#, wait_class, event, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

This will help you in diagnosing contention issues within the database.

5. Checking AWR Reports for SQL Performance

Oracle’s Automatic Workload Repository (AWR) captures detailed performance statistics. The following query lists the top 10 longest-running SQL queries from AWR:

SELECT sql_id, elapsed_time_total, executions,
       elapsed_time_total/executions AS avg_elapsed_time
FROM dba_hist_sqlstat
ORDER BY elapsed_time_total DESC
FETCH FIRST 10 ROWS ONLY;

AWR provides historical performance data, enabling better tuning and optimization.

Conclusion

Finding long-running SQL queries in Oracle is essential for performance tuning and system health. By leveraging views like V$SESSION, V$SESSION_LONGOPS, V$ACTIVE_SESSION_HISTORY, and DBA_HIST_SQLSTAT, you can proactively monitor, analyze, and optimize queries to improve overall database performance. 

Regularly analysis and tuning can significantly reduce query execution times and enhance user experience.

Oracle SQL Developer: Hidden Gems, Tips and Tricks - Part2

 

Oracle SQL Developer: Tips & Tricks for the Developers.

Oracle SQL Developer is a powerful, free integrated development environment (IDE) for working with Oracle databases. 

Many of the developers use it for writing queries and managing schemas, however there are numerous hidden features and tricks that can significantly boost productivity and efficiency. 

We will see some of the best tips and how you can make most of the Oracle SQL Developer tool.

1. Customizing SQL Developer

Enabling Dark Mode & Custom Themes

SQL Developer supports UI customization, including dark mode. To enable it:

1. Navigate to Tools > Preferences.

2. Go to Code Editor > PL/SQL Syntax Colors.

3. Choose a dark color scheme or customize individual colors.

Customizing Keyboard Shortcuts

Use the following to increase efficiency, modify shortcuts:

1. Tools > Preferences > Shortcut Keys.

2. Assign frequently used functions (e.g., CTRL+ENTER for executing queries).

Using Code Templates & Snippets

You can use frequently used SQL patterns as templates:

1. Go to Tools > Preferences > Database > SQL Editor Code Templates.

2. Define a new template like:

   SELECT * FROM $TABLE$ WHERE $COLUMN$ = '$VALUE$';

3. Use it in the editor by typing the shortcut and pressing TAB.

Setting Up Auto Format for SQL Queries

SQL Developer has a built-in formatter:

1. Tools > Preferences > Database > SQL Formatter.

2. Adjust settings (e.g., indentation, keyword casing).

3. Press CTRL+F7 to auto-format queries.

---

2. 

Generating ER Diagrams Automatically

You can generate the visualization of the database structures with ER diagrams:

1. Right-click on a connection and select Data Modeler > Import > Data Dictionary.

2. Select tables and generate the diagram.

Tracking SQL History & Restoring Lost Queries

You can use this option in order to recover a lost query :

• Use F8 to open SQL History and retrieve past queries.

• Use View > Files to browse auto-saved scripts.

Using the Hierarchical Profiler to Optimize Queries

Find slow execution queries:

1. Enable profiling in Tools > Preferences > Database > PL/SQL Profiler.

2. Run DBMS_HPROF for performance analysis.

Running Multiple Queries in Parallel

• Open multiple SQL worksheet tabs (CTRL+SHIFT+N).

• Run different queries simultaneously using different connections.

---

3. Useful Tools and Utilities

Database Copy & Compare Tool

Compare databases without external tools:

1. Tools > Database Copy to duplicate schemas.

2. Tools > Database Diff to compare changes.

Exporting Query Results to Excel, JSON, and XML

Quickly export data:

• Run a query, right-click the results, select Export.

• Choose XLS, JSON, XML, CSV formats.

Using SQL Developer as an SSH Client

Connect to databases via SSH:

1. Tools > Preferences > Database > SSH.

2. Configure tunneling to access remote databases securely.

Executing SQL Scripts via Command-Line Interface (CLI)

Run SQL scripts from the command line:

sqldeveloper.exe -script my_script.sql

This is useful for automation and batch processing.

---

4. Debugging & Performance Tuning

Using the Explain Plan & Autotrace Effectively

Analyze query performance:

• Press F10 to generate an execution plan.

• Use Autotrace (CTRL+SHIFT+E) for real-time performance metrics.

Leveraging Real-Time SQL Monitoring

For long-running queries, use the following SQL Query

SELECT * FROM V$SQL_MONITOR;

This helps track execution times and bottlenecks.

Finding Unused Indexes and Optimizing Execution Plans

Identify unused indexes:

SELECT * FROM DBA_INDEX_USAGE;

This can help remove unnecessary indexes to improve performance.

---

5. Advanced Scripting and Automation

Creating and Running PL/SQL Debug Sessions

To debug stored procedures:

1. Enable debugging in Tools > Preferences > Debugger.

2. Right-click a PL/SQL procedure and select Run with Debugger.

Scheduling and Automating Database Tasks

Use DBMS_SCHEDULER to automate jobs:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'MY_BATCH_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN my_procedure; END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=1',
    enabled => TRUE
  );
END;
/

Building Custom Reports in SQL Developer

Create reports under View > Reports and define custom queries to generate insights.

Final Conclusion : 

Oracle SQL Developer is much more than just an SQL editor. By leveraging these useful tips, you can significantly boost your efficiency and productivity. 

Whether you’re customizing the UI, automating tasks, or optimizing queries, these tips will make SQL Developer a powerhouse for database professionals.

Oracle SQL Developer - Hidden Gems, Tips and Tricks - Part1

 

Oracle SQL Developer: Hidden Gems, Tips & Tricks

In this post, we will see some of the features which are available in Oracle SQL Developer which can be utilized in building stuffs. we’ll explore some lesser-known features, shortcuts, and productivity hacks that can significantly improve your workflow.

Oracle SQL Developer is a powerful tool for database development and administration, yet many users only scratch the surface of its capabilities. 

1. Customizing SQL Developer for Efficiency

• Using Code Templates & Snippets
• Enabling Dark Mode & Custom Themes
• Customizing Keyboard Shortcuts
• Setting Up Auto Format for SQL Queries

2. Features You Might Not Be Using

• Generating ER Diagrams Automatically
• Running Multiple Queries in Parallel
• Tracking SQL History & Restoring Lost Queries
• Using the Hierarchical Profiler to Optimize Queries

3. Key Tools and Utilities

• Database Copy & Compare Tool
• Exporting Query Results to Excel, JSON, and XML
• Using SQL Developer as an SSH Client
• Executing SQL Scripts via the Command-Line Interface (CLI)

4. Debugging & Performance Tuning

• Using the Explain Plan & Autotrace Effectively
• Leveraging Real-Time SQL Monitoring
• Finding Unused Indexes and Optimizing Execution Plans

5. Advanced Scripting and Automation

• Creating and Running PL/SQL Debug Sessions
• Scheduling and Automating Database Tasks
• Building Custom Reports in SQL Developer

By mastering these features, you can turn Oracle SQL Developer into a productivity powerhouse tool.

In the next post we will see deeper into some of the features....

---

How to Export Database in Oracle SQL Developer

Here are the steps to for exporting the data in Oracle SQL Developer. These are very simple steps which can be performed to extract any of the database objects.

To export a database in Oracle SQL Developer, follow these steps:

Method 1: Export Using Data Pump

1. Open Oracle SQL Developer and connect to your database.
2. Open DBA Panel:
   • Go to View → DBA.
   • Right-click your connection and select Add Connection.
   • Expand the connection and navigate to Data Pump → Export Jobs.
3. Create an Export Job:
   • Right-click Export Jobs and select New Export Job.
   • Choose Full, Schema, Table, or Tablespace Export.
   • Configure the directory, file name, and options (compression, parallelism, etc.).
   • Click Start to export.

Method 2: Export Schema/Tables Using SQL Developer GUI

1. Connect to the database in SQL Developer.
2. Go to Tools → Database Export.
3. Select Export Options:
   • Choose whether to export a full schema, specific tables, or objects.
   • Set output format (SQL script, CSV, etc.).
4. Choose Save Location:
   • Specify file name and directory for the exported file.
5. Click Finish to generate the export file.

Method 3: Export Data Only (CSV, Excel, etc.)

1. Right-click a table and select Export.
2. Choose the format (CSV, Excel, JSON, XML, etc.).
3. Specify the file location.
4. Click Next → Finish.

These are some of the methods which can be leveraged for the database export. Try and test it out with these methods and choose the best that suits you.