ORA-14107: partition specification is required for a partitioned object – ORA-03205: partition name is required when partitioned type is specified – ORA-03200: the segment type specification is invalid
DBMS_SPACE.SPACE_USAGE package is used to detect fragmentation at table, partition level etc..
It is straightforward for table fragmentation to be detected using this package but for partition level, there should be confusion on syntax level without specifying the partitions.
ORA-14107 is triggered, if the table is partitioned and the partition name should be specified and if not, then this error is expected.
SQL> set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'TABLE', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
Enter value for schema_name: C##HYDRA
Enter value for table_name: RP
old 15: dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'TABLE', v_unformatted_blocks,
new 15: dbms_space.space_usage ('C##HYDRA', 'RP', 'TABLE', v_unformatted_blocks,
declare
*
ERROR at line 1:
ORA-14107: partition specification is required for a partitioned object
ORA-06512: at "SYS.DBMS_SPACE", line 214
ORA-06512: at line 15
If the segment type is not correctly specified, then ORA-03200 is thrown.
SQL> set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'PARTITION', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
Enter value for schema_name: C##HYDRA
Enter value for table_name: RP
old 15: dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'PARTITION', v_unformatted_blocks,
new 15: dbms_space.space_usage ('C##HYDRA', 'RP', 'PARTITION', v_unformatted_blocks,
declare
*
ERROR at line 1:
ORA-03200: the segment type specification is invalid
ORA-06512: at "SYS.DBMS_SPACE", line 214
ORA-06512: at line 15
Partition name should be specified at the last argument and if not ORA-03205 is thrown
SQL>
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'TABLE PARTITION', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
SQL> v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/ 24 25
Enter value for schema_name: C##HYDRA
Enter value for table_name: RP
old 15: dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'TABLE PARTITION', v_unformatted_blocks,
new 15: dbms_space.space_usage ('C##HYDRA', 'RP', 'TABLE PARTITION', v_unformatted_blocks,
declare
*
ERROR at line 1:
ORA-03205: partition name is required when partitioned type is specified
ORA-06512: at "SYS.DBMS_SPACE", line 214
ORA-06512: at line 15
Check the available segment types in dba_segments.
SQL> select distinct segment_type from dba_segments;
SEGMENT_TYPE
------------------
LOBINDEX
INDEX PARTITION
TABLE SUBPARTITION
ROLLBACK
TABLE PARTITION
NESTED TABLE
LOB PARTITION
LOBSEGMENT
INDEX
TABLE
TYPE2 UNDO
CLUSTER
12 rows selected.
Describe the procedure dbms_space for more information and clarity on the options available
SQL> desc dbms_space
FUNCTION ASA_RECOMMENDATIONS RETURNS ASA_RECO_ROW_TB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ALL_RUNS VARCHAR2 IN DEFAULT
SHOW_MANUAL VARCHAR2 IN DEFAULT
SHOW_FINDINGS VARCHAR2 IN DEFAULT
PROCEDURE AUTO_SPACE_ADVISOR_JOB_PROC
PROCEDURE CREATE_INDEX_COST
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DDL VARCHAR2 IN
USED_BYTES NUMBER OUT
ALLOC_BYTES NUMBER OUT
PLAN_TABLE VARCHAR2 IN DEFAULT
PROCEDURE CREATE_TABLE_COST
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLESPACE_NAME VARCHAR2 IN
AVG_ROW_SIZE NUMBER IN
ROW_COUNT NUMBER IN
PCT_FREE NUMBER IN
USED_BYTES NUMBER OUT
ALLOC_BYTES NUMBER OUT
PROCEDURE CREATE_TABLE_COST
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLESPACE_NAME VARCHAR2 IN
COLINFOS CREATE_TABLE_COST_COLUMNS IN
ROW_COUNT NUMBER IN
PCT_FREE NUMBER IN
USED_BYTES NUMBER OUT
ALLOC_BYTES NUMBER OUT
FUNCTION DBFS_DF RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
USERID NUMBER IN
NTBS NUMBER IN
INTS_LIST TABLESPACE_LIST IN
PROCEDURE FREE_BLOCKS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SEGMENT_OWNER VARCHAR2 IN
SEGMENT_NAME VARCHAR2 IN
SEGMENT_TYPE VARCHAR2 IN
FREELIST_GROUP_ID NUMBER IN
FREE_BLKS NUMBER OUT
SCAN_LIMIT NUMBER IN DEFAULT
PARTITION_NAME VARCHAR2 IN DEFAULT
PROCEDURE ISDATAFILEDROPPABLE_NAME
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FILENAME VARCHAR2 IN
VALUE NUMBER OUT
FUNCTION OBJECT_DEPENDENT_SEGMENTS RETURNS DEPENDENT_SEGMENTS_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJOWNER VARCHAR2 IN
OBJNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN
OBJTYPE NUMBER IN
FUNCTION OBJECT_GROWTH_TREND RETURNS OBJECT_GROWTH_TREND_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_OWNER VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
OBJECT_TYPE VARCHAR2 IN
PARTITION_NAME VARCHAR2 IN DEFAULT
START_TIME TIMESTAMP IN DEFAULT
END_TIME TIMESTAMP IN DEFAULT
INTERVAL INTERVAL DAY TO SECOND IN DEFAULT
SKIP_INTERPOLATED VARCHAR2 IN DEFAULT
TIMEOUT_SECONDS NUMBER IN DEFAULT
SINGLE_DATAPOINT_FLAG VARCHAR2 IN DEFAULT
FUNCTION OBJECT_GROWTH_TREND_CUR RETURNS REF CURSOR
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_OWNER VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
OBJECT_TYPE VARCHAR2 IN
PARTITION_NAME VARCHAR2 IN DEFAULT
START_TIME TIMESTAMP IN DEFAULT
END_TIME TIMESTAMP IN DEFAULT
INTERVAL INTERVAL DAY TO SECOND IN DEFAULT
SKIP_INTERPOLATED VARCHAR2 IN DEFAULT
TIMEOUT_SECONDS NUMBER IN DEFAULT
FUNCTION OBJECT_GROWTH_TREND_CURTAB RETURNS OBJECT_GROWTH_TREND_TABLE
FUNCTION OBJECT_GROWTH_TREND_I_TO_S RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
INTERV INTERVAL DAY TO SECOND IN
FUNCTION OBJECT_GROWTH_TREND_SWRF RETURNS OBJECT_GROWTH_SWRF_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_OWNER VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
OBJECT_TYPE VARCHAR2 IN
PARTITION_NAME VARCHAR2 IN DEFAULT
FUNCTION OBJECT_GROWTH_TREND_S_TO_I RETURNS INTERVAL DAY TO SECOND
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SECSIN NUMBER IN
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
CHAIN_PCENT NUMBER OUT
PARTITION_NAME VARCHAR2 IN DEFAULT
PRESERVE_RESULT BOOLEAN IN DEFAULT
TIMEOUT_SECONDS NUMBER IN DEFAULT
FUNCTION OBJECT_SPACE_USAGE_TBF RETURNS OBJECT_SPACE_USAGE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_OWNER VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
OBJECT_TYPE VARCHAR2 IN
SAMPLE_CONTROL NUMBER IN
PARTITION_NAME VARCHAR2 IN DEFAULT
PRESERVE_RESULT VARCHAR2 IN DEFAULT
TIMEOUT_SECONDS NUMBER IN DEFAULT
PROCEDURE PARSE_SPACE_ADV_INFO
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
INFO VARCHAR2 IN
USED_SPACE VARCHAR2 OUT
ALLOCATED_SPACE VARCHAR2 OUT
RECLAIMABLE_SPACE VARCHAR2 OUT
PROCEDURE SPACE_USAGE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SEGMENT_OWNER VARCHAR2 IN
SEGMENT_NAME VARCHAR2 IN
SEGMENT_TYPE VARCHAR2 IN
UNFORMATTED_BLOCKS NUMBER OUT
UNFORMATTED_BYTES NUMBER OUT
FS1_BLOCKS NUMBER OUT
FS1_BYTES NUMBER OUT
FS2_BLOCKS NUMBER OUT
FS2_BYTES NUMBER OUT
FS3_BLOCKS NUMBER OUT
FS3_BYTES NUMBER OUT
FS4_BLOCKS NUMBER OUT
FS4_BYTES NUMBER OUT
FULL_BLOCKS NUMBER OUT
FULL_BYTES NUMBER OUT
PARTITION_NAME VARCHAR2 IN DEFAULT
PROCEDURE SPACE_USAGE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SEGMENT_OWNER VARCHAR2 IN
SEGMENT_NAME VARCHAR2 IN
SEGMENT_TYPE VARCHAR2 IN
SEGMENT_SIZE_BLOCKS NUMBER OUT
SEGMENT_SIZE_BYTES NUMBER OUT
USED_BLOCKS NUMBER OUT
USED_BYTES NUMBER OUT
EXPIRED_BLOCKS NUMBER OUT
EXPIRED_BYTES NUMBER OUT
UNEXPIRED_BLOCKS NUMBER OUT
UNEXPIRED_BYTES NUMBER OUT
PARTITION_NAME VARCHAR2 IN DEFAULT
PROCEDURE SPACE_USAGE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SEGMENT_OWNER VARCHAR2 IN
SEGMENT_NAME VARCHAR2 IN
SEGMENT_TYPE VARCHAR2 IN
SUOPTION NUMBER IN
SEGMENT_SIZE_BLOCKS NUMBER OUT
SEGMENT_SIZE_BYTES NUMBER OUT
USED_BLOCKS NUMBER OUT
USED_BYTES NUMBER OUT
EXPIRED_BLOCKS NUMBER OUT
EXPIRED_BYTES NUMBER OUT
UNEXPIRED_BLOCKS NUMBER OUT
UNEXPIRED_BYTES NUMBER OUT
PARTITION_NAME VARCHAR2 IN DEFAULT
PROCEDURE UNUSED_SPACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SEGMENT_OWNER VARCHAR2 IN
SEGMENT_NAME VARCHAR2 IN
SEGMENT_TYPE VARCHAR2 IN
TOTAL_BLOCKS NUMBER OUT
TOTAL_BYTES NUMBER OUT
UNUSED_BLOCKS NUMBER OUT
UNUSED_BYTES NUMBER OUT
LAST_USED_EXTENT_FILE_ID NUMBER OUT
LAST_USED_EXTENT_BLOCK_ID NUMBER OUT
LAST_USED_BLOCK NUMBER OUT
PARTITION_NAME VARCHAR2 IN DEFAULT
FUNCTION VERIFY_SHRINK_CANDIDATE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SEGMENT_OWNER VARCHAR2 IN
SEGMENT_NAME VARCHAR2 IN
SEGMENT_TYPE VARCHAR2 IN
SHRINK_TARGET_BYTES NUMBER IN
PARTITION_NAME VARCHAR2 IN DEFAULT
FUNCTION VERIFY_SHRINK_CANDIDATE_TBF RETURNS VERIFY_SHRINK_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SEGMENT_OWNER VARCHAR2 IN
SEGMENT_NAME VARCHAR2 IN
SEGMENT_TYPE VARCHAR2 IN
SHRINK_TARGET_BYTES NUMBER IN
PARTITION_NAME VARCHAR2 IN DEFAULT
After specifying the correct argument, procedure is successfully executed.
SQL> set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'TABLE PARTITION', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes,'R_MAY2022');
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
Enter value for schema_name: C##HYDRA
Enter value for table_name: RP
old 15: dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'TABLE PARTITION', v_unformatted_blocks,
new 15: dbms_space.space_usage ('C##HYDRA', 'RP', 'TABLE PARTITION', v_unformatted_blocks,
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 0
Full Blocks = 0
PL/SQL procedure successfully completed.