Create Schemas (Database Users)

Introduction:

Once the database is installed and configure, and now that we have OEM set up - and therefore full DBA control, we can create the schemas that are not associated with OWB - these are created through OWB itself.

I will add 2 schemas; ctl (Control), and a user account.

The ctl schema fulfills a number of functions:

• Audit:
• Data in the warehouse should be tracable to its source. When a bean counter rocks up and asks how a particular figure was derived, you need a good answer. ctl holds information about external sources of data, when they arrived and from where.
• Runtime Status:
• While Oracle Warehouse Builder and Oracle Workflow have a number of facilities for monitoring and tracking warehouse load processes, there are a number of gaps that need to be filled. This component is essentially a status engine that allows us to independently control loads. It can also form the basis for user reporting.
• Error Reporting:
• This enhances the built-in error handling functions.

The user account is where most of the devlopment takes place. Commercially, this could be done in a few ways. Some organisations prefer to set up 1 or a few general development Oracle accounts that everyone uses. Others give each develoer their own account. I will do the latter.

I already have 'steve' set up as a Linux user so I will give that user their own Oracle account. If steve is going to be accessing Oracle from his command line, he will need his Linux environment set up too.

Aim:

To create the initial storage and security infrastructure for the warehouse.

Requirements:

The database is installed and OEM is available.

Procedure:

Set up steve's Linux environment:

• Log in to Linux as steve.
• Open Terminal.
• $ vi .bash_profile
• Add the following right below the line; 'export PATH':
  

# Oracle definitions & locations. export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/10.2.0.1/db01 export PATH=$PATH:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export ORACLE_TERM=vt100 export ORACLE_SID=`grep Y$ /etc/oratab | cut -d: -f1 | head -1` umask 022

• Install chora.
  

Set up the Oracle side:

• On the PC.
  
• Start OEM:
• -Start-
• -All Programs-
• -Oracle - OracleClient10g-
• -Enterprise Manager Console-
• (It wouldn't be a bad idea to create a shortcut for this - it gets a lot of use.)
• Expand -Databases-
  
• Create a generic developer role on sta:
• Expand -STA-
• Expand -Security-
  
• Expand -Roles-
• Hover the pointer over any role and Right-click.
• -Create...-
• Create Role:
• General tab:
• Name: DEVELOPER
• Role tab:
• Ensure that the Granted Roles list contains the following:
• CONNECT
• RESOURCE
• PLUSTRACE
• SELECT_CATALOG_ROLE
• [Create]
• Role successfully created.
• [OK]
  
• Create steve's Oracle account:
• Expand -STA-
• (If you set up Preferred Credentials, this should just open up.)
• Expand -Security-
• Expand -Users-
• Hover the pointer over any user and Right-click.
• -Create...-
• Create User
• General tab:
• Name: ops$steve
  
• Authentication: -External-
  
• Tablespaces:
• Temporary: -TEMP-
• Role tab:
• Ensure that the Granted Roles list contains the following:
• DEVELOPER
• Quota tab:
• Highlight: -USERS-
• Unlimited
• [Create]
• User successfully created.
• [OK]
• Test account:
• Log in to Linux as steve.
• Open Terminal.
• $ sqlplus /
• SQL*PLUS starts and takes you to the 'SQL>' prompt.
• SQL> select sysdate from dual;
• This returns today's date.
• Set up an EXPLAIN_PLAN table:
• SQL> @$ORACLE_HOME/rdbms/admin/utlxplan
• This comes back with: 'Table created.'
  

• Create the ctl user:
  
• Expand -STA-
• (If you set up Preferred Credentials, this should just open up.)
• Expand -Security-
• Expand -Users-
• Hover the pointer over any user and Right-click.
• -Create...-
• Create User
• General tab:
• Name: ctl
  
• Enter Password: Tried this?
• Conform Password: *********
• Tablespaces:
• Temporary: -TEMP-
• Role tab:
• Ensure that the Granted Roles list contains the following:
• DEVELOPER
• Quota tab:
• Highlight: -USERS-
• Unlimited
• [Create]
• User successfully created.
• [OK]