Your Tools: SQLDeveloper and SQLcl

In the previous post (link), I insisted: Everything is SQL, and SQL is Everything.

So if all the information you need is presented in the form of tables and views, and everything is SELECT-able, you should get familiar with SQL and with the tools to run SQL.

Your tools of choice should be SQLcl or SQL-Developer, or the SQL-Developer web variety, if you want to be totally hip  (downloads and information here).

You may also know that SQL*Plus is still available on Every Platform where the RDBMS itself is deployed.

And there are others... if you are used to other SQL-tools, you will find that most of them can connect to an Oracle database, and work just fine. For example TOAD or DBeaver will also do the job just fine (some documentation here).

Running a query from SQLDeveloper mostly looks like this:

In there  you see I am using SQL to join two views, and I am looking for statement executed by SCOTT, with the heaviest IO-stmnt listed on top. This information, and many other valuable data is available to query using just the tools and SQL that you already know.

But as a DBA, I have a strong preference for using a CLI (Command Line Interface) next to my GUIs or other tools. The big advantage of a CLI is the capability to "script" your commands. Scripts will "store your knowledge" and make it repeatable, re-usable.

In my case, I would use SQLDeveloper to do ad-hoc inspections, queries. But once a query or a report needs to be re-run more then once, when I think it is useful in future, I will create a script. From that point on, the query can be run from using its filename.sql from the CLI. 

Example is the script "pr.sql" a very small script that I use everywhere and all the time: it sets the SQL-Prompt to tell me I which user, which database and which server I am connected to, just to make sure I am always typing at the correct prompt... 

SQL> 

SQL> connect scott/tiger@orclpdb1 

Connected.

SQL> 

SQL> @pr

set sqlprompt "SCOTT @ ORCLCDB @ oracle-21c-vagrant > "

SCOTT @ ORCLCDB @ oracle-21c-vagrant > 

Because "Everything is SQL", that script actually goes out and uses SQL to find the user, the database (or PDB) and the host this database runs on, and then sets the prompt accordingly. 

If you make a habit of creating and using scrips, then, over time, you will collect a nice set of scripts. 

The first good reason for using the CLI tools, SQL*Plus or SQLcl, is that those tools can run scripts quickly using the start command or the @ operator. If you stick with the GUIs, you will at some point get tired of copy-pasting your code-snippets of SQL-commands into the GUI. Running a script from the command-prompt takes less of a mouse-and-type effort.

And once you are using the CLI, you should investigate the setting of the environment-variable $SQLPATH. (windows: %SQLPATH ). This variable can contain one or more directories where you can store your scripts and _always_ have them at your fingertips. Not to mention the useful scripts you might copy from others who have already gone there and done that.

In Summary:

 - Download and get to know the tools.

 - Explore the data, find your information.

 - Consider creating your own set of scripts.

 - Use environment-variables to always have the scripts available.

Next blogs...: 

Tips to avoid the mistakes I made, and still see others making

And links to where you can find good re-usable scripts already built by others. 

(insert future links here..)

And... I told you: "SQL is Everything and SQL is Everything"