Temp DMT LOGGING mode

[D Maverick]

Hi,

Is there any effect in setting a Dictionary-Managed Temporary Tablespace to NOLOGGING or LOGGING mode? If there is any, what's the effect on the tablespace?

I personally think that temporary tablespaces whether DMTs or LMTs is always in NOLOGGING mode. Please correct me if im wrong.

Just a thought that cross my mind. Will Oracle allow a permanent DMT with permanent objects to be converted to a temporary DMT?

Thanks

[rohitsn]

Hi,
Bacically LOGGING and NO-LOGGING mode defines that whether all the activities done to the objects into that tablespace ,should be written to redo-log files or not.
For ex.If you have a tablespace USERS and the mode of the tablespace is in LOGGING mode and you have a table called EMP into that tablespace.Now whenever you perform any DML activity on the table such as Insert/Update/Delete,then all the activities will be recorded into the redo-log files.
Putting the tablespace in NOLOGGING mode,will not generate any redo entries for the DML's performed on this table.Some redo will be generated only when extent allocation or de-allocation is done for this object.
Yes,temporary tablespace whether DMT's or LMT's is always set to NOLOGGING mode since these tablespace is only needed for sorting operations and the data is flushed off as soon as you shutdown the database.So there is no need for this kind of tablespace to LOGGING mode.
No ,I dont think so that Oracle will allow to convert a Permament Dictionary Managed tablespace to Temporary tablespace,since the main function of Temporary and Permament tablespace will lose.

Regards,
ROhit Nirkhe,Oracle/Apps DBA,OCP 8i
oracle-support@indiatimes.com

[D Maverick]

very well said =) Many Thanks

[ggnanaraj]

Originally posted by D Maverick
Will Oracle allow a permanent DMT with permanent objects to be converted to a temporary DMT?

Thanks

Yes, possible...

To change an existing permanent dictionary-managed tablespace to a temporary tablespace, use the ALTER TABLESPACE statement. For example:

ALTER TABLESPACE tbsa TEMPORARY;

Check this out...
http://download-east.oracle.com/docs.../doc/index.htm

HTH.

[ccastaneda]

Originally posted by D Maverick
Will Oracle allow a permanent DMT with permanent objects to be converted to a temporary DMT?
Thanks

How ggnanaraj said.. its possible with the ALTER TABLESPACE statement..

Just don't forget that you cannot alter tablespace to type temporary if any objects exists in the tablespace..