Preparation (NT) (95/98)| Generation | Usage | Common Problems
Preparing to install a Designer 2000 Repository on Windows NT
Step 1: Log on to Oracle
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 'd:\orant\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 'd:\orant\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.
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 'd:\orant\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).
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).
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:
@ d:\orant\repadm20\utl\ckrorole.sqlOnce 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];So the example looks like (Figure 1.13):
grant execute on dbms_lock to rep_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.
NEXT: Generate the Designer 2000 Repository