Friday, 28 February 2025

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.

Posted by at