This video shows the steps required to download and install SQL Developer, connect using SQL Developer to and Oracle instance and perform a quick overview of the graphical interface.
- [Instructor] SQL Developer is not included as part of the Oracle software installation, and we'll need to go grab it from Oracle.com for us to use it. So, in your virtual machine, please switch to your web browser. In the Google search box, type download SQL Developer. The first result will be Oracle SQL Developer downloads. Click on that link. Click on accept license agreement.
And remember, you have to be registered with Oracle in order to be able to download SQL Developer. If not, you can always register free account. I have already registered. You can select which operating system you plan to run SQL Developer on. As you can see, we have multiple options. Windows 64-bit, Windows 32-bit, Mac OS, Linux and more. That's because SQL Developer is the go-to tool for many Oracle users when they either want to open a connection from their desktop or laptop computers to an Oracle database and work with that database or when running SQL Developer locally on the same server as the Oracle database.
We'll use option number two in our example, and to do that we'll need to download the Linux RPM version of SQL Developer. You can just click on the download link. You can either sign in with your existing Oracle.com username and password or click on the create account button to create a new account. Since I already have an Oracle.com username and password, I'll be using my existing account to download SQL Developer. We should select to save SQL Developer and click OK.
In addition, if you go back to the previous page, since SQL Developer is a Java program, you'll also need to download the Java development kit, version eight or higher, otherwise you will not be able to run SQL developer. It doesn't matter if you plan to run SQL Developer directly on the server, where you have Oracle installed, or on your local laptop or desktop. You will need Java anyway. So in our case we can click on the JDK 8 link, and we will be taken to the Oracle Java development kit version eight download page.
In this page, we will need to click on accept license agreement, and since we are running on Linux 64-bit, we should select the Linux 64-bit RPM version of Java. It will make the installation a lot easier. So we can just go ahead and click on the download link. Select save, click OK, and once the download's complete, we can start the installation of SQL Developer. Now that the download has finished, let's go back to our terminal window, go to the default download directory, which is under the Oracle user home directory, and in the Downloads with a capital D subdirectory.
Once we are in the Downloads subdirectory, we can take a look at its content and see that we have two files, the Java development environment and SQL Developer. We'll need to install both files, starting with the Java development kit. In order to do that, please be sure to switch to the root user in your operating system, go to home, Oracle, Downloads with a capital D. Remember, that's the default download directory for the browser running under the Oracle user.
We can verify that the files are indeed there. And then run the RPM installation command for both files. You can just RPM dash capital U v h and the JDK, RPM, click enter. Following the successful installation of the JDK, we can go ahead and install SQL Developer. RPM dash capital U, vh, and this time we choose SQL Developer.
Let's patiently wait for the installation to finish. It can take a few seconds. SQL Developer has been successfully installed. Let's open another terminal window, make sure we are logged in as the Oracle user, and type SQL Developer, enter. SQL Developer will now prompt us for the full path of the Java installation we have performed in our previous step. For your convenience, a file has been placed in your exercise files directory that contained that path.
So you can just copy and paste it to the terminal window. Assuming the Java installation was successful, and you've specified the correct path for the Java home directory, SQL Developer will momentarily start. It will ask us if we want to import settings from a previous version of SQL Developer. We have nothing to import, so you can select no. And we will be greeted with this SQL Developer welcome screen.
We can just click on OK. So take it all in. This is a user interface you're going to be working with a lot for the foreseeable future. SQL Developer is the go-to UI for both developers and administrators for working with the Oracle database. And remember, you don't have to run it on the same machine as where you have Oracle running. This is just for the sake of our course to make things simpler. You can also download SQL Developer and run it on your own desktop or laptop.
To connect to a database server using SQL Developer, we'll need to click on the green plus icon in the upper left part of the screen. We can specify any name we want for the connection, such as My_Oracle_Instance. Provide a username and password that is a database username and a database password. Because we have not created any other users so far, we can use the system user for which we have supplied the password during the installation and we can type the password.
We can also have SQL Developer save the user password for us, so future connections to My_Oracle_Instance could be done without a password. We also need to specify the host name, where the Oracle database is running. Because our Oracle database is running locally, we can specify localhost as the host name, specify the listener port 1521 is correct for our environment. Our listener is listening on port 1521.
And we have to specify the Oracle instance name, which in our case is a lowercase orcl. We are now ready to try and connect, using SQL Developer, to our database. Let's click on the test button to verify our connection parameters are okay. As you can see, it says status, success. This means that the parameters, such as username, password and database credentials that we have supplied, as well as the server host name, port and SID, or instance name, are correct.
We can now save this connection, and as you can see, it appeared in the connections pane for SQL Developer. And we can click on the connect button to actually open the connection. Clicking on the connect button opened a new tab in SQL Developer and we are connected to the My_Oracle_Instance. We can expand the connection and minimize the button thing and see that we get a really convenient and user-friendly view of tables, views, indexes and a lot of other Oracle-logical objects that we have in our database.
We can also run SQL commands directly from SQL Developer. For example, select * from v$database, which is one of Oracle's data dictionary views, allowing us to view information about the database we are connected to. We can write this command, highlight it, and click on the green play button. This will run the statement. The result will be provided below. Very convenient.
We can also run Oracle administration commands from this pane. For example, if we'll go into the other users subfolder, we see that there are a lot of users that have been precreated for us during the database installation process. Some of those users are system users, internal users, which we should not touch. However, we also have an HR, which is a sample user, created for us by Oracle so that we can play and work with some sample tables.
However, that user is locked by default and we'll need to open the account as well as reset the password. We can quickly do that by typing the commands alter user hr account unlock. This will unlock the account for the HR user, as well as alter user hr identified by and the password.
Let's keep it simple, hr. I can highlight the sentence, click on play, and we now have our HR user enabled. Let's define another connection and connect to the HR user. So I'm going to minimize the first connection I've created, click on the green plus button, and define a new connection. This time, I'm going to call it HR_Connection. Specify hr as the username and hr as the password.
Sure, let SQL Developer save the password for us. I'm connecting to the same database, so I'm leaving the host name and listener port values the same. And under SID I'm specifying orcl as the instance name. Let's test the connection. As you can see, the connection test was successful. We can save this connection, which will now appear in the right-hand connections pane, and we can click on the connect button. We now have another tab opened called HR_Connection, not to be confused with the previous tab we have opened, which was logged in with the system user.
So go back to the HR_Connection tab, and every SQL command that you'll execute inside this worksheet will execute within the HR_Connection. We can also expand the HR_Connection on the connections pane and see a segmented view of all of the tables, views, indexes, procedures, as well as other logical objects that have been created by the HR user. Again, these are all created for us as part of the Oracle samples schemas during the installation.
We can expand the tables folder and see a list of all tables for the HR user. We can double-click on a table, and see its structure opened in a dedicated tab. We see that the table has three columns: COUNTRY_ID, COUNTRY_NAME, and REGION_ID. We can also click on the data subtab and see information from the table. We can also see the indexes created on the table. So clicking on the indexes subtab will show us that we have one index created in our table and in case we want to see the SQL command that was initially used to create the table, kind of like reverse engineering the table, which has already been created, back to a SQL command, we can find that out by clicking on the SQL tab.
This will show us the create table statement used to created the COUNTRIES table. Let's go ahead and close the COUNTRIES table tab and go back to our HR_Connection tab. We can here write the SQL commands that we want executed inside our HR_Connection to the database. For example, select * from countries. We can highlight the SQL command, press play, and get the results.
So as you can see, SQL Developer is a very easy and convenient tool for working with an Oracle database. You get access to the database using a very easy-to-understand user interface. Now SQL Developer has a lot of options. We won't cover all of these options in our course, but feel free to experiment. Oh, and one last thing. In case you want to disconnect from an existing connections such as the HR connection we have open or the My_Oracle_Instance connection we have open, we can do it directly from the connection pane.
For example, if we'll minimize the tables folder and the HR_Connection item, we can right click on it and select disconnect. This will terminate the connection. We can do the same thing for the system connection we have opened earlier. Right-click and select disconnect. So that is it, a brief overview of Oracle SQL Developer, which should be your go-to user interface when developing with an Oracle database or administrating an Oracle instance.
After completing this course, you'll have fundamentals required for installation, configuration, and administration of an Oracle 12c database.
- Database instance and storage
- Instance memory pools
- Instance background processes
- Client connections
- Database storage file types
- Control files and backup files
- Multitenant databases
- Starting and stopping the database
- Installing Oracle 12c software
- Using the developer tools
- Database management