The OTN toolbar and Metalink classic

For those who believe that new coke was just a great marketing wheeze to get people to appreciate old coke more, there is probably a case to be made for arguing that MOS (My Oracle Support) is a great way to make people appreciate how good old Metalink was (now renamed classic like old coke.)

So for those of you who use the OTN toolbar to launch Metalink and are disappointed that the button has effectively been hijacked, and now takes you to MOS, you will be pleased to hear that with only a couple of minor registry changes, the old functionality can be restored, and it can take you directly to the old style Metalink login screen.

You will require 2 new keys, UserButtons and METALINK, with the UserButtons key being under the standard otn toolbar hive and the METALINK key being under the New UserButtons key thus:

HKEY_CURRENT_USER\Software\oracle_dba_toolbar\UserButtons\METALINK

Then under the MTALINK key, you will need 2 new string values: “caption” and “link”, where Caption will be the text label to the right of the Metalink button in the toolbar, and the link will be the address of the classic Metalink page (https://metalink2.oracle.com/).

Alternatively, you can place the following in a text file with a .reg extension and double click on it to incorporate the contents into your registry.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\oracle_dba_toolbar\UserButtons]

[HKEY_CURRENT_USER\Software\oracle_dba_toolbar\UserButtons\METALINK]
“Caption”=”Classic Metalink”
“Link”=”https://metalink2.oracle.com/”

The RAC roundtable

Please note as a non-expert, I don’t guarantee my ability to accurately summarise everything!

The experts were Julian Dyke, Joel Goodman, + 2 others.

The first question concerned connection pooling, and the unbalanced load experienced wile attempting to load balance.

Several issues were discussed while closing in on a conclusion.

Joel explained the new FAN events covering service goodness that have been introduced in 11gR2. However these new asynchronous notifications are only used if the JDBC ICC client is used or if the middle tier was aware of these notifications.

As the person asking the question appeared to be setting sensible min and max handlers, it was concluded that the issue was that load balancing was occurring at connect time. I.e. Differing numbers of connections are established at connect time to the RAC servers based on current load, whereas the goal should have been uniform connection allocation initially, and then load balancing needs to be established on the basis of allocation from the existing pool on the basis of current load. Essentially the goal wasn’t to increase the number of connections to notes that were being underutilised, but to rather use more frequently the connections that already existed to the nodes that were under utilised.

Key to achieving this runtime load balancing was utilisation of services to handle listener connections.

There was a brief associated discussion as to which connect time algorithm should be used.

The conclusion was that clbgul (?)=long should be set, although doubt was expressed that this actually related to the original question.

A curious question was then asked about RAC in a virtual IBM server environment, Doubt was expressed that RAC was actually supported in that environment, but that doubt seemed to ebb away.

The question was, should the virtual RAC environment be given another node, more CPUs in existing nodes or an additional CPUs in the existing nodes.

The simple answer was
1) Better CPUs
2) More CPUs
3) More nodes.

However then came the caveats.
1) The amount of batch work may influence the decision.
2) While faster CPUs is traditionally considered the solution that involves less risk, there is the possibility that the increased throughput might increase load on the interconnect, which is probably the biggest performance inhibitor. Ergo there is no simple answer!
3) As the environment was virtual, then it was probably worthwhile just trying it and seeing what the result was!
4) It was pointed out that if the reason for going RAC was HA rather than performance, than all the above answers were wrong! As the main issue would be by going to a 3 server RAC cluster, the user benefited from the fact that a node failure would result in the loss of 1/3rd of the available capacity, rather than ½ that would currently be experienced.

There was a brief discussion of interdependence of clusterware, asm and rdbms versions, and from what point rolling upgrades were available.

Clusterware has always supported rolling upgrades.
ASM supports rolling upgrades from 11g.

Finally the version of Clusterware should be >= the version of ASM >= the version of RDBMS.

Next the question of RAC on GPFS vs. ASM on AIX was asked.

Many questions and observations about technology and politics of file system choice were tackled before attendees attested that RAC worked well on both!

Before that answer was established, the fact that ASM didn’t suit SAP was observed, because SAP requires access to a native file system.

ASMfs would e introduced with ASM 11.2 and would expose the ASM as a normal file system.

An advantage of ASM was that it opened, held and cached data file file handles, which was an optimisation that wouldn’t be possible with other file systems.

Utilisation of the white papers produced by the MAA group was recommended.

What are the effects of internal redundancy?
Memory, IO and CPU! In that order!

The issue of block versioning in RAC was covered.

Essentially multiple versions of blocks can exist on different nodes. When writing, this isn’t a consideration. The latest version of the block needs to be either read from disk, or passed from another node in the cluster causing traffic. However for reading, out of date duplicate blocks on the current node can be utilised for logical reads!

Again, as with the security round table, only the surface seemed to be scratched!

A summary of the Oracle Security round table.

The experts present were:

Pete Finnigan (of petefinnigan.com) – Pete I hope requires no introduction, before Pete I was an Oracle security virgin!
Paul Wright (of Markit) – From the previous days Oracle security session seems to be a heavy proponent of hedgehog form sentrigo.
Slavik Markovich (of Sentrigo (originator of Hedgehog)) – on the basis of his session, a strong proponent of proactive pl/sql security hole discovery.

And possibly Kev Else (of no fools limited) – listed on the agenda; however I failed to confirm his identity or presence.

Very roughly, Pete Finnigan expressed the position that open routing is the greatest general security risk. The ability for anyone to plug a laptop into an open Ethernet socket and then be able to connect directly to the database!

Secondly the implementation of security at the application layer, where the functionality of a user is restricted within an application, but when connecting directly either through SQL*Plus or Excel had little or no restriction on the SQL they could execute.

There was then the consideration of the nature and the source of the threats confronting an organisation.

Threats were predominantly not malicious, but rather based on the failings of various carbon based life forms. The propensity of people to place critical data on CDs or USB sticks, and then not be able to verify what happened to that data or who had access to it.

However there was also the suggestion as more companies have a direct exposure to the internet; the proportion of the risk that was internal (in the past estimated to be 80%) was dropping with organised gangs attempting to attack financial institutions.

Next was an observation made at sites that implement a data map – a system where access to sensitive data is recorded.

The behavior observed, was that people soon to leave an organisation often accessed much more data in the period before they left than they would in normal use of the systems.

Pete then proposed a methodology for reducing user’s privileges.

1) Check what privileges a user holds, both directly and through roles.
2) Check what type of objects that a user owns.
3) Identify roles that a user has been granted, but doesn’t require to create the objects that exist.
4) Audit that user on the roles that the user in theory doesn’t need.
5) If after a couple of months revoke the privileges that the user doesn’t use and doesn’t need.

It was then stated that this was only an approach to system privileges, father actions would then need to be taken to curtail object privileges.

Issues relating to the vulnerabilities that were introduced by not following Oracles recommendations for having a separate oinstall installation user and oper and oasys groups.

If performed correctly, a privileged UNIX user (not oracle) will connect to sqlplus /nolog, connect internal and only acquire public privileges, rather than the sys privileges that are acquired when the oracle user performs these instructions.

There was then an encouragement to prioritorise and escalate security implementation on the basis of an investigation of the importance of the data to be protected. Essentially some of your databases may only hold administrative data, and hardening these databases is substantially less important than hardening those databases that may contain personal or financial information.

There was a discussion regarding whistle blowing, and the stated fact that many firms were now obliged to have a risk officer or security officer, and it is to that person in the first case that security issues should probably be raised.

There was a little more, but I suspect that even tripling the time allocated, we would have only scratched the surface!

Linux and the Centro cross rail line

Somewhat off topic:

Having spent another good day at UKOUG 2008 and being a local boy I took the 21:14 to Longbridge to take me home.

Interestingly before departure, the LCD screens pumping news weather and adverts into the carriages went blank; shortly to be followed by a Linux boot screen.

I have seen games machines in pubs and railway information terminals displaying blue screens of death and even yesterday the screens at the ICC were displaying the helpful information that an error had occurred in a Microsoft C++ library, but this is probably the first evidence I have seen of Linux encroaching on this market.

The fact that the service on the Centro trains is almost certainly not directly paid for the commuters using the service may be a factor in the choice, or there is the possibility that the spread of Linux is actually much more widespread that is immediately apparent.

Ultimately we have a service provider who could have used Windows to provide the service but have chosen Linux instead.

I’d love to see the business case behind that, but I suspect that it is just another very small confirmation that in backing Linux, Oracle has made a very shrewd move.

ukoug 2008

It seems to be 8 months since I last blogged. Unfortunately my work seems to be becoming less and less oracle related. However with the onset of the 25th anniversary UKOUG conference, I do have a little to blog!

Much of the excitement this year has related to the launch of the exadata machine, which is perhaps best described as a Data Warehouse accelerator.

In technical terms, some of the processing that was previously performed by the database server has been offloaded to ‘intelligent’ Disk arrays.

Ultimately, a little very clever software has been used to leverage high end commodity hardware to produce a relatively cheap very fast Data warehouse machine. Moving Oracle even father ahead of SQL*Server in the crucial Decision management arena.

However as this technology has little relevance to my current customer, I skipped most of the sessions on exadata apart from the demonstration of Oracle’s exadata simulator.

Unlike some attendees, it is not my intention to blog about all the sessions that I attend, ultimately I attend the sessions to learn myself, and without independent investigation, I lack confidence that I could fully relate their content accurately.

What I do intend to do, is relate some of the information from the round tables that I attend. It is my opinion that the frank exchange of information between experts that these sessions consist of is highly valuable, and without formal slides available for obvious reasons should be documented and disseminated.

BMC and O7_DICTIONARY_ACCESSIBILITY

For those wishing to install BMC Patrol on an Oracle 8.1.7.4 database whit the init.ora parameter O7_DICTIONARY_ACCESSIBILITY is set to FALSE the following grants have to be performed as sys:

grant select on V_$DATAFILE to bmc;
grant select on V_$SESSION to bmc;
grant select on V_$SESSION_WAIT to bmc;
grant select on V_$LATCH to bmc;
grant select on V_$FILESTAT to bmc;
grant select on V_$LATCHNAME to bmc;
grant select on V_$SESS_IO to bmc;
grant select on V_$SQL to bmc;
grant select on V_$SESSTAT to bmc;
grant select on V_$LATCHHOLDER to bmc;
grant select on V_$PARAMETER to bmc;
grant select on V_$STATNAME to bmc;
grant select on DBA_USERS to bmc;
grant select on V_$DATABASE to bmc;
grant select on V_$SYSTEM_EVENT to bmc;
grant select on V_$SYSSTAT to bmc;
grant select on V_$SGA to bmc;
grant select on V_$ROWCACHE to bmc;
grant select on V_$SHARED_SERVER to bmc;
grant select on V_$SGASTAT to bmc;
grant select on V_$LOCK to bmc;
grant select on V_$LOG to bmc;
grant select on V_$ROLLSTAT to bmc;
grant select on DBA_ROLLBACK_SEGS to bmc;
grant select on DBA_DATA_FILES to bmc;
grant select on DBA_FREE_SPACE to bmc;
grant select on V_$LIBRARYCACHE to bmc;
grant select on V_$DISPATCHER to bmc;
grant select on DBA_TABLESPACES to bmc;
grant select on V_$PQ_SYSSTAT to bmc;
grant select on DBA_JOBS to bmc;
grant select on V_$ARCHIVED_LOG to bmc;
grant select on SM$TS_AVAIL to bmc;
grant select on DBA_OBJECTS to bmc;
grant select on V_$INSTANCE to bmc;
grant select on V_$ARCHIVE_DEST to bmc;
grant select on SM$TS_FREE to bmc;
grant select on V_$TEMP_SPACE_HEADER to bmc;

grant execute on DBMS_LOCK to bmc;
grant execute on DBMS_ALERT to bmc;
grant execute on DBMS_SYSTEM to bmc;

If the above are not performed then it will not be possible to compile the BMC$PKKPKG package.

NB. statements above assume BMC as the name of the BMC Oracle user account.

Oracle Home selector

What does the Oracle home selector do?
Is the fact that it doesn’t appear to be included with 11g a problem?
And finally is Oracle locator express (ole.exe http://www.dbmotive.com/oracle_home_selector.php) an adequate replacement?

The first place to investigate the function of the Oracle home selector has to be the the Metalink Note:66464.1.

This note describes the behavior of the oracle home selector thus:

‘A change in selection [of the oracle home] means that the specified Oracle home will appear as the first directory in the PATH environment variable, but no change is written to the Windows NT Registry.’

Secondly we can analyse the behavior of the utility further using regmon and filemon (http://technet.microsoft.com/en-us/sysinternals/default.aspx)

Using regmon we can establish that;

It opens HKLMSOFTWAREORACLEALL_HOMES
It enumerates HKLMSOFTWAREORACLEALL_HOMES
It iteratively reads HKLMSOFTWAREORACLEALL_HOMESID?, HKLMSOFTWAREORACLEALL_HOMESID?
and HKLMSOFTWAREORACLEALL_HOMESID?PATH

It reads HKLMSYSTEMCurrentControlSetControlSession ManagerEnvironmentPATH

It removes the selected oracle home (if present) from the path, and it adds the selected oracle home to the beginning of the path, writing the resulting new path back to the registry.

Using filemon we can see that:
It writes to the system and system.alt files under c:winntsystem32configsystem folder (essentially confirmation that it is modifying the registry)

So contrary to the Metalink note, the way that the Oracle home selector changes the PATH variable is in fact implemented via a modification of the associated registry entry.

What doesn’t the Oracle Home selector do?

1 It doesn’t interact with the ORACLE_HOME environment variable.

2 It doesn’t touch the Java path related elements, so while it allows you to select a specific version of the oracle binaries that are to be used, the java binaries used will be the latest installed, unless manual editing of the PATH decrees otherwise.

3 It wont change the environment from the command line without user interaction. (It prompts the user to accept the home change even when this is done via the command line parameter.)

What does ole.exe do?

Well ole.exe uses different registry keys, enumeration all the keys under HKLMSOFTWAREORACLE and selecting the oracle path information from the HKLMSOFTWAREORACLEHOME?ORACLE_HOME tree, it also examines the TNS_ADMIN registry key and the TNS_ADMIN environment variable, although, as yet I haven’t seen the application modify any settings.

Is ole.exe an adequate replacement for the oracle home selector?

I suppose it is, although because it locates the ORACLE_HOME information from different registry settings, then it is possible that in some circumstances it may behave differently to the Oracle home selector.

Is the oracle home selector of any value at all?

Considering that it doesn’t appear to interact with the ORACLE_HOME environment variable, I can’t see that the utility has that much value anyway. If you have to change the ORACLE_HOME manually, then you are probably better off writing a wrapper for your oracle utilities instead:

So to run a specific version of sqlplus I create a bat file sqlplusw.bat that contains the following lines:

set ORACLE_HOME=c:orantdesigner
PATH c:orantdesignerbin
sqlplusw.exe

Which seems to me to be a much more convenient solution than using the home selector to change the path variable, and then use the System properties dialog box to change the ORACLE_HOME environment variable setting, and then invoke the utility.

So in conclusion, while OLE appears to be an adequate replacement for the Oracle Home selector, unless I have missed some functionality of the two utilities I can’t see any value in either except for the command line phobics of which I suppose there are a few.

PLSQL_Warning in 11g

In preparation for the on/off/on upgrade of our application from Oracle 8.1.7 to 9.2.8, I thought it would be interesting to migrate the PL/SQL to my installation of an 11g Oracle database that came out of the UKOUG conference OTN install fest, and also, out of interest see what warnings come out of the PL/SQL compiler when you set PLSQL_Warning=’enable:all’ compilation option.

Pleasingly out of almost 45,000 lines of source, only one new error was generated:

SQL> show errors;
Errors for PACKAGE BODY P_BODY:

LINE/COL ERROR
——– —————————————————————–
211/5 PLS-00593: default value of parameter “P_PARAM” in body must match that of spec

Here the only problem was that while Oracle 8i is happy for a default parameter value to be specified only in the package body definition, Oracle 11g requires the default parameter to be specified in both the package and the package body.

Eg. while the following code works in 8i, it doesn’t in later releases:

SQL> create or replace PACKAGE P_BODY IS
2 PROCEDURE delete_actual_data (
3 p_parameter IN VARCHAR2
4 );
5 END;
6 /

Package created.

SQL> create or replace PACKAGE body P_BODY IS
2 PROCEDURE delete_actual_data (
3 p_parameter IN VARCHAR2 := ‘N’
4 ) IS
5 BEGIN
6 NULL;
7 END;
8 END;
9
10 /

Package body created.

A small anomaly:

If a package does include errors or warnings, then if the package and body is compiled, then the errors are not displayed, only the following cryptic error message:

SQL> alter PACKAGE P_BODY compile plsql_warnings=’enable:all’;

SP2-0809: Package altered with compilation warnings

SQL> show errors;
SP2-0564: Object “p_bodyplsql_warnings=’enable:all’” is INVALID, it may not be described.

However if only the body is compiled, then the errors and warnings are successfully displayed.

So that leaves me with a selection of PLW warnings to evaluate and/or fix:

PLW-05005: function P_FUNCTION returns without value at line 244
PLW-06002: Unreachable code
PLW-06006: uncalled procedure “P_PROCEDURE” is removed.
PLW-06009: procedure “P_PROCEDURE” OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
PLW-06010: keyword “NAME” used as a defined name
PLW-06010: keyword “REF” used as a defined name
PLW-06010: keyword “RESULT” used as a defined name
PLW-07202: bind type would result in conversion away from column type
PLW-07203: parameter ‘P_PARAMETER’ may benefit from use of the NOCOPY compiler hint
PLW-07204: conversion away from column type may result in sub-optimal query plan

A superficial review of the error messages manual indicates that the number of potential warnings has grown substantially from the 9 and 11 warnings of 10g releases 1 and 2, to a total of 36 documented warnings in 11g release 1. With 3 of the 8 warning types received being new ones from 11g.

Oracle 10 R1 manual page:

http://download.oracle.com/docs/cd/B14117_01/server.101/b10744/plwus.htm#sthref64

Oracle 10 R2 manual page:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/plwus.htm#ERRMG53664

Oracle 11 R1 manual page:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28278/plwus.htm

A little security foible?

Having attended the most recent UKOUG Oracle on windows SIG and heard the excellent talk on security by Pete Finnegan (www.petefinnigan.com), obviously the first thing I did was check the default user name and passwords supplied with Oracle on some of our systems.

And as anticipated OUTLN/OUTLN and DBSNMP/DBSNMP both let me log onto the database.

So I raised the issue with our DBA who was sure that the accounts had been locked.

Indeed, after further investigation, the development databases, which had been cloned from production did have all the appropriate accounts locked!

The only thing that had happened recently in production is that we did a database re organisation that included the full cycle of:

Full export.
Database re-creation.
Full import with data.
Full import without data.

So our current theory is that somewhere in this process, the fact that these default accounts were locked has been lost.

One to check when I have a little more time.

Meanwhile, I’d advise both locking and changing the passwords on these accounts, which is probably what we should have been doing anyway!

Running Forms under Citrix 1

I’m sure everyone has their favorite scripts, either home grown or purloined to assist with database monitoring but there are always more things to check and new things to monitor.

The following scripts were developed for the analysis and monitoring of disconnection problems experienced after the system was converted into a three tear application by running the forms client under Citrix.

While developing the script I read the metalink article Note:274697.1 LOGOFF and LOGOFF BY CLEANUP Do Not Have Any LOGON Records in DBA_AUDIT_TRAIL and Vice-Versa.

The article defines its purpose to be:

This bulletin explains what are the differences between the LOGOFF and LOGOFF BY CLEANUP shown in SYS.AUD$ table and why they do not have any associated LOGON records in DBA_AUDIT_TRAIL and vice-versa.

While it includes examples of how normal logons and logoffs are recorded in theaudit table, it doesn’t explicitly indicate how disconnected processes are logged.

Thus given Oracles ‘vice versa’ comment above, I assumed that they were different and the timestamp on the ‘LOGOFF BY CLEANUP’ records would indicate the time that the process was terminated or the time the pmon process detected the dead process.

Having analysed the data collected, I came to the conclusion that my assumptions were wrong and it appears that rather than being different, the way Oracle handles disconnections is exactly the same way that it handles normal logoffs, with the logon record being cannibalised in the process.

The scripts:

The following is run whenever we suspect that there is a problem, which breaks down the disconnected sessions by client machine (Citrix server) and hour of the day:

SELECT terminal,
  TRUNC(logoff_time),
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 0, 1, 0)) zero,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 1, 1, 0)) one,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 2, 1, 0)) two,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 3, 1, 0)) three,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 4, 1, 0)) four,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 5, 1, 0)) five,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 6, 1, 0)) six,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 7, 1, 0)) seven,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 8, 1, 0)) eight,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 9, 1, 0)) nine,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 10, 1, 0)) ten,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 11, 1, 0)) eleven,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 12, 1, 0)) twelve,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 13, 1, 0)) thirteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 14, 1, 0)) fourteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 15, 1, 0)) fifteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 16, 1, 0)) sixteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 17, 1, 0)) seventeen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 18, 1, 0)) eighteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 19, 1, 0)) nineteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 20, 1, 0)) twenty,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 21, 1, 0)) twentyone,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 22, 1, 0)) twentytwo,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 23, 1, 0)) twentythree
  FROM dba_audit_trail
 WHERE action_name = ‘LOGOFF BY CLEANUP’
  AND logoff_time > TRUNC(SysDate)
 GROUP BY terminal, TRUNC(logoff_time)
/

The following is run every morning and added to a cumulative log which gives a breakdown of disconnected sessions by Hour by Day which is used to identify trends:

SELECT TRUNC(logoff_time),
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 0, 1, 0)) t0,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 1, 1, 0)) t1,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 2, 1, 0)) t2,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 3, 1, 0)) t3,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 4, 1, 0)) t4,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 5, 1, 0)) t5,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 6, 1, 0)) t6,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 7, 1, 0)) t7,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 8, 1, 0)) t8,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 9, 1, 0)) t9,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 10, 1, 0)) t10,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 11, 1, 0)) t11,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 12, 1, 0)) t12,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 13, 1, 0)) t13,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 14, 1, 0)) t14,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 15, 1, 0)) t15,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 16, 1, 0)) t16,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 17, 1, 0)) t17,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 18, 1, 0)) t18,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 19, 1, 0)) t19,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 20, 1, 0)) t20,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 21, 1, 0)) t21,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 22, 1, 0)) t22,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24′ )), 23, 1, 0)) t23
  FROM dba_audit_trail
 WHERE TRUNC(logoff_time) = TRUNC(sysdate – 1 )
  AND action_name = ‘LOGOFF BY CLEANUP’
 GROUP BY TRUNC(logoff_time)
/