I have seen a counter argument..
</code> http://www.ixora.com.au/q+a/0104/11095147.htm <code>
from Steve
states this:
---- start of quote ----
The only drawback with locally managed tablespaces is that used-extent information is not kept in the data dictionary. It must be read from the segment header blocks (and additional extent map blocks if any) whenever it is required, including for queries against DBA_SEGMENTS and DBA_EXTENTS. If a tablespaces with a large number of mostly small segments is locally managed rather than dictionary managed, then access to these views can cause a lot more physical I/O and thus impact the cache retention of user data. And if the segments are mostly constant in size then the risk of the tablespace contributing to ST enqueue contention if dictionary managed is low. Thus there is little motivation to make such a tablespace locally managed and a minor performance risk in doing so, hence my comment.

Why do you prefer that? What is the advantage of the DD managed space over LMT in this situation?

I would have a preference for dictionary management for any tablespace with a large number of mostly small constant sized segments. SYSTEM fits that description, but RBS and TEMP certainly do not - TOOLS and USERS may do, but probably do not.

--- end of quote

What do you think Tom?

Hi,
We created LMT for our application. We used uniform allocation of 1M. Since the application has 3000 tables with zero rows (and these tables are very rarely used), we are literally wasting 3000M space. And hence we decided to convert the tablespace back to DMT. After converting the tablespace to DMT (using dbms_space_admin), however, now even if I create a new object with initial extent size of 16K, it still gets allocated 1M.

Is this the expected behaviour? Or is there something else I need to do so that the object gets allocated the space I specified?

Thanks

Just an additional note - I'm using LMT for SYSTEM in 9.2 w/ no problems so far

Tom,
What is the suggested value for using a LMT with uniform extent management for a datafile size of 2GB without wasting space i.e., how much should be reserved for bitmap management? Do we also need to reserve one block, as with traditional file sizing method i.e, file size +one block?

As for extent size -- you need to know the size of the objects you are going to put in there.  

From Oracle 9i Release 2 , SYSTEM Tablespace can also be a Locally Managed Tablespace .. infact it is the defaut.. time to say good bye to Dictionary Managed Tablespace has truly arrived !!!

OrA

From Oracle 9i Release 2 , SYSTEM Tablespace can also be a Locally Managed Tablespace .. infact it is the defaut.. time to say good bye to Dictionary Managed Tablespace has truly arrived !!!

OrA

"I do not frequently query DBA_SEGMENTS and DBA_EXTENTS -- most systems I would think would not either."

The main culprits here tend to be monitoring tools. Heaps of them go thundering through dba_segments every 'n' mins so they can pop up a little flashing red light on your screen. Enterprise Mgr springs to mind :-)

(Oracle8i.7 EE Release 8.1.7, Solaris )
What will you tell about RBS tablespace in LMT. When I tried to create rollback segment in such tablespace than I accounted with :
1. SQL>  create tablespace ROLLBACKS logging datafile '/opt/home/pluto/oracle8/dbs/oradata/oracle8/rollbacks.dbf'
  2   size 10M reuse extent management local;

Tablespace created.

SQL> alter tablespace ROLLBACKS online;

Tablespace altered.

SQL> create rollback segment  R01  tablespace ROLLBACKS
  2   storage (initial 50K next 50K minextents 20 maxextents unlimited);
create rollback segment  R01  tablespace ROLLBACKS
*
ERROR at line 1:
ORA-25151: Rollback Segment cannot be created in this tablespace

2. 
SQL>   create tablespace ROLLBACKS logging datafile '/opt/home/pluto/oracle8/dbs/oradata/oracle8/rollbacks.dbf'
  2   size 10M reuse extent management local uniform size 128K;

Tablespace created.

SQL>  alter tablespace ROLLBACKS online;

Tablespace altered.

SQL> create rollback segment  R01  tablespace ROLLBACKS
  2   storage (initial 50K next 50K minextents 20 maxextents unlimited);

Rollback segment created.

SQL>  select * from dba_rollback_segs;

SEGMENT_NAME  TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS STATUS
------------------------------ ------ ------------------------------ ---------- 
SYSTEM          SYSTEM            51200         51200           2        ONLINE         
R01            ROLLBACKS        1024000         131072          1        OFFLINE R02               RBS             10240         51200          20        ONLINE    

I see that new rollback have 1 extent instead 20 against
R02 which is in DML with 20 extents (as usually).
Thanks. Victor 

Well, the first one wasn't UNIFORM extents, it was system allocated (not all extents would be the same size).  Since rule #1 of all rollback segments is "use the same sized extent", we don't permit you to use that tablespace.  The second one works because you have uniform extents.

As for what you see -- it is a side effect of using a locally managed tablespace whereby the STORAGE parameters are interpreted "nicer".

Look -- you asked for 20 50k extents.
That is 1,000k of data.
Depending on your blocksize, that'll be rounded a bit.

That request gets converted into an initial size (1m in your case). So the storage parameter ( initial 1m ) is the same in this LMT as ( inital 50k next 50k minextents 20 ) -- maxextents unlimited is a truly bad idea for rBS and we limit you to 32k extents max.

So, we created the RBS with 1m of data which satisfies your request for 20 50k extents.  If you looked at DBA_EXTENTS and counted the number of extents, you would see 8.

Here is an example on my system:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create tablespace rbs_demo
  2  datafile '/tmp/rbs_demo.dbf'
  3  size 10m reuse
  4  extent management local uniform size 128k;
Tablespace created.

<b>same as yours..</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> create rollback segment rbs_demo_01
  2  tablespace rbs_demo
  3  storage (initial 50k next 50k minextents 20 maxextents unlimited);
Rollback segment created.

<b>same create statement...</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec print_table( 'select * from dba_rollback_segs where segment_name = ''RBS_DEMO_01'' ' )
SEGMENT_NAME                  : RBS_DEMO_01
OWNER                         : SYS
TABLESPACE_NAME               : RBS_DEMO
SEGMENT_ID                    : 7
FILE_ID                       : 12
BLOCK_ID                      : 9
INITIAL_EXTENT                : 1146880
NEXT_EXTENT                   : 131072
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 32765
PCT_INCREASE                  : 0
STATUS                        : OFFLINE
INSTANCE_NUM                  :
RELATIVE_FNO                  : 12
-----------------

PL/SQL procedure successfully completed.


<b>Here, I got 1.1meg -- it rounded different given my block size, I have 8k blocks so it is really 20*56k = 1,146,880.  Now, to get 1146880 bytes, we needed 9 * 128k extents..</b>



ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*)
  2    from dba_extents
  3   where segment_name = 'RBS_DEMO_01'
  4  /

  COUNT(*)
----------
         9

<b>which is exactly what I got.

So, this is working exactly as expected here.  With LMT's -- don't even BOTHER with

NEXT
PCTINCREASE
MINEXTENTS
MAXEXTENTS <<<=== totally ignored

Since we'll just use initial next pctincrease and minextents to figure out what the true INITIAL allocation (not really initial extent, initial ALLOCATION) will be.  It would be much much more understandable for all of us humans just to put an initial allocation rather then make us do the rounding up and math in our heads.</b>



ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop rollback segment rbs_demo_01;

Rollback segment dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop tablespace rbs_demo;

Tablespace dropped. 

Tom,

We get the below error:


Here's the error:

SVRMGR> create public rollback segment rbs1 tablespace th_rbs01
storage (initial 1M next 1M minextents 50 optimal 50M) 2> ;
create public rollback segment rbs1 tablespace th_rbs01
*
ORA-01552: cannot use system rollback segment for non-system tablespace 'TH_RBS01'


Currently we are using 8.1.7.3 version? How to overcome that if we want to use LMT for Rollback?

Tom,
I am a DBA and an outside consultant is telling and convincing one of our application developers that DMT's out perform and are better then LMT's. He is making us go from 9.2.0 to 8.1.7.4 because he has it stuck in his head that LMT's are evil. I know that is not true because we have done all of our own testing and our standard is to use LMT's. They perform the same if not better than DMT's.
Can you give me more proof to show this consultant the light.
Thanks
Rich

In LM tablespace RB (1M extent) I created rb segments.
e.g.
CREATE PUBLIC ROLLBACK SEGMENT rbs_20
TABLESPACE rb
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 20
MAXEXTENTS 2500
OPTIMAL 20M );

I wrote procedure that creates script like build_db.sql with ddls to create rollback segments. What I would like to get is same command as in example.
I used as source v$rollstat, DBA_ROLLBACK_SEGS.
The result was:
CREATE PUBLIC ROLLBACK SEGMENT rbs_20
TABLESPACE rb
STORAGE (
INITIAL 20M
NEXT 1
MINEXTENTS 1
MAXEXTENTS 32765
OPTIMAL 20M );

I could correct and calculate MINEXTENTS as
MINEXTENTS=INITIAL(20M)/DBA_TABLESPACES.INITIAL_EXTENT(1M)

But I do not see how I could find correct MAXIXTENTS = 2500
Any help appreciated.

Hello Tom,
I have same problem as richard. One of my customer is stuck with dictionary managed ts.We migrated from 8.0.5 to 9.2. now all the tablespaces are LM.There is one table in which rate of insertion of records is very high.Now the application is not performing as it used to on 8.0.5.When the end user enters the data and gives print command, it hangs for a while as if its waiting for data to commit.Sometimes a blank page is printed.I checked all the tuning everything is fine. And the customers keeps on telling me from the start that its because the system cannot allocate the extent fast and he wants dictionary managed TABLE. Can you throw some light on this

Hi Tom,

Hope you can answer this one (timewise, that is!), as I am trying to get this working. I will be reading other sources while i wait for an answer if you are too busy....

But...I want to switch over our Backup Database (Standard 8.1.7.4) to Read-Only and report off it. As far as I can tell, I only need to put it into Read only mode, which I have done, and give it a temporary tablespace to sort with.
But when I create the TS, I get this:

Connected to:
Oracle8i Release 8.1.7.4.1 - Production
JServer Release 8.1.7.4.1 - Production

SQL> select * from dual;

D
-
X
[proof it is read-only and up and running]

SQL> ed
Wrote file afiedt.buf

  1  CREATE TEMPORARY TABLESPACE tbs_1 TEMPFILE 'F:\ORA_TMP\TMP_SORT_01.dbf'
  2*      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512M
  3  
SQL> /
CREATE TEMPORARY TABLESPACE tbs_1 TEMPFILE 'F:\ORA_TMP\TMP_SORT_01.dbf'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace
'HTSMALL'
ORA-06512: at line 10

I'm slightly mystified by this error message.

It's all I see I have to do to get this into Read-Only mode. Am I missing something?

Your help is appreciated. You are the BEST.
Mark 

Hi Tom,

No DDL triggers in play here i can see. Here is more info as I think I have solved this, but cannot actually try it until later. Sorry for my (justified) paranoia...

This is my RPT_TEMP temporary tablespace on LIVE (from DBA Studio 'Show Object DDL'):

CREATE TEMPORARY
TABLESPACE "RPT_TEMP" TEMPFILE 'E:\ORA_TS\RPT_TEMP.DBF' SIZE
257M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32768K


And this is the one on the STAND-BY:

CREATE TEMPORARY
TABLESPACE "RPT_TEMP" TEMPFILE EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 32768K

As you can see, The Stand-By has no tempfile in this tablespace. So, I think I need to add a tempfile to this tablespace on the Stand-By:

ALTER TABLESPACE "RPT_TEMP"
ADD TEMPFILE 'E:\ORA_TS\RPT_TEMP.DBF' SIZE 263168K

Also, I believe my other issue will be the USER has to have RPT_TEMP assigned to them as the TEMPORARY tablespace, correct?

This looks like it should work.

Thanks again.

Hi again,

Upon further review, it appears there are DDL triggers involved here. Do these qualify?

CREATE OR REPLACE TRIGGER HT4.AUDIT_DDL_CHANGES
AFTER CREATE OR DROP OR ALTER ON SCHEMA
BEGIN
INSERT INTO dll_audit_log
VALUES(
SYSDATE
,SYS_CONTEXT('USERENV', 'SESSION_USER')
,SYS_CONTEXT('USERENV', 'OS_USER')
,SYS_CONTEXT('USERENV', 'HOST')
,SYS_CONTEXT('USERENV', 'TERMINAL')
,ora_sysevent
,ora_dict_obj_type
,ora_dict_obj_name);
END;

AND ...

CREATE OR REPLACE TRIGGER HT4.DDL_TRIGGER
BEFORE CREATE OR ALTER OR DROP
ON SCHEMA

DECLARE
oper ddl_log.operation%TYPE;

BEGIN
SELECT ora_sysevent
INTO oper
FROM dual;

IF oper IN ('CREATE', 'DROP') THEN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, ora_client_ip_address, NULL, USER, SYSDATE
FROM dual;
ELSIF oper = 'ALTER' THEN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, ora_client_ip_address, sql_text, USER, SYSDATE
FROM v$open_cursor
WHERE UPPER(sql_text) LIKE 'ALTER%';
END IF;
END ddl_trigger;

Will disabling these and applying logs to stand by be enough, or do I have to drop them?

Regards,
Mark

Thanks!

Excellent Tom, thanks.

I disabled the triggers on LIVE, applied the logs, put the DB in read-only, and was able to create the tempfile now no problem.

Do you see any problems if I enable the triggers on the LIVE DB now with respect to reporting off the read-only, and I apply the logs again tomorrow?

I'm thinking the trigger does not fire unless I specifically try a DDL operation on that DB. Since its in Read Only, I won't be able to.

Thanks again,
Mark

Yes... I can check the OPEN_MODE of V$Database...

Thanks!

tom,

Do you have any mesurable tests that prove LMT temp tablespaces are better than DMT temp tablespaces? Thus far, I have only used LMTs and I do kow their benefits, including the skipping of tempfiles during RMAN backup. But my current client uses DMT for SYSTEM,TEMP and RBS. They agree LMT "maybe good", but they want proof to get approval from business users. Justify the effort to move these to LMT. I did search your site for test cases but could not find any.

I did a trace using 10046, created global temp tables on DMT temp tablespaces, inserted huge sets of rows, and wrote SELECTs to use sort(temp) space. I did see the temp usage go up considerably. However, when I viewed the results using TKPROF, I could not find the usage of FET$ or UET$ go up considerably to justify my recommendation.

I could not post the test scripts because of security issues. Any pointers for deterministic results appreciated.

thanking in advance.

It has been a while since the original question. From 10.2 onward, the system tablespace defaults to locally managed. Would you recommend converting a system that has been upgraded all the way back from version 8 and is now on 10.2.0.3 to migrate to a locally managed SYSTEM tablespace?
(incidentally I am typing straight text and the formatting looks loopy)

First and foremost, LMT tablespaces decrease fragmentation to the acceptable level.
Second, for the dictionary managed tablespaces SMON
occasionally runs coalescing job which can be rather
expensive. It is even more expensive if the tablespace in question is SYSTEM tablespace because of the locks on the
underlying objects. Locks are never good for concurrency.
Locks on the system objects are especially bad.
Third, you also pay a performance penalty: dictionary
managed tablespaces are managed via SQL commands versus
LMT tablespaces which are managed by a simple bitmap
manipulation. Last but not least is the possibility of the strange bugs. DMT code is old, rarely used and by using DMT
when nobody else in the world is, exposes you to potentially nasty and disastrous bugs.

Unfortunately, not ever dropping or truncating in system tablespace is a myth. There are quarterly patchsets which frequently modify data dictionary and do drop/create/rebuild objects. Also, major patch set installations are doing the same thing. SMON coalescing is not a big issue, but I do recollect problems with it. We had to turn it off on an OPS database with 4 HP 9000 nodes. There was an event which turned that feature off. Of course, on an 8i database, all tablespaces were DMT.
Main objection to DMT is SQL. You are using SQL to manage extents. That is much slower then bitmap manipulation. As for the DMT code, I cannot prove it because I do not work for Oracle, but all I see these days are LMT. I haven't worked with DMT in at least 2 years. If a database was migrated from 8i to 9i, the machine it was on has, most likely, already been decommissioned. During the move to the new machine, all tablespaces were moved to LMT. No problems there. Oracle 9i came out in 2003, databases were converted to 9i in 2005 and then converted to 10g in 2007. I haven't seen a DMT since 2005.

Hi Tom,
   We work on clarity(niku) application. I have a table ATT_PMT which is truncated daily and then loaded with sqlldr. On one fine day when the developer complained that he could not insert the data, i could see Enqueue (ST) happening on the table. So i moved the table ATT_PMT from DMT to a LMT tablespace and then unusable indexes were rebuilt on a LMT tablespace. 
       After moving the table a query which was using the Index of ATT_PMT table had started doing Full-Table-Scan. I analyzed the table & indexes to see if it starts using the index. But it did not use it. Again i moved back the table and index to a DMT, and guess the index was used.

 Any particular reason behind this?

Thanks for the answer. 

Version : 9.2.0.7
OS : SUN 5.8 sun4u sparc 
Optimizer : Choose