Creating Tablespaces for Data Extract Tables and Indexes

Creating Tablespaces

You must create tablespaces to contain the tables and indexes required for data extract. Oracle recommends creating a separate tablespace for tables and for indexes, and for creating each tablespace:

as locally managed
with autoallocate on
with automatic segment space management
with a block size of 16 kb

Use the following command:

CREATE TABLESPACE dxtables DATAFILE '/u02/oracle/data/dxtables01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

For more information, see the Oracle Database Administrator's Guide.

Parent topic: Creating Tablespaces for Data Extract Tables and Indexes

Entering Tablespace Names in Reference Codelists

To enable users to create tables using the Data Extract View Builder, you must specify to which tablespace and tablespace index you want to add tables for each Study Access Account. You specify these tablespaces and index tablespaces in two installation reference codelists that store data extract tablespace information:

Note:

You must also remove invalid values from the reference codelists to prevent users from selecting them for study access accounts. See DX_INDEX_TABLESPACE Installation Codelist and DX_VIEW_TABLESPACE Installation Codelist for information.

Parent topic: Creating Tablespaces for Data Extract Tables and Indexes

Creating Data Extract Access Accounts Using Local Tablespaces

You must create a Data Extract Access Account (under Conduct, navigate to Data Extract, then Study Access Accounts) and specify a tablespace for the account's tables and for its indexes. When the tablespace is defined as locally managed, the View Builder creates tables and indexes for that account as follows:

The extract tables are created in a single step rather than the previous approach that created a temporary table and then, after determining the size of the table, a second permanent table. This feature capitalizes on the ability to use locally managed tablespaces with the AUTOALLOCATE feature that automatically sizes the table.
The tables are created using the Oracle database table compression feature. Since data extract tables tend to have many repeated keys and values, this should result in significantly less space usage and more efficient data access.
The indexes are created with leading key compression that results in significantly more compact and efficient indexes.
Index statistics are now computed as the indexes are created, which results in faster statistics calculation.
Repeated account maintenance of the ROLLSNAP account will take advantage of the more efficient space allocation method used in Locally Managed Tablespaces when it drops and recreates tables.
Tables are created with the NOLOGGING attribute. This reduces the table creation time significantly by avoiding writing to the redo logs. The price of this option is that recovery from database failure using redo logs will not recreate extract tables that have not been otherwise backed up and restored. Since the tables can be recreated from the extract views at any time by rerunning account maintenance in FULL, this trade-off is usually acceptable. However, if you do not want to use the NOLOGGING behavior, you can override it by creating the locally managed tablespace with the FORCE LOGGING attribute.

Parent topic: Creating Tablespaces for Data Extract Tables and Indexes