Creating data files in Oracle (Production Mapping)

Create new tablespaces to store the product library feature classes and tables.

The following table lists the tablespaces that are going to be created in the SQL example.

TABLESPACE

ArcSDE_PARAMETER

PRODLIB_BDATA

Business table

PRODLIB_BINDEX

Business table index

PRODLIB_FDATA

Feature table

PRODLIB_FINDEX

Feature table index

PRODLIB_SDATA

Spatial Index table

PRODLIB_SINDEX

Spatial Index table index

PRODLIB_ADATA

Adds table (versioned)

PRODLIB_AINDEX

Adds table index

PRODLIB_DDATA

Deletes table (versioned)

PRODLIB_DINDEX

Deletes table index

Tablespaces for the product library
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "prodlibadmin";

CREATE SMALLFILE TABLESPACE "PRODLIB_BDATA" 
DATAFILE D:\oracle\ORADATA\PRODLIBDB\PRODLIB_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 "PRODLIB_BINDEX" 
DATAFILE D:\oracle\ORADATA\PRODLIBDB\PRODLIB_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 "PRODLIB_FDATA" 
DATAFILE D:\oracle\ORADATA\PRODLIBDB\PRODLIB_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 "PRODLIB_FINDEX" 
DATAFILE D:\oracle\ORADATA\PRODLIBDB\PRODLIB_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 "PRODLIB_SDATA" 
DATAFILE D:\oracle\ORADATA\PRODLIBDB\PRODLIB_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 "PRODLIB_SINDEX" 
DATAFILE D:\oracle\ORADATA\PRODLIBDB\PRODLIB_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 "PRODLIB_ADATA" 
DATAFILE D:\oracle\ORADATA\PRODLIBDB\PRODLIB_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 "PRODLIB_AINDEX" 
DATAFILE D:\oracle\ORADATA\PRODLIBDB\PRODLIB_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 "PRODLIB_DDATA" 
DATAFILE D:\oracle\ORADATA\PRODLIBDB\PRODLIB_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 "PRODLIB_DINDEX" 
DATAFILE D:\oracle\ORADATA\PRODLIBDB\PRODLIB_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';

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\PRODLIBDB\prodlib_Bdata01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\PRODLIBDB\prodlib_Bindex01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\PRODLIBDB\prodlib_Fdata01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\PRODLIBDB\prodlib_Findex01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\PRODLIBDB\prodlib_Sdata01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\PRODLIBDB\prodlib_Sindex01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\PRODLIBDB\prodlib_Adata01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\PRODLIBDB\prodlib_Aindex01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\PRODLIBDB\prodlib_Ddata01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\oracle\ORADATA\PRODLIBDB\prodlib_Dindex01.dbf' RESIZE 400M;
NoteNote:

Copying and pasting the examples may cause syntax errors.

9/26/2014