AWR from Oracle SQLDeveloper, super simple

 

This is going to be a simple post, and a little regurgitation.

You cannot run the classic awrrpt.sql from sqldeveloper, because it cannot access the database servers directories to find the rdbms scripts.  But, it's easy to work around this.

The server actually just calls a procedure, which is listed below (dbms_workload_repository.awr_report_html).  In this example, I want html output.

Therefore, if you are using sqlDeveloper, and you want to run an AWR, it's really easy.

First, get the AWR ID of the start and finish times, using this SQL:

select snap_id, begin_interval_time,end_interval_time from dba_hist_snapshot;

Easy, now run the following and press the "Run Statement" green arrow ->

set linesize 8000
set feedback off;
set heading off;
set verify off;
SELECT output FROM    TABLE(dbms_workload_repository.awr_report_html ((select dbid from v$database),(select instance_number from v$instance),1956,1957,8 ));
quit;

Then copy the output from the query result window into a new file and call it AWR.html

Open that in a browser and you are in AWR territory.  Note that you need to be EE and you also need the right permissions.  Enjoy not having access to the database server.

regex search and replace in visual studio code

Where do I begin...

Always starts with a seemingly easy task, this one was uploading F9860 into Google bigquery - simple right?

Move it into GCS and then create a table, easy...

but, I kept have problems because the description field has ","s in it.

Okay there are a couple of fixes for this, firstly - just cleanse the data - super easy:

translate(SIMD, chr(10)||chr(11)||chr(13),’,’, ' ')

The code above will change commas, tabs and new lines into a space - perfect...  That means that the csv will have no dodgy commas.

But I was not running the SQL, so it was plan B - post SQL fix

I needed a regex that would find commas in Quotes, that was easy enough:

("[^",]+),([^"]+")

Then I needed to replace this in visual code studio, using the two grouped outputs $1$2

Note that you do this with the fields above, .* allows you to use capture groups in the replacements.  Note that I needed to run this a couple of times - as it was only doing the first comma.

There there is a little hovering over the results to see the "replace all" option - and we are done.  This did save me booting the virtual to do it at the command line.