Alter session bug
This applies to the following setup:
- DBAComp version: any version compiled against Oracle10g
- Oracle client version: 10.2
- Target database version: 9.2
- Platforms: all I've tested
The Oracle bug number is 4904743 (metalink note 352513.1).
This bug causes the command 'alter session set nls_date_format ...' to
have no effect in Pro*C if dynamic SQL is used.
This is exactly what happens in DBAComp. After connecting, the date
format is set to a format that includes the date and the time portions.
Due to this bug this command is ignored and the date format not set
There are 2 effects
- All date columns are displayed without the time portion
- AWR and Statspack reporting does not work correctly
The workaround is to use an anonymous PL/SQL block using the
'execute immediate' command to run the 'alter session'. This way the
command is no longer ignored.
Since all SQL commands executed in DBAComp come from the version
respective dbacomp.x.y.sql file, the workaround must be applied to
dbacomp.9.2.sql. To do this you have 2 possibilities:
1. Either look for the 'alter_session' SQL and replace it by the
alter_session: declare \
fmt varchar2(30) := 'dd-mm-yy
formats must be identical so that to_date(<timestamp>) \
does not generate an ora-1830 */ \
immediate 'alter session set NLS_DATE_FORMAT='''||fmt||''''; \
immediate 'alter session set NLS_TIMESTAMP_FORMAT='''||fmt||''''; \
2. Or download the modified
which incorporates the above change. Replace
the original file with this modified one.
To test, restart DBAComp and look for a date column.
If is is formatted as it should then your change was a success.