Databases Reference
In-Depth Information
We have also seen how to list all migrated rows in a (temporary) table with the
ANALYZE
TABLE… LIST CHAINED ROWS INTO…
command. The script to create a
CHAINED_ROWS
table is located at
$ORACLE_HOME/rdbms/admin/utlchain.sql
.
There are two other methods to resolve row migration: performing an
ALTER TABLE MOVE
,
or using
EXPORT/IMPORT
procedures to dump and reload the data into the table.
Estimating table size with different PCTFREE parameter
Often the value of
PCTFREE
is set very low to avoid wasting space, this is not a wise strategy,
unless we are talking about insert-only tables, for example, something similar to an audit trail,
where we can use a
PCTFREE
0 parameter.
To estimate the size of a table using different values for the
PCTFREE
parameter, we can use
the
CREATE_TABLE_COST
of the
DBMS_SPACE
package:
SET SERVEROUTPUT ON
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_table_cost (
tablespace_name => 'EXAMPLE',
avg_row_size => 4500,
row_count => 100,
pct_free => 10,
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line('Used Bytes: '||l_used_bytes);
dbms_output.put_line('Allocated Bytes: '||l_alloc_bytes);
end;
/
In this procedure, we have set the tablespace to use the average row size and the row count,
we can try different
pct_free
parameters to estimate the space needed by the table, both
allocated and used.
