Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: LMT and Siebel/Peoplesoft?

Re: LMT and Siebel/Peoplesoft?

From: Andy <enzoweb_at_hotmail.com>
Date: 20 Jan 2002 17:18:17 -0800
Message-ID: <8d4033cd.0201201718.661c701@posting.google.com>


I came aboard a Siebel implementation and suggested Locally Managed Tablespaces, but one of the guys who has been here longer said he tried it and it slowed down the data load significantly. I haven't tested it for myself but took his word for it. They are using the Siebel load process, not import.

One thing we have been using successfully is to use Transportable Tablespaces to move the data around. This is a lot quicker since it doesn't export/import the data but just the structure.

Can't help with the truncate though, although I accidentally hit on what may be a 'cheat'; we have been dropping the tablespaces (can't have 2 tablespace names the same with transportable) and I have been truncating/dropping tables then dropping the tablespace, then deleting the data files. I accidentally deleted a datafile before dropping the tablespace, so stopped and restarted the database. Received a 'unable to identify file" message, so did an 'alter database datafile offline drop' on the offending file. This enabled me to start the database and then drop the tablespace. I haven't tried it, but it may be quicker to write a script to do an "alter .. offline drop;" of all the datafiles, delete the files, then stop/start the database and run the script and finally drop the tablespace.

"Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message news:<3C4A963E.72AB93F5_at_exesolutions.com>...
> They make sense only if you value performance and dislike fragmented
> tablespaces. Otherwise just ignore them.
>
> Daniel Morgan
>
>
>
> Vikas Agnihotri wrote:
>
> > Oracle 8.1.7, Solaris 8
> >
> > Do LMTs make sense for 3rd party apps like Siebel & Peoplesoft?
> > Suppose it is a dedicated database only for these apps.
> >
> > I see that they have ~3000 tables and 13000 (yes, 13000) indexes. 80%
> > of the tables are empty to pretty small (~100K). The remaining range
> > from 1MB to 15MB.
> >
> > I created a LMT with a uniform extent of 100K.
> >
> > The problem is that for a upgrade process the are doing, they
> > frequently want me to export/truncate/import all the tables.
> >
> > There is not a single FK in all the tables, so what I do is truncate
> > all the tables and 'imp ignore=y'
> >
> > The larger (20%) of the tables have 100s of extents. So, even my
> > simple 'truncate' script takes ~40-45 minutes to run!
> >
> > [I even tried 'truncate table reuse storage' to ensure that it doesnt
> > deallocate/allocate extents unnecessarily]
> >
> > Does LMT have something to do with this? How can I improve my import
> > performance here?
> >
> > In general, if anyone out there has dealt with these Siebel/Peoplesoft
> > type of apps, could you share your experiences? Do you use LMTs?
> > Needless to say, these companies' tech support is no help. Their
> > recommendations smell of Oracle 7.x days. If I told them I was using
> > LMTs, they probably wouldnt even approve of it!
> >
> > Thanks
Received on Sun Jan 20 2002 - 19:18:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US