Monday, April 23, 2007

Prepare for Oracle Designer DB Repository

I'd like to use Oracle Designer 10g for reverse engineering my existing Oracle 10g database. But it need a database repository with properly setting on Oacle Database Initialization Parameters. The following are the recommended minimum settings:


compatible = 9.0.0 # for an Oracle9i database
compatible = 8.1.7 # for an Oracle8i database
max_enabled_roles = 30
sort_area_size = 262144
sort_area_retained_size = 65536

hash_area_size = 1048576
optimizer_index_caching = 50
optimizer_index_cost_adj = 25
shared_pool_size = 32000000

db_block_buffers # comment out on an Oracle9i database
db_block_buffers = 2000 # on an Oracle8i database
open_cursors = 3000
processes = 100
db_file_multiblock_read_count=16 # for a 4K Oracle block size
db_file_multiblock_read_count=32 # for a 2K Oracle block size
db_file_multiblock_read_count=8 # for a 8K Oracle block size

To check the value of any database initialization parameter do the following step:
1. Connect to the database using SYS acount with AS SYSDBA clause. Example:

$ sqlplus "/as sysdba"

2. Show the parameter value with the command show parameter parameter_name. Example:
SQL>show parameter max_enabled_roles

If the value smaller than the value stated above, then set the parameter value by the following step:
1. Alter system and set param_name = param_value with scope = spfile. Example:

SQL> alter system set max_enabled_roles = 30 scope = spfile;

2. The change will efect after restart the database. So, the next step is restart the database.

Until this step, It is ready for Installation of database repository needed by Oracle Designer.

No comments: