This is a post on Things you need to know about Oracle SQL developer IDE .I hope you will like it.Please do provide feedback
Introduction to Oracle SQL developer Tool
Oracle SQL Developer Tool is an Integrated development environment (IDE) for working with SQL in Oracle databases. Oracle Corporation provides this product free; it uses the Java Development Kit.
Oracle SQL Developer Tool is a free integrated development environment that simplifies the development and management of Oracle Database in both traditional and Cloud deployments. SQL Developer offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a reports interface, a complete data modeling solution, and a migration platform for moving your 3rd party databases to Oracle.
Preferences tweak
| Code Editor – Fonts | Choose the fonts which are good to your eyes |
| Database – Licensing | Disable the Tuning and Diagnostic Packs part if you are not licensed for it |
| Database – Worksheet – SQL History Limit | The number of statements you want saved is defaulted to 100. Bump it up to 500 if you want a few days more worth of queries in your ‘backup.’ |
| Database – Worksheet – Show Query Results in new tabs | Choose yes for it so that you can different query results in separate tab for easily usage |
| Hiding Database Object Types from your Connection Trees | You don’t need to see all the things. You can select whatever you want to see in the Navigation filter in Database preferences |
| External Editor | you have to define the file types and associated editors in the preferences. |
| Connection Script Startup | We can setup connection startup script so that we don’t need to worry about the settings |
How to perform Oracle SQL developer download
Follow these steps to download Sql developer for Oracle
(1)Go to the Oracle Technology Network page for SQL Developer at
| Note: If a Windows 64-bit SQL Developer kit that includes JDK 7 is available, you can download and install that on a Windows 64-bit system, and SQL Developer will use the embedded JDK that is provided with that kit. However, if you need or simply want to use a JDK on your Windows 64-bit system, you can install the JDK (if it is not already installed) and the Windows 32/64-bit SQL Developer kit, and SQL Developer will use the JDK that is installed on your system. |
(2) If you do not need or want to install a suitable Java Development Kit (JDK 7 or later), go to step 3. Otherwise, download and install the JDK as follows:
(a) On the SQL Developer Downloads page (Download Sql developer), click the Download link next to SQL Developer requires JDK 7 or above.
(b)On the Java SE Development Kit 7 Downloads page, in the table of Java SE Development Kits, accept the Oracle Binary Code License Agreement for Java SE.
(c)Click the link for the download that you need (for example, the Windows x64 link for a Windows 64-bit system).
(d) Save the file anywhere on your system (such as a “temp” folder).
(e) Install the JDK (for example, on Windows, double-click the .exe file name and follow the displayed instructions).
(3) On the Oracle Technology Network page for SQL Developer at Download Sql developer, click the Downloads tab (next to Overview).
(4) Read and accept the license agreement.
(5) Follow the instructions for downloading and installing SQL Developer.
How to install Oracle sql developer Tool
The installation itself is simple. For example, on a Windows PC you can unzip the downloaded file into C:\, which will create C:\sql_developer with files and folders in and under it.
If you are asked to enter the full pathname for the JDK, click Browse and find it. For example, on a Windows system the path might have a name similar to C:\Program Files\Java\jdk1.7.0_82.
- Unzip the SQL Developer kit into a folder (directory) of your choice, which will be referred to as <sqldeveloper_install>. Ensure that the Use folder names option is checked when unzipping the kit.
Unzipping the SQL Developer kit causes a folder named sqldeveloper to be created under the <sqldeveloper_install> folder. For example, if you unzip the kit into C:\, the folder C:\sqldeveloper is created, along with several subfolders under it.
- To start SQL Developer, go to <sqldeveloper_install>\sqldeveloper, and double-click exe.
If you are asked to enter the full pathname for the JDK, click Browse and find java.exe. For example, the path might have a name similar to C:\Program Files\Java\jdk1.7.0_82.
After SQL Developer starts, you can connect to any database by right-clicking the Connections node in the Connections Navigator and selecting New Connection. Alternatively, if you have any exported connections, you can import these connections and use them.
How to migrate the connection?
If you have used a previous release of SQL Developer, you may want to preserve database connections that you have been using. To preserve database connections, save your existing database connections in an XML file. To save the connections, right-click the Connections node in the Connections Navigator and select Export Connections. After you complete the installation described in this guide, you can use those connections by right-clicking the Connections node in the Connections Navigator and selecting Import Connections
How to Uninstall Oracle SQL Developer Tool
Before you uninstall SQL Developer, if you plan to install SQL Developer (the same or an updated version) later, you may want to save your existing database connections. So check for how to export connection.
To uninstall SQL Developer, remove the entire SQL Developer installation directory (that is, the directory named sql developer and all directories and files under it in the hierarchy).
If you also want to remove all user-specific SQL Developer information, you should also delete the directory under which that information is present \Documents and Settings\<your_user>\Application Data\SQL Developer
If you have created a shortcut for SQL Developer, and if you do not plan to install SQL Developer into the same location again, you should remove that shortcut or modify the shortcut properties to reflect the new location.
Favorite SQL Developer Tool Shortcuts
- ctrl-enter : executes the current statement(s)
- F5 : executes the current code as a script (think SQL*Plus)
- ctrl-space : invokes code insight on demand
- ctrl-Up/Dn : replaces worksheet with previous/next SQL from SQL History
- ctrl-shift+Up/Dn : same as above but appends instead of replaces
- shift+F4 : opens a Describe window for current object at cursor
- ctrl+F7 : format SQL
- ctrl+/ : toggles line commenting
- ctrl+e: incremental search
You can Configure Keyboard Shortcuts in SQL Developer
Tools > Preferences > Shortcut Keys
| Important feature Code Editor: Completion Insight The Completion Insight pane contains options for the logical completion (autocomplete options) of keywords and names while you are coding in the SQL Worksheet. When you pause for the auto-popup time (if the auto-popup is enabled) or when you press Ctrl+Space, code insight provides a context-sensitive popup window that can help you select parameter names. Completion insight provides you with a list of possible completions at the insertion point that you can use to auto-complete code you are editing. This list is based on the code context at the insertion point. To exit code insight at any time, press Esc or continue typing. You can enable or disable automatic completion and parameter insight, as well as set the time delay for the popup windows. |
FAQ for Oracle SQL developer
(1) How it helps developer/DBA?
SQL Developer offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a reports interface, a complete data modelling solution, and a migration platform for moving your 3rd party databases to Oracle.
(2) What platforms are supported?
Oracle SQL Developer 4.1 is available for Windows 7, 8, 8.1 and Windows Server 2008/2012, Linux or Mac OS X. (See full Certification)
(3) What JDK is supported?
Oracle SQL Developer 4.1 release requires Java version 1.8.
If SQL Developer cannot find a local JDK, you will be prompted to enter the location path for the JDK. Note that the prompt wants only the folder location, not the java.exe. For example C:\Program Files\Java\jdk1.8.0_40.
(4) Installation of a new version of SQL Developer
All new releases of SQL Developer require a full installation. Download and unzip the file into an empty folder.
(5) Migrating settings from previous releases
After you have installed the product you are asked if you want to migrate from a previous release. This migrates previously set preferences and connections.
(6) Migrating Passwords
Passwords are migrated when installing a new version on the same machine. When porting SQL Developer to a new machine, passwords will not be migrated unless protected with a pass phrase..
(7) What is the Location of User Defined Reports, Snippets and SQL History
User-defined reports, SQL History and user-defined snippets files are located in the \Documents and Settings\<your_user>\Application Data\SQL Developer folder. These are also migrated
(8) How it supports Third Party Databases
SQL Developer supports IBM DB2 UDB LUW and iSeries, Microsoft SQL Server, MySQL, Sybase Adaptive Server and Teradata..
You will not see any third party tabs in the connections dialog until you have installed the drivers. Read this Getting Started document to show you how.
You can also download and include the third-party database drivers manually. See the existing documentation for that on OTN. Alternatively, you can use Check for Updates to setup these drivers.
Do not use the latest MySQL driver 5.1. Use the 5.0.x drivers for MySQL.
How to do password change in Oracle Sql developer
SQL Developer does not provide a way to reset user password after the password has expired.
The option to Reset Password option is also greyed out as show below.
SQL Developer has a built in built-in password option which can be enabled by adding Oracle Instant Client software.
Steps to enable Reset Password option are
STEP 1 – Download Oracle Instant Client software from
http://www.oracle.com/technetwork/topics/winx64soft-089540.html
STEP 2 – Extract Oracle Instant Client
STEP 3 – Enable the “Use OCI/Thick driver” option under Tools -> Preference -> Database -> Advance
After this,restart the client, and then you should be able to change the password
How to get Explain plan in Oracle Sql Developer
It is quite easy in Oracle Sql developer. Just hit the F10 or the explain button on the top to see the explain plan
There is no need to write explain plan statement like this
explain plan set statement_id = 'ha21' into plan_table for SELECT DISTINCT EXCEPTION_TYPE EXCEPTION_TYPE_ID FROM MSC_EXCEPTION_DETAILS_VIEW WHERE CATEGORY_SET_ID=12;
How to change Font size in Oracle Sql Developer Tool
You can change the font size of the Editor by navigating to menu item:
Tools–> Preferences –> Code Editor —> Fonts
You can change font size of whole application using ide.properties file. This file can be found for example:
C:\Database_Oracle_Home\sqldeveloper\sqldeveloper\system\oracle.sqldeveloper.1.1.3.2769
Related Articles
oracle date functions : Check out this post for oracle date functions, oracle date difference in years,oracle date difference in days, oracle date difference in months.
Oracle sql online test : Check out this Oracle sql online test for interview preparation & certification. This will help in checking your understanding on Oracle SQL
Oracle LISTAGG Function : Find all the useful details about Oracle LISTAGG Function, how to use it as analytical function,Post 12 cR2 behavior, overflow on truncate
truncate table oracle : Truncate TABLE in Oracle is faster than delete from table in oracle. It is DDL statement and it does not fire the on delete triggers
Oracle Create table :Tables are the basic unit of data storage in an Oracle Database.we covers how to use Oracle create table command to create table with foreign key /primary key
Recommended Courses
Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins, Creating Tables and modifying its structure, Create View, Union, Union -all and much other stuff. A great course and must-have course for SQL starter
The Complete Oracle SQL Certification Course : This is a good course for anybody who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developers. This course gives us tricks and lessons on how to effectively use it and become a productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skills. This is a good course to learn about it and start doing sql performance tuning