Oracle SQL Developer - now in web!

by Gleb Otochkin on Jun 11, 2018 12:00:00 AM

For some time, I was trying to use a Google Pixelbook for my work. It was kind of a challenge and among other things, I struggled to work without a good web-based SQL client or tool. I used a jump box as a workaround, but it didn’t feel like a right solution for me. Finally, it looked like I found the solution. Oracle recently announced that it was rolling out the web version of their famous SQL Developer. So far, it is available only for the Oracle public cloud but hopefully, with time, we will get it for on-prem databases. Of course, I set it up right away and started to test. Here I am going to share my first impression of the tool. The tool is currently available for Oracle Database Cloud Service (DCS) and is going to be gradually implemented across all services in Oracle Public Cloud (OPC). To make it work, what you first need is to enable the developer for a schema in the database. This means you have to have a database in OPC DCS and a schema to work with. Let’s try it first for an application schema. I have a sample user SH_USER with some tables in my database and am going to enable the developer service for it. After connecting to the database server as the opc user, we need to switch to the root and create a plain text file with the password for the database user:

[opc@oradbc ~]$ sudo -s
 [root@oradbc opc]# touch /home/oracle/password.txt
 [root@oradbc opc]# chmod 600 /home/oracle/password.txt
 [root@oradbc opc]# vi /home/oracle/password.txt

Then we run command to enable the tool:

[root@oradbc opc]# cd /var/opt/oracle/ocde/assistants/ords
 [root@oradbc ords]# ./ords -ords_action="enable_schema_for_sdw" -ords_sdw_schema="SH_USER" -ords_sdw_schema_password="/home/oracle/password.txt" -ords_sdw_schema_container="PDB1" -ords_sdw_schema_enable_dba="FALSE"
 WARNING: Couldn't obtain the "dbname" value from the assistant parameters nor the "$OCDE_DBNAME" environment variable
 Starting ORDS
 …
 
 
 SQL Developer Web user enable finished...
 Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
 Version 18.1.0.0.0
 INFO: To access SQL Developer Web through DBaaS Landing Page, the schema "PDB1/sh_user" needs to be provided...
 INFO: "SH_USER" schema in the "PDB1" container for SQL Developer Web was enabled successfully...
 [root@oradbc ords]#

Easy enough. The tool is ready. Now we have to open HTTPS port for our database host if it hasn't already been opened. By default, you have some predefined security rules, including one which can enable the port 443 for all, but I prefer to create a custom rule and open the port only for a certain IP or a range of IP addresses.

Everything is ready and we can go forward and try the tool. In the browser's address bar we type "https://you public IP for database". It redirects us to a default page where we can see the SQL Developer among other services.

You've noticed that by default the SQL Developer schema is defined as "PDB1/pdbadmin". We need to change it to the user we have granted access to before. In our case, it is our SH_USER. After that, we can push the "Go" button.

You will be asked for the user name and password and be redirected to the SQL Developer page.

On the home page, we have the "Worksheet" and "Data Modeler" tabs. It is quite enough for the start and allows us to run SQL queries, use autotrace, analyze an execution plan and do some data modeling. We can save our worksheet and use it later. The worksheets are visible only for the users, so you don't need to worry if somebody else can see it.

You have all the great stuff from the SQL Developer, such as suggestions and checking for syntax.

Of course, you can use it for formatting.

The Data Modeler provides you with visual dependencies for your schema.

What I found a bit strange was that in the execution plan and "autotrace" outputs, you could see an operation but not the option. For example, we can see access to the table but it doesn't show us that it is "FULL" table access. Looks like the column "OPTION" has been lost somehow but I believe it is going to be fixed soon.

That was a pretty good start and was quite enough for a non-dba user. You remember we've used ords_sdw_schema_enable_dba="FALSE" when we enabled the tool. What if we enable the SQL Developer for the SYSTEM user and use option "TRUE" for ords_sdw_schema_enable_dba? After enabling the system user on the same database and logging on the system, we see a completely different homepage.

The home page for DBA shows some general information about current status for the database and two new tabs with DBA and OS-related reports and tools including SQL monitor and many other options. It would take a couple of pages to describe all the tools and reports there and I am not going to do it here. Please try it by yourself and explore. I really liked the tool. Except for that lost column in the execution plan table, I didn't find any problems trying different options and reports. It worked fast enough and when you were timed out and logged back, it kept all information and your queries in the worksheet. It is a good start and I hope to use the tool not only for DBCS but for some other cloud services like ADWC and maybe non-cloud databases.

No Comments Yet

Let us know what you think

See all

Share this

Oracle SQL Developer - now in web!

Share this

No Comments Yet

Subscribe by email