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? ——-…
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
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;
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);
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.
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. …