Oracle Tools for Application and Database Modeling and Design

Preparation (NT) (95/98)| Generation | Usage | Common Problems

Preparing to install a Designer 2000 Repository on Windows 95/98

Step 1: Log on to Oracle

Once Oracle7 is installed, log into the database instance that will serve the repository. Use the default instance ORCL that is installed with Oracle7. Start SQL*Plus 3.3 to log on (SQL*Plus 3.3 will act as the command line interpreter to the database, similar to a terminal screen to a UNIX server or the DOS command prompt on a Windows machine). If Oracle7 is on the local machine, no entry needs to be made in the Host String box (Figure 1.1), because Oracle will use ORCL as the default database. On installation, the account sys is created with all privileges and roles necessary for database administration. Internal is an alias for the sys account with SYSDBA privileges. The password for this account is oracle. You can use either sys or internal to prepare Oracle7 for a repository (Figure 1.2). Should you make a mistake logging on (Figure 1.3), re-enter the username and password.

Figure 1.1: The SQL*Plus Log On Dialog

Figure 1.2: A Successful Log On

Figure 1.3: An Unsuccessful Log On


Step 2: Create the repository tablespaces

Once logged in, issue the SQL statement:

select * from dba_data_files;
This will give the location of the current tablespace datafiles so the repository tablespace datafiles will be in the same location (Figure 1.4). To create tablespaces, issue the SQL statement

create tablespace [name] datafile '[filepath]' size [filesize]M default storage (initial 100k next 100k pctincrease 5);
where [name] is the name of the tablespace, [filepath] is the name and absolute location of the datafile, and [filesize] is how large, in MB's, the datafile should be when allocated (Figure 1.5). For example:

create tablespace des2 datafile 'c:\orawin95\database\des2data.ora' size 100M default storage (initial 100k next 100k pctincrease 5);
Ideally, create 2 tablespaces, one for the repository tables and one for the repository indexes (this is a performance issue. Tests have shown it really does make a difference when separated). So back to the example, substite des2_i for des2 and des2_idata.ora for des2data.ora (Figure 1.6) and issue the statement:

create tablespace des2_i datafile 'c:\orawin95\database\des2_idata.ora' size 100M default storage (initial 100k next 100k pctincrease 5);
Feel free to use whatever you like for [name] as long as you remember what you used when it comes time to create the repository.

Figure 1.4: Tablespace Datafiles in Oracle7

Figure 1.5: Creating the Tables Tablespace


Figure 1.6: Creating the Indexes Tablespace


Step 3: Confirm System Tablespace Requirements

Now that the repository tablespaces are in place, make sure that the SYSTEM tablespace is big enough to hold the additional overhead of a Designer 2000 repository. Issue the statement:

select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
to get a snapshot of the current space availability of your Oracle7 database (Figure 1.7). If the SYSTEM tablespace is less that 60MB, add another datafile to the tablespace. Do this with the SQL statement:

alter tablespace system add datafile '[filepath]' size [filesize]M;
where, similar to the create statement, [filepath] is the name and absolute location of the datafile, and [filesize] is how large, in MB's, the datafile should be when re-allocated. For example, the statement:

alter tablespace system add datafile 'c:\orawin95\database\sys2orcl.ora' size 100M;
will add the datafile sys2orcl.ora to the system tablespace (Figure 1.8). Re-issue the statement:

select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
used earlier to confirm that the System tablespace is at least 60MB in size (Figure 1.9).

Figure 1.7: Tablespace Sizes (looks like 'system' is too small!)

Figure 1.8: Add a Datafile to 'system'


Figure 1.9: Tablespace Sizes (looks like 'system' is OK now)


Step 4: Create the Repository Owner Account

To create the owner, issue the statement:

create user [name] identified by [pwd] default tablespace [table_tablespace] temporary tablespace temporary_data;
where [name] is the account name, [pwd] is the account password, and [table_tablespace] is the tablespace for repository tables (not indexes) created earlier. For example, the statement:

create user rep_owner identified by demo default tablespace des2 temporary tablespace temporary_data;
will create an account rep_owner with the password demo (Figure 1.10).

Figure 1.10: Creating the Repository Owner Account


Step 5: Grant Repository Ownership Rights to the Owner Account

This repository owner account requires privileges unique to the ownership of a repository. Two of these privileges are connect and resource. To grant privilege, use the SQL statement:

grant [right(s)] to [account];
For example, this will be (Figure 1.11):

grant connect, resource to rep_owner;
Now, to make this account an "official" repository owner, create a role called DES2000_OWNER. Oracle installs the script to create this role (thus granting all of the special privileges that an owner needs) in the subdirectory '/repadm20/utl/' called 'ckrorole.sql'. To execute a script in SQLPlus 3.3, use a '@' followed by the script path and name (Figure 1.12). For example:

@ c:\orawin95\repadm20\utl\ckrorole.sql
Once that has completed, a few finishing statements for the owner account need to be executed. They are:

grant execute on dbms_lock to [owner_account];
grant execute on dbms_pipe to [owner_account];
grant create session, alter session, create table, create synonym, create sequence to [owner_account];
grant des2000_owner to [owner_account];
So the example looks like (Figure 1.13):

grant execute on dbms_lock to rep_owner;
grant execute on dbms_pipe to rep_owner;
grant create session, alter session, create table, create synonym, create sequence to rep_owner;
grant des2000_owner to rep_owner;

Figure 1.11: Grant 'CONNECT' and 'RESOURCE' to the repository owner

Figure 1.12: Create the 'DES2000_OWNER' role


Figure 1.13: Grant all relevant privileges to the repository owner


Step 6: Altering the size of the SGA (System Global Area)

Finally, enough memory for Oracle7 must be allocated to facilitate the generation of a repository. Open up the initialization file for the ORCL instance ('initorcl.ora', located in the /database directory) in a text editor (Figure 1.14, 1.15). Search for the 'shared_pool_size' parameter and change it to 6000000 (6MB)(Figure 1.16). Restart the Oracle7 instance.

Figure 1.14: Locate the Initialization File

Figure 1.15: Open 'initorcl.ora' in a Text Editor


Figure 1.16: Increase the 'shared_pool_size' Parameter to 6MB


NEXT: Generate the Designer 2000 Repository


Main Page About the Book Student Resources Oracle Resources Instructor Resources Contact Us

1999 Prentice-Hall, Inc., A division of Pearson Education, Upper Saddle River, New Jersey 07458 Legal Statement
Comments should be directed to webmaster@prenhall.com