Hi,
I have BaanERP5b and Oracle8.0.6. And I want to upgrade to Oracle8i. I think I have to change tabledef, ORACLE_HOME, ORACLE_SID, but...

Can I reuse the tablespace? or I have to export and import all the tables (000,...),...

Yes, the existing tablespaces(datafiles) can be reused. There is no need to perform an export/import - unless there are other reasons for doing so.

Once the database is upgraded, you just need to amend your ORACLE_HOME path within Baan. - Maintain Database/Table Definitions (tabledef6.2)

ORACLE_SID would remain unchanged.

I think, it is better to create a new database. So you can use the "local tablespaces" from 8i. They are much better then the old ones.

Hi wfitsh ,

In which regards are the "locally managed tablespaces" better for you? Did you do actually any systematic performance tests to see any inprovements?

Most people I know, just use them for temp and rbs.

They have some considerable disadvantages in regards to your flexibility to influence storage.

I would appreciate any real life "performance" experience for the locally managed tablespaces, not just Oracle marketing.

Anybody has thos experience and did some proper measurements?

Regards

Markus

You've heard about fragmentation of tables in the tablespaces?

You should use locally tablespaces with different unified extents for different "table-types". Small tables (like parameter-tables) you place in 128K-extent-tablespaces, greater tables you place in 4M-extent-tablespaces and large tables you should place in 128M-extent-tablespaces.

PCT_INCREASE is history....

Great,

We are not using pctincrease since 1962 :-)

Setting up several tablespaces with different storage defaults was always possible and a lot of DBAs are doing this.

So where is the advantage?

If there is no performance advantage, than why use a new and most likely therefore buggy feature?

All extents in an database are stored in the views sys.uet$ and sys.fet$ in the system-tablespace. The extents in a locally managed tablespace where stored as a bitmap in the tablespaces itself. These bitmaps are faster to access.

The SMON read these two views permanent to look for extent fragmentation and to coalesce tablespaces. (This don't affect tablespaces with PCT=0 or locally tablespaces.)

So, i prefer the locally managed tablespaces, because they are easier to handle and a little bit faster. I had a lot of trouble with dictionary tablespaces. I won't use the dictionary managed tablespaces anymore.

So i convert all Oracle8.0 databases to Oracle8i with locally managed tablespaces....

PS: I've never heard someone talk about the buggy new feature "locally managed tablespaces"... So try it, Markus... ;-)

Sorry for being a typical cynic.

Everything new somehow ends up as being declared "buggy" in my mind, before I see it working for some time.

Somehow the reverse of "not guilty unless proven", but seems to be appropriate for most IT decisions.

Sorry again, I will try it.

Markus

Here's a portion of an article by Gaja Krishna Vaidyanatha, titled "MYTHS & FOLKLORE ABOUT ORACLE8I PERFORMANCE TUNING" and author of "Oracle Tuning 101" on locally managed tablespaces:

THE LOCALLY-MANAGED TABLESPACE VS. FRAGMENTATION & REORGANIZATION MYTH:
Locally Managed Tablespaces (LMTs) eradicate all kinds of space fragmentation problems, thus eliminating the need for table reorganization.

FACT:
LMTs eliminate file-level free space fragmentation issues. They also eliminate some object-level fragmentation issues related to extents of different sizes. LMTs manage the extents of an object by using either UNIFORM or AUTOALLOCATE options for tablespace space management. The space management for LMTs is done using a bitmap, in the first extent of the tablespace. Not curing the disease (PCTFREE and PCTUSED) will cause block-level and row-level fragmentation even for objects stored in LMTs. And in the bigger scheme of things, block-level and row-level fragmentation is what counts against performance.