LOCALLY MANAGED & DICTIONARY MANAGED TABLESPACE

[Ablakios]

Hi, All
We have dictionary managed tablespaces on 8.1.7 (NT platform) with database in archivelog mode, and would like to convert or simply create new locally managed tablespace for only TEMP, RBS. INDEX if not ALL.

Our temp tablespace is almost out of space. Don't really want to add any more data file but recreate a locally managed TEMP tablespace along with the RBS, INDEX instead.

What do you advise?
Mix DICT Managed and LMT? If yes . what do I really get out that .....performance wise?

What is the best way and simple way of handing this?

Please advise.
Thanks

[julian]

Start with the TEMP tablespace. Drop your DMTS and create a LM TEMP TS using the tempfile keyword. Do you know how to do that?

[Ablakios]

Julian,

I will appreciate the " HOW TO" from the expert.
Now remember that we are runing on archivelog mode.
What are the steps or queries before I drop the online temp tablespace?

Do i check for sort before?
Do I put the tablepace offline?
Do I drop tablespace (name) including contents?
Please advise.

[julian]

Originally posted by Ablakios
Julian,

I will appreciate the " HOW TO" from the expert.

If possible (in order to easilly drop the TEMP TS) bounce the instance and run:

DROP TABLESPACE TEMP;

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/dev/vx/rdsk/datadg/disk26' 
SIZE 1000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Of course, replace '/dev/vx/rdsk/datadg/disk26' with your filename.

[tycho]

Hi Ablakios,

You can use the output off the following query to change all your tablespace (except the system and temp tablespace) to locally managed.

select 'exec dbms_space_admin.tablespace_migrate_to_local('''||name||''');'
from v$tablespace
where name not in ('SYSTEM','TEMP')
/

HTH

Tycho

[TimHall]

Remember, there are issues with:

dbms_space_admin.tablespace_migrate_to_local

Although it makes the tablespace locally managed it does not affect the extent size management. You cannot make the migrated TS take on the AUTOALLOCATE or UNIFORM SIZE attributes.

If you can it is better to recreate the tablespace. If you can't do this the migration is the next best thing.

Cheers

[tycho]

Hi Tim,

In his question ablakios writes.

-------------------------
What is the best way and simple way of handing this?
-------------------------

DBMS_SPACE_ADMIN looks simple enough.

In future he can maybe drop & recreate or move his tablespaces if the system has enough disk space to cope with the extra space needed.

HTH

Tycho

[Ablakios]

Hi Tim and Tycho
Thank you both for the prompt response.

Tim you said
dbms_space_admin.tablespace_migrate_to_local

makes the tablespace locally managed it does not affect the extent size management. You cannot make the migrated TS take on the AUTOALLOCATE or UNIFORM SIZE attributes.

I f it does not take on the AUTOALLOCATE or UNIFORM SIZE what the benefit for making it LMT?

Tycho you said


You can use the output off the following query to change all your tablespace (except the system and temp tablespace) to locally managed.

select 'exec dbms_space_admin.tablespace_migrate_to_local('''||name||''');'
from v$tablespace
where name not in ('SYSTEM','TEMP')

Questions
1) The temporary tablespace is most fragmented among the rest of the tablespace and that one the reason why I would like to have LMT. why do you say except system & temp tablespace.

2) I think I want to go by your route executing dbms_space_admin.tablespace_migrate_to_local('''||name||''');'

Does ("'|| name|| '") here refers to individual tablespace name?
Can you give me an example?
Do I have to run this script one by one for all the tablespace that I want to convert into LMT?
Please advise.

[tycho]

Hi Ablakios,

First things first.

It is not yet possible to have a locally managed system tablespace in oracle (version 9iR1).

A tablespace containing temporary segments can only be locally managed with the uniform allocation type.

For this you have to drop and recreate the tablespace.

The query I gave you gives you output like this:

SQL> @c:\query\tstolocal

'EXECDBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('''||NAME||''');'
======================================================================
exec dbms_space_admin.tablespace_migrate_to_local('RBS');
exec dbms_space_admin.tablespace_migrate_to_local('DATA');
exec dbms_space_admin.tablespace_migrate_to_local('INDX');
exec dbms_space_admin.tablespace_migrate_to_local('TOOLS');

You have to copy the output on your sql prompt.

That will migrate your tablespace to locally managed.

HTH

Tycho

[TimHall]

Originally posted by Ablakios

If it does not take on the AUTOALLOCATE or UNIFORM SIZE what the benefit for making it LMT?

You still get the benefit of reduced dictionary contention since extent allocation/dellocation is controlled within the tablespace using bitmaps.

Since your main focus is the TEMP tablespace you will have to drop and recreate it.

When you move to 9iR2 you will be able to migrate the SYSTEM tablespace, but after looking at the list of restrictions I think it's better to create a new instance from scratch

tycho: I agree the dbms_space_admin option is the easiest route for most tablespaces.



[Edited by TimHall on 05-22-2002 at 10:15 AM]