Is there a maximum number of lines that can be put inside the ora_storage file?

We want to set up some new companies in different SIDs, and some tables are going to be large and require specific table by table entries in ora_storage file.

Thanks.

Shah,

I don't know if there is a limit, we did use a ton of entries ages ago. Luckily, I had the ancient environment to refer to.
[OLD_DEV:bsp]/app/lms/lmsd/opt/baan/sy16/bse/lib/ora>wc -l ora_storage_param*
2 ora_storage_param
11502 ora_storage_param.20020415
11656 ora_storage_param.20020416
11656 ora_storage_param.20020424
12 ora_storage_param.20020522
29250 ora_storage_param.20020610
40958 ora_storage_param.20020611
46812 ora_storage_param.20020613
46966 ora_storage_param.20020627
47044 ora_storage_param.20020710
41236 ora_storage_param.20020711
35344 ora_storage_param.20020725
35428 ora_storage_param.20020731
35450 ora_storage_param.20020910
41360 ora_storage_param.20020911
47314 ora_storage_param.20021029
47330 ora_storage_param.20021031
47348 ora_storage_param.20021105
47362 ora_storage_param.bad
12 ora_storage_param.old
624042 total

[DEV:bsp]/app/lms/lmss/opt/bse/lib/ora>wc -l ora_storage_param*
45 ora_storage_param
43 ora_storage_param.old
88 total
We successfully ran the system with 47k+ lines in that file before we changed our implementation model and went lean with fewer entries. We have multiple databases and many companies. Although, it helps in managing Oracle storage well, it added a lot of maintenance overhead from a Baan Administrator's perspective.

Hi Shah,

Even though I do not know a physical limit, I suggest not to have thousands of entries in that file. There are several reasons for this:

a) It's an admin nightmare
b) For each database access (also for all selects) the oracle driver has to read the optimization value in on eof the columns of the file. A large ora_storage is known to negatively influence performance
c) It's simply not necassary.

Let me explain the last point:
In Oracle 9,10,11 you can use dictionary mapped tablespaces. In that configuration the old oracle wisdom to reduce the number of extents for performance reasons does not apply anymore. So that whole manual extent management done in the past is not needed anymore. The only entries you will need in ora_storage are used for assigning tables/indexes to tablespaces. If this is done on a per company or per module basis, then the numbers of entries in ora_storage should be greatly reduced.

Thank you, Gentlemen, for the quick feedback.

The main reason we use the ora_storage is to direct the table creation in BAAN to the defined tablespaces in ora_storage - the main culprits at present being the integration transaction tables and the project cost ledger tables.

After looking at the report about 47k lines, I must confess the concern at our end is not justified. We have about 3000 lines, and after expected addition of four more companies, it should not exceed 7500 to 8000 lines, assuming we use the same structure of tablespace distrubution.

But it was good to have a second opinion.

the ora_storage should contain only 2 lines. For the creation of tables please put these things in ora_storage_param as mentioned by NPRao. That file will only be accessed by the creation of tables while the ora_storage will be read for all table access.

Hope this helps,
Dick

As Dik mentioned ideally, for most Baan installation 2 lines would be enough.
In much complex system like ours where we have multiple companies (65+) and database (30+) per environment (dev, qa, prod).

We had to change our implementation model to use the Oracle-10g's feature - Transportable Table Space, which improved our High Availability, Database Cloning (prod to dev/qa/sandboxes) etc.
More Info -
What is Transportable Tablespace?
Mechanism to quickly move a tablespace across Oracle databases
Most efficient means to move bulk data between databases.
– File is simply copied at OS level

Cross Platform Transportable
Tablespace Benefits
Fast data distribution in Data Warehousing environment (data marts)
Used for publishing structured data (i.e. catalogs on CDs)
Sharing of read only tablespaces by heterogeneous systems
Allowing a database to be migrated from one platform to another


Oracle Database Cross Platform Transportable Tablespace (http://www.oracle.com/technology/deploy/availability/htdocs/xtts.htm)

Now our ora_storage_param looks like -

[DEV:bsp]/app/lms/lmss/opt/bse/lib/ora
>cat ora_storage_param
#---------------------------------------------------------------------------
# S T O R A G E
# Please edit this file only with the DBA Module of Baan
# Syntax: table:company:table/index:private/group:object type:optimize options:refresh time:parameters
#---------------------------------------------------------------------------
*:781:T:TABLESPACE DATA01_781 PCTFREE 5 INITRANS 3
*:781:I:TABLESPACE INDEX01_781 PCTFREE 5 INITRANS 3
*:753:T:TABLESPACE DATA01_753 PCTFREE 5 INITRANS 3
*:753:I:TABLESPACE INDEX01_753 PCTFREE 5 INITRANS 3
*:724:T:TABLESPACE DATA01_724 PCTFREE 5 INITRANS 3
*:724:I:TABLESPACE INDEX01_724 PCTFREE 5 INITRANS 3
*:723:T:TABLESPACE DATA01_723 PCTFREE 5 INITRANS 3
*:723:I:TABLESPACE INDEX01_723 PCTFREE 5 INITRANS 3
*:722:T:TABLESPACE DATA01_722 PCTFREE 5 INITRANS 3
*:722:I:TABLESPACE INDEX01_722 PCTFREE 5 INITRANS 3
*:721:T:TABLESPACE DATA01_721 PCTFREE 5 INITRANS 3
*:721:I:TABLESPACE INDEX01_721 PCTFREE 5 INITRANS 3
*:720:T:TABLESPACE DATA01_720 PCTFREE 5 INITRANS 3
*:720:I:TABLESPACE INDEX01_720 PCTFREE 5 INITRANS 3
*:719:T:TABLESPACE DATA01_719 PCTFREE 5 INITRANS 3
*:719:I:TABLESPACE INDEX01_719 PCTFREE 5 INITRANS 3
*:718:T:TABLESPACE DATA01_718 PCTFREE 5 INITRANS 3
*:718:I:TABLESPACE INDEX01_718 PCTFREE 5 INITRANS 3
*:717:T:TABLESPACE DATA01_717 PCTFREE 5 INITRANS 3
*:717:I:TABLESPACE INDEX01_717 PCTFREE 5 INITRANS 3
*:716:T:TABLESPACE DATA01_716 PCTFREE 5 INITRANS 3
*:716:I:TABLESPACE INDEX01_716 PCTFREE 5 INITRANS 3
*:715:T:TABLESPACE DATA01_715 PCTFREE 5 INITRANS 3
*:715:I:TABLESPACE INDEX01_715 PCTFREE 5 INITRANS 3
*:714:T:TABLESPACE DATA01_714 PCTFREE 5 INITRANS 3
*:714:I:TABLESPACE INDEX01_714 PCTFREE 5 INITRANS 3
*:713:T:TABLESPACE DATA01_713 PCTFREE 5 INITRANS 3
*:713:I:TABLESPACE INDEX01_713 PCTFREE 5 INITRANS 3
*:440:T:TABLESPACE DATA01_440 PCTFREE 5 INITRANS 3
*:440:I:TABLESPACE INDEX01_440 PCTFREE 5 INITRANS 3
*:360:T:TABLESPACE DATA01_360 PCTFREE 5 INITRANS 3
*:360:I:TABLESPACE INDEX01_360 PCTFREE 5 INITRANS 3
*:330:T:TABLESPACE DATA01_330 PCTFREE 5 INITRANS 3
*:330:I:TABLESPACE INDEX01_330 PCTFREE 5 INITRANS 3
*:320:T:TABLESPACE DATA01_320 PCTFREE 5 INITRANS 3
*:320:I:TABLESPACE INDEX01_320 PCTFREE 5 INITRANS 3
*:310:T:TABLESPACE DATA01_310 PCTFREE 5 INITRANS 3
*:310:I:TABLESPACE INDEX01_310 PCTFREE 5 INITRANS 3
*:*:T:TABLESPACE DATA01 PCTFREE 5 INITRANS 3
*:*:I:TABLESPACE INDEX01 PCTFREE 5 INITRANS 3

This implementation is still leaner than the 47k lines and little more than 2 lines. It is 2-line entry per company.

As Dik mentioned ideally, for most Baan installation 2 lines would be enough.
In much complex system like ours where we have multiple companies (65+) and database (30+) per environment (dev, qa, prod).

<<SNIPP>>

This implementation is still leaner than the 47k lines and little more than 2 lines. It is 2-line entry per company.

What reason could you think of to use anything else than an ora_storage or ora_driver param that looks as follows:

*:*:T:group:014:0:
*:*:I:group:014:0:

Best regards,
Dick

After reading NPRao's reaction a bit better I discovered that he agrees with met that the ora_storage can be left to 2 lines and that the real stoorage logic must be stored in ora_storage_param (or whatever it is called in Baan IV). Sorry for any inconvenience I gave you.

best regards,
Dick

I suppose we get to wait till we migrate to ORACLE 10 before we can use these very good suggestions.

I must save this post where I can get back to it in a hurry later ...

I suppose we get to wait till we migrate to ORACLE 10 before we can use these very good suggestions.
Suggestions are Oracle version independent, but do relate to Baan versions. In Baan 4 I think we only have something as a ora_storage2 file and from verson 5 and onwards, the ora_storage_param and ora_driver_param. If you are on Baan 4 please use an ora_storage2 file:

<rdbms>_storage -> the storage data
<rdbms>_storage2 -> as small as possible like ora_driver_param

Hope this helps,
Dick