Convert an html table to excel spreadsheet

A quick and dirty post for a quick and dirty technique that I used to get the Oracle CPU Database Risk Matrix into a spreadsheet. This won't always work (the html table I was interested in happened to have the data in the same line of html as the tag), but it's a start.

- Do 'View Source' to get the html for the table. - Find the table - Cut and paste the table into a text file. I called mine "risk_matrix_raw.txt" - Create an awk file (mine was called risk_matrix.awk) with the following lines:



- awk the text file, then pipe into sed to remove some of the tags


- Open the .txt file in Excel. When I did this, Excel asks whether its a fixed width or a delimited file. I specified that the file was delimited with a '~' and Excel opened it.

Changing the DOS command prompt

All the options for changing the command prompt are, logically enough, shown by:

prompt /?

My current favorite, especially when my current directory path is quite long is:

prompt $P$_$T$G

$P is the path
$_ is a line feed
$T is the time
$G is a '>'

So my current command prompt would be, for example:

D:\oracle\product\10.2.0\db_1\OPatch\opatchprereqs\oui
19:44:36.95>

Creating a timestamp string in DOS

I found a better way of doing this using the DOS variables %DATE% and %TIME%

To extract the elements individually you can do:

set DD=%DATE:~0,2%
set MM=%DATE:~3,2%
set YYYY=%DATE:~6,4%
set HH=%TIME:~0,2%
set MI=%TIME:~3,2%
set SS=%TIME:~6,2%

....or to do it in one hit:

set TIMESTAMP=%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%^
%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
echo %TIMESTAMP%

TIMESTAMP will then look like this:

20080811100011

Note: I only just noticed, somewhat shamefully, that if you do this before 10:00am, you get a space in the timestamp, because the hour is represented as ' 9', rather than '09'. When I figure out a workaround I'll post it.

DOS equivalent of unix 'whence' or 'type'

Found this construct looking in the help for FOR - I can't quite work out how to use it in a batch file, sadly.....

for %I in ("sqlplus.exe") do echo %~$PATH:I

gives

D:\oracle\product\10.2.0\client_1\BIN\sqlplus.exe

Update:

With many thanks to my friend Dave....

To create a whence.bat script, you need to have the following content:

@echo off
set TO_FIND=%1
for %%I in ("%TO_FIND%") do echo %%~$PATH:I

...then you can type, for example, 'whence sqlplus.exe' to locate sqlplus.

opatch error: Perl lib version (v5.8.3) doesn't match executable version (v5.6.1) at x:\oracle...

Got the following error:

C:\oracle\ora92\OPatch>perl opatch.pl version
Perl lib version (v5.8.3) doesn't match executable version (v5.6.1) at x:\oracle
\10.2.0\perl\5.8.3\lib/MSWin32-x86-multi-thread/Config.pm line 32.
Compilation failed in require at x:\oracle\10.2.0\perl\5.8.3\lib/MSWin32-x86-mul
ti-thread/lib.pm line 6.
BEGIN failed--compilation aborted at x:\oracle\10.2.0\perl\5.8.3\lib/MSWin32-x86
-multi-thread/lib.pm line 6.
Compilation failed in require at opatch.pl line 88.
BEGIN failed--compilation aborted at opatch.pl line 88.

Kind of obvious, but the fix here is to set your PERL5LIB variable correctly...

C:\oracle\ora92\OPatch>set PERL5LIB=C:\perl\lib
C:\oracle\ora92\OPatch>perl opatch.pl version

Oracle Interim Patch Installer version 1.0.0.0.57
Copyright (c) 2007 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

OPatch Version: 1.0.0.0.57

instType uknown

Started getting this error from a batch job which was using oradim to do a shutdown.

instType uknown: db not started; use RDBMS as default

I think this has just been occurring since we put a 10G install on the same server as the existing 9i and 8i databases.

Anyhow it was fixed by explicitly setting ORACLE_HOME and prefixing oradim with %ORACLE_HOME%\bin\

Determining which CPUs have been applied

The question of how to determine which CPUs have been applied to a database came up at a couple of presentations at the UKOUG, including mine.

A quick and dirty way (which I didn't think of at the time) would be to look in the CPU directory:

c:\> dir t:\oracle\ora92\cpu

11/06/2007 18:12 .
11/06/2007 18:12 ..
02/05/2006 17:30 CPUApr2006
30/04/2007 19:21 CPUApr2007
28/03/2006 18:51 CPUJan2006
......

Then to check which databases the catcpu script was run against, you can look in the directories themselves - the spoolfiles have the name of the database in the title:

c:\> dir t:\oracle\ora92\cpu\CPUAPr2007

30/04/2007 19:21 .
30/04/2007 19:21 ..
30/04/2007 18:19 4,918 APPLY_SID1_30Apr2007_19_19_49.log
30/04/2007 18:26 4,918 APPLY_SID2_30Apr2007_19_20_11.log

TNS-03505: Failed to resolve name - OID glitch

We're in the process of migrating from Oracle Names to LDAP, and found the following fairly trivial gotcha.

If you have a space between the word LDAP and the comma in the DIRECTORY_PATH, as below, then it won't work

NAMES.DIRECTORY_PATH= ( LDAP , ONAMES, TNSNAMES)

Oracle options - update from Niall

Subsequent to the previous post, I got a note from Niall, saying that

at least in 10g you can query dba_feature_usage_statistics as follows

SELECT NAME,VERSION,DETECTED_USAGES
FROM DBA_FEATURE_USAGE_STATISTICS
WHERE DETECTED_USAGES > 0;

Thanks, Niall!

Oracle lifetime support

I went to the UK Oracle User Group Unix SIG meeting yesterday. There was a lot of good stuff, as usual.

Something I had missed entirely (I have no idea how, to be honest) was that the 'Oracle Lifetime Support' policy has been published.

The significant changes from my POV is that 5 years after the General Availability date of Oracle software, CPUs and other software updates will have to be paid for on top of the existing support payments - at least that's my understanding.

Details are here, and there's also a handy table showing the different support levels here

Getting Windows server and Oracle options details from the command line

I've been asked to retrieve some details on:
- the options in use on our databases and
- the servers they run on.

Oracle feature usage can be retrieved as follows:

col parameter format a35
col value format a10
select name, banner from v$version, v$database;
select name,parameter, value from v$option, v$database
where parameter in (
'Partitioning',
'Real Application Clusters',
'Spatial',
'OLAP Window Functions',
'Oracle Label Security',
'OLAP',
'Oracle Data Mining'
)
order by 2,1;

Both of the commands below get server information from the commandline.

psinfo is from sysinternals and runs as follows:

psinfo \\servername

This gives uptime, service packs, CPUs and memory installed

systeminfo seems to be part of Windows XP but seems to work running it against Windows 2000 servers. It runs as follows:

systeminfo /S servername /U username /P password

This gives a lot more than psinfo - it lists all the Windows patches, and a bit more of the basic detail.

I learnt about systeminfo from this online version of Robbie Allen's Windows Server Cookbook, which looks really good.

Most used tiddlywiki commands for MainMenu

I put this into my MainMenu tiddler to give me a quick reference for my most used tiddlywiki formatting commands.

!Cheat sheet
|''Bold''|{{{''text''}}}|
|__Uline__|{{{__text__}}}|
|//Italic//|{{{//text//}}}|
|Bullets|{{{*text}}}|
|No.s|{{{#text}}}|
|Heads|{{{!text}}}|
|Table|{{{|t|t|}}}|
|Quote|{{{<<<>>>}}}|
|{{{Mono}}}|{{{{{{text}}}}}}|
|[[Tid]]|{{{[[Text]]}}}|
|[[Help|http://www.blogjones.com/TiddlyWikiTutorial.html#EasyToEdit]]|{{{[[t|url]]}}}|

Windows Powershell for IT Administrators in Reading

This free 'evening class' looks good:

Windows Powershell for IT Administrators

...although its a shame its not in London :)

DOS equivalent to unix ls -ltr

ls -ltr gives you a list of files sorted in order of the time they were last modified, with the most recent showing up last.

I use this quite a lot to locate recently updated log files, a file I've just edited etc.

The DOS equivalent would be:

dir /OD

The /O is order by. The 'D' is date.

DOS equivalent to unix find -name -print

I'm planning to type up a couple of DOS and Windows equivalents of some of my most used unix commands.

These are very much limited, partial equivalents. In this particular case, I'm not replicating all the functionality of the unix 'find', but 9 times out of 10 when I use find, I use it like this:

find . -name 'sqlnet.ora' -print

You could of course use the Windows Explorer search. However, this doesn't help if you want to, say, save the results into a text file or process them in a batch script. And, also, I prefer the commandline!

Anyhow, my quick and dirty DOS equivalent is:

dir /B /S sqlnet.ora

Some explanation, if its needed:
- The /S says do a recursive listing
- The /B is actually do a 'bare' listing with no size or date info. In this context though its use is that it prefixes the directory onto the output for each file

Bookmarking Oracle Metalink in IE or Firefox

This may well be obvious, but....

If you try and save the link to a Metalink note whilst you are actually in the Note, it doesnt always work very well.

Typically you get:
Name:ShowDoc
URL:https://www.metalink.oracle.com/metalink/plsql/f?p=130:14:42099925743
7239999::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_b
lack_frame,p14_font:NOT,1066139.6,1,1,1,helvetica,yada,yada,yada

There are two problems with this:
- the Name's not very useful, and more significantly,
- the URL won't work

The URL doesn't work because of the string of numbers prior to the 4 consecutive colon's. My guess is that this represents the login credentials of your current session, and these won't work once you terminate that session or after you've re-booted your PC.

I currently use a couple of workarounds for this. Both involve right-clicking on a link to the page you're currently on, so one way or another you have to navigate away from that page. It's a bit manky, but it does seem to work.

Either:

1. Go to the top of the note
2. click on the 'Bookmark' link (at the top right).
3. This takes you into your own personal list of Metalink bookmarks.
4. Right-clink on the bookmark you've just saved
5. select 'Bookmark this link' (Firefox), 'Add to Favourites' (IE)

Sporadically I then delete all of the bookmarks on my Metalink bookmark page.

Or:

1. Use the back arrow, to go back to the page you came from
2. Right-clink on the bookmark you've just saved
3. select 'Bookmark this link' (Firefox) or 'Add to Favourites' (IE)

You then get the title of the note in the Bookmark, and a working URL. You need to already be logged into Metalink for it to work, though.

I *think* I had this working with del.icio.us as well, but since there was some sort of change to the Metalink login, the URL of the page doesn't persist through the login screen.

Powershell and SFU both free

Just a brief post. In conversation at the Oracle on Windows SIG last week, some doubt was raised as to whether Powershell and Services for Unix were free or not.

According to this FAQ, Powershell is free:
Microsofts Powershell FAQ

According to this press release, SFU is too!
2004 Microsoft Press release

DOS script to output contents of latest file matching a given spec

Some of our batch jobs write log information to standard output. Others create dated log files.

Our scheduling tool kind of works best if log information goes to standard output - it captures it then allows you to inspect it from console.

I wrote the following script to spool out the latest version of a given dated log file.

I called it show_log.bat So if you run it as follows:

cmd /k show_log d:\logfiles\my_batch_job_*.log

...it displays the latest my_batch_job_*.log that it can find, where * is any string - in our case typically a date of some sort. So I put a call to show_log at the end of each batch job which creates a dated log file and I can see it from the scheduler console too.

If you have a directory that is called my_batch_job_*.log it wouldn't work so well - I had to use /S to get dir to return the full pathname for the file.

echo off

rem This routine accepts a file specification (in the
rem format you use for rem dir) as a paremeter, then
rem displays the last updated file matching that
rem specification to stdout
rem
rem It replaces log2stdout.pl

set DIR_STRING="%1"
echo Dir String %DIR_STRING%

rem
rem These couple of lines work out what the last
rem updated file is. The dir command orders by
rem the updated date. The /S means do a recursive
rem search. This is there to ensure that the dir
rem returns the full path name rather than to
rem actually search a directory structure.
rem
rem We would get unexpected results if the
rem dir_string does actually match
rem a directory.

set LAST_FILE=
for /f %%I in ('dir /B /N /S /OD %DIR_STRING% ') do set LAST_FILE=%%I
echo Last file is %LAST_FILE%

if "%LAST_FILE%"=="" exit

rem
rem Retreiving the date stamp of the file for display

for /f "tokens=1,2" %%I in ('dir %LAST_FILE% ^| findstr /V "Volume Directory ( ^$"') do set DATE_CREATED=%%I %%J
echo Date created is %DATE_CREATED%

echo Going to output this file:
dir %LAST_FILE% | findstr /V "Volume Directory ( ^$"
echo -----

rem
rem Echoing line by line rather than simply using
rem 'type' because I wanted to prefix each line
rem with the files datestamp. The reason for this is
rem because I don't want there to be any confusion
rem as to what file is being looked at, either
rem because this script has failed or because
rem the file you're expecting hasn't actually
rem been created

for /F "delims=" %%I in (%LAST_FILE%) do echo %DATE_CREATED% Log: %%I

exit

gcompile.sql - Generate a list of compile statements

I've never had much joy with utlrp, and I'm too lazy to work out why.

Anyway, this works for me:

set pagesize 0
spool compile.sql
select decode(object_type,
'PACKAGE BODY',
'alter ' || 'package ' ||' ' ||
owner ||'.' || object_name ||' compile body;' ||
CHR(10) ||
'sho err',
'TYPE BODY',
'alter ' || 'type ' ||' ' ||
owner ||'.' || object_name ||' compile body;' ||
CHR(10) ||
'sho err',
-- OTHER STUFF
'alter ' || object_type ||' ' ||
owner ||'.' || object_name ||' compile;' ||
CHR(10) ||
'sho err')
from dba_objects
where status = 'INVALID'
and owner like '%&owner%'
-- and owner not in ('SPOT', 'IMON_USER', 'PERFSTAT', 'SYS', 'SYSTEM','LMS', 'INDEX_MAINTENANCE')
order by owner;
spool off

Week of Oracle exploits 'gets suspended'

According to this:

http://www.argeniss.com/woodb.html

"We are sad to announce that due to many problems the Week of Oracle Database Bugs gets suspended.

We would like to ask for apologizes to people who supported this and were really excited with the idea, also we would like to thank the people who contributed with Oracle vulnerabilities."