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;

Remarque :

Copying and pasting the examples may cause syntax errors.