More undocumented APIs in DBMS_SPACE

There seem to be quite a few procedures in the package DBMS_SPACE which do not find a mention in the Oracle documentation. One such procedure is

PROCEDURE OBJECT_SPACE_USAGE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_OWNER VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
OBJECT_TYPE VARCHAR2 IN
SAMPLE_CONTROL NUMBER IN
SPACE_USED NUMBER OUT
SPACE_ALLOCATED NUMBER OUT
PARTITION_NAME VARCHAR2 IN DEFAULT
PRESERVE_RESULT BOOLEAN IN DEFAULT
TIMEOUT_SECONDS NUMBER IN DEFAULT

which is used by OEM if you use the Segment Findings section on the Database Home page if you configure the same.

It could be useful to monitor space consumption of segments whilst subjected to continuous DML operations.

set serveroutput on;
declare
v_space_used number;
v_space_allocated number;
begin
dbms_space.object_space_usage(‘SCOTT’,'EMP','TABLE PARTITION',0,v_space_used, v_space_allocated, 'P1');
dbms_output.put_line('SPACE USED = '||v_space_used);
dbms_output.put_line('SPACE ALLOCATED = '||v_space_allocated);
end;
/


SPACE USED = 454248
SPACE ALLOCATED = 458752

PL/SQL procedure successfully completed.

I haven’t figured out what sample_control is though. Also this procedure does not work for spatial indexes as you encounter an ora-600.

KST tracing

In a recent post Kevin Closson highlights the perils of the effect of the the ORA_CRS_HOME mounpoint filling up.
If you use the RAC service framework services would relocate to another node after going offline on the node on which the filesystem is full

In a RAC cluster if a process encounters an ora-600 or an ora-7445 (I haven't checked for other errors) each node in the cluster dumps trace files for each process with suffix trw in directories called cdmp_YYYYMMDDHHMISS under bdump. This instrumentation is referred to as KST tracing. So if you have a batch job which continuously encounters the before mentioned errors this can cause a plethora of cdmp* directories generated which could fill up the filesystem.
These trace files can be formatted by an internal Oracle tool called Trace Loader (trcldr).

You can disable this functionality by setting trace_enabled=false which
can be changed dynamically. After making this change you will see the following
message in the diag trace file

***********************************************
KST tracing is turned off, no data is logged
***********************************************

This functionality is also disabled if you set _diag_daemon=false. This would disable the DIAG daemon and hence disable other functionality like the dumping of systemstates by the DIAG process when a self deadlock occurs. DIAG is the only background process which can be killed without instance death.It is the 2nd last process to be terminated because it needs to perform trace flushing to file system. By default, the terminating process, usually PMON, gives 5 seconds to DIAG for dumping. This is governed by the parameter _ksu_diag_kill_time. So after dumping all
diagnostic data PMON will wait for _ksu_diag_kill_time before killing the instance.
This delay has been fixed via bug 5599293