Oracle SQL Developer – now in web!

Posted in: Technical Track

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:

[[email protected] ~]$ sudo -s
[[email protected] opc]# touch /home/oracle/password.txt
[[email protected] opc]# chmod 600 /home/oracle/password.txt
[[email protected] opc]# vi /home/oracle/password.txt

Then we run command to enable the tool:

[[email protected] opc]# cd /var/opt/oracle/ocde/assistants/ords
[[email protected] 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 - Production
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...
[[email protected] 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.

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Regarded by his peers as an Oracle guru, Gleb is known for being able to resolve any problem related to Oracle. He loves the satisfaction of troubleshooting, and his colleagues even say that seeking Gleb’s advice regarding an issue is more efficient than looking it up. Gleb enjoys the variety of challenges he faces while working at Pythian, rather than working on the same thing every day. His areas of speciality include Oracle RAC, Exadata, RMAN, SQL tuning, high availability, storage, performance tuning, and many more. When he’s not working, running, or cycling, Gleb can be found reading.

2 Comments. Leave new

Hello, I was enabling SQL developer for a common user available in my PDB nd followed all the above steps but this somehow doesn’t work as the SQL dev login page doesn’t recognize anything after C# – is there a workaround for this please?

Gleb Otochkin
August 31, 2018 1:39 pm

Hi Fayyaz,
The solution to that is the following.
Let say you have a common user C##OK in your PDB1 database and enabled the developer for the schema.
On the database landing page you need to specify PDB1/c_ok and then, when you are redirected to the login page, put your real user name like C##OK and its password.
I think I may create another short blog post about it.



Leave a Reply

Your email address will not be published. Required fields are marked *