Table Of Contents

Creating tablespace and user in CEPM Database

Create tablespace and user in Oracle

Creating tablespace and user in CEPM Database

---

It is assumed that the database is already installed and running, and you have the system password. Following examples contain sample scripts, wherein you can modify the parameters as per your requirement.

Create tablespace and user in Oracle

In this example, the CEPM tables are being installed in Oracle with userid 'cepmUser' on Linux.

I. Create tablespace for the CEPM data:

---

Step 1 Create a script called CreateCepmTableSpace.sql as given below:

REM connect "SYS"/"&&sysPassword" as SYSDBA

set echo on

spool /tmp/CreateCepmTbs.log

CREATE BIGFILE TABLESPACE CEPM.DATA

DATAFILE '/home/oracle/oracle/product/10.2.0/oradata/cepmdb/CEPM_DATA.dbf' SIZE 2048M

BLOCKSIZE  8192 

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1280K

SEGMENT SPACE MANAGEMENT AUTO

ONLINE;

alter database datafile

   '/home/oracle/oracle/product/10.2.0/oradata/cepmdb/CEPM_DATA.dbf'

autoextend ON next 32m maxsize 32048M

spool off

exit

Step 2 Modify the path for the datafile with the directory path where you wish your data to be resided in.

Step 3 Select filename from dba_data_files to find out where other datafiles in the database are.

II. Create the database user:

Step 4 Create a script called CreateCepmUser.sql to create a user as given below:

REM connect "SYS"/"&&sysPassword" as SYSDBA

set echo on

spool /tmp/CreateCepmUser.log

CREATE USER &&cepmUser IDENTIFIED BY &&cepmPassword

      default tablespace CEPM_DATA

      temporary tablespace temp;

grant create session to &cepmUser;

grant alter session to &cepmUser;

grant unlimited tablespace to &cepmUser;

grant create table to &cepmUser;

grant create cluster to &cepmUser;

grant create synonym to &cepmUser;

grant create view to &cepmUser;

grant create sequence to &cepmUser;

grant create database link to &cepmUser;

grant create procedure to &cepmUser;

grant create trigger to &cepmUser;

grant create type to &cepmUser;

grant create operator to &cepmUser;

grant create indextype to &cepmUser;

grant connect to &cepmUser;

grant create any type to &cepmUser;

grant create any view to &cepmUser;

grant query rewrite to &cepmUser;

grant create job to &cepmUser;

spool off

exit

Step 5 Run it with the command:

sqlplus system as sysdba @CreateCepmUser

•The prompt for "Enter password" is the system password. Let's create the account cepm/cepm by running this script.

•The prompt for 'Enter value for cepmuser' is the name of the cepm schema to be created.

•The prompt for 'Enter value for cepmpassword' is the new password that is used for the new userid you are creating.

Make sure the user is created successfully and all grants succeed.

On execution of the above scripts, your Oracle DB tablespace and user are created with necessary privileges granted.

---