Create Database Instance, Tablespace, Schema User, and Tables
The 3DOrchestrate Distribution Server can be configured to use an existing database instance or you can create a new instance for it.
-
You can create a new database instance using any tools you wish, including the Oracle Database Configuration Assistant. You can use the following command options if desired, which applies default settings:
$ORACLE_HOME\bin\dbca -silent -createDatabase -characterSet AL32UTF8 -nationalCharacterSet AL16UTF16 -templateName $ORACLE_HOME\assistants\dbca\templates\General_Purpose.dbc -gdbName %ORACLE_SID% -sid $ORACLE_SID -sysPassword <PSWD> -systemPassword <PSWD> -dbsnmpPassword <PSWD> -sysmanPassword <PSWD> -emConfiguration LOCAL -storageType FS -datafileJarLocation $ORACLE_HOME\assistants\dbca\templates -sampleSchema false -datafileDestination $ORACLE_HOME\oradata -recoveryAreaDestination $ORACLE_HOME\oradata\archlogs
In this command, substitute your specific values for the following:
$ORACLE_HOME
- Path to your Oracle software installation.
$ORACLE_SID
- An identifier for this newly created database instance.
- <PSWD>
- Various passwords as defined in your environment.
-
Create the tablespace.
Log in to the database instance as a user with SYSDBA privileges and execute the following SQL statements:
CREATE SMALLFILE TABLESPACE "<TS_NAME>" DATAFILE '<Data_File_Location>/<TS_NAME>.DBF' SIZE <size> AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
In these commands, substitute your specific values for the following:
- <TS_NAME>
- The name for the tablespace, for example
3DOrch_TS
. - <Data_File_Location>
- Full path to the location of your data file destination.
-
Create the schema user.
Log in to the database instance as a user with SYSDBA privileges and execute the following SQL statements:
ALTER SYSTEM SET PROCESSES=600 SCOPE=SPFILE; ALTER SYSTEM SET OPEN_CURSORS=2048 SCOPE=BOTH; ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED; ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; CREATE USER <USER> PROFILE "DEFAULT" IDENTIFIED BY "<PW>" DEFAULT TABLESPACE "<TS_NAME>" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK; GRANT CREATE TABLE TO <USER>; GRANT CREATE SESSION TO <USER>; GRANT UNLIMITED TABLESPACE TO <USER>; GRANT SELECT ON SYS.DBA_PENDING_TRANSACTIONS TO <USER>; GRANT SELECT ON SYS.DBA_2PC_PENDING TO <USER>; GRANT SELECT ON SYS.PENDING_TRANS$ TO <USER>; GRANT EXECUTE ON SYS.DBMS_XA TO <USER>;
In these commands, substitute your specific values for the following:
- <USER>
- The username you want, for example
3DOrchAdmin
. - <PW>
- The password you want.
- <TS_NAME>
- Name of the tablespace you just created.
-
Creat the tables.
Log in to the database instance as the schema user just created. Execute the following SQL script:
START "<server_install_dir>\<os>\reffiles\SMAExeServer\database\oracle\createtables.sql"
In this command, substitute your specific values for the following:
- <server_install_dir>
- Full path to the base installation directory of the 3DOrchestrate Distribution Server. For example, the default on Windows is C:\Program Files\Dassault Systemes\3DOrchestration\V6R2016x\.
- <os>
- The operating system subdirectory in
<server_install_dir>
. This is either \win_b64\ or /linux_a64/.
If the 3DOrchestrate Distribution Server installation is not on this machine, copy the createtables.sql file to the Oracle database machine and execute it there.