LOCALLY MANAGED TEMP TABLESPACES?!?!

[Sudmill]

If we set our locally managed tablespaces (tablespace TEMP) do we still need to automatically extend the datafiles/ tablespace or is this whole process automated in Oracle 9/8i ?

Not sure I fully understand what it does for you ( I know it sorts out the extents .. but can you still set/disable AUTOEXTEND????)

I cant seem to find the current total size of the TEMP tablespace in any views! Why doesnt it appear in the dba_free_extents or dba_free_space views?

Thanks in advance ! Cheers

John (Sudmill)

 

[karluk]

The resizing of the temp datafiles is still either done manually or by setting autoextend on. To set it on, use the command

ALTER DATABASE TEMPFILE '{full path of temp file}'
AUTOEXTEND ON NEXT {extension size};

Please look at the following dictionary views for information about locally managed tablespaces. These views all exist in Oracle 8.1.7. I'm not sure about any changes in 9i.

V$TEMPFILE, DBA_TEMP_FILES, V$TEMP_EXTENT_MAP, V$TEMP_EXTENT_POOL, V$TEMP_SPACE_HEADER

Locally managed tablespaces are supposed to be ideal candidates for local management. The main advantages are reduced overhead from not needing to update the Oracle catalog and no generation of redo activity.