row_count The anticipated number of rows in the table.
pct_free The pctfree setting for the table.
used_bytes The space used by the table??™s data. This value includes the overhead due
to the pctfree setting and other block features.
alloc_bytes The space allocated to the table??™s data, based on the tablespace characteristics.
This value takes the tablespace extent size settings into account.
For example, if you have an existing tablespace named USERS, you can estimate the space
required for a new table in that tablespace. In the following example, the CREATE_TABLE_COST
procedure is executed with values passed for the average row size, the row count, and the pctfree
setting. The used_bytes and alloc_bytes variables are defined and are displayed via the DBMS_
OUTPUT.PUT_LINE procedure:
declare
calc_used_bytes NUMBER;
calc_alloc_bytes NUMBER;
begin
DBMS_SPACE.CREATE_TABLE_COST (
tablespace_name => 'USERS',
146 Oracle Database 11g DBA Handbook
avg_row_size => 100,
row_count => 5000,
pct_free => 10,
used_bytes => calc_used_bytes,
alloc_bytes => calc_alloc_bytes
);
DBMS_OUTPUT.PUT_LINE('Used bytes: '||calc_used_bytes);
DBMS_OUTPUT.PUT_LINE('Allocated bytes: '||calc_alloc_bytes);
end;
/
The output of this PL/SQL block will display the used and allocated bytes calculated for these
variable settings. You can easily calculate the expected space usage for multiple combinations of
space settings prior to creating the table.
Pages:
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269