Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


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
OK

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:
SQL> select * from SYS.V_$FLASH_RECOVERY_AREA_USAGE;

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.name
         ,  b.value_string
    from v$sql a
         ,  v$sql_bind_capture b
where a.sql_id = b.sql_id
  and a.sql_id=&SQLID;