Last year for the OTN Appreciation Day I wrote about one of my favourite Oracle Analytic functions, LISTAGG. I don’t have a new favourite bit of SQL, so this time I’m writing about my love of SQL Developer.
If you are not using it, you are really missing out on a lot of free functionality. Just take a look at the things you can explore from the VIEW and the TOOLS menus.
As a sort of hybrid developer / DBA / data modeler I find the SQL Developer to be my “Swiss Army Knife” database tool. I use it in so many ways at so many times in a project.
I could go on about the OLAP and Data Mining features or how (subject to the correct DB license) it can be used to investigate and tune problem queries. However, today I am going to mention the DBA view. Quick confession time, I am a fake-DBA – my interests are in sizing, performance, security and storage, I don’t retain a detailed knowledge of the DBA views and the more obscure DDL syntax, but I do know where to look to find something I can adapt.
One of those places is the query result pane in SQL Developer – that may sound a surprise, but by right clicking on the query results we can elect to save the grid as report
Then simply copy the report SQL and adapt it to the requirements. For example, I needed to list the users and their quotas on given tablespace. By finding the quota for one user I could adapt the SQL Developer generated SQL to my needs:
SELECT "Tablespace", "Quota (Bytes)" FROM ( SELECT tablespace_name AS "Tablespace", max_bytes "Quota (Bytes)" FROM dba_ts_quotas WHERE username =:name AND max_blocks != 0 )
SELECT username, max_bytes "Quota (Bytes)" FROM dba_ts_quotas WHERE tablespace_name = 'USERS';