Sunday, August 2, 2015

DBMS_SPACE.CREATE_TABLE_COST and DBMS_SPACE.CREATE_INDEX_COST for space planning


DECLARE
 l_used_bytes NUMBER;
 l_allocated_bytes NUMBER;
 BEGIN
 DBMS_SPACE.CREATE_TABLE_COST (
 tablespace_name => 'TEST',
 avg_row_size => 701,
 row_count => 10000000,
 pct_free => 10,
 used_bytes => l_used_bytes,
 alloc_bytes => l_allocated_bytes);
 DBMS_OUTPUT.PUT_LINE ('used = ' || l_used_bytes || ' bytes'
 || 'allocated = ' || l_allocated_bytes || ' bytes');
END;

declare
 l_index_ddl VARCHAR2(1000);
 l_used_bytes NUMBER;
 l_allocated_bytes NUMBER;
 BEGIN
 DBMS_SPACE.create_index_cost (
 ddl => 'create index cmp_idx on user_log(id_num)',
 used_bytes => l_used_bytes,
 alloc_bytes => l_allocated_bytes);
 DBMS_OUTPUT.PUT_LINE ('used = ' || l_used_bytes || 'bytes'
 || ' allocated = ' || l_allocated_bytes || 'bytes');
 END;

No comments:

Post a Comment