ORACLE - New Segment Resource Estimation

SET SERVEROUTPUT ON

DECLARE l_ddl VARCHAR2(500);

l_used_bytes NUMBER;

l_alloc_bytes NUMBER;

BEGIN

DBMS_SPACE.create_table_cost (tablespace_name => 'USERS', avg_row_size => 106, row_count => 1000000, pct_free => 10, used_bytes => l_used_bytes, alloc_bytes => l_alloc_bytes);

DBMS_OUTPUT.put_line ('new table (TS=USERS): used=' l_used_bytes ' bytes allocated=' l_alloc_bytes ' bytes');

l_ddl := 'CREATE INDEX scott.emp_idx_1 ON scott.emp(job, mgr, hiredate)';

DBMS_SPACE.create_index_cost ( ddl => l_ddl, used_bytes => l_used_bytes, alloc_bytes => l_alloc_bytes);

DBMS_OUTPUT.put_line ('scott.emp_idx_1 : used=' l_used_bytes ' bytes allocated=' l_alloc_bytes ' bytes');

END;

/

new table (TS=USERS): used=124125184 bytes allocated=125829120 bytes

scott.emp_idx_1 : used=280 bytes allocated=65536 bytes

PL/SQL procedure successfully completed.