Standard GIS storage recommendations favor keeping index and log files separate from vector and tabular business tables. For performance reasons, it is better to position the business, feature, and spatial index tables separately and position tablespace data files based on their usage pattern. For a multiversioned, highly active editing geodatabase, database files of the VERSIONS tablespace may be separated and dispersed across available disks to avoid input/output contention.
Create new tablespaces to store the BIS workspace feature classes and tables.
The following table lists the tablespaces that are going to be created in the SQL example.
| TABLESPACE | CONFIGURATION_KEYWORD_PARAMETER |
|---|---|
BIS_BDATA | Business table |
BIS_BINDEX | Business table index |
BIS_FDATA | Feature table |
BIS_FINDEX | Feature table index |
BIS_SDATA | Spatial Index table |
BIS_SINDEX | Spatial Index table index |
BIS_ADATA | Adds table (versioned) |
BIS_AINDEX | Adds table index |
BIS_DDATA | Deletes table (versioned) |
BIS_DINDEX | Deletes table index |
BIS_XMLDOC | XML table |
BIS_XMLIDX | XML table index |
BIS_RASTER | Raster table |
BIS_RINDEX | Raster table index |
BIS_RBLK | Raster BLK table |
BIS_RBLKIDX | Raster BLK table index |
The following script can be used to create tablespaces using Oracle SQL Plus or Oracle SQL Developer.
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "bisadmin";
CREATE SMALLFILE TABLESPACE "BIS_BDATA"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_BDATA01.dbf'SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_BINDEX"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_BINDEX01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_FDATA"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_FDATA01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_FINDEX"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_FINDEX01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_SDATA"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_SDATA01.dbf'SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_SINDEX"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_SINDEX01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_ADATA"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_ADATA01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_AINDEX"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_AINDEX01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_DDATA"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_DDATA01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_DINDEX"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_DINDEX01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_XMLDOC"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_XMLDOC01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_XMLIDX"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_XMLIDX01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_RASTER"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_RASTER01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_RINDEX"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_RINDEX01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_RBLK"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_RBLK01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
CREATE SMALLFILE TABLESPACE "BIS_RBLKIDX"
DATAFILE D:\oracle\ORADATA\BISDB\BIS_RBLKIDX01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE 400M
LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
Set the data files’ initial size to 10 MB so there is no delay in the creation of the tablespaces. You can then resize the data files to avoid fragmentation.
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Bdata01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Bindex01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Fdata01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Findex01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Sdata01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Sindex01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Adata01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Aindex01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Ddata01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Dindex01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Xmldoc01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Xmlidx01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Raster01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Rindex01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Rblk01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\BISDB\bis_Rblkidx.dbf' RESIZE 400M;
You can use Oracle Enterprise Manager to verify the data files and tablespaces.
Feedback on this topic?