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 multi-versioned, highly active editing geodatabase, database files of the VERSIONS tablespace may be separated and dispersed across available disks to avoid input/output contention.
创建用于存储工作空间要素类和表的新表空间。
The following table lists the tablespaces that are going to be created in the SQL example.
| TABLESPACE | CONFIGURATION KEYWORD |
|---|---|
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 |
PRODLIB_XMLDOC | XML documents |
PRODLIB_XMLINDEX | XML documents index |
PRODLIB_SDEDICT | Data dictionary (if implementing user-schema geodatabase) |
PRODLIB_SDEDICTINDEX | Data dictionary index (if implementing user-schema geodatabase) |
The following script can be used to create tablespaces using Oracle SQL*Plus or Oracle SQL Developer.
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';
CREATE SMALLFILE TABLESPACE "PRODLIB_XMLDOC"
DATAFILE D:\ORACLE\ORADATA\PRODLIBDB\PRODLIB_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 "PRODLIB_XMLINDEX"
DATAFILE D:\ORACLE\ORADATA\PRODLIBDB\PRODLIB_XMLINDEX01.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_SDEDICT"
DATAFILE D:\ORACLE\ORADATA\PRODLIBDB\PRODLIB_SDEDICT01.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_SDEDICTINDEX"
DATAFILE D:\ORACLE\ORADATA\PRODLIBDB\PRODLIB_SDEDICTINDEX01.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';
将数据文件的初始大小设置为 10 MB，以避免在创建表空间时发生延迟。然后，可以重新调整数据文件的大小以避免产生碎片。
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;
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\PRODLIBDB\prodlib_Xmldoc01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\PRODLIBDB\prodlib_Xmlindex01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\PRODLIBDB\prodlib_Sdedict01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\PRODLIBDB\prodlib_Sdedictindex01.dbf' RESIZE 400M;