It’s ODC Appreciation Day

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.

NewImage

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.

NewImage

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

NewImageThen 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
 )

Becomes:

SELECT
 username,
 max_bytes "Quota (Bytes)"
FROM
 dba_ts_quotas
WHERE
 tablespace_name = 'USERS';

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s