This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Negative Values in V$TEMP_SPACE_HEADER

Whilst trying to figure out a reliable method of monitoring temporary tablespace usage I came across this problem.

The v$temp_space_header bytes_used column was displaying negative values.

Metalink has document 467435.1 which states quite simply that in and later, the temp file header(s) may have been corrupted.

The solution, drop and re-create the temp file.

SAP FBL5N and Change Document Authorisation

After spending tedious amounts of time in SU24 and performing an authorisation trace I was unable to work out how users got “Change Document” access in FBL5N (Customer Line Item Display).

Access to change documents via transaction FBL5N, which is inherently a display only transaction, is controlled by giving transaction FB02 to the user.
You have to look in the source of program RFITEMAR:

Adding transaction FB02 (plus maintaining the subsequent authorisation activities) to a users role, as well as FBL5N, provides the “Change Document” button on the menu bar in FBL5N:

ORA-01114 IO Error But What Is The Oracle File

Whilst creating a new 10GB index on a large 40GB table I was experiencing the
“ORA-01114: IO error writing block to file <file#> (block # <block#>)” error.

The main vital piece of information is the file number (<file#>). This can be found in the DBA_DATA_FILES or DBA_TEMP_FILES views as the FILE_ID column.

So, you construct a query to go find the file name:

SELECT file_id, file_name
FROM dba_data_files
WHERE file_id = <the number>
SELECT file_id, file_name
FROM dba_temp_files
WHERE file_id = <the number>;

NOTE: I query both just in case. I’ve worked on systems where the TEMP tablespace has not been created as “TEMPORARY”!

After running the query, you get “0 rows returned”.

Well, in this case, the TEMP tablespace was a proper TEMPORARY tablespace as the file_id was returned from the DBA_TEMP_FILES view.

I knew that the file numbers were somehow linked to the number of permitted files in the database.  This is controlled by the DB_FILES database parameter.
Looking at the Oracle documentation (for 10g) here:

You will see that Oracle states that:
…the DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance.

In short this means that the DB_FILES parameter only applies to datafiles and not tempfiles.
Therefore, all other files will be outside this parameter range.

My parameter DB_FILES was set to 1024.
My reported file# in the ORA-01114 error was greater than 1024.
If you look at the file_id values for the DBA_TEMP_FILES view, you’ll notice that the file_id numbers conflict with the file_id values in DBA_DATA_FILES.
Since the tempfiles exist outside the range of DB_FILES, I subtracted the value of parameter “DB_FILES” from the reported file#, to achieve the file_id for tempfiles belonging to temporary tablespaces.

Sure enough, when I subtracted 1024 (my DB_FILES value) from my reported file id in the ORA-01114, I got 2, which equated to the second tempfile in the TEMP tablespace.
The partition that the tempfile was sitting in was 95% full, but the tempfile couldn’t expand by the requested next extent size as it would be more than the partition free space.  Increasing the partition size fixed my problem.

Is there another way of viewing the online file# vs the DBA_x_FILES file_id field?  I don’t know, but if I find one, I’ll post it here.


A background job has produced a short dump with SYSTEM_NO_TASK_STORAGE.
The “Heap Memory” section in ST02 showed a maximum use of 1.5GB, but we had allocated nearly 1.9GB according to the “abap/heap_area_nondia” and “abap/heap_area_dia” instance profile parameters.

The ST02 short dump analysis shows the source code line where the problem occurred. The line of code doesn’t look specifically interesting, it is, however, requiring a slight increase in memory allocation.
If the program is running in DIALOG then it will be allocated Extended Memory first, followed by HEAP (local process) memory.
If the program is running in BACKGROUND then it will be allocated HEAP memory first, followed by Extended Memory.

Extended Memory is pre-allocated at system startup according to the EM initial setting in the SAP instance profile.  It is then increased up to the maximum specified in the instance profile per user and per application server.
HEAP memory is only allocated as it is needed within each of the SAP dw.* OS processes, up to the maximum specified (in instance profile) per user or per application server.  It is then released back to the OS when the process finishes processing (SAP restarts the work process) and it has used over a specific amount of memory as set in parameter abap/heaplimit.

Since this was a background job, we can assume that we exhausted HEAP memory and should have automatically switched to Extended Memory.
However, the Kernel section of the short dump showed that we may have experienced an issue obtaining more virtual memory from the OS:

*** Error in libunwind: Out of memory. Try with a higher value >
 > for UNWIND_RESERVE_MEM (current value = 16).
 (0) 0x40000000017ae480 [dw.sapXXX_DVEBMGS01]
 (1) 0x40000000017ae2b0 [dw.sapXXX_DVEBMGS01]
 (2) 0x40000000021de880 [dw.sapXXX_DVEBMGS01]
 (3) 0x40000000021e45d0 [dw.sapXXX_DVEBMGS01]
 (4) 0x400000000115e860 [dw.sapXXX_DVEBMGS01]
 (5) 0x400000000120b3a0 [dw.sapXXX_DVEBMGS01]
 (6) 0x40000000010542f0 [dw.sapXXX_DVEBMGS01]
 (7) 0x4000000001111940 [dw.sapXXX_DVEBMGS01]
 (8) 0x40000000011ae790 [dw.sapXXX_DVEBMGS01]
 (9) 0x40000000012fb090 [dw.sapXXX_DVEBMGS01]
 (10) 0x40000000012fd7d0 [dw.sapXXX_DVEBMGS01]
 (11) 0x400000000188cae0 [dw.sapXXX_DVEBMGS01]
 (12) 0x4000000001896e70 [dw.sapXXX_DVEBMGS01]
 (13) 0x4000000001891670 [dw.sapXXX_DVEBMGS01]
 (14) 0x40000000018949a0 [dw.sapXXX_DVEBMGS01]
 (15) 0x400000000187f1e0 [dw.sapXXX_DVEBMGS01]
 (16) 0x40000000014d1ce0 [dw.sapXXX_DVEBMGS01]
 (17) 0x400000000149e8d0 [dw.sapXXX_DVEBMGS01]
 (18) 0x4000000001496fc0 [dw.sapXXX_DVEBMGS01]
 (19) 0x4000000001364c30 [dw.sapXXX_DVEBMGS01]
 (20) 0x4000000000ed4af0 [dw.sapXXX_DVEBMGS01]
 (21) 0x4000000000ed4a90 [dw.sapXXX_DVEBMGS01]
 (22) 0xc000000000045880 main_opd_entry + 0x50 [/usr/lib/hpux64/]

Is this case, you could either add more OS memory (essentially you have over allocated memory somewhere), or you could resize the abap/heap_area_nondia parameter to a smaller amount, so that it will switch to EM sooner.

Oracle Tracing & Interpreting Traces – Notes

Oracle Trace Details; abstracted from MYOS Note ID 376442.1 and added flair.

This section details various ways of actually getting an Oracle session level trace file, interpreting the trace file and useful links to MYOS notes about the trace data:

/* Oracle 10g+ trace session using DBMS_MONITOR includes BINDS and WAITS */
/* However, this is limited to one session only */
                                       serial_num =>7218,
                                       waits      => TRUE,
                                       binds      => TRUE);


                                        serial_num => 7218);

/* Oracle 9i Trace excludes BINDS and WAITS */
/* Again, limited to one session only */

/ * Trace your own session with BINDS and WAITS */
ALTER SESSION SET EVENTS ''10046 trace name context forever, level 12'';

/* Trace using OraDebug with BINDS and WAITS */
connect / as sysdba
oradebug setorapid 9834  << Insert your Oracle PID here.
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

/* And switch it off again */
oradebug event 10046 trace name context off

/* Logon trigger trace for Oracle 9i+ */
/* This is the best way to trace when using Oracle Forms because it allows for multiple spontaneous sessions.
I can also confirm that this traces sessions executed on a database link */
/* First you must grant ALTER SESSION to the user */

/ * Now create the trigger */
lcommand varchar(200);
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;

/* Simply disable the trigger when done */

/* List non-system users, very useful when attempting to trace a session */
SELECT username,sid,serial#,status,module
   FROM v$session
 WHERE username is not null
      AND username <> 'SYS'
 ORDER BY username;

/* Enable the trace on ALL current sessions of a specific user in the system. */
set serveroutput on size 10000;
FOR c1_row IN (SELECT sid,serial#
FROM v$session where username = '<<A USER>>') LOOP
DBMS_OUTPUT.PUT_LINE('Enabling trace for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => c1_row.sid, serial_num => c1_row.serial# ,waits => TRUE, binds => TRUE);
DBMS_OUTPUT.PUT_LINE('Tracing for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
select username,sql_trace from v$session order by username;

/* Disable the trace on ALL current sessions of a specific user in the system. */
set serveroutput on size 10000;
FOR c1_row IN (SELECT sid,serial#
FROM v$session where username = '<<A USER>>') LOOP
DBMS_OUTPUT.PUT_LINE('Disabling trace for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => c1_row.sid, serial_num => c1_row.serial#);
DBMS_OUTPUT.PUT_LINE('Tracing disabled for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
select username,sql_trace from v$session order by username;

/* Run TKProf as sysdba with EXPLAIN plan included */
/* See TKProf Interpretation (9i and above) [ID 760786.1] */
tkprof <tracefile>.trc /tmp/<tracefile> explain=" / as sysdba"

/* TKProf sorting by highest elapsed time */
tkprof <tracefile>.trc /tmp/<tracefile> sort=fchela,exeela,prsela

Once you have your trace file and you have run it through TKProf and are still struggling, you may need to read it raw.
Check out the following MYOS articles:
“Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output [ID 39817.1]”.

The excellent but slightly unheard of “Oracle Performance Diagnostic Guide (OPDG) [ID 390374.1]”.