RE: locally and dictionary managed TEMP tablespace

Hi,

You cannot use the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure to migrate a temporary tablespace that is dictionary-managed to a locally managed tablespace. You must drop the dictionary-managed temporary tablespace and re-create it as locally managed.

1. Start SQL*Plus (you must have CREATE TABLESPACE privilege)
2. Find out the name of your dictionary-managed temporary tablespace:

SELECT tablespace_name, extent_management FROM dba_tablespaces
WHERE contents = 'TEMPORARY';

3. Drop the temporary tablespace

DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES

4. Create a locally managed temporary tablespace

CREATE TEMPORARY TABLESPACE <temp_tablespace_name> TEMPFILE '<file_name>' SIZE 2M
AUTOEXTEND ON; 5. Verify your newly created locally managed temporary tablespace with the following queries:

SELECT tablespace_name, extent_management FROM dba_tablespaces
WHERE contents = 'TEMPORARY';

SELECT tablespace_name, file_name
FROM dba_temp_files;

HTH, Quamrul Polash
To: nileshkum_at_gmail.com
CC: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org Subject: Re: locally and dictionary managed TEMP tablespace From: Mayen.Shah_at_lazard.com
Date: Sat, 9 May 2009 09:04:51 -0400

Since it is temp tablespace why not
simply drop and create new one?

"nilesh kumar" <nileshkum_at_gmail.com>

Sent by: oracle-l-bounce_at_freelists.org May 09 2009 09:00 AM

Please respond to

nileshkum_at_gmail.com

To
ORACLE-L <oracle-l_at_freelists.org>

cc

Subject
locally and dictionary managed TEMP
tablespace

Hello DBA's Good Morning,

Hope Everyone is doing good.

I have gone crazy with a question, I dont have a requirement to do so. Just wanna know.

Is it possible to convert a temporary tablespace which is locally managed to dictionary managed and vice versa in 10g.

As per my knowledge we cannot convert a dicitionary managed temp tablespace to locally managed using dbms_space_admin package. Since it would require the tablespace to be online, read write and permanent one. And also the best of all option would be to drop and re create.

I searched but could'nt get a doc which would explain this.

Thanks

Khajwania

--
http://www.freelists.org/webpage/oracle-l