Installation and Configuration › How to Prepare Your DBMS Environment › Tasks to Create an Oracle DBMS

Tasks to Create an Oracle DBMS

The following tasks must be performed by the DBA and system administrator responsible for installing the software on an Oracle database management system. Information provided in this section includes installation and Oracle connectivity software requirements.

These steps can be performed using graphical tools but you can also use SQL *Plus (all versions), SQL*DBA (either command line or full screen mode), or the Oracle Enterprise Management Console. Examples of SQL commands are included where appropriate. Data file paths, data file sizes, role names, and user names are included for example only.

1. 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.

2. 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.

3. 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;
   

4. 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;
   

5. 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.

6. 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;
   

7. 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;
   

8. 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;
   

9. 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.

10. 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.

11. Grant the CA ERwin Data Modeler Workgroup Edition User role to each Oracle user that uses the database.

    For example:

    grant MMUSER to USER1;
    

    grant MMUSER to USER2;
    

    grant MMUSER to USER3;
    

    Important! You must specify the User role name created in Step 9 for these Grant statements.

12. Grant the CA ERwin Data Modeler Workgroup Edition User role to the Installer role.

    For example:

    grant MMUSER to MMINSTALL;
    

13. Grant the CA ERwin Data Modeler Workgroup Edition Installer role to the CA ERwin Data Modeler Workgroup Edition Installer user. You must also grant DBA privileges to the user you designate as the Installer.

    For example:

    grant MMINSTALL to STEVE;
    

    grant DBA to STEVE;
    

    Note: The DBA role is necessary only during the installation or upgrade. You can strip the installer of the DBA role after the installation or upgrade is complete.

14. Run the Setup program.

    When the setup is complete, select the Initialize option and connect to Oracle as the CA ERwin Data Modeler Workgroup Edition Installer user (for example, STEVE).

15. 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).

16. Add the Installer user as the CA ERwin Data Modeler Workgroup Edition administrator.

    When the Security Manager is opened, your user name is automatically added to the Administrator profile in the Security Profile list. You can also designate one of the users with the MMUSER role (from step 12) for routine operations. This completes the setup and you can begin using the software.

    Note: After successful installation, you can revoke the Installer role and grant the User role to the CA ERwin Data Modeler Workgroup Edition Installer user.