Tuesday 28 February 2023

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"




Wednesday 22 February 2023

With Oracle RDBMS: Everything is SQL

Everything is SQL, and SQL is Everyting.

One of the things I really like about Oracle RDBMS is that it follows the “12-rules of Codd” very well. 

And What I specifically like, All of the RDBMS information is accessible via SQL.

In Oracle, “Everything is a Table or View”. 
And thus, everything can be queried by SQL. 

If you need to observe or “diagnose” your Oracle Database, you can (and should) do so via SQL. And you will also learn a few things every time you delve into something. 

 Disclaimer: I'm writing this bcse I am trying to convince some newbies and DevOps folks to learn just a little more about Oracle.... 

 This blogpost is to show you how you can Get to Know Everything about your Oracle database by just using the SQL interface. For example: I can ask what tables are in my current schema with a query on a view called "USER_TABLES" 

SCOTT @ ORCLPDB1 > select table_name from user_tables order by 1 ;
TABLE_NAME 
-----------------------
DEPT
EMP
LOG_STATS
PT PT_C
PT_CC
PT_CCC
T
T_C
T_CC
T_CCC

11 rows selected. 

SCOTT @ ORCLPDB1 > 

And if you want to dig deeper: you can ask the database where it stores the actual datafiles, for example from v$datafile: 

SCOTT @ ORCLPDB1 > select name from v$datafile ; 
NAME 
------------------------------------------------------------- 
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf 

SCOTT @ ORCLPDB1  >

And in that spirit, following notably rule nr 4 of Codd, all the data and metadata of your Oracle database can be found via SQL. 

I strongly encourage any DBA (and Developer, Devops, etc.. ) to explore the available documentation on the DBA_% and V$... views. Everything you need to know about your database is There. And much more.  All reachable with Select-statements. 
Link to the official doc here ....

note: Compare Oracle to unix/linux. Those of you who are brought up with unix/linux will recognise this. In unix, “Everything is a File”. 

My fingers are itching to show more SQL and to show how you can inspect the darker corners of your database with some some nifty SQL-stmtns.

Also, I want to make some points about tooling, issue some tips and some Warnings. With powerfull tools comes some responsability on using them correctly. 
The more I think (write) about it, the more there is to elaborate upon.
Guess I have to do some more blogs...

But my main message would be: Check the 12 rules of Codd, and explore some of the documentation about the internal views. There is your starting point.

For now, just remember: Everything is SQL !