Note: Technical Talk being moved to:

Wednesday, 23 May 2012

Run sqlplus from Textpad

For people who use the product "Textpad". If you wish to run sqlplus scripts direct through Textpad, you can set it up as follows:
In Textpad navigate to:
Configure > Preferences > Tools
Add Program
Browse and select sqlplus

Then navigate to
Configure > Preferences > Tools > sqlplus
and in the right hand pane, in the field marked Parameters add:
/nolog @$File

This will essentially invoke sqlplus /nolog and if the SQL script you have opened in Textpad contains the line connect username/password@database you can use the shortcut key (in my case ctrl-1) to execute the SQL through Textpad.

Tuesday, 22 May 2012

Foreign Archive Logs

A colleague of mine recently noticed our Flash Recovery Area listed some FOREIGN ARCHIVED LOGS as taking up space in the flash recovery area. We knew from a recent implementation that these were left-overs from a previous incarnation of our database and we needed to remove them. We achieved this task the following way:
Determined the space being used:

Crosschecked the foreign archive logs using rman
RMAN> crosscheck foreign archivelog all;

Removed the foreign archive logs using rman
RMAN> delete expired foreign archivelog all;

Monday, 14 May 2012

Check Bind Variables

Here's a way you can check on what bind variable values have been parsed in a query.
SELECT a.sql_id
         ,  a.sql_text
         ,  b.value_string
    from v$sql a
         ,  v$sql_bind_capture b
where a.sql_id = b.sql_id
  and a.sql_id=&SQLID;