Month: April 2007

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.

Oracle Beefs

I’ve got very few beefs with Oracle. It is extremely complicated and tough to learn compared to other relational databases, but that’s partially offset by the tremendous documentation, and the huge Oracle community.Don’t get me wrong, I love Oracle. …

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569