Home > Getting Started with Oracle... > Creating Tablespaces for OLAP
![]() Previous |
![]() Next |
You should create undo, permanent, and temporary tablespaces that are appropriate for storing analytic workspaces. Tablespaces created using default parameters may use resources inefficiently. Use the SQL CREATE
clauses listed in Table: SQL CREATE TABLESPACE Commands for OLAP.
Analytic workspaces are stored in the owner's default tablespace unless specified otherwise.
SQL CREATE TABLESPACE Commands for OLAP
Tablespace | CREATE Command |
---|---|
Undo |
|
Permanent |
|
Temporary |
|
Each dimensional object occupies at least one extent in a permanent tablespace. A fixed extent size may waste most of the allocated space. For example, if an object is 64K and the extents are set to a uniform size of 1M (the default), then only a small portion of the extent is used.
Oracle OLAP uses the temporary tablespace to store all changes to the data in a cube, whether the changes are the result of a data load or data analysis. Saving the cube moves the changes into the permanent tablespace and clears the temporary tablespace. This usage creates numerous extents within the tablespace.
Suppose you want to create an undo tablespace for OLAP named OLAPUNDO
.
To create an undo tablespace named OLAPUNDO:
Create the tablespace:
CREATE UNDO TABLESPACE olapundo DATAFILE '$ORACLE_BASE/oradata/undo.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
Edit the system parameter file to include the following settings:
UNDO_TABLESPACE=OLAPUNDO UNDO_MANAGEMENT=AUTO
Restart the database.
Suppose you want to create a permanent tablespace named GLO
for the GLOBAL
analytic workspace. You can make GLO
the default tablespace for the GLOBAL
owner, or you can override the default tablespace when creating the analytic workspace.
To create a permanent tablespace named GLO:
Create the tablespace:
CREATE TABLESPACE glo DATAFILE '$ORACLE_BASE/oradata/glo.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Assign GLO
as the default tablespace for the GLOBAL
user:
ALTER USER global DEFAULT TABLESPACE glo QUOTA UNLIMITED ON glo;
When creating the GLOBAL
analytic workspace, select GLO
as the tablespace if it is not the default.
Suppose you want to create a temporary tablespace named GLOTMP
for the GLOBAL
analytic workspace. GLOTMP
must be the default temporary tablespace for the owner of the analytic workspace.
To create a temporary tablespace named GLOTMP:
Create the tablespace:
CREATE TEMPORARY TABLESPACE glotmp TEMPFILE '$ORACLE_BASE/oradata/glotmp.tmp' SIZE 50M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
Assign GLOTMP
as the default temporary tablespace for the GLOBAL
user:
ALTER USER global TEMPORARY TABLESPACE glotmp;