DBAsupport.com Forums - Powered by vBulletin
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 46

Thread: advantage using dict over locally managed??

  1. #1
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    56
    Guys,


    Right now, I am only able to justify the strength of using data dictionary tablespace is just for backward compatibility which is not strong enough.

    I wonder if there are other advantages of using dictionary managed tablespaces over locally managed tablespaces.
    Sometimes I do feel confused myself over this thing especially for oracle8i and later.

    Please help. Thanks in advance.

    Ric
    Oracle freak & about-to-be DB2 freak
    Singapore

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    You should always use Locally Managed Tablespaces where possible. It is highly likely that Oracle10i will not allow dictionary management at all. The only reason to stay with dictionary management is if you're living in the dark ages. Oracle9i Rel 2 allows the SYSTEM tablespace to be local also.

    When using Oracle9i you should also use Automatic Segment Free Space Management.
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I am not so sure about Automatic Segment Free Space Management. It´s been proved by some known peep that this feature is not as good as it sounds. In fact slows down queries and DMLs in non-rac environment

    I have seen problems reported on Metalink forums about this feature as well

  5. #5
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    56
    Experts,

    Sorry perhaps I am not clear enough in explaining what happens. Right now our client is using Oracle8.1.7 due to JDE OneWorld limitation. So I am asking this in the light of 8.1.7.

    What I understand is Oracle never strongly recommended using locally managed tabs in Oracle 8i where they strongly recommended people to use one in Oracle9i. I want to know whether, from your expertise, there are problems or limitations on using this feature in Oracle8.1.7.

    Thanks.

    Ric
    Oracle freak & about-to-be DB2 freak
    Singapore

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    IMHO, you are wasting your time with Dictionary Managed tablespaces. I have been using LMTs in 8.1.5 and 8.1.7 databases for over 2 years without one single problem.
    Jeff Hunter

  7. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Yeah I agree with Jeff, you really should jump on the LMT band wagon.

    Oh and Pando, that's a bit concerning about the Auto Segment Management slowing down queries. I'm in the process of migrating a large 8.0.4 datawarehouse to Oracle 9i, I wonder how much of a difference it'll have...

    Later,
    OCP 8i, 9i DBA
    Brisbane Australia

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do a simple test to check the oracle release is bug free with the LMT.
    I found a bug in LMT in 9i.

    Create a LMT with initial and next size 16K.
    Create a table on this new tablespace.
    Insert rows using a PL/SQL block so that number of extents can reach around 30000. Then truncate the table. Wait and watch how much time Oracle needs to truncate the table.
    If the table is truncated within 5 to 10 minutes, then you are safe and you can use LMT. If not, do not use LMT.



  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by tamilselvan
    Do a simple test to check the oracle release is bug free with the LMT.
    I found a bug in LMT in 9i.

    Create a LMT with initial and next size 16K.
    Create a table on this new tablespace.
    Insert rows using a PL/SQL block so that number of extents can reach around 30000. Then truncate the table. Wait and watch how much time Oracle needs to truncate the table.
    If the table is truncated within 5 to 10 minutes, then you are safe and you can use LMT. If not, do not use LMT.
    With all due respect, this is one of the most silly tests I've ever heard of.

    First, I don't see what bug you haver found with LMT in 9i. At least not from the above description. Can you be more specific about that bug of yours?

    Second, with LMT you don't specify INITIAL and NEXT at all, you either specify UNIFORM extent size or AUTOALlOCATE.

    Third, no one in his right mind will have segments of 30.000 extents, neither in LMT nor in DMT. So what is the point of that test?

    Fourth, just out of curriosity, have you tried to truncate a table of 30.0000 extents in dictionary managed tablespace? What was the timing? What is the whole big deal with those magic facts/numbers (LMT + 30.000 extents + TRUNCATE + 5-10 minutes)?

    Fifth, even if the above test proves there is/there is not a specific bug (that I can't see) with LMT, it does not proove that "then you are safe and you can use LMT" at all - there could be tens of bugs related with LMT that this test doesn't even touch! But this is not a point - there could be tens of bugs related to DMT that noone of us is avare of too! So Sricky, don't waste your time, switch to LMT wherever possible and whenever you are sure that known LMT bugs are not applicable to your specific situation/bussiness rules.

    [Edited by jmodic on 08-27-2002 at 05:00 PM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Yes. I tested LMT and Dictionary managed tablespace after creating 30000 extents for a table.
    Oracle 9i took more than 12 hours to truncate a table. Where as in DMT, it took 10 minutes.

    A test is a test. There is no silly test in IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width