Tasks to Create an Oracle DBMS

Installing and Configuring Mart › Prepare Your DBMS Environment › Tasks to Create an Oracle DBMS

Tasks to Create an Oracle DBMS

The DBA and the system administrator responsible for installing the software on an Oracle database management system performs these tasks.

Use graphical tools or SQL *Plus (all versions), SQL*DBA, or the Oracle Enterprise Management Console to perform these tasks. Examples of SQL commands are included where appropriate. Data file paths, data file sizes, role names, and user names are included for example only.

Check SYSTEM tablespace.
The installation creates several stored procedures. All triggers, stored procedures, and packages are kept in the Oracle SYSTEM tablespace. The standard size of the SYSTEM tablespace assumes that you are not using procedural options, so the SYSTEM tablespace often needs to be expanded. If other Oracle applications are not using procedural code, then the SYSTEM tablespace should be expanded to 32 MB. If other Oracle applications also use procedural code, expand the SYSTEM tablespace to at least 32 MB.
Check Rollback Segment tablespace.
If your instance uses UNDO tablespace, do not create rollback segments.

Significant rollback space is required for installation and use. The rollback segments should be in their own separate tablespace and each have at least 16 MB of available space. There should be one rollback segment for every four concurrent users, with a maximum of 50 rollback segments. The available space should scale upward with increasing numbers of rollback segments. Finally, the rollback segment optimal parameter should be set to control rollback segment growth and space consumption.

Note: For Steps 3, 4, and 5, use Dictionary-managed tablespaces.

Create a data tablespace of at least 32 MB.

For example:

CREATE TABLESPACE MyMart

DATAFILE '/db01/oracle/rdbms9i/data/mymart.ora' SIZE 100M;

Or For Locally Managed extents:

CREATE TABLESPACE Mymart

DATAFILE '/db01/oracle/rdbms9i/data/mymart.ora' SIZE 100M

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Create a index tablespace of at least 32 MB.

For example:

CREATE TABLESPACE MMARTINDEX

DATAFILE '/db02/oracle/rdbms9i/data/mmartindex.ora' SIZE 75M;

Or For Locally Managed extents:

CREATE TABLESPACE MMARTINDEX

DATAFILE '/db02/oracle/rdbms9i/data/mmartindex.ora' SIZE 75M

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Create a temporary tablespace.

For example:

CREATE TEMPORARY TABLESPACE MMTEMP TEMPFILE '/db03/oracle/rdbms9i/data/mmarttemp.ora' SIZE 50M;

Or For Locally Managed extents:

CREATE TEMPORARY TABLESPACE MMTEMP TEMPFILE '/db03/oracle/rdbms9i/data/mmarttemp.ora' SIZE 50M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Note: For more details about syntax and options regarding tablespace creation, see the appropriate Oracle documentation.

Create an Oracle user with DBA privileges to be used by the CA ERwin Data Modeler Workgroup Edition Installer or designated schema owner.
Assign the data tablespace as this user’s default tablespace, and the temporary tablespace as this user’s temporary tablespace.

For example:
```
CREATE USER STEVE IDENTIFIED BY STEVE
```
```
DEFAULT TABLESPACE MyMart
```
```
TEMPORARY TABLESPACE MMTEMP
```
```
QUOTA UNLIMITED ON MyMart
```
```
QUOTA UNLIMITED ON MMARTINDEX;
```
Create the CA ERwin Data Modeler Workgroup Edition Installer role.
The following example is the role required by the Oracle user installing CA ERwin Data Modeler Workgroup Edition.
```
CREATE ROLE MMINSTALL;
```
Grant Oracle privileges to the Installer role.
The following example shows the Oracle privileges that the CA ERwin Data Modeler Workgroup Edition Installer needs to install on Oracle. For the last command, you must log in as sys with the sysdba role in the user-name or the command will fail.
```
grant create sequence to MMINSTALL;
```
```
grant create table to MMINSTALL;
```
```
grant create view to MMINSTALL;
```
```
grant drop public synonym to MMINSTALL;
```
```
grant create public synonym to MMINSTALL;
```
```
grant create procedure to MMINSTALL;
```
```
grant select on dba_data_files to MMINSTALL;
```
Create the CA ERwin Data Modeler Workgroup Edition User role.
For example:
```
CREATE ROLE MMUSER;
```
When you select this role as the CA ERwin Data Modeler Workgroup Edition User role during Step 4 of the installation procedure, the Setup program generates grant statements that grant object level privileges to this role.
Grant the create session Oracle privilege to the User role.
For example:
```
grant create session to MMUSER;
```
Note: The create session privilege is the only privilege that an Oracle user needs to use the database.
Grant the CA ERwin Data Modeler Workgroup Edition User role to the Installer role.
For example:
```
grant MMUSER to MMINSTALL;
```
Select the CA ERwin Data Modeler Workgroup Edition tablespaces and User role.
When prompted for tablespace and role information, select the CA ERwin Data Modeler Workgroup Edition data tablespace, the CA ERwin Data Modeler Workgroup Edition index tablespace, and the CA ERwin Data Modeler Workgroup Edition User role (the role created in Step 9).