Creating the Oracle Instance, Tablespace, Schema User, and Tables

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.

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.

Verify that the 3DOrchestrate Database is Configured Correctly

Log in to the database instance as a user with SYSDBA privileges and execute the following SQL statement:
```
select * from nls_database_parameters;
```
The result of this query should show the following lines:
```
...
NLS_CHARACTERSET AL32UTF8
...
NLS_NCHAR_CHARACTERSET AL16UTF16
```
Next execute the following statement:
```
select TABLESPACE_NAME from DBA_TABLESPACES where TABLESPACE_NAME = '<TS_NAME>';
```
Replace <TS_NAME> with the name of the tablespace you created earlier. The result of this query should show that the tablespace exists.
Next execute this statement:
```
select USERNAME from DBA_USERS where USERNAME = '<USER>';
```
Replace <USER> with the name of the schema user you created earlier. The result of this query should be:
```
USERNAME
------------------------------
<USER>
1 row selected.
```
Finally, log out and then log back in as the schema user, with the schema user password you defined earlier. Verify that the tables have been properly created by executing the following SQL statement:
```
select TABLE_NAME from ALL_TABLES where TABLE_NAME = 'V6FIPERSYS' AND OWNER = '<USER>';
```
Replace <USER> with the name of the schema user. The result of this query should be:
```
TABLE_NAME
------------------------------
V6FIPERSYS
1 row selected.
```