It depends on your data (and this assumes that you don't care about the different behaviors of the two data types). You can put together a test with sample data that is similar to what you're actually storing to find out what approach will use less space.
I'll create a table num_test with two different columns, one declared as a number and one as a binary_float.
create table num_test (
num_col number,
flt_col binary_float
);
If I populate it with 100,000 rows of data in increments of 0.001
begin
for i in 1 .. 100000
loop
insert into num_test( num_col, flt_col )
values( i/1000, i/1000 );
end loop;
end;
then I can see the minimum, maximum, and average size required to store the values
select min( vsize(num_col) ), max( vsize(num_col) ), avg( vsize(num_col) ),
min( vsize(flt_col) ), max( vsize(flt_col) ), avg( vsize(flt_col) )
from num_test;
If you run that, you'll see that the number column is averaging 3.89 bytes/ row (with a min of 2 and a max of 4) while every binary_float required 4 bytes of storage. So there is a small savings to using number in this case.
But is that the whole story? Nope. If we use different sample data, we get very different results. Let's run the same test but divide everything by 3
begin
for i in 1 .. 100000
loop
insert into num_test( num_col, flt_col )
values( i/1000/3, i/1000/3 );
end loop;
end;
Now, if you run the same vsize query, you'll see that the number column takes up to 21 bytes of storage and averages 7.69 bytes for the 200,000 rows. The flt_col is still using a fixed 4 bytes for every row.
Depending on your data, you may find that one or the other approach uses more space. Of course, where there are substantial differences in space utilization, there is the potential for more substantial rounding issues when you move to floating point numbers.
Note as well that the results may be version-dependent. I did my test on an 11.2 database where the documentation indicates that binary_float always takes 4 bytes of storage. In 10.2, the documentation indicates that binary_float took 5 bytes due to the inclusion of an extra length indicator. It's not obvious to me why a 32-bit value would ever need a length byte so I'm hard-pressed to understand why an earlier version of Oracle might have required the fifth byte, but the documentation is consistent that it did.
Why using original Export and Import utilities (imp and exp respectively), which are deprecated, while since 10g Oracle provides a new, more efficient facility called Data Pump?
You can use Data Pump Import with REMAP_TABLESPACE parameter to specify the new tablespace name for the objects being imported:
$ impdp hr REMAP_TABLESPACE=tbs_1:tbs_6 DIRECTORY=dpump_dir1 \
DUMPFILE=employees.dmp
One of the things that would require you to use original Import is that you already have a file generated by original Export, and don't have access to the source database for using Data Pump Export, because as per Database Upgrade Guide:
Dump files generated by the Data Pump Export utility are not
compatible with dump files generated by the original Export utility.
Therefore, files generated by the original Export (exp) utility cannot
be imported with the Data Pump Import (impdp) utility, and vice versa.
In which case you can (as seems to me) follow the Reorganizing Tablespaces section in Database Utilities, which describes the procedure:
If a user's quota allows it, the user's tables are imported into the
same tablespace from which they were exported. However, if the
tablespace no longer exists or the user does not have the necessary
quota, then the system uses the default tablespace for that user[…]
Basically you need to set the user's default tablespace during import to one which should contain the imported objects.
SQL> alter user spongebob default tablespace newtbs;
Where newtbs is the name of the new tablespace.
See also:
Best Answer
Initrans is most important for concurrent inserts in the same blocks of a table. You normally recognize this when there is contention for the block headers. As long as there is enough free space in the block, list can grow in the free space of the block. So if you know that for some tables you have many concurrent jobs inserting data in the same end of the table, it will help to set the INITRANS to a value similar to that of the number of concurrent jobs.
pct_used means that - in your case - as soon as a block gets 4% space free, that block will get to the free list to accept new rows.
pct_free means that as soon the free space in a block falls below pct_free, the block is no longer accepting new rows, in order to keep space for updates where rows can grow.
The table statistics show a statistic like AVG_SPACE, AVG_ROW_LEN.