Hi,

I am currently working on the data sizing for each table to be loaded in a datawarehouse.

Ref: https://docs.oracle.com/cd/E11882_01/doc.112/e26211/app_size.htm#DMAOG301

I have seen a procedure from https://docs.oracle.com/database/121/ARPLS/d_space.htm#ARPLS68115

Kindly help me on the script below for space usage. Is there some other important procedures from the link above for data volume calculation?

SQL> DECLARE
  2  v_partition_name VARCHAR2(30):=UPPER('&4');
  3  v_unformatted_blocks NUMBER,
  4  v_unformatted_bytes NUMBER,
  5  v_fs1_blocks       NUMBER,
  6  v_fs1_bytes                 NUMBER,
  7  v_fs2_blocks                NUMBER,
  8  v_fs2_bytes                 NUMBER,
  9  v_fs3_blocks                NUMBER,
 10  v_fs3_bytes                 NUMBER,
 11  v_fs4_blocks                NUMBER,
 12  v_fs4_bytes                 NUMBER,
 13  v_full_blocks               NUMBER,
 14  v_full_bytes                NUMBER
 15
 16  BEGIN
 17  DBMS_SPACE.SPACE_USAGE (
 18     segment_owner          => UPPER('&1') ,
 19     segment_name            =>UPPER('&2'),
 20     segment_type             =>UPPER('&3'),
 21     unformatted_blocks   =>v_unformatted_blocks,
 22     unformatted_bytes     => v_unformatted_bytes,     OUT NUMBER,
 23     fs1_blocks                 =>  v_fs1_blocks           ,
 24     fs1_bytes              =>  v_fs1_bytes,
 25     fs2_blocks             => v_fs1_blocks ,
 26     fs2_bytes              => v_fs2_bytes ,
 27     fs3_blocks           =>   v_fs1_blocks ,
 28     fs3_bytes             =>   v_fs3_bytes,
 29     fs4_blocks           =>   v_fs1_blocks ,
 30     fs4_bytes             =>   v_fs4_bytes,
 31     full_blocks          =>    v_fs1_blocks,
 32     full_bytes           =>    v_full_bytes,
 33     partition_name  => v_partition_name);
 34
 35
 36  DBMS_OUTPUT.PUT_LINE('v_total_blocks              :' || v_total_blocks);
 37
 38  END;
 39  /
Enter value for 4: MOBILE_DATA_2018_10
Enter value for 1: TESTBI
Enter value for 2: MOBILE_DATA
Enter value for 3: TABLE PARTITION
v_unformatted_blocks NUMBER,
                           *
ERROR at line 3:
ORA-06550: line 3, column 28:
PLS-00103: Encountered the symbol "," when expecting one of the following:
:= . ( @ % ; not null range default character
ORA-06550: line 16, column 1:
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
:= . ( , @ % ; not null range default external character
ORA-06550: line 22, column 58:
PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
The symbol "." was substituted for "NUMBER" to continue.
ORA-06550: line 38, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
end not pragma final instantiable order overriding static
member constructor map

Thanks,

Roshan

SQL> DECLARE
  2     v_partition_name VARCHAR2(30);
  3     v_unformatted_blocks NUMBER;
  4     v_unformatted_bytes NUMBER;
  5     v_fs1_blocks       NUMBER;
  6     v_fs1_bytes                 NUMBER;
  7     v_fs2_blocks                NUMBER;
  8     v_fs2_bytes                 NUMBER;
  9     v_fs3_blocks                NUMBER;
 10     v_fs3_bytes                 NUMBER;
 11     v_fs4_blocks                NUMBER;
 12     v_fs4_bytes                 NUMBER;
 13     v_full_blocks               NUMBER;
 14     v_full_bytes                NUMBER;
 15
 16     BEGIN
 17     DBMS_SPACE.SPACE_USAGE (
 18        segment_owner          => UPPER('MCDONAC') ,
 19        segment_name            =>UPPER('T'),
 20        segment_type             =>UPPER('TABLE'),
 21        unformatted_blocks   =>v_unformatted_blocks,
 22        unformatted_bytes    => v_unformatted_bytes,
 23        fs1_blocks           =>  v_fs1_blocks           ,
 24        fs1_bytes            =>  v_fs1_bytes,
 25        fs2_blocks           => v_fs1_blocks ,
 26        fs2_bytes            => v_fs2_bytes ,
 27        fs3_blocks           =>   v_fs1_blocks ,
 28        fs3_bytes            =>   v_fs3_bytes,
 29        fs4_blocks           =>   v_fs1_blocks ,
 30        fs4_bytes            =>   v_fs4_bytes,
 31        full_blocks          =>    v_full_blocks,
 32        full_bytes           =>    v_full_bytes,
 33        partition_name       => v_partition_name);
 34
 35
 36     DBMS_OUTPUT.PUT_LINE('v_unformatted_blocks   :' || v_unformatted_blocks);
 37     DBMS_OUTPUT.PUT_LINE('v_unformatted_bytes    :' || v_unformatted_bytes);
 38     DBMS_OUTPUT.PUT_LINE('v_full_blocks          :' || v_full_blocks);
 39     DBMS_OUTPUT.PUT_LINE('v_full_bytes           :' || v_full_bytes);
 40
 41     END;
 42     /
v_unformatted_blocks   :0
v_unformatted_bytes    :0
v_full_blocks          :1602
v_full_bytes           :13123584

PL/SQL procedure successfully completed.