NAZIM'S CRICKET BLOG

Available db_recovery_file_dest

2011-12-12T10:02:00.000+11:00

A gr8 script picked up somewhere along the way to check the available amount of db_recovery_file_dest space.

select name,
  floor(space_limit/1024/1024) "Size_MB",
  ceil(space_used/1024/1024) "Used_MB",
  floor(space_limit/1024/1024) - ceil(space_used/1024/1024) "Available_MB",
  round(ceil(space_used/1024/1024) / floor(space_limit/1024/1024) * 100)  || '%' "Percent Used"
  from v$recovery_file_dest
  order by name;

chmod

2011-11-08T13:31:00.000+11:00

Only just found out, instead of working out the numeric settings of chmod, the following conventions can be "literally" used:

chmod u+rwx
chmod g+rwx
chmod o+rwx

chmod u-rwx
chmod g-rwx
chmod o-rwx

Export to Pipe

2011-10-24T12:04:00.002+11:00

Using fifo (first in first out) you can redirect your database exports through a pipe straight to gzip.

make fifo pipe
mkdir fifo db.fifo

write the file output to gzip and send to background
cat db.fifo | gzip > db.dmp.gz &

run the export to the fifo pipe
exp username/password file=db.fifo log=db.log etc

PeopleSoft SymbolicID

2011-10-13T15:35:00.001+11:00

Note to self - symbolicid is stored in table:

PSACCESSPRFL

Ian Healy - Legen ... wait for it ... dary!!

2011-09-27T18:04:00.000+10:00

What a generous bloke. Signed a whole bunch of items. Amazing stuff!!

OEM - UDM Collection Interval

2011-09-27T11:23:00.000+10:00

You may have noticed that User Defined Metrics (UDM) in Oracle Enterprise Manager (OEM) have a minimum collection time of 5 minutes.

If you wish to change this interval, you can do so by editing a xml file on the server which is the target of the UDM.

On target server, navigate to $ORACLE_AGENT_HOME/sysman/emd/collection and edit file oracle_database_{SID}.{SERVER}.xml.

Search for the name of the UDM and edit the IntervalSchedule tag e.g.

<CollectionItem NAME="{UDM_NAME}">
<Schedule>
<IntervalSchedule INTERVAL="{n}"/>
</Schedule>

Check Server UpTime

2011-08-13T07:35:00.000+10:00

systeminfo | find "Time"

Ardmona Cricket Cards

2011-08-06T09:50:00.003+10:00

Recent inclusions to the collection are sets of Ardmona 1979 and 1980 cricket cards:

Ardmona 1979 Set

Ardmona 1980 Set

Change Apex 4.0 Admin Password

2011-08-05T15:49:00.000+10:00

To change the ADMIN user password on Apex 4.0 run the script apxxepwd.sql. This will change the encrypted password stored in table apex_040000.wwv_flow_fnd_user.

Rem Copyright (c) Oracle Corporation 1999 - 2006. All Rights Reserved.
Rem
Rem NAME
Rem apxxepwd.sql
Rem
Rem DESCRIPTION
Rem Changes the password for the INTERNAL ADMIN user
Rem
Rem NOTES
Rem Assumes the SYS user is connected.
Rem
Rem REQUIRENTS
Rem - Oracle 10g
Rem
Rem
Rem MODIFIED (MM/DD/YYYY)
Rem jstraub 08/01/2006 - Created
Rem jkallman 09/29/2006 - Adjusted current_schema to FLOWS_030000
Rem jkallman 08/02/2007 - Change FLOWS_030000 references to FLOWS_030100
Rem jkallman 07/08/2008 - Change FLOWS_030100 references to FLOWS_040000
Rem jkallman 10/02/2008 - Change FLOWS_040000 references to APEX_040000

Rem

set define '&'

set verify off

alter session set current_schema = APEX_040000;

prompt ...changing password for ADMIN

begin

wwv_flow_security.g_security_group_id := 10;
wwv_flow_security.g_user := 'ADMIN';
wwv_flow_security.g_import_in_progress := true;

for c1 in (select user_id
from wwv_flow_fnd_user
where security_group_id = wwv_flow_security.g_security_group_id
and user_name = wwv_flow_security.g_user) loop

wwv_flow_fnd_user_api.edit_fnd_user(
p_user_id => c1.user_id,
p_user_name => wwv_flow_security.g_user,
p_web_password => '&1',
p_new_password => '&1');
end loop;

wwv_flow_security.g_import_in_progress := false;

end;
/

Added to the Site

2011-07-07T11:07:00.000+10:00

Donated by my friend Jack ...

Been A Good Book Week

2011-07-04T19:00:00.000+10:00

Have had a decent week of autograph hunting at second hand book shops. Picked up Max Walker for $9, Keith Stackpole for $20 and the bargain of the year, Brian Booth for 50¢

Trade Section

2011-06-25T23:57:00.000+10:00

Added a Trade Section to the cricket website ...

NAZIM'S CRICKET PAGE (Trade Section)

Autographed Caricatures

2011-06-14T21:17:00.002+10:00

The latest addition to the collection is a range of signed caricatures by John Ireland:

Latest Acquisition

2011-04-17T23:22:00.000+10:00

Lucky pick up from a library booksale

Couple of Pics from the new setup

2011-04-04T23:13:00.000+10:00

Frontosa Colony and Albino Pleco

Check Uptime on Oracle Database (Unix)

2011-03-30T08:04:00.002+11:00

ps -ef | grep pmon

DOS Script to Email Results of a Database Export

2011-03-29T12:31:00.003+11:00

Had to write a batch script to check on the status of a nightly database export and email status. Some of what I've done may not be perfect, but it does the job nicely and portions can be reused, hence it's appearance on this blog for future reference.

@ECHO OFF
REM Enable local environments to be changed without affecting anything else.
SETLOCAL

REM check the export.log file for the filedate and pass it into a variable. Done by doing a directory listing on the file, returning just the filename and then looping through and capturing the file's timestamp into a variable

for /f "tokens=* delims=" %%a in ('dir/b export.log') do (
for /f "tokens=1 delims=" %%i in ("%%a") do (
set _fdt=%%~Ti
)
)

REM Write the logfile contents to a secondary file. This is only done to allow us to append the results of a TSM tape backup of the export.dmp to a logfile and email it in the email body.

type export.log > bkup.log

REM set a variable _status with a default of ERRORS. If the process has a status of SUCCESS, the following commands will overwrite this variable. If not, it will report it as ERRORS in the email

SET _status=ERRORS

REM Loop through the earlier generated secondary logfile and find the string "Export terminated successfully without warnings" which is a standard Oracle output found in export logs

for /f "tokens=*" %%s in ('type bkup.log ^| find /i "Export terminated successfully without warnings"') do (SET _status=SUCCESS)

REM Processes to follow a SUCCESS which includes sending the export.dmp to TSM tape storage. Append the TSM results to the secondary logfile generated earlier.
if %_status%==SUCCESS (
cd \TSM\baclient\
dsmc archive -des="TSM Archive Name %_fdt%" -archmc=ARCH14D export.dmp >> bkup.log

Use a third party application, postie to email the results

c:\postie\postie -host:mailhost -to:sender@email.com -s:"SUCCESS:ExportDaily (%USERDOMAIN%) for %_fdt%" -nomsg -from:sender@email.com -file:bkup.log
)

REM Email as ERRORS if the above step fails

if %_status%==ERRORS c:\postie\postie -host:mailhost -to:recipient@email.com -s:"ERRORS:ExportDaily (%USERDOMAIN%) for %_fdt%" -nomsg -from:sender@email.com -file:D:\backup\obj_live\bkup.log

Alter Database Open Resetlogs Upgrade

2011-03-18T16:51:00.000+11:00

Had to restore a database to a point-in-time from a hotbackup. Followed Steps:

Copied datafiles to target
Created a New Controlfile
Recovered using syntax: RECOVER DATABASE UNTIL TIME 'YYYY-MM-DD:HH:MI' USING BACKUP CONTROLFILE;
Ran ALTER DATABASE OPEN RESETLOGS;

... and that's when the wheels fell off, with the database instance terminated and errors in the alert log advising startup upgrade needs to be done.

The reason for this is that the target database was upgraded sometime after the point in time recovery date.

In the past, when faced with this situation, I was able to point the database and the /etc/oratab entries to the old Oracle Home and recover that way, but in this instance the old home had been removed and well a more elegant solution needed to be found.

Resolution:

Copied datafiles to target
Created a New Controlfile
Recovered using syntax: RECOVER DATABASE UNTIL TIME 'YYYY-MM-DD:HH:MI' USING BACKUP CONTROLFILE;
Ran ALTER DATABASE OPEN RESETLOGS UPGRADE;

The next step is to then apply the upgrade scripts and breathe a sigh of relief!

Meeting Jeff Thomson

2011-03-11T23:45:00.000+11:00

"Ashes to ashes, dust to dust; if Lillee don't get you Thommo must!"

Had the opportunity today of meeting Jeff Thomson at a function, where he was guest speaker. At 60 years of age, Thomson is an unassuming, very likeable, down-to-earth bloke.

The first half of the evening was rather a let down, as Thomson delivered jokes that wouldn't have been much funnier at the hands of a professional stand-up comedian. However, he more than made up for the light hearted banter with some insightful comments about his career.

His anecdotes relating to his good friend Len Pascoe were hilarious, though I shan't bother repeating them here, as "you had to be there".

Most interesting was the Q&A session, where a lot of interesting information was gleaned. I personally asked him two questions (paraphrasing):

Q. Did you ever feel like you were playing second string to Dennis Lillee and what was your relationship with Lillee like?

A. Lillee and I were good mates. I used to scare the sh*t out of batsmen and then Lillee would do his thing and get the wickets. It was a great partnership, which later on was mirrored in the partnership between McGrath and Warne. All I ever wanted to do was bowl fast.

Q. Who do you think will win the world cup this year?

A. I reckon Australia are a good chance. They've not lost anything so far and if Hussey is in form, they should do well. India are not that special.

Other snippets of information gleaned from the Q&A included the following. Please note, I did not take notes and am just recalling from memory what was said. Some of what was mentioned, may have been misinterpreted by myself, so if you want Thomson's personal opinion on any of these matters, don't quote me, ask the man yourself (Is that a decent enough disclaimer for the dirt I'm about to dish out?)

Hilditch is likely to be offered another 2 year extension of contract as Selector, which is bad news for Australian cricket.
Ponting has his favourites and as a result talented players like Brad Hodge have not had a decent run in the team.
Michael Clarke is not liked by the players and the selectors would rather have as captain someone such as Tim Paine.
Thomson doesn't rate Phil Hughes as a test calibre batsman and would rather see someone like David Warner given a chance to play test cricket.
From the current crop of players, he rates Brett Lee the highest. Does not appear to have any time for the likes of Tait, Siddle or Mitchell.
In his opinion, most bowlers don't spend enough time honing their craft on the cricket pitch. As an example, Steve Finn when not picked for England, was sent off for strength and conditioning instead of playing cricket for his county side. Thomson feels that the only way to get the best out of bowlers is to have them bowl a lot.
His role model was "his dad" and guys such as Fred Trueman.

Pardon the paunch - I should've worn black, as I'm told it's more slimming (doubt it would've helped much though)

MySQL Spool File

2011-03-11T16:48:00.007+11:00

Thanks to my friend LW for helping sort this one out.

You can increase the verboseness (is that a word?) of a batch output logfile in MySQL, by repeating the "-v" switch.

This is amazingly useful if for example you are running some DML and wish to capture in your output logfile the number of rows being inserted/deleted/updated.

mysql -u user -ppassword database_name -v -v < test.sql > test.log

Lightening Strikes Thrice!! (Warning: Disturbing Images)

2011-03-10T08:52:00.000+11:00

AAARRRGGG!!!! Today for the third time, in a lifetime of keeping fish, I've lost my entire colonies of happy, healthy, breeding fish. This time to equipment failure. Murphy's Law being what it is, these things always happen in the dead of night when no one is around to notice of course.

My primary air-filter stopped working and the secondary didn't have enough juice to keep the fish alive, as the population in my tank, through breeding was starting to get out of control. My own fault of course, for not thinning them out and there is only so much equipment that can be added to a tank!

Anyhow, just lost an array of fantastic fish including Frontosas, Duboisies, Mainganos, Electric Yellows. The only survivors, my clown loaches, bristlenose catfish and one or two hybrid cichlids, which may not live through the day :(

Not sure what to do now, may just give the survivors to a friend and call it a day!

Obtaining a Cricket Postmark

2011-02-24T22:07:00.000+11:00

Thanks to some fabulous advice from Colleen of Australian PictorMarks I was able to obtain this awesome cricket postmark through Australia Post on an Ashes Cover.

I think the end result is quite pleasing to the eye.

The Great Forgery!!

2011-02-24T19:38:00.000+11:00

As the Sydney Cricket Ground turned pink for Jane McGrath this summer, I found myself the lucky recipient of a signed and framed Test Shirt from Phil Hughes, courtesy of the McGrath Foundation.

Whilst awaiting my prize, I asked my daughter to illustrate the shirt for my work newsletter. Looking at her handiwork alongside the now received shirt, I feel quite satisfied that should she fall upon hard times in the future, she may well have a decent career in forgery!

SQL Server - Query Backups Taken

2011-02-21T10:52:00.000+11:00

Note to self ... Good place to check backups taken:

dbo.backupset

PeopleSoft - Access Log Table

2011-02-18T12:48:00.000+11:00

A good place to check login/logout activity

PSACCESSLOG

MySQL Global Options File - Storing Password

2011-02-03T13:53:00.001+11:00

New to MySQL, but will make notes for my benefit, and for the odd person who stumbles on this post via Google. I need to write a few cron scripts for a MySQL database and I don't think I should put the connect string in them for the world to see.

A reasonable solution found is to simply store the password in the /etc/my.cnf (options) file.

Added the following text to achieve this. Also decided to store the default database name as well to make life simpler.

[client]
password={password}
database={dbname}

For this setting to take affect, just had to restart the database. So now the cron script can simply connect to the correct database by typing in "mysql".

Need to assess security implications of this decision as I proceed to learn the nuances of MySQL.

Check O/S Version

2011-02-02T09:39:00.001+11:00

uname -a

Also .....

LINUX:
cat /etc/issue

AIX:
oslevel
oslevel -r (shows technology level)
oslevel -s (shows service pack)

Oracle 9 - Manually Create Standby DB from Hotbackup

2010-10-30T09:19:00.000+11:00

This is basically a "Note-To-Self".

High Level Steps for Reference, are:

 Copy Datafiles across.

 On Target, set up env variables, password file, oratab entry etc.

 In the init.ora, pay particular attention to parameters:

- db_name -> Same as the Primary Database to match the Controlfile

- log_archive_format

- log_archive_dest

- log_file_name_convert

- db_file_name_convert

- fal_client

- fal_server

 Use a standby controlfile from the hotbackup, or create one manually

Recovery Time

2010-10-30T09:12:00.002+11:00

It's quite useful sometimes to be able to see the timestamp associated with a Database Recovery.

select to_char((max(TIME_DP)),'DD MON YYYY HH24:MI') FROM SMON_SCN_TIME;

SQLServer - Check Mirror Status

2010-10-29T09:24:00.003+11:00

A handy SQL contributed by Andrew R.

SELECT left(a.name,50) 'Database'
      , CASE b.status WHEN 0 THEN 'Suspended'
        WHEN 1 THEN 'Disconnected'
        WHEN 2 THEN 'Synchronizing'
               WHEN 3 THEN 'Pending Failover'
               WHEN 4 THEN 'Synchronized'
               ELSE 'Investigate status'
         END 'Mirror_Status'
      , local_time 'Time'
   from master.sys.databases a
      , msdb.dbo.dbm_monitor_data b
  where a.database_id = b.database_id
    and b.local_time = (select max(A_ED.local_time) 
                   from msdb.dbo.dbm_monitor_data A_ED
                  where b.database_id = A_ED.database_id
                             and A_ED.local_time <= GETDATE())
       group by a.name, b.database_id, b.status, b.local_time
       order by 1,3;

Undo Tablespace Usage

2010-09-08T19:48:00.000+10:00

select sum(blocks)
     , sum(bytes)/1024/1024
  from dba_undo_extents;

Check Version of SQL Server Database

2010-09-02T09:12:00.001+10:00

select @ @ version

Truncate and Shrink Database

2010-06-28T15:26:00.001+10:00

On SQLServer I've learnt that we can't take for granted that a table truncate will reuse storage. Therefore to reclaim empty space following the truncate of a large table, I ran:

DBCC SHRINKDATABASE (DBNAME, TRUNCATEONLY)
GO

Unix Yesterday's Date

2010-06-18T09:56:00.001+10:00

date -d '1 day ago'

date -d 'yesterday'

TSM - Command Line Restore

2010-06-08T15:53:00.003+10:00

dsmc restore "/path/file*" -pick -inactive -subdir=y

to pick files - enter line number followed by +, separated by colons for example 1:2+

cmdline stop/start/check-status of a service remotely

2010-06-01T16:04:00.005+10:00

Using the sc utility ...

sc \\servername stop ServiceName
sc \\servername start ServiceName

sc \\servername query ServiceName

Inspired by this, I decided to create a little DOS program which an end-user could use to stop/start a service.

@ECHO OFF
SETLOCAL enableextensions

for /f "tokens=4" %%s in ('sc query OracleServiceXE ^| find /i "state"') do (SET _state=%%s)

for /f "tokens=2" %%t in ('sc query OracleServiceXE ^| find /i "service_name"') do (SET _service_name=%%t)

ECHO ================================
ECHO %_service_name% is %_state%
ECHO ================================

if %_state%==RUNNING goto SRVC-R
if %_state%==STOPPED goto SRVC-S

:SRVC-R
echo %_service_name% Is RUNNING, would you like to STOP it? Y/N
set /p Input1=
if /i "%Input1%"=="y" sc stop OracleServiceXE
echo.
sleep 10

for /f "tokens=4" %%s in ('sc query OracleServiceXE ^| find /i "state"') do (echo %_service_name% is %%s)

pause
exit /b

:SRVC-S
echo %_service_name% Is STOPPED, would you like to START it? Y/N
set /p Input1=
if /i "%Input1%"=="y" sc start OracleServiceXE
echo.
sleep 10

for /f "tokens=4" %%s in ('sc query OracleServiceXE ^| find /i "state"') do (echo %_service_name% is %%s)

pause
exit /b

Check Oracle Blocking Locks

2010-05-07T14:47:00.003+10:00

SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi') dttm
, ' --- BLOCKER --- '
, lo.session_id
, lo.process
, lo.os_user_name
, dob.object_name
, dob.object_type
, ' --- BLOCKING --- '
, s.sid
, s.username
, s.status
, s.schemaname
, s.osuser
, s.process
, s.machine
, s.terminal
, s.program
, s.sql_hash_value
, s.logon_time
, p.spid
, l.id2
, l.ctime
FROM v$session s
, v$process p
, v$lock l
, v$locked_object lo
, dba_objects dob
WHERE s.lockwait is not null
AND p.addr(+) = s.paddr
AND l.sid = s.sid
AND l.type='TX'
AND l.id2 = lo.xidsqn
AND lo.object_id = dob.object_id

Count network connections

2010-05-05T13:33:00.002+10:00

Task:

Get a count of number of network connections on a Windows server.

My Solution:

Create a batch file containing the following lines:

REM list network connections and write them to a file
netstat -n > netstat_count.txt

REM search for non-existant string in file and return line count instead
find /v /c "zzz" netstat_count.txt

This returns something like ...
---------- NETSTAT_COUNT.TXT: nnn

Oracle / SQL Server Equivalent

2010-04-28T14:18:00.003+10:00

This post shall define the equivalent views between Oracle and SQLServer.

Oracle	SQLServer
sys.dba_tables	information_schema.tables
sys.dba_tab_columns	information_schema.columns

OEM Repository Scripts

2010-04-27T13:27:00.008+10:00

This Blog will be a running compilation of useful Enterprise Manager Repository scripts (run as the SYSMAN user).

Database Sizes
SELECT target_name, ROUND (SUM (FILE_SIZE) / 1024 / 1024 / 1024) GB
FROM MGMT$db_datafiles_all
GROUP BY target_name

Datafile Sizes - A good way to find datafiles incorrectly set to autoextend to Unlimited, i.e. growing to 32768M
SELECT target_name
, file_name
, sum (file_size) / 1024 / 1024
FROM mgmt$db_datafiles_all
HAVING sum(file_size)/1024/1024 > 15000
GROUP BY target_name, file_name
ORDER BY 3 desc;

Tablespace Allocated and Used Space Query
select A.RT
, A.ALLOCATED
, B.USED
from (
SELECT TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM') RT
, AVG(AVERAGE) ALLOCATED
FROM MGMT$METRIC_DAILY
WHERE TARGET_NAME LIKE {database_name}
AND METRIC_NAME='tbspAllocation'
and key_value={tablespace_name}
and METRIC_COLUMN='spaceAllocated'
group by TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM')
) a,
(
SELECT TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM') RT
, AVG(AVERAGE) USED
FROM MGMT$METRIC_DAILY
WHERE TARGET_NAME LIKE {database_name}
AND METRIC_NAME='tbspAllocation'
and key_value={tablespace_name}
and METRIC_COLUMN='spaceUsed'
group by TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM')
) b
where a.rt = b.rt
order by a.rt DESC;

Clear Alerts
select 'execute em_severity.delete_current_severity(p_target_guid =>'''||sev.target_guid||''', p_metric_guid =>'''||sev.metric_guid||''', p_key_value =>'''||key_value||''');' as command
from MGMT_TARGETS tgt, MGMT_CURRENT_SEVERITY sev
where TGT.TARGET_NAME like '%{TGT_NAME}%';
For older severities - just a straight delete from MGMT_SEVERITY will do the trick.

User Defined Metrics
select *
from mgmt$metric_current
where metric_name like '%UDM%';

Syntax for Impersonating a User

2010-04-20T10:28:00.002+10:00

execute as user='username'
go

Oracle 10g Log Mining

2010-04-19T20:57:00.003+10:00

Please note the database parameter log_parallelism needs to be set at 1 for this to work

In this day and age of flashback configuration, log mining almost seems like an out-dated process, never to see the light of day. However, today it came in handy.

I was asked to check for any transactional rollbacks which may have been issued on the database during a certain time period. Unfortunately flashback was only configured for a short duration on the database and the time I had to check was out of bounds.

I therefore ....

Copied across the archive log files from backup tape to the archive log directory.

I then added to log miner the archive log files I wanted mined:
exec SYS.DBMS_LOGMNR.ADD_LOGFILE('pathname/filename.arc');

The next step was to start log-mining based on the timestamps
BEGIN
SYS.DBMS_LOGMNR.START_LOGMNR(
STARTTIME => TO_DATE('13-Apr-2010 08:00', 'DD-MON-YYYY HH24:MI')
,ENDTIME => TO_DATE('13-Apr-2010 16:00', 'DD-MON-YYYY HH24:MI')
,OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + SYS.DBMS_LOGMNR.CONTINUOUS_MINE
);
END;
/

This then gave me the information I needed:
SELECT timestamp, sql_redo FROM v$logmnr_contents

I concluded the task by clearing out logminer:
exec SYS.DBMS_LOGMNR.END_LOGMNR;

Trigger to Create Public Synonyms

2010-04-15T18:12:00.001+10:00

We have an application which creates dynamic temporary tables, which require public synonymns generated to enable users to access these tables. The solution which came to mind was to create a DDL trigger. However, once I'd finished writing the trigger, I discovered the following limitation:

ORA-30511 invalid DDL operation in system triggers. 
Cause
An attempt was made to perform an invalid DDL operation in a system trigger. Most DDL operations currently are not supported in system triggers. The only currently supported DDL operations are table operations and ALTER?COMPILE operations.

I tried a few different options such as playing around with my favourite pragma autonomous_transaction method, but couldn't get the trigger to execute the "create public synonym" command.

Therefore, I decided to create a stored procedure which would accept the parameters (objowner and objname) and wrote the trigger to execute a background job which would run the procedure in a minute - nice and easy solution.

The Stored Procedure
CREATE OR REPLACE PROCEDURE SYN_PROC (objname in varchar2, objowner in varchar2) IS

begin

execute immediate 'create public synonym  ' || objname || '  for ' || objowner || '.' || objname;

end;
/

The Trigger
CREATE OR REPLACE TRIGGER SYN_TRIG
AFTER create ON SCHEMA
declare

jobnum   number;

BEGIN 

IF ORA_DICT_OBJ_OWNER='SCHEMA_OWNER' AND ORA_DICT_OBJ_NAME LIKE 'TABLENAME%' THEN

   SYS.DBMS_JOB.SUBMIT (jobnum, 'ORACLE_DBA.SYN_PROC(''' || ORA_DICT_OBJ_NAME || ''',''' || ORA_DICT_OBJ_OWNER || ''');' , sysdate+(1/24/60));

END IF;

END;
/

Recreate Users and Roles

2010-04-14T16:38:00.002+10:00

At my workplace, there is often a need to capture user and role information prior to performing a database refresh, to allow existing user accounts and permissions to be re-created post-refresh.

I have come up with the following scripts to assist with this task:

List and generate scripts to recreate accounts with Domain Authenticaton:
select 'CREATE LOGIN [DOMAIN\' + name + '] FROM WINDOWS WITH DEFAULT_DATABASE=[ceinfraprd]' from dbo.sysusers where isntname=1  


List and generate scripts to reinstate the roles:
select distinct 'exec sp_addrolemember ' + '''' + y.name + ''', ' + '''' + x.name + ''''
  from
(select uid, name, groupuid
  from dbo.sysusers a
     , dbo.sysmembers b
 where a.uid = b.memberuid) as x,
(select uid, name, groupuid
  from dbo.sysusers a
     , dbo.sysmembers b
 where a.uid = b.groupuid) as y
where x.groupuid = y.uid

Oracle Shell Script - Emailing the Logfile

2010-04-13T09:16:00.000+10:00

An example of a shell script which connects to multiple unix hosted databases and runs a simple SQL. This version emails the logfile to people on the mailing list.

#!/bin/ksh
#
# Shell script that connects to multiple database servers and runs a SQL
#

MAIL_LIST="nazim@nazimcricket.com"

# Server List
SERVERS='server1 server2 server3'

LOG="/tmp/logfile.log"

> $LOG

for SERVER in $SERVERS
do

sqlplus -s username/password@$SERVER << EOF | tee -a $LOG
select * from global_name;
exit;
EOF

done

uuencode ${LOG} ${LOG} | mail -s "Results Log" ${MAIL_LIST}

MS SQL Server user script

2010-04-12T15:31:00.000+10:00

I don't have much experience with SQLServer, so I'm sure there are better ways of doing this. This is the way I've worked out how to list users and their granted roles ....

select distinct x.uid, x.name, y.name
  from
(select uid, name, groupuid
  from dbo.sysusers a
     , dbo.sysmembers b
 where a.uid = b.memberuid) as x,
(select uid, name, groupuid
  from dbo.sysusers a
     , dbo.sysmembers b
 where a.uid = b.groupuid) as y
where x.groupuid = y.uid
order by x.name

Oracle Shell Script with Password Prompts

2010-04-12T09:29:00.001+10:00

An example of a shell script which connects to multiple unix hosted databases and runs a simple SQL. This version prompts for the username/password.

#!/bin/ksh
#
# Shell script that connects to multiple database servers and runs a SQL
#

# Script expects the username and password to be parsed as arguments.
# If not found, it will error with message ...

if [[ $# -lt 2 ]]
then
        print "\nUSAGE: script_name.ksh username password\n"
        print "\nExample: script_name.ksh scott tiger\n"
        exit 0
fi

username=$1
password=$2

# Server List
SERVERS='server1 server2 server3'

LOG="/tmp/logfile.log"

> $LOG

for SERVER in $SERVERS
do

sqlplus -s $username/$password@$SERVER << EOF | tee -a $LOG
select * from global_name;
exit;
EOF

done

Oracle Shell Script

2010-03-31T11:03:00.002+11:00

An example of a shell script which connects to multiple unix hosted databases and runs a simple SQL.

#!/bin/ksh
#
# Shell script that connects to multiple database servers and runs a SQL
#

# Server List
SERVERS='server1 server2 server3'

LOG="/tmp/logfile.log"

> $LOG

for SERVER in $SERVERS
do

sqlplus -s username/password@$SERVER << EOF | tee -a $LOG
select * from global_name;
exit;
EOF

done

Check Oracle Database Size

2010-02-25T13:49:00.001+11:00

A good way to check the size of your database is to add up the size of your tablespaces (including Temp) and the redo logs as follows:

select sum(megabytes) 
 from (
select sum(bytes)/1024/1024 megabytes from dba_temp_files
union
select sum(bytes)/1024/1024 megabytes from dba_data_files
union
select sum(bytes)/1024/1024 megabytes from v$log
);

RMAN Archivelog Deletions

2010-02-06T16:54:00.003+11:00

Note to self

rman nocatalog<< DATA
connect target
delete noprompt archivelog until time 'SYSDATE-n';
DATA

rman nocatalog<< DATA
connect target
delete noprompt archivelog until sequence= nnnnnnn;
DATA

A scenario where so many archivelogs are being generated, that you have to resort to using RMAN to clear out archivelogs on the fly, can also be supported by increasing the physical space for archivelog files alongwith boosting the db_recovery_file_dest_size parameter i.e. increasing the flash recovery area.

ORA-19815: WARNING: db_recovery_file_dest_size of n bytes is n% used, and has n remaining bytes available.
alter system set db_recovery_file_dest_size=nM/G scope=both;

Close Encounter with Pakistan Cricket team

2010-01-25T08:19:00.000+11:00

On Saturday night, happened to be out and about in Sydney. Decided to get some takeaway from a Pakistani restaurant, but was denied service as they were playing host to the Pakistan Cricket team.

I wish I had done better, but for my troubles, I was able to get autographs from Shoaib Malik, one of the Akmal brothers (Kamran I think?) and Waqar Younis.

Also had some luck at a second hand book shop where I picked up for a pittance a West Indian cricket book with autographs of Michael Holding and Colin Croft.

Links to photos and scans of the autographs ....

http://www.nazimcricket.com/me_younis.jpg
http://www.nazimcricket.com/images/younis11.jpg
http://www.nazimcricket.com/me_malik_akmal.jpg
http://www.nazimcricket.com/images/malmal.jpg
http://www.nazimcricket.com/images/holcroft.jpg

My Reading List for 2010

2010-01-09T16:15:00.000+11:00

Also looking forward to the release of:

The Wilt Inheritance - Tom Sharpe

Dexter Is Delicious - Jeff Lindsay

War Torn - Andy McNab

Check last applied archive log

2009-12-07T15:54:00.002+11:00

SELECT max(sequence#) from v$log_history;

Also, the following

archive log list;

Houston Rockets

2009-11-19T21:06:00.002+11:00

A few of my favourite collectables:

Resize Online Redo Logs - Rough Notes

2009-11-13T14:01:00.011+11:00

To resize redo log files, we have to drop and recreate them. The status of these files is critical.

Files with a status of:

CURRENT - cannot/must not be dropped. To change the status of a current file, we have to initiate a log switch (ALTER SYSTEM SWITCH LOGFILE;).

ACTIVE - Is likely to return an error; ORA-01624: log 1 needed for crash recovery of instance. This can be overcome by generating a checkpoint (ALTER SYSTEM CHECKPOINT GLOBAL;).

INACTIVE - May be dropped.

UNUSED - May be dropped.

Once again, the status of these files can be changed by initiating a log switch (ALTER SYSTEM SWITCH LOGFILE;).

The following SQLs assist with the task of:

Checking pertinent redo log file details

SELECT a.group#
     , a.member
     , b.bytes / 1024 / 1024 MB
     , b.status
  FROM v$logfile a
     , v$log b 
 WHERE a.group# = b.group#
order by a.group#;

Dropping redo log file groups
ALTER DATABASE DROP LOGFILE GROUP n;

Resizing redo log file

ALTER DATABASE ADD LOGFILE GROUP n 
('/member_path/redo_logfile_name1',  
 '/member_path/redo_logfile_name2') SIZE nM REUSE;

For a standby database the following additional steps are needed before dropping a online redo log group ...

Stop Standby Recovery
ALTER DATABASE RECOVER MANAGED STANDBY CANCEL;

Set Standby File Management to Manual
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

Clear Logfile Group

If the STATUS of the logfile group is CLEARING or CLEARING_CURRENT, you can set it to unused by running command ALTER DATABASE CLEAR LOGFILE GROUP n;

Logon Audit Trigger

2009-11-03T10:29:00.006+11:00

This is my version of a logon audit trigger. It picks up every iteration of a logon, even to the point of when a session switches from ACTIVE/INACTIVE mode.

The more conventional way of creating a logon audit trigger would be to simply capture environment variables using the SYS_CONTEXT function, utilising USERENV, environment variables, for example ..

sys_context('USERENV','CURRENT_USER')
sys_context('USERENV','CURRENT_USERID')
sys_context('USERENV','HOST')
sys_context('USERENV','OS_USER')

Create a table to store the information provided by the trigger

create table nazim_logon_audit 
(  sid number
 , pid number
 , spid varchar2(12)
 , process varchar2(12)
 , status varchar2(8)
 , username varchar2(30)
 , osuser varchar2(30)
 , program varchar2(48)
 , client_info varchar2(64)
 , module varchar2(48)
 , logon_time varchar2(30)
 , last_call_et varchar2(30)
);

Create the trigger

create or replace trigger
   nazim_logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into nazim_logon_audit
(select s.sid 
     , p.pid 
     , p.spid
     , s.process
     , s.status
     , s.username
     , s.osuser
     , s.program
     , s.client_info
     , s.module
     , to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss')
     , to_char(sysdate - (s.last_call_et /86400),'yyyy-mm-dd hh24:mi:ss') 
  from v$session s
     , v$process p
 where s.paddr = p.addr
   and s.username=user 
   and s.logon_time = sysdate
 );
commit;
END;
/

Oracle Process Details with SQL Text

2009-11-02T13:35:00.002+11:00

A useful query to check database processes alongwith the executed SQL statement:

select s.sid
     , p.pid
     , p.spid
     , s.process
     , s.status
     , s.username
     , s.osuser
     , s.program
     , a.name
     , s.client_info
     , s.module
     , to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss')
     , to_char(sysdate - (s.last_call_et /86400),'yyyy-mm-dd hh24:mi:ss')
     , sq.sql_text
  from v$session s
     , v$process p
     , audit_actions a
     , v$sql sq
 where s.paddr = p.addr
   and s.command = a.action
   and sq.address (+) = decode(s.sql_address,'00', s.prev_sql_addr, s.sql_address)
 order by s.status;

Field Key

Oracle Session ID

Oracle Process ID

OS Process ID

OS Client Process ID

Status of Session

Oracle Username

OS Username

OS Program Name

Command in Progress

Client Info from DBMS_APPLICATION_INFO.SET_CLIENT_INFO proc

Module Info from DBMS_APPLICATION_INFO.SET_MODULE proc

Time of Logon

Last Call

SQL Text

Oracle - Media Recovery

2009-10-01T10:34:00.004+10:00

A gem of a tip from my friend Mike N. which I was unaware of. Had a database in noarchivelog mode. Copied it across to a new server. Due to a minor mishap encountered "ORA-01113: file needs media recovery" on the target database.

To fix this, ran the command RECOVER DATABASE UNTIL CANCEL and when prompted to specify log, in the absence of any archivelogs, pointed it to the redo log files and was able to recover. Following this off with a ALTER DATABASE OPEN NORESETLOGS and saved myself the hassle of recopying all the datafiles again.

Another tip along the same lines provided by my mate Ken W. Once again, performed a RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL. Supplied the archivelogs I had available but attempting to do ALTER DATABASE OPEN RESETLOGS found it required another archivelog which I did not have. Therefore recreated the CONTROLFILE using syntax CREATE CONTROLFILE SET DATABASE "DBNAME" RESETLOGS FORCE LOGGING NOARCHIVELOG REUSE. Followed this up with a ALTER DATABASE OPEN RESETLOGS - problem solved.

Note to Self - Move Existing Database

2009-09-25T16:00:00.009+10:00

To move an Oracle database from one server to another ...

Shutdown source database.

Create directories on target (/oracle/SID/datafiles, /oracle/admin/SID/sysfiles, /oracle/env/dbs, /oracle/pass)

Copy across datafiles, tempfiles, controlfiles, pfile, spfile, password file redo logs and environment files.

Add an entry in /etc/oratab.

Modify the parameter db_domain (ALTER SYSTEM SET DB_DOMAIN=domain SCOPE=spfile)

Alter database rename global_name.

Check the parameter service_names.

Update oracle names.

Setup cron entries / scheduled tasks.

Oracle 10g - Flashback (Schema)

2009-08-31T16:41:00.006+10:00

Technically there is no schema level flashback feature available. However, provided your recovery period does not include any data dictionary changes (DDL) then you could actually use datapump to flashback export the schema and import it back in.

To achieve this you would use syntax like ...

expdp username/password directory=data_pump_dir dumpfile=data_pump_dir:dumpfile.dmp logfile=data_pump_dir:logfile.log schemas=SCHEMA flashback_time=\"to_timestamp('timestamp', 'yyyy-mm-dd hh24:mi')\"

The import process would use syntax like ...

impdp username/password directory=data_pump_dir dumpfile=DUMPFILE.DMP remap_schema=SCHEMA:SCHEMA

In this instance, I'm remapping the schema to another schema, so I can keep both in play for comparison purposes. You may choose to overwrite it instead.

Oracle 10g - Flashback (Table)

2009-08-27T16:37:00.011+10:00

The ability to flashback transactions to a point in time is quite a handy feature. Here is how it all works ....

First of all, the oracle init.ora parameter db_flashback_retention_target needs to be set. This parameter specifies the upper limit (in minutes) on how far back in time the database may be flashed back. The default value is 1440 minutes (24 hours).

How far back one can flash back a database depends on how much flashback data Oracle has kept in the recovery area.

To execute a FLASHBACK TABLE command, you need the FLASHBACK TABLE or FLASHBACK ANY TABLE privilege. In addition, you must have INSERT, DELETE and ALTER privileges on all the specified tables.

Retrieving a Table with Flashback Query

SQL> SELECT * FROM PAKISTAN_CRICKET;

FIRST_NAME           LAST_NAME           
-------------------- --------------------
Khan                 Imran               
Akram                Wasim               
Younis               Waqar               
Afridi               Shahid              
Miandad              Javed               

5 rows selected.

Drop the table.

SQL> DR0P TABLE PAKISTAN_CRICKET;

Table dropped.

The default action associated with the drop command in 10g is to place the object in a recycle bin.  You can view the contents in the recycle bin using ..

SQL> SELECT * FROM RECYCLEBIN;

You may choose to permanently delete the table by purging it from the recyclebin or you could recover it via the following flashback command .. 

SQL> FLASHBACK TABLE PAKISTAN_CRICKET TO BEFORE DROP;

Flashback successful.

Other options available with the FLASHBACK TABLE command are illustrated below .. 



SQL> SELECT * FROM PAKISTAN_CRICKET;

FIRST_NAME           LAST_NAME           
-------------------- --------------------
Khan                 Imran               
Akram                Wasim               
Younis               Waqar               
Afridi               Shahid              
Miandad              Javed               

5 rows selected.

Oracle 10g - Flashback (Row)

2009-08-27T11:33:00.037+10:00

The ability to flashback transactions to a point in time is quite a handy feature. Here is how it all works ....

How far back one can flash back a database depends on how much flashback data Oracle has kept in the recovery area.

Retrieving a Row with Flashback Query

SQL> SELECT * FROM AUSTRALIA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Ricky                Ponting
Michael              Clarke
Stuart               Clark
Brad                 Haddin
Brett                Lee

5 rows selected.

Delete a row of data - make a note of the deletion time for the purpose of this exercise.

SQL> DELETE FROM AUSTRALIA_CRICKET WHERE FIRST_NAME='Brett';

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM AUSTRALIA_CRICKET;

FIRST_NAME           LAST_NAME           
-------------------- --------------------
Ricky                Ponting             
Michael              Clarke              
Stuart               Clark               
Brad                 Haddin              

4 rows selected.

Now to use a flashback query to view the data as it was prior to the delete.

SQL> SELECT * FROM AUSTRALIA_CRICKET AS OF TIMESTAMP TO_TIMESTAMP('2009-08-27 15:26','YYYY-MM-DD HH24:MI');


FIRST_NAME           LAST_NAME           
-------------------- --------------------
Ricky                Ponting             
Michael              Clarke              
Stuart               Clark               
Brad                 Haddin              
Brett                Lee                 

5 rows selected.

You may restore the deleted row as follows

SQL> INSERT INTO AUSTRALIA_CRICKET(SELECT * FROM AUSTRALIA_CRICKET AS OF TIMESTAMP TO_TIMESTAMP('2009-08-27 15:26','YYYY-MM-DD HH24:MI') WHERE FIRST_NAME='Brett');

1 row inserted.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM AUSTRALIA_CRICKET;

FIRST_NAME           LAST_NAME           
-------------------- --------------------
Ricky                Ponting             
Michael              Clarke              
Stuart               Clark               
Brad                 Haddin              
Brett                Lee                 

5 rows selected.

PeopleSoft - Install as a Service

2009-06-25T10:41:00.003+10:00

In PeopleTools 8.49 we can now install the Windows Application Servers and Process Schedulers as a Service.

This can easily be setup using new a menu option on psadmin.exe 4) Service Setup.

In my case, I only wanted to configure the Process Scheduler as a Service. This was easily achieved by Editing the Service Configuration File (psntsrv.cfg) and commenting out the line - Application Server Domains=.

Once the service has been configured, use the 2) Install a Service menu option and you're ready to rock!

Oracle/Unix - Pipe to Gzip

2009-06-18T12:04:00.005+10:00

As an example, I wish to spool/pipe out an oracle export file straight to gzip on unix.

Create a Pipe

mkfifo ppipe

In the background send the pipe contents to gzip

cat ppipe | gzip -c > filename.dmp.gz &

Run the export to the pipe

exp username/password file=ppipe log=filename.log

Data Pump - Dynamic List of Tables to Export

2009-06-05T15:07:00.002+10:00

In PeopleSoft, I needed to export out all configuration/system-owned tables as a backup each week. To achieve this, my first step was to create a view listing all these tables, by filtering out those which are not prefixed with "PS_"

CREATE OR REPLACE VIEW EXPORT_WEEKLY (TABLE_NAME) AS
SELECT table_name
  FROM all_tables
 WHERE owner = 'SYSADM' 
   AND NOT REGEXP_LIKE (table_name, '^PS(_)');

I then used Oracle datapump to export the tables listed in this view ..

expdp username/password parfile=parfilename

parfile contents

DIRECTORY=dpump_dir1
DUMPFILE=dpump_dir1:dumpfile.dmp
LOGFILE=dpump_dir1:logfile.log
SCHEMAS=SCHEMANAME
INCLUDE=TABLE:"IN (SELECT TABLE_NAME FROM EXPORT_WEEKLY)"
CONTENT=DATA_ONLY

PeopleSoft List Of System Tables

2009-06-04T16:37:00.003+10:00

To obtain a list of all "system owned" tables in PeopleSoft, i.e. those which are not prefixed by a PS_, run the following SQL:

SELECT table_name
  FROM all_tables
 WHERE owner = 'SCHEMA_OWNER'
   AND NOT REGEXP_LIKE (table_name, '^PS(_)');

Temp Tablespace Usage

2009-05-05T16:10:00.003+10:00

A useful script to check on users/processes using up temp tablespace.

SELECT distinct s.username
     , s.sid
     , s.serial#
     , s.osuser
     , u.tablespace
     , u.contents
     , u.segtype
     , u.extents
     , u.blocks
  FROM v$session s
     , v$sort_usage u
 WHERE s.saddr=u.session_addr
order by s.username, s.osuser;

Note: blocks * size of blocks = mbytes

Create User Scripts

2009-05-05T12:08:00.009+10:00

There are many ways of cloning a user account or two from one database to another. Here are a few simple SQLs to dynamically generate scripts from the source database, which can then be executed on the target database.

I have in this example assumed that the tablespaces etc in the source and target database are identical in name and size. I have also chosen to set all the user roles (by default) as Default.

select 'CREATE USER ' || username || ' IDENTIFIED BY VALUES ''' || password || ''' DEFAULT TABLESPACE ' || default_tablespace || ' TEMPORARY TABLESPACE ' || temporary_tablespace || ' PROFILE ' || profile || ' ACCOUNT UNLOCK;' from dba_users where username in (&USERS);
select 'GRANT ' || granted_role || ' TO ' || grantee || decode(admin_option,'YES',' WITH ADMIN OPTION;',';') from dba_role_privs where grantee IN (&USERS);
select 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || ' TO ' || grantee || decode(grantable,'YES',' WITH GRANT OPTION;',';') from dba_tab_privs where grantee IN (&USERS);
select 'ALTER USER ' || username || ' QUOTA ' || decode(max_bytes,-1,'UNLIMITED ON ' || TABLESPACE_NAME || ';', max_bytes/1024 || 'K ON ' || TABLESPACE_NAME || ';') from dba_ts_quotas where username in (&USERS);

DAD Config - Note To Self

2009-05-04T09:20:00.009+10:00

In Oracle 10, the modplsql Database Access Descriptors may be set in the following file:

$ORACLE_HOME/Apache/modplsql/conf/dads.conf
<Location /pls/dad name>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseConnectString server:1521-port:dbname.server ServiceNameFormat
PlsqlAuthenticationMode Basic
PlsqlDefaultPage homepage
</Location>

Report Manager - Note to Self

2009-05-01T09:44:00.003+10:00

Data source for Report Manager Administration page is PS_CDM_LIST_VW.

Data source for Report Manager List Page is PSRF_PGLET_VW. This requires EIPs to be working.

Related: PSRF_REPORT_FOLDERS channel/queue. PSRF_REPORT_xxxx messages.

PeopleSoft - Find Job Applicants

2009-04-07T10:12:00.003+10:00

I have little exposure to the PeopleSoft Human Resources module, so I cannot be certain that this SQL would work globally.

Anyhow, to obtain information about job applicants, you could run SQL.

select a.job_req_nbr
     , a.posting_title
     , c.first_name || ' ' || c.last_name   
     , a.position_nbr
  from ps_job_requisition a
     , ps_posn_appliedfor b
     , ps_names c
 where a.job_req_nbr = b.job_req_nbr
   and b.applid = c.emplid
   -- use one or more of the following conditions
   and posting_title like ?
   and c.last_name like ?
   and a.job_req_nbr= ?
   and a.position_nbr= ?

Fish Pics

2009-02-21T08:58:00.013+11:00

I've yet to learn the art of aquarium photography, but here goes nothing ....

Some of the fish I've got in together are technically incompatible and are a product of circumstance.

The Ups and Downs of Fish Keeping

2009-02-08T10:00:00.005+11:00

I've kept tropical fish ever since I was a teenager. I wasn't very good at it until a friend gave me a colony of Convict cichlids back in 1992. He was using them as feeder fish for his Oscars. With a whole colony of hardy convicts to look after, I was able to hone my skills and within a short period of time they were breeding like rabbits. I ended up selling them a few years later when I moved to the US.

Upon my return to Oz, I decided to try my hand again at keeping fish. I got myself a 4ft tank and started slowly. However, no matter how hard I tried, I just couldn't get water chemistry and balance of it all sorted. Fortunately a friend of mine with vastly more experience lent me a hand and got me over this hurdle. In 2 short years, I once again had a vibrant tank full of breeding pairs of bristlenose catfish and a range of colourful cichlids.

Unfortunately, in 2005, during renovations to my home, we had a thunderstorm whilst a section of the roof was off. This caused water to seep into the wiring and tripped the circuit, cutting off the air supply to my fishtank. Overnight it wiped out my entire population of cichlids. The bristlenose survived as they probably were able to swim to the surface and take a breath.

I decided to press on with the hobby and replenished with some maingano cichlids and also got myself a small ghost knife fish. Another 3 years passed and by this time the mainganos were breeding as much as the bristlenose were and my pride and joy, the ghost knife fish had grown immensely. I had the perfect setup when lightening struck again. This time an appliance malfunctioned during the night and tripped the circuits. By the morning all except the bristlenose and a handful of maingano fry (babies) died.

At this point it would have been easy to give up. However, I decided to mull it over and whilst browsing at a local petshop, I came across 4 Tropheus Duboisis. These fish, from Lake Tanganyika, are quite unlike the Lake Malawi cichlids and South American Cichlids I have been accustomed to. They are supposedly quite difficult to look after; Finicky with diet (herbivores) and water conditions and tend to be quite expensive. On the spur of the moment I decided to ask the petshop owner if he would trade the duboisis for a bunch of my bristlenose catfish and later on I even managed to get the details of the breeder he bought them from, so I could acquire some more. They do better as a larger colony.

So I'm once again dusting myself off and getting on the saddle. I'll still miss the fantastic ghost knife fish I lost this year, but these marvellous new fish take some of the sting away. Maybe this time I'll invest in some battery powered air pumps or something!

Latest Cricket Stamp News

2009-01-01T11:29:00.002+11:00

StampNews.com : M.C.C. and Cricket Association of Bengal put a joint stamp on history

Excellent Find

2008-12-31T13:24:00.005+11:00

I recently visited a large second-hand bookstore in Sydney. On my previous visit there, after hours of searching, I came across one cricket book which was signed by Mike Atherton. Hoping to repeat the experience, I walked in and wouldn't you know it, the first book I picked up turned out to be signed by Dennis Lillee and priced at a meagre $10.

Encouraged by my success, I went through a few more and within minutes found a real gem. A book signed by Garry Sobers, personalised to some guy but priced at only $15, it was an awesome find.

I did find one other book, signed by Kerry O'Keefe, but I decided to leave it as it was in average condition and on the other side of the signed page was a long and rather intimate note from the previous owner's girlfriend. Felt a bit creepy to buy something so personal.

Bradman Museum

2008-12-12T09:08:00.005+11:00

Last week I had an opportunity to visit the Bradman Museum in Bowral.

The experience was not as fulfilling as I had hoped. The museum is quite small, as is the attached oval and the displays whilst nice are not particularly special. A number of items are quite commonly available modern pieces, which was especially disappointing.

To top things off, no photography is allowed in the museum, so what little there was to see could not be captured for future enjoyment.

Another issue to bear in mind is that if you happen to be travelling with family or friends who aren't interested in cricket, I'm afraid they will find little to captivate their attention in town, whilst you're in the museum.

To compare notes I'll need to revisit Bradman's birthplace in Cootamundra, but from memory, whilst the museum setup there is not as elegant as Bowral's, the pieces of memorabilia are certainly more fascinating.

PeopleSoft - Find Menu Path

2008-11-28T15:21:00.004+11:00

PDF document containing a handy mod-pl/sql based stored procedure to find the PeopleSoft Menu Path, using a Page, Process or Component as the search criteria.

path.pdf

PeopleTools - Running Multiple Installs

2008-11-21T16:38:00.005+11:00

Running multiple versions of PeopleTools from the same PC can sometimes be problematic. This is caused by the fact that each version's Configuration Manager writes to the exact same Registry Entry on the computer, namely "ConnectId" and "ConnectPswd".

For installs where the "people" credentials are the same across different PeopleTools installs, this of course is not a problem. The only thing is that the last Configuration Manager invoked has it's settings written to the Registry, which can be a little annoying.

To get around this, it's important to set the desired ConnectId and ConnectPswd prior to invoking PeopleTools. Obviously this would be tedious for users to do manually, as they would have to start up Configuration Manager and type in the correct credentials for the version of PeopleTools they wish to run. It would also, of course be a significant security risk if they knew the people password.

Therefore, I have created a batch file which I have placed in each PeopleTools version's bin directory. When invoked it calls the Configuration Manager, imports the configuration file containing the correct (encrypted) people credentials for that version, i.e. sets the Registry entries, and then invokes the Application Designer executable.

So instead of users double-clicking the Application Designer executable, I get them to run my batch file instead.

rem invoke Configuration Manager and Import Configuration Settings File
pscfg -import:c:\path\file.cfg -quiet

rem invoke Application Designer
pside

Oracle OEM Mining

2008-11-20T17:35:00.001+11:00

It can be quite useful to mine the tables stored in the Oracle Enterprise Manager repository.

To check the size of your databases in gigabytes, simply run:

select target_name
     , to_char (sum (file_size) / 1024 / 1024 / 1024, '999,999.99') sze
  from mgmt$db_datafiles_all
group by target_name
order by 2 desc;

Oracle - Toad vs OEM: Statistics

2008-11-14T14:34:00.002+11:00

Something I've learnt (the hard way) is that statistics generation using Toad is inferior to Oracle Enterprise Manager. Main reason is that Toad tends to lean towards using the analyze command rather than the dbms_gather.statistics package. For my money, the following method seems to be the panacea of all table statistics issues:

dbms_stats.gather_table_stats( 
ownname=> 'OWNERNAME', 
tabname=> 'TABLENAME' , 
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, 
cascade=> TRUE);

Oracle Table and Index Sizes

2008-11-06T14:48:00.006+11:00

A useful query to check your large tables and indexes.

In this case, I'm checking those with a combined size of 1gb.

select y.table_name
     , x.tbl_size
     , y.idx_size
     , (tbl_size + idx_size) ttl_mb
  from (select segment_name
             , sum (bytes / 1024) / 1024 tbl_size
             , segment_type
          from dba_segments
         where segment_type in ('TABLE') 
           and owner = '{OWNER}'
        group by segment_name, segment_type) x
     , (select table_name
             , sum (bytes / 1024) / 1024 idx_size
             , segment_type
          from dba_segments a
             , dba_indexes b
         where a.segment_type in ('INDEX')
           and a.owner = '{OWNER}'
           and a.tablespace_name = b.tablespace_name
           and a.segment_name = b.index_name
          group by segment_type, table_name) y
 where x.segment_name = y.table_name 
   and (tbl_size + idx_size) > {1000}
order by tbl_size + idx_size desc;

Another method, applicable to auto segment space managed segments is to use the dbms_space package in lieu of going through dba_segments. In this case, however, I do use dba_segments as a join in order to get a count of the extents.

Unfortunately this method provided some inaccurate results in a locally managed environment.

set serveroutput on

DECLARE

su NUMBER;
sa NUMBER;
cp NUMBER;
v_kount NUMBER;
v_stmt VARCHAR2(1000);

cursor tbl_space is
select a.table_name
, a.tablespace_name
, a.owner
, sum(b.extents) num_extents
from dba_tables a
, dba_segments b
where a.table_name = b.segment_name
and a.owner = b.owner
and a.owner in ('{OWNER}')
group by a.table_name, a.tablespace_name, a.owner;

cursor idx_space is
select a.index_name
, a.tablespace_name
, a.owner
, sum(b.extents) num_extents
from dba_indexes a
, dba_segments b
where a.index_name = b.segment_name
and a.owner = b.owner
and a.owner in ('{OWNER}')
group by a.index_name, a.tablespace_name, a.owner;

BEGIN

dbms_output.enable(1000000);

dbms_output.put_line('DT_COLLECTED' || ',' || 'OWNER' || ',' || 'OBJECT_NAME' || ',' || 'OBJECT_TYPE' || ',' || 'OBJECT_TBSPC' || ',' || 'ALLOCATED_MB' || ',' || 'USED_MB' || ',' || 'PERCENT_USED' || ',' || 'NUM_EXTENTS' || ',' || 'ROW_COUNT');

for i in tbl_space loop

v_stmt := 'select count(*) from ' || i.owner || '.' || i.table_name;

execute immediate v_stmt into v_kount;

dbms_space.object_space_usage(i.owner, i.table_name, 'TABLE', NULL, su, sa, cp);

dbms_output.put_line(sysdate || ',' || i.owner || ',' || i.table_name|| ',' || 'TABLE'|| ',' || i.tablespace_name || ',' || TO_CHAR(sa/1024/1024,'999999.99')|| ',' || TO_CHAR(su/1024/1024,'999999.99')|| ',' || to_char((su / sa)*100,'99.99') || '%'|| ',' || i.num_extents|| ',' || v_kount);

end loop;

for i in idx_space loop

dbms_space.object_space_usage(i.owner, i.index_name, 'INDEX', NULL, su, sa, cp);

dbms_output.put_line(sysdate || ',' || i.owner || ',' || i.index_name || ',' || 'INDEX' || ',' || i.tablespace_name || ',' || TO_CHAR(sa/1024/1024,'999999.99') || ',' || TO_CHAR(su/1024/1024,'999999.99') || ',' || to_char((su / sa)*100,'99.99') || '%' || ',' || i.num_extents || ',' || 0);

end loop;

END;
/

Random Image Generator

2008-11-01T00:02:00.015+11:00

On my website I have been using a third party PHP random image generator which scrolls through my /images folder and displays a random image.

This has been working fine except that my images are stored in multiple folders and as a result I've been missing out on displaying quite a few of these. Also, I don't have any control in terms of turning the random image returned into a hyperlink to the page containing it etc.

To solve this problem, I decided I would use the power of SQL to return a random image, rather than use PHP.

Basically, I have a number of tables in my database, all of which contain a couple of common fields:

IMAGE - Stores the directory and name of the Image.

URL - Stores the URL of the page on which the image appears.

I achieved what I was after in a couple of very simple steps:

Created a View to bring all the IMAGE and URL information into one place:

CREATE OR REPLACE VIEW IMAGE_VW (IMAGE, URL) AS 
SELECT IMAGE, URL FROM TABLEA
UNION
SELECT IMAGE, URL FROM TABLEB;

Implemented the following SQL and PHP code:

<?php

$queryx  = "SELECT IMAGE, URL FROM IMAGES_VW ORDER BY RAND() LIMIT 1";
$resultx = mysql_query($queryx);

while($row = mysql_fetch_row($resultx))
{
    $IMAGE = $row[0];
    $URL = $row[1];
 
    echo "<a href=$URL><img src=$IMAGE></a>";

}

?>

Geoff Lawson Sacking

2008-10-26T18:43:00.004+11:00

The sacking of Geoff Lawson by the Pakistan Cricket Board has left me fuming. When Lawson was selected over Dav Whatmore, I was amazed to say the least. He just didn't seem to be the right choice and nothing Lawson has done to-date has changed my opinion on that.

However, the manner in which he has been sacked is reprehensible. First the new PCB Chairman Ijaz Butt announces in a press conference that Lawson is useless. He then goes on to say that whilst he isn't impressed by Lawson, he will support him for the remainder of his contract (August 2009) and then out of the blue he sacks the man.

According to reports, Lawson met with Butt-head, agreed to a follow up meeting and then heard about his sacking from the media. When he attempted to contact the Chairman to clarify his position, he was stone-walled.

In justification of their unprofessional behaviour, statements such as these have been made:

"Lawson had not even attempted to meet the chairman even once since he took over,” said the official. “We felt it reflected poorly on his attitude that he didn’t want to meet the chairman and explain what he wanted and where he wanted the team to go.”
When told that Lawson was upset that the chairman had not made any effort to meet him, the official asked simply: “Why should it be that way round? He is the chairman. The coach should seek him out.”

This high and mighty Chairman had no problem "summoning" several people to his office, including the current captain, yet he did not have the common decency to extend the same courtesy to the coach? What a load of garbage.

The Pakistan Cricket Board and it's new officials are an absolute disgrace and I for one fervently hope that Lawson is able to sue the pants off them (not that it would matter to these egotistical jerks).

Phew .... I did say I was fuming, didn't I? :-)

How Times Have Changed

2008-10-18T09:40:00.003+11:00

Cricket civilises people and creates good gentlemen. I want everyone to play cricket in Zimbabwe; I want ours to be a nation of gentlemen. Robert Mugabe

PeopleSoft Shutdown Process Scheduler

2008-10-17T14:18:00.016+11:00

Had a very specific task today, which I thought would be good to document for future reference. Others may also find some parts of it useful.

Background:
We have a PeopleSoft Reporting database (PeopleTools Version 8.43.16) which suffers constantly from rogue nvision and excel processes hanging around on the Windows process schedulers for days on end.

The database is refreshed nightly from our Production environment and any reports running at the time of refresh need to be cleared out. Simply shutting down the Windows process scheduler is not always sufficient to address the issue, as Excel spreadsheets in particular may be left hanging around.

The other side-effect is inspite the process scheduler being shutdown, some reports get left with a status of "Processing" on the process monitor. These can later on impede other jobs in the queue.

3rd Party Utilities:
Implemented my solution with the aid of the following utilities:

SCHEDULED TASK MANAGER from Microsoft.

SQLPLUS from Oracle.

PSADMIN from PeopleSoft.

PSKILL from SysInternals.

BAT2EXE Shareware.

Implemented Solution - Summary:
Created a batchfile which:

Executes a sqlplus script. This script parses the specified windows process scheduler server name and updates the process monitor tables, placing all reports which have a status of "Initiated" or "Processing" to a status of "Cancelled". Results of this are written to a logfile.

Invokes psadmin to shutdown the process scheduler.

Uses pskill to eliminate any Excel or Nvision processes.

Invokes psadmin to start up the process scheduler.

Invokes psadmin to verify the status of the process scheduler and writes this to a logfile.

To Finish Off:

Converted the batchfile to an executable using BAT2EXE. This was done to mask the credentials used in the call to the sqlplus script.

Created a Scheduled Task to run the executable nightly with the windows process scheduler server name used as a variable. This allows me the flexibility of rolling the script out to various windows process scheduler servers.

Implemented Solution - Syntax:
The batchfile contains the following lines of code:

sqlplus username/password@DATABASE @sqlscript.sql %1% > logfile.log
psadmin -p stop -d DATABASE
pskill EXCEL.exe
pskill psnvs.exe
psadmin -p start -d DATABASE
psadmin -p status -d DATABASE > logfile2.log

The sqlplus script contains the following lines of code:

def server=&1
update psprcsrqst set runstatus=8 where runstatus in (6,7) and servernamerun='&1';
update psprcsque set runstatus=8 where runstatus in (6,7) and servernamerun='&&1';
commit;
exit;

The scheduled task manager calls the executable as follows:

{path}\executablename servername_variable

SQLPLUS Ignore Ampersands

2008-10-02T11:32:00.003+10:00

In SQLPLUS when an ampersand (&) is encountered, Oracle treats it as a value prompt. For example, if you try to execute the following statement: INSERT INTO DEPARTMENT (DEPARTMENT_NAME) VALUES ('Finance & Treasury'); SQLPLUS will return a prompt: Enter value for treasury: To have SQLPLUS ignore the ampersand, use the following command prior to executing your query:

SET DEFINE OFF
INSERT INTO DEPARTMENT (DEPARTMENT_NAME) VALUES ('Finance & Treasury');

Lookup SQL Error on Unix

2008-10-01T16:06:00.004+10:00

command is:

oerr ORA 99999

PeopleSoft Monitor Message

2008-10-01T10:27:00.009+10:00

These queries replicate the Monitor Message Page.

Message Instance

SELECT A.CHNLNAME AS "Channel Name"
     , SUM(C.APMSG_XTB_0) AS "Error"
     , SUM(C.APMSG_XTB_1) AS "New"
     , SUM(C.APMSG_XTB_2) AS "Started"
     , SUM(C.APMSG_XTB_3) AS "Working"
     , SUM(C.APMSG_XTB_4) AS "Done"
     , SUM(C.APMSG_XTB_5) AS "Retry"
     , SUM(C.APMSG_XTB_6) AS "Timeout"
     , SUM(C.APMSG_XTB_7) AS "Edited"
     , SUM(C.APMSG_XTB_8) AS "Canceled"
  FROM PSAPMSGPUBHDR A
     , PSAPMSGXTB C
WHERE A.PUBSTATUS = C.APMSG_XTB_STAT
GROUP BY A.CHNLNAME
ORDER BY A.CHNLNAME;

Publication Contract

SELECT A.CHNLNAME AS "Channel Name"
     , SUM(C.APMSG_XTB_0) AS "Error"
     , SUM(C.APMSG_XTB_1) AS "New"
     , SUM(C.APMSG_XTB_2) AS "Started"
     , SUM(C.APMSG_XTB_3) AS "Working"
     , SUM(C.APMSG_XTB_4) AS "Done"
     , SUM(C.APMSG_XTB_5) AS "Retry"
     , SUM(C.APMSG_XTB_6) AS "Timeout"
     , SUM(C.APMSG_XTB_7) AS "Edited"
     , SUM(C.APMSG_XTB_8) AS "Cancelled"
  FROM PSAPMSGPUBCON A
     , PSAPMSGPUBHDR B
     , PSAPMSGXTB C
 WHERE A.PUBID = B.PUBID
   AND A.PUBNODE = B.PUBNODE
   AND A.CHNLNAME = B.CHNLNAME
   AND A.PUBCONSTATUS = C.APMSG_XTB_STAT
GROUP BY A.CHNLNAME
ORDER BY A.CHNLNAME;

Subscription Contract

SELECT A.CHNLNAME AS "Channel Name"
     , SUM(C.APMSG_XTB_0) AS "Error"
     , SUM(C.APMSG_XTB_1) AS "New"
     , SUM(C.APMSG_XTB_2) AS "Started"
     , SUM(C.APMSG_XTB_3) AS "Working"
     , SUM(C.APMSG_XTB_4) AS "Done"
     , SUM(C.APMSG_XTB_5) AS "Retry"
     , SUM(C.APMSG_XTB_6) AS "Timeout"
     , SUM(C.APMSG_XTB_7) AS "Edited"
     , SUM(C.APMSG_XTB_8) AS "Canceled"
  FROM PSAPMSGSUBCON A
     , PSAPMSGPUBHDR B
     , PSAPMSGXTB C
 WHERE A.PUBID = B.PUBID
   AND A.PUBNODE = B.PUBNODE
   AND A.CHNLNAME = B.CHNLNAME
   AND A.SUBCONSTATUS = C.APMSG_XTB_STAT
GROUP BY A.CHNLNAME
ORDER BY A.CHNLNAME;

Note: You could use the field LASTUPDDTTM to filter by "Days"

Windows Services

2008-09-20T20:27:00.004+10:00

Reference I find useful, as some servers at my workplace have hidden Control Panel options. To invoke Control Panel > Administrative Tools > Services, run:

c:\windows\system32\services.msc

UNION [ALL], INTERSECT, MINUS Operators

2008-09-17T10:10:00.032+10:00

The UNION operator is useful when you want to draw information from two or more tables that all have the same structure.

Take for example two tables AUSTRALIA_CRICKET containing a few players from the Australian Cricket Team and KOLKATA_CRICKET containing a few players from the IPL Kolkata Cricket Team.

SQL> SELECT * FROM AUSTRALIA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Ricky                Ponting
Michael              Clarke
Stuart               Clark
Brad                 Haddin
Brett                Lee

5 rows selected.

SQL> SELECT * FROM KOLKATA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Sourav               Ganguly
Shoaib               Akhtar
Ricky                Ponting
Brendon              McCullum
Chris                Gayle

5 rows selected.

You can see from the results that Ricky Ponting is a player common to both tables.

To return data from both these tables, you could write:

SQL> SELECT * FROM AUSTRALIA_CRICKET
  2  UNION
  3  SELECT * FROM KOLKATA_CRICKET;

FIRST_NAME                     LAST_NAME
------------------------------ -----------------------
Brad                           Haddin
Brendon                        McCullum
Brett                          Lee
Chris                          Gayle
Michael                        Clarke
Ricky                          Ponting
Shoaib                         Akhtar
Sourav                         Ganguly
Stuart                         Clark

9 rows selected.

You'll note Ricky Ponting was returned only once using the UNION syntax.

This is what happens if you change the UNION to UNION ALL:

SQL> SELECT * FROM AUSTRALIA_CRICKET
  2  UNION ALL
  3  SELECT * FROM KOLKATA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Ricky                Ponting
Michael              Clarke
Stuart               Clark
Brad                 Haddin
Brett                Lee
Sourav               Ganguly
Shoaib               Akhtar
Ricky                Ponting
Brendon              McCullum
Chris                Gayle

10 rows selected.

This time Ricky Ponting appears twice. Once for each table.

If you want only rows that appear in all tables, you would use the INTERSECT operation:

SQL> SELECT * FROM AUSTRALIA_CRICKET
  2  INTERSECT
  3  SELECT * FROM KOLKATA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Ricky                Ponting

1 row selected.

To round things off, if you wish to return data from the first table only, excluding data common between the two tables, you would use the MINUS operation:

SQL> SELECT * FROM AUSTRALIA_CRICKET
  2  MINUS
  3  SELECT * FROM KOLKATA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Brad                 Haddin
Brett                Lee
Michael              Clarke
Stuart               Clark

4 rows selected.

Or ... in Reverse:

SQL> SELECT * FROM KOLKATA_CRICKET
  2  MINUS
  3  SELECT * FROM AUSTRALIA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Brendon              McCullum
Chris                Gayle
Shoaib               Akhtar
Sourav               Ganguly

4 rows selected.

PeopleSoft Scheduled Jobsets

2008-09-16T11:21:00.013+10:00

A useful query to lookup "Active" scheduled jobsets.

select SCHEDULENAME
     , JOBNAMESRC
     , TO_CHAR(STARTDATETIME,'HH24:MI') TM
     , A.RECURNAME
     , RUNMONDAY
     , RUNTUESDAY
     , RUNWEDNESDAY
     , RUNTHURSDAY
     , RUNFRIDAY
     , RUNSATURDAY
     , RUNSUNDAY
  from ps_schdldefn a
     , psxlatitem b
     , ps_prcsrecur c
 where a.schedulestatus=b.fieldvalue
   and a.recurname = c.recurname
   and b.fieldname='SCHEDULESTATUS'
   and b.xlatshortname='Active'
order by TM;

The numeric values in the RUN{DAY} columns indicates what days of the week the jobs are scheduled to run.

Oracle Process Details

2008-09-09T14:56:00.026+10:00

A useful query to check database processes:

select s.sid
     , p.pid
     , p.spid
     , s.process
     , s.status
     , s.username
     , s.osuser
     , s.program
     , a.name
     , s.client_info
     , s.module
     , to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss')
     , to_char(sysdate - (s.last_call_et /86400),'yyyy-mm-dd hh24:mi:ss')
  from v$session s
     , v$process p
     , audit_actions a
 where s.paddr = p.addr
   and s.command = a.action
 order by s.status;

Field Key

Oracle Session ID

Oracle Process ID

OS Process ID

OS Client Process ID

Status of Session

Oracle Username

OS Username

OS Program Name

Command in Progress

Client Info from DBMS_APPLICATION_INFO.SET_CLIENT_INFO proc

Module Info from DBMS_APPLICATION_INFO.SET_MODULE proc

Time of Logon

Last Call

Accessing TSM through java client on UNIX

2008-09-08T15:30:00.009+10:00

Thanks to a tip from my mate Ken W.

Invoke TSM Java client on AIX 5.2:

Start up a x-terminal program such as x-win

Set the display using syntax export DISPLAY={ipaddress}:0.0

Set Java Path e.g. export PATH=$PATH:/{java directory path}

Start TSM dsmj

Oracle Apex Installation

2008-08-22T15:56:00.018+10:00

Steps for Installation of Application Express using Oracle 10g Express Edition Database and Apex 3.1.1 on Windows XP - SP2.
Pre-requisites ....

Download Oracle 10g Express Database for Windows (OracleXE.exe) from Oracle's website.

Download Oracle Application Express for Windows (apex_3.1.1.zip) from Oracle's website. Install the Database ....

Install the Oracle Database by double clicking on the OracleXE.exe.

Accept the License Agreement.

Define the Installation Directory. The default is c:\oraclexe

Set a System password. In my case I've used oracle as the password.

Review the summary information. The destination folder is c:\oraclexe, port for the database listener is 1521 and port for the http listener is 8080. Click to continue the installation process.

Click Finish at the end of the installation process. Configure a Database user for installation and administration of Apex. This user will require sysdba privileges ....

Go to the database Home Page All Programs > Oracle Database 10g Express Edition - Go to Database Homepage (Note: This is started up if the option is checked at the end of the database installation).

At the database login page enter Username as sys and password as set earlier oracle.

After successful login, select the Administration link, then select the Database Users link. Click on the Create button.

On the Create User page specify the username e.g. admin and password e.g. oracle. Confirm the password oracle.

The default tablespace is USERS and temporary tablespace will be TEMP. These need not be changed.

Specify the User Privilege Roles to be Connect, Resource, DBA.

Select the check all button for the directly granted system privileges section. Install Application Express ....

Extract the apex_3.1.1.zip file to a desired location for the Apex Software. In my case, I have chosen to keep it with the Database Home. Therefore my extract is to c:\oraclexe\. The extract will create a folder/directory called apex with a number of sub-directories under it.

Start a DOS session Start > Run > cmd and change directory to the Apex home (cd c:\oraclexe\apex).

Invoke sqlplus from the DOS prompt and login as the Database user created in the previous step i.e. sqlplus admin/oracle as sysdba .

This will take you to a sqlplus prompt SQL>. At the prompt, type in command SQL>SHOW PARAMETER SHARED_POOL_SIZE. Set the shared pool size to 100MB using the command SQL>ALTER SYSTEM SET SHARED_POOL_SIZE='100M' SCOPE=BOTH;.

Check if the value was set correctly by repeating the command SQL>SHOW PARAMETER SHARED_POOL_SIZE.

Commence installation of Apex by issuing the following command at the sqlprompt SQL>@apexins oracle USERS USERS TEMP /i/. This command runs a sql script called apexins.sql and passes in the parameters: Tablespace for the Apex application user (USERS), Tablespace for the Apex files user (USER), name of the Temporary Tablespace (TEMP) and virtual directory for the images folder (/i/).

The installation will take some time to complete and at the end of it you will be disconnected from the database.

Go to the Application Express homepage http://localhost:8080/apex/apex_admin and log in with username admin and password oracle. One Last Thing ....
If after installation you cannot see any images on the page, you can reset the prefix /i/ by running sql c:\oraclexe\apex\utilities\reset_image_prefix.sql. In my case, I was unable to get the prefix to work correctly, so I ran the following command SQL>@reset_image_prefix at the prompt Enter the Application Express image prefix [/i/] I typed in my full path i.e. c:\oraclexe\apex\images/ (Note the path ends with "/" which is a requirement of apex). I then left all other prompts blank and the script ran successfully and images were viewable to me.

Footnote: I'm told that you need to change the application images directory alias in the application properties too.

DOS - Append DateTime to Filename

2008-08-19T16:57:00.008+10:00

In DOS, you can append the date/time to a filename as follows:

SET DAY=%DATE:~4,2%
SET MTH=%DATE:~7,2%
SET YR=%DATE:~10,4%
SET HR=%TIME:~0,2%
SET HR0=%TIME:~0,1%
IF "%HR0%"==" " SET HR=0%TIME:~1,1%
SET MIN=%TIME:~3,2%
SET SEC=%TIME:~6,2%
SET MYDATE=%YR%%MTH%%DAY%-%HR%%MIN%%SEC%

The above construct sets the date and time formats and can be adjusted according to whatever format is desired

The variable MYDATE can then be appended to the file as follows:

C:\dir > filename_%MYDATE%

Resulting in something like:

filename_20080819-165818

Oracle - Playing Around with Views

2008-08-19T16:37:00.010+10:00

Just mucking about with some SQL to see what tables are referenced in views:

select name
  from dba_dependencies 
 where type='VIEW'
   and referenced_name in ('EMP','DEPT','BONUS');

To narrow this down further to views which use all three tables, we could add:


select name
  from (
select name
  from dba_dependencies 
 where type='VIEW'
   and referenced_name in ('EMP','DEPT','BONUS')
           )
having count(*) = 3 -- Same count as the number of tables
group by name;

To then check the actual SQL script for the views returned here, we could add:

select view_name
     , text
  from dba_views
 where view_name in 
( 
select name
  from (
select name
  from dba_dependencies 
 where type='VIEW'
   and referenced_name in ('EMP','DEPT','BONUS')
           )
having count(*) = 3
group by name  
)

I am sure there are better ways of doing these, but there you have it, a simple SQL to get more information about views and the tables used by them.

Oracle Create Table

2008-08-17T11:41:00.005+10:00

To create a table identical to another table, yet without any of the data, simply use the following syntax:

CREATE {TABLENAMEA} AS (SELECT * FROM {TABLENAMEB} WHERE 1=2);

Oracle Append DateTime to spool file

2008-08-15T16:49:00.010+10:00

How to append the date/time to an output spool file in SQLPLUS:

SQL>column dttm new_value v_dttm noprint;
The above construct is used to select a value into a user variable

SQL>select to_char(sysdate,'yyyymmddhh24miss') dttm from dual;
Here we see the user variable data being passed in, i.e. the date and time format. Note I have not used spaces, colons etc in between my date and time strings as sometimes these can lead to SP-0332 Spooling Errors.

SQL>spool filename_&v_dttm..log
Note the two fullstops (..) are deliberate

Oracle Send Mail Procedure

2008-08-12T10:35:00.007+10:00

A useful way of sending email through Oracle.

Parameters such as the SMTP host servername, SMTP port may be entered in as default values or be passed in for greater flexibility

CREATE OR REPLACE PROCEDURE SEND_MAIL
(  p_smtp_host in varchar2 default {'smtp_host'} 
 , p_smtp_port in number default {smtp_port}
 , p_sender in varchar2 default null
 , p_recipient in varchar2 default null
 , p_subject in varchar2 default null
 , p_message in varchar2 default null
) IS

connection UTL_SMTP.CONNECTION;

BEGIN

 connection := utl_smtp.open_connection( p_smtp_host, p_smtp_port );

  utl_smtp.helo( connection, p_smtp_host );
  utl_smtp.mail( connection, p_sender );
  utl_smtp.rcpt( connection, p_recipient );
  utl_smtp.data( connection, 'Subject: ' || p_subject  || utl_tcp.crlf ||       p_message );
  utl_smtp.quit( connection );

EXCEPTION
WHEN OTHERS THEN
 dbms_output.put_line(SQLERRM);

END;
/

The procedure may be called using syntax as follows.
Note: Parameters which are passed in with default values on the SEND_MAIL procedure, e.g. p_smtp_host and p_smtp_port may be excluded from this call.

exec send_mail  ( p_smtp_host => 'smtp_host', -
                  p_smtp_port => smtp_port, -
                  p_sender    => 'sender@sender_address', -
                  p_recipient => 'recipient@recipient_address', -
                  p_subject   => 'Subject Line - Cricket Site', -
                  p_message   => 'Message Body - Check out www.nazimcricket.com' -
                );

Grenada Loves Warnie

2008-08-07T15:29:00.013+10:00

Mobile companies aren't the only ones who adore Shane Warne!

First there was this homage (on stamps) for being one of Wisden's Cricketers of the Century.

Now he's being revered on an entire stamp issue. Designed by artist Phillip Howe. These are, in my opinion, simply spectacular.

I must obtain a copy for my collection soon.

Here is a sample.