Please select the topic you want to know on SQL Developer. You can also go through all to ensure that you are not missing anything. Happy learning.
This page contains (Skip to topic)..
- Create a connection to a database in SQL Developer
- Updating Existing Connection in SQL Developer
- Deleting an existing connection in SQL Developer
- Finding Database Objects in SQL Developer
- Filters On Objects in SQL Developer
- Refresh Button in SQL Developer
- SQL Developer Worksheet
- How to open SQL Developer worksheet?
- Four frequently used icons for SQL Worksheet in SQL Developer
- SQL Developer Query Output Section
- SQL Developer Execution Log
Create a connection to a database in SQL Developer
We have the database server running either in local machine or somewhere over the network. As I have installed oracle database in my machine, its running in localhost for me.
#1 Click on the “New Connection” tab
You can click on the “New Connection” tab in either of the following ways. However, if you do not have your connections tab open, then go to View > Connection to open the connections window.
- Right click on the Connections icon. It will open up a dialog box with “New Connection” at the top.
- Or, you can directly click on the green + sign on the top left corner of the connections section.
- Or, press Ctrl + N on Windows and Command + N on mac, to open up the New dialog box. Select “Database Connection” and hit OK.
- Or, go to File > New to open up the New dialog box. Then select “Database Connection” and hit OK.
I am using the method as per 1st point as shown in the figure.
#2 How to configure “New / Select Database Connection” to connect to Oracle Database
This is the important section to make a connection to the Oracle database. The numbers in the list match the number in the explanation below.
- Connection Name: This is the name of the connection. It is for our reference. It has nothing to do with the username in case you are wondering.
- Username: As I am using Oracle sample schema to explain you, for me username is hr. This is the username which has access to the database, it is trying to connect.
- Password: The password is configured while creating a user. For sample schema hr, the password is hr.
- Save password: You can save the password in SQL Developer. You need to make the checkbox checked which is to the left if save the password. This will save some time for us, or else you will have to provide the password each time you will try to log in.
- Hostname: The hostname is the IP address or server name of the machine which has oracle server running. If you have SQL Developer in the same machine, you can put the hostname as localhost.
- Port: Oracle server runs on 1521 port by default. This should be the listener port number which has Oracle server running. In the real world, you may have to change both hostnames as well as the port. You can ask your DBA for the same.
- SID: The Oracle System ID (SID) is unique to a particular database on a system. As one cannot have more than one database with the same SID on a computer system, we can use them to identify a particular database.
- Service Name: The instance name or service name to which we want to connect to. As we created a pluggable database for installing sample schema, for us its value is otPDB. By default, Oracle database keep the service name as orcl.
- Test: Once you have filled up the above details, you hit the Test button. If everything is fine and your database is running, then you should get the status message as Success.
- Save: You can save the connection details by clicking the save button.
- Connect: Click on the Connect button to log in to the database. It should open a worksheet to work on.
The above figure shows the NEW/Select Database connection dialog box with my system oracle running server data.
#3 Successful connection to an Oracle database
You can ensure that you are successfully connected to the newly created connection name e.g. hr by seeing that all 3 showing the same connection name. The left and right icons have an active connection icon in it;
You can also fire the following query to check if you are connected to the database.
SQL> select * from dual; D - X Elapsed: 00:00:00.006Finding Database Objects
Updating Existing Connection in SQL Developer
#1 Right click on existing connection
Updating an existing connection in SQL Developer is quite easy. All you have to do is right click on the connection name. If you can not find it, go to view > connections.
#2 Go to the properties option
Then it will open up a new menu bar. Click on the properties option as shown below.
#3 Update the login or database details as per requirement
Once you click on the properties option, you will see the New / Existing Database Connection dialog box coming up. You can refer to the figure below.
#4 Test the connection and save or connect
Please note that if you change the connection name and save, it will save it as a new entry. You can change the value related to login info and database server info. Once that is done, please test the connection and save.
Do you want to know how to delete a connection in SQL Developer? We will talk about that in the next section.
Deleting an existing connection in SQL Developer
#1 Right click on the existing connection
Open up connection window pane by clicking view > connections, in case it is not visible for you. Now, right click on the connection name you want to delete.
#2 Click on delete option to delete the connection
On clicking the delete option, it will ask you to confirm as shown below. Once you confirm, you can not undo the operation. The connection details will be gone.
#3 Check if successfully deleted the connection
Please check for the connection name in the connection section. It should not be present.
Sometimes, if you can not delete a connection, this might be because of uncommitted transactions or some processing happening on a session. In that case, you might have to ask the DBA to kill your session or use sys account to do the same.
Finding Database Objects in SQL Developer
In the last post, we talked about creating a connection, updating an existing connection, deleting the existing connection. In this part, we will talk about how we can use the connections section to find database objects.
Please do not worry about what is a database object and all. We will see different objects types in oracle later on. For example, employees table is a database object. In fact, anything you see inside the HR schema in the connections section is database object.
If I want to search for a database object, I need to know what kind of object it is.
#1 Click the plus icon left to the kind of object inside connections
Let’s say, Employees!! As we know, it is a table, we will click the plus icon on the left of Tables inside the schema.
It will open up all the tables inside the HR schema.
#2 Click on the desired object (in this case its a table)
When you click on any table, a tab will open up in the working area. It has all kinds of information related to its type. This information is displayed as submenu below the header. For table type, we can see columns, data, model,…., SQL etc. When you click on the submenu, you will get that information below.
Let us consider table type in this case. Clicking on columns give you the column names, data types etc. The data tab shows you the data inside the able. You can explore the rest by yourself.
#4 Refresh the object
There is a refresh button below the submenu. Any change in data by other users in the database will be shown to you then and there on hitting that blue little refresh button.
Filters On Objects in SQL Developer
Ability to filter while searching for an element helps to save a lot of time. So, let us see how the filter works in the connections section in this post.
Each icon in the connection is like a node. You can apply on schema and kinds of objects basically. When you apply a filter, it only filters the nodes inside it.
#1 Apply filter on schema
Goto connections section. Right click on the connection name where you want to apply a filter. It is the HR schema for our case. After applying the filter it will only display objects which satisfy the filter logic or filter conditions. The filter dialog box has following things in it.
- Operators (=, <>, LIKE, NOT LIKE)
- Case sensitive check box
#2 Apply filter on Objects
Let us consider we want to apply a filter to the Tables node. We need to right click on the Tables icon and click on “Apply Filter”. Here, you will see that you have to make the logical expression as done for the filter on the schema.
Here you can add more than one logical expressions by clicking the + icon.
There are two more things to know here.
- You can either choose “Match Any” or choose “Match All”. “Match Any” is logical OR operation and “Match All” is AND logic.
- Secondly, you can also override the schema filter by making the checkbox checked on the bottom left corner of the filter dialog window.
We can refresh either all the connections at once or a single node inside any schema.
It basically loads the SQL Developer data with a fresh copy taken from the Oracle database it is connected to.
Refresh Connections in SQL Developer
We can refresh all the connections at once by clicking the refresh button as shown in the figure. It loads everything back again. As a result of which, now we can see the newly created tables, objects states (valid/invalid) etc. Please refer to the figure below.
Refresh Object category in SQL Developer
Though refreshing whole connection will refresh the object category present inside the schemas, you may do not want to do it. This is time-consuming if you are connected to huge production databases or multiple environments. Hence, we should be refreshing particular object category if refreshing connections as a whole is unnecessary.
To refresh an object category. Head to connections > expand the connections > expand the schema > right click on the object category > Refresh. This is shown in the figure.
Refresh object metadata window in SQL Developer
As we click on a specific object, it will open up a window as a tab having its meta info, data. It does not get refreshed automatically. So, if the meta or data gets changed in the Oracle database, we are left with old data in the SQL Developer. This is where the refresh button helps. It pulls out the latest info from the database and shows it to us in the same place itself.
Refresh of the query output in SQL Developer
SQL Developer displays the output in the Query Output section. If you see, there is a refresh button out there. Instead of running the same query again and again, you can just refresh the result and SQL developer will get you the latest data from oracle server. Pretty cool, right !! I use it all the time.
SQL Developer Worksheet
SQL Developer worksheet always to write and run SQL queries. There are two ways to open SQL Developer worksheet.
How to open SQL Developer worksheet?
Opening SQL Developer worksheet from Connections section
You can right click on the schema name on the connections section, then you can click on “open worksheet”. It will ask you for username and password if you are not connected.
Just below the menus, there is a toolbar menus section. You can click on the icon and select the environment for which you want to open the worksheet. You can also choose the environment directly by clicking the small down arrow next to the SQL worksheet icon.
Four frequently used icons for SQL Worksheet in SQL Developer
“RUN” icon in SQL worksheet
The run command lets us run a single SQL Query or more than one SQL Queries one after another. When you execute a query with this command, it opens up the “Query Result” as a grid in the Query Output section.
The Run icon stays disabled if nothing is there in the SQL Worksheet. Once you start writing the query, it becomes clickable.
Let us see the same in the screenshot.
One important thing to know is, you do not need to terminate the SQL Query with a semicolon if you want to run in SQL developer. It will just run fine.
“Run As Script” icon in the SQL worksheet
The Run As Script icon lets us run SQL commands and spits out the output in the Script Output tab. It shows the output in one script output section.
Commit icon in SQL Developer
When we have any uncommitted transactions, we issue commit command to make a permanent change in the database. This icon lets us commit the uncommitted transaction.
Rollback icon in SQL Developer
Rollback icon lets you rollback any uncommitted transaction.
SQL Developer Query Builder
There is a submenu next to worksheet. It helps to graphically build a query. SQL developer writes the query for you behind the scene.
SQL Developer Query Output Section
The SQL Developer Query output has three major sections icons. Those are,
- Pin the result
- Refresh the result
- SQL of the result
You run a query using the Run command, It opens up the resultset in the output section. Then if you run another query in the same worksheet then, it refreshes the same tab in the query output section. However, if you pin the query output and run any SQL query, it opens up a new tab. Now, you can move back and forth to view different query result in the same session.
We can also refresh the query result by hitting the refresh icon in the result tab. It executes the same query again and fetches the data.
In case you forgot, which tab is for which query, there is nothing to worry. Thankfully, we have the SQL tab, which gives us the SQL query fired. Pretty cool, yeah !!
SQL Developer Execution Log
We can see the history of SQL queries run in the SQL developer. It shows you all the SQL queries run with extra pieces of information like the database, the time is taken, etc. This helps to quickly get the previously run queries.