Locally Managed Tablespaces

[rabel]

Crikey! I've been using locally managed tablespaces for a while (in prior contract positions) and have never had any problems with it (under 8.1.7). In my new contract, the production DBA does not want to use locally managed tablespaces because she heard from Oracle Support that they do not encourage the use of them, and furthermore, they discourage the use of TEMPFILEs. What gives? Has anyone else had problems (with EXPORT for instance) with LMT or TempFiles? Why would Oracle Support discourage their use?

[jdorlon]

I have heard exactly the opposite....that Oracle encourages them. Maybe the DBA at your new contract heard that when Oracle was still under 8.1.5 or even earlier (non-production) version of 8i....

I use LMTs (and temporary tablespaces) in a handful of databases (8.1.6.2 and higher) and have never had a problem with them either.

-John

[pando]

LMT are lovely

[padmam]

Hi, 11th May 2001 20:33 hrs *******

Locally managed tablespaces do not use the data dictionary to track free space and extent allocation. Instead, they maintain a bitmap of the free and used blocks or sets of blocks contained within the tablespace itself. Using the bitmap eliminates the need for recursive SQL operations against the data dictionary whenever space is allocated, alleviating the potential performance bottleneck. There are many benefits to using locally managed tablespaces. Their nature makes them attractive for use as temporary tablespaces. Temporary tablespaces are used for sorts, and they often sustain high extent allocation/deallocation activity.

Locally managed tablespaces also eliminate the need to periodically coalesce free space. Extents are tracked using bitmaps, and free space is represented by a pattern of bits. Adjacent free extents will be seen as one large block of free space, because all the bits representing those extents are adjacent to each other. So automatic coalescing of free space occurs by design.

Finally, locally managed tablespaces not only make it easier for you to create a tablespace where all the extent sizes are uniform, but they also make it possible for you to enforce that uniformity at the tablespace level. No more worrying that someone will create a table with the wrong extent size, thereby fragmenting your well-planned tablespace.

Cheers

Padmam

[padmam]

Hi, 11th May 2001 20:36 hrs *******

It is a good idea to create all tablespaces (except SYSTEM of course ) as locally managed in 8i. The only caveats are to keep the number of extents per object well below (db_block_size / 16) - 7, and to avoid intensive use of the DBA_SEGMENTS and DBA_EXTENTS data dictionary view families.

Cheers

Padmam

[rcaballe]

In 9i LMT will be the default option of new tbs!!
Figure out if they are good, and the new option of pctused self-managed too.

[uday]

padmam,

can you explain your above posting in detail... about the (dbblocksize -16 ) /7

uday

[rcaballe]

Yeah, I wanna know too, because there is a doc that says you will be fine with objects below the 1024 extents mark.

[padmam]

Hi, 12th May 2001 16:05 hrs *******

The answer to your question is detailed in the second link.This second link was mentioned in the first oracle link from which i got it.

Note that oracle in its first link has mentioned about the steve Adams independent view on this subject plus link to his site.

http://www.oracle.com/oramag/oracle/...ml?o60o8i.html

http://www.ixora.com.au/tips/creation/extents.htm

http://www.ixora.com.au/q+a/0104/11095147.htm

http://www.ixora.com.au/q+a/space.htm

cheers

Padmam

[pando]

I wonder how he got the formula but anyway is this saying that number of extents of a segment should not be bigger than 505 in 8K db block sizes? I mean this is not practical, when I create LMT I always use 128K extent size if I limit maxextents to 505 my segment can only grow to 50MB more or less? And if this is the case how on the earth can I put large tables in 128k tablespaces, I could create tablespace for large, medium sand small objects but hey I thought the idea of LMT was to get rid of this management process