Hello all,

We have Baan IV c2 with Oracle 7 as the back-end.

We wan to re-organize our tables so as to reduce the fragmentation in the tables.
Steps that we are planning are -
1. Find out the current size of all Baan tables and indices.

1. Take export of all Baan Tables at Baan level (by Create Seq. dump of tables ...)

2. Drop the tables and indices at Oracle level.

3. Modify ora_storage to have INITIAL equal to the current size.

4. Import the tables at Baan level (by Create table from Seq. dump).


Are these steps OK ? Is anything else also required ?

Are there any other better and faster ways of re-organizing the tables ?

Please advise.

Baaniac,

You can also re-organize your tables by using the native Oracle IMP/EXP utilities. Usually this is much faster than Baan's bdbpre/bdbpost.

Thanks Pat for your prompt reply.

But we have UNIX at the back-end and the file size limitation is just 2 GB.

exp utility of Oracle creates just the data dumps of size > 2 GB. !!
So that option is not suitable. !!

Depending on what version of oracle you have you can do two ways of export:
- prior to 8i:
create a list of tables (with size) and then create the PAR files with a maximum of 500 tables per file or the maximum nr. of tables before the size is 2GB
- 8i or after:
use the file=file1 file2 ... and filesize=x (max. size of export file(s) in bytes) parameters within exp.

Originally posted by baaniac
Thanks Pat for your prompt reply.

But we have UNIX at the back-end and the file size limitation is just 2 GB.

exp utility of Oracle creates just the data dumps of size > 2 GB. !!
So that option is not suitable. !!

Baaniac,

Your UNIX flavour might support +2GB file systems (HP-UX, Solaris etc).

Thanks Pat and Victor for your kind consideration.

We have HP-UX 10.0 as our HP Unix server.
Our vendor has confirmed that no single file can have size > 2 GB.

When we take export at Oracle level using exp utility for tables tfgld410, tfgld106 and tfgld418 independently, then also the size of individual dump is found to be > 2 GB.

And that is the main problem.

Otherwise, exp and imp at Oracle level would have been useful.

Thanks

Originally posted by baaniac
We have HP-UX 10.0 as our HP Unix server.
Thanks

Baaniac,

I guess you are confined to using bdbpre/bdbpost then. But I would seriously consider upgrading both you Oracle and UNIX version in the near future - or at least try to convince your management of the necessity of it :)

You still have the option to export to tape or "compress on the fly". See the following link for more details: http://www.jlcomp.demon.co.uk/faq/bigexp.html

Have you considered looking in Oracle's MetaLink web resource because its free to Oracle customers. It has a wealth of information on things like how to export / import data beyond the 2Gig file limit by using concatenated files, or to/from tape in UNIX.

Also, please read "How to Stop Defragmenting and Start Living: The Definitative Word on Fragmentation" by Bhaskar Himatsingka and Juan Loaiza of Oracle. You can find it on MetaLink. It contains valuable and current defragmentation guidelines for Oracle 7 and 8.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Hi baniac,

You can use bdbpre/post, but especially if you have big tables, you will be doomed, because of long dump/loading times.

You can speed up this times a bit by setting the environment variables "ORA_MAX_ARRAY_FETCH", "ORA_MAX_ARRAY_INSERT", "RDS_FULL", "SSTS_SET_ROWS" to something like 200.


But still imp/exp would be much better. How do you do it?

simple: use unix pipes!

create a pipe
export into this pipe in the background
read from this pipe with the "split" command to get smaller files.

Works like a snap. Did it many times!

Regards

Markus

Thanks everyone for your valuable suggestions.

I would definitely try to implement your suggestions.

Yes, upgrade to higher version with higher end servers is definitely on the cards.
Till that time, we need to manage it.

Thanks again.

Baan Oracle expert recommends MAX_ARRAY_INSERT never be greater than 10 if using Oracle 7 and less than ps 7.1c.02. At Oracle 8.1.7 (latest for 5.0b) and ps 7.1c.02 you can successfully use MAX-ARRAY_INSERT=100.

Steve,

Did the Baan guy give you a reason why not use high values? In our case we were adviced to cap some of the server resource variables after stumbling on ORA-1455 errors (due to Oracle client OCI bug <8.0.6)

Only that high values priot to 7.1c.02/8.1.7 caused DB insert failures occasionally.

I have commonly used the larger values for bdbpre/post/reconfig without any problems. I have had one case where a customer had to decrease the value due to a problem with running parallel inserts into the same table when using multiple bshells as a performance booster.

Anyone have any other bad experiences with this setting?

--Jim

Hello All,

I just wanted to share my experience in re-Organizing tables which we completed last week-end.

I.
1st as was suggested, I took the export of a sample table using Oracle's EXP utility. When I checked the table definition in the export, I found that the table definition had outrageously high values of INITIAL and NEXT extents; meaning we would have run out of free space at the time of importing tables itself. !!!

I checked this with some other tables in other tablespaces. But, the result was the same.

After further investigations, it was found that export dump contained values from current storage of tables - dba_segments, dba_extents etc.

But if you look at the way internally Oracle allocates data blocks, it is way different.

So just scrapped the idea of export at Oracle level and instead, took the export at Baan level itself; but dropped tables at Oracle level.

II.
We had developed then our own utlity which got ideal size of tables and indexes from rowids and index_stats respectively.
(I have attached that utility in this posting; which we had got years back.)

III.
Based upon the sizes reported, we modified ora_storage.

IV.
When I started importing tables using Baan's session "Create Table from Dumps" (ttaad4227m000), it was reducing free space not as per our expectations and was allocating more space per table.
So I stopped the import and dropped the tables once again.

V.
Then, I tried creating tables at Baan level - session "Create Tables" (ttaad4230m00).
And, I found the free space coming to be just as per our expectations.
So just went ahead with the import.

VI.
Again, when I took export of newly imported tables at Oracle level, it showed values of INITIAL and NEXT extents different from the 1st export.

Conclusion :-
1. Oracle's export may contain very high values of INITIAL and NEXT which might be more than free space in hand.

2. Creating tables 1st in Baan and then importing data saves free space more than that in directly importing data by session ttaad4227m000.

Thanks

Baaniac,

Some comments:

As to the EXP: did you export your table with compress on? In that case it is normal that your initial extent is high because it will be as just big to have your table fit into 1 extent. You can easily change the NEXT value after the import of course.
What do you mean Oracle allocating blocks differently internally? Do you mean the variance between allocated and used space?

As to using bdbpost: did you use 'drop table' before import when running 'Create Tables from sequential dumps'. If so then it should not make any difference whether you use 'Create Tables' first or not. Both will recreate the tables with your current ora_storage settings.

Hello Pat,

1.
I did use compress option of exp utility.

Here it is a sample of how I had used :-

exp USERID=baan/baan FILE=ttdinv700001.dmp INDEXES=Y ROWS=Y CONSTRAINTS=Y COMPRESS=Y FULL=N TABLES=TTDINV700001 LOG=TTDINV700001.explog DIRECT=Y
FEEDBACK=1000

For above table, it gave INITIAL of size equivalent to 825M ... for just 2 million records. !!

When I had tried the above command with ROWS=N just to get table definition, then also I got the same results. !!

2.
Yes. I had dropped the tables before starting importing.

And still, I got different results with free space when I made use of directly ttaad4227m000 and first create tables in Baan and then use ttaad4227m000. !!

Ideally speaking, there should not be any difference between the results of the both ways; as was rightly pointed out by you.
But ...

Thanks