Locally managed tablespace & NEXT extent sizing

I am kind of getting lost here.

I know that when you create a table with the locally managed tablespace, the extent allocation in either UNIFORM or of AUTO. But when I was playing with this, I noticed that when to convert a tablespace from LOCALLY managed to the dictionay managed one and then back to the LOCALLY managed tablespace, you can alter your extent sizing on your table.

Whats up with this? Any one can explain ?

Thanx,

Sam

You're seeing this on an 8i (or lower) database?

Oracle9i Release 2 - Managing Space in Tablespaces
Tablespaces allocate space in extents. Tablespaces can use two different methods to keep track of their free and used space:

Locally managed tablespaces: Extent management by the tablespace
Dictionary managed tablespaces: Extent management by the data dictionary
When you create a tablespace, you choose one of these methods of space management. You cannot alter the method at a later time.

:) What you mentioned were all true. But the issue I mentioned is something that I noticed on 9i (9.2.0.3)

(i.e)

Code:

---

1. Created the tablespace with the tablespace management set to locally managed.

2. Created the user with this as the default tablespace.

3. created the table with the initial and next set to 1M and noticed that next set to 0 after table was created.

4. Tried altering the table's next extent size and go the ORA-25150

5. Upto this point it all worked the way they are supposed to be working.

6. Moved the tablespace to dictionay managed using the

  execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('tablespace_name');

7. Changed the next extent and it was changed

8. Now changed the tablespace to locally managed one using

  execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(tablespace_name =>'tablespace_name', rfno => #);
 
9. Now tried change the next extent size and I was succesful

10. So created a new table under the same tablespace and tried to change the next extent size, I was successful

---

This was all the cause of the puzzle. Aren't I supposed to get the ORA- 25150 when I try changing the next extent size on a locally managed tablespace? instead of allowing me to change it.


-Sam

Code:

---

SQL> select tablespace_name, extent_management
  2  from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                        LOCAL
UNDOTBS1                      LOCAL
TEMP                          LOCAL
CWMLITE                        LOCAL
DRSYS                          LOCAL
EXAMPLE                        LOCAL
INDX                          LOCAL
ODM                            LOCAL
TOOLS                          LOCAL
USERS                          LOCAL
XDB                            LOCAL

11 rows selected.

SQL> conn / as sysdba
Connected.
SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS');
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS'); END;

*
ERROR at line 1:
ORA-10616: Operation not allowed on this tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

---

Note 235805.1 on MetaLink says this is a bug in 9.2, fixed in 10.0.

Your issue is that you have converted from local to dictionary back to local. When you convert from dictionary to local you get into a third type of extent management. You also need to look at allocation_type from dba_tablespaces. Your extent_management would be LOCAL, but your allocation_type will be USER (I think).

Quote:

---

Originally posted by stecal

Code:

---

SQL> select tablespace_name, extent_management
  2  from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                        LOCAL
UNDOTBS1                      LOCAL
TEMP                          LOCAL
CWMLITE                        LOCAL
DRSYS                          LOCAL
EXAMPLE                        LOCAL
INDX                          LOCAL
ODM                            LOCAL
TOOLS                          LOCAL
USERS                          LOCAL
XDB                            LOCAL

11 rows selected.

SQL> conn / as sysdba
Connected.
SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS');
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS'); END;

*
ERROR at line 1:
ORA-10616: Operation not allowed on this tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

---

Note 235805.1 on MetaLink says this is a bug in 9.2, fixed in 10.0.

---

You have a different problem here. You are trying to create a dictionary managed tablespace in a database that can only handle LMTs (because the system tablespace is LMT).

Sambavam :

you would like to see this quote.

Quote:

---

Scenario 5: Migrating from a Dictionary-Managed to a Locally Managed Tablespace
You migrate a dictionary-managed tablespace to a locally managed tablespace. You use the TABLESPACE_MIGRATE_TO_LOCAL procedure.

Let us assume that the database block size is 2K, and the existing extent sizes in tablespace tbs_1 are 10, 50, and 10,000 blocks (used, used, and free). The MINIMUM EXTENT value is 20K (10 blocks). In this scenario, you allow the bitmap allocation unit to be chosen by the system. The value of 10 blocks is chosen, because it is the highest common denominator and does not exceed MINIMUM EXTENT.

The statement to convert tbs_1 to a locally managed tablespace is as follows:

EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('tbs_1');


If you choose to specify a allocation unit size, it must be a factor of the unit size calculated by the system, otherwise an error message is issued.

---

Abhay.

Thanx Jeff. That explains why. Also my thanx to all those who contributed their thoughts to the problem.

Sam