Parallel Execution - Expert Oracle Database Architecture - page 675

Extent Trimming with UNIFORM vs. AUTOALLOCATE Locally-Managed Tablespaces
That's all we need to do with regard to setting up the load component. Now, we would like to investigate how space is
managed in a locally-managed tablespace (LMT) that uses UNIFORM extent sizes, compared to how space is managed
in an LMT that AUTOALLOCATE s extents. In this case, we'll use 100MB extents. First, we create a tablespace called
LMT_UNIFORM , which uses uniform extent sizes:
EODA@ORA12CR1> create tablespace lmt_uniform
2 datafile '/u01/dbfile/ORA12CR1/lmt_uniform.dbf' size 1048640K reuse
3 autoextend on next 100m
4 extent management local
5 UNIFORM SIZE 100m;
Tablespace created.
Next, we create a tablespace named LMT_AUTO , which uses AUTOALLOCATE to determine extent sizes:
EODA@ORA12CR1> create tablespace lmt_auto
2 datafile '/u01/dbfile/ORA12CR1/lmt_auto.dbf' size 1048640K reuse
3 autoextend on next 100m
4 extent management local
5 AUTOALLOCATE;
Tablespace created.
Each tablespace started with a 1GB data file (plus 64KB used by locally-managed tablespaces to manage the
storage; it would be 128KB extra instead of 64KB if we were to use a 32KB blocksize). We permit these data files to
autoextend 100MB at a time. We are going to load the following file, which is a 10,000,000-record file:
$ ls -lag big_table.dat
-rw-r----- 1 dba 1018586660 Feb 27 21:27 big_table.dat
It was created using the big_table.sql script found in the “Setting Up Your Environment” section at the
beginning of this topic and then unloaded using the flat.sql script available on http://tkyte.blogspot.
com/2009/10/httpasktomoraclecomtkyteflat.html . Next, we do a parallel direct path load of this file into each
tablespace:
EODA@ORA12CR1> create table uniform_test
2 parallel
3 tablespace lmt_uniform
4 as
5 select * from big_table_et;
Table created.
EODA@ORA12CR1> create table autoallocate_test
2 parallel
3 tablespace lmt_auto
4 as
5 select * from big_table_et;
Table created.