Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: switching to locally managed tablespaces
Yet more questions...
I've been playing with this import by dropping the main data tablespace and recreating it with various uniform extent sizes, on my last go around I created a 4 gig tablespace and it ended up 95% full with 3.7 gig of data, the prod database the export file came from has this same tablespace 3.3 gig with 2.7 gig of data. When I created the tablespace with extents set to auto allocation the data took up about the same space as in the prod db.
How much and what impact does the export file have on importing into a LMTS?
Should I be altering my export params beforehand? ie compress=x?
I am currently re importing the data with the tablespace set to uniform 1 meg extents, I'll see how that goes. Incidentally the largest table in this tablespace has a long raw column had high row chaining in 7.3.3 that I could not seem to "fix" by rebuilding the table with higher pctfree etc.., in an earlier test migration to 8i with DMTS the row chaining improved considerably with the same settings.
TIA Howard J. Rogers wrote:
>
> "Glen A Stromquist" <glen_stromquist_at_nospam.yahoo.com> wrote in message
> news:ssoU8.55635$vo2.3051371_at_news2.telusplanet.net...
>> Howard J. Rogers wrote: >> >> > Hi Glen, >> > >> > Go for it: LMTs are good things, and we like them lots. >> > >> > Under no circumstances use that procedure Oracle thoughtfully provided
>> > convert, however. It doesn't do an awfully good job: you end up with
>> > extent sizes if you started with them, and half the benefits of LMTs >> > therefore just disappear out the window. >> > >> > If it were me, and if you had sufficient disk space (and given your >> > reported size, I think you probably do), I would instead be inclined to >> > create a bunch of new LM tablespaces, and simply issue the 'alter table
>> > move tablespace Y' command. When everything has been moved, you can >> > then just drop the old DM tablespaces, as they'll be empty. You have >> > then to rebuild all indexes, which is a slight bummer, but only a >> > slight one. >> > >> > This way you don't have to rely on export/import more than you need to. >> > The data stays available in the database. You use simple SQL commands.
>> > also get complete control over where to move things (export/import, you >> > have to fiddle quite hard because of its propensity to put things into >> > tablespaces with exactly the same names as they came from). >> > >> > If you do the conversion, remember to create a range of extent-sized >> > tablespaces (64K, 512K, 1M, 8M, 64M), and move the right table(s) into
>> > right tablespace. >> > >> > Best of luck, >> > HJR >> > >> > >> > >> > >> > >> > "Glen A Stromquist" <glen_stromquist_at_nospam.yahoo.com> wrote in message >> > news:RxnU8.55176$vo2.3045819_at_news2.telusplanet.net... >> >> >> >> On the database that I am upgrading from 7.3.3 to 8.1.7 I just created >> >> DMTS's on the 8.1.7 instance then did my import. all went ok, but
>> >> there is no mad panic to get this into production I was thinking on >> > blowing >> >> the tablespaces away, re creating the tablespaces as locally managed, >> >> then redoing the import. >> >> >> >> Any particular gotcha's I should look out for here? >> >> >> >> This DB is less than 4 gig, so I am not talking a huge database here. >> >> >> >> Is this the best method?, or should I use the Oracle supplied package
>> >> convert the tablespaces from DMTS to LMTS instead? >> >> >> >> >> >> TIA >> >> Thanks Howard - >> >> I opted to "blow away" the old tablespaces and are in the process of >> recreating them as I am writing this, since I have to refresh the DB with
>> fresh export/import from the current prod db before I put it this one >> into production it is not a big deal to have the data available at all >> times. The only thing I am waffling on is whether to give them uniform >> extents & what size if so, or leave it to auto allocate.