-
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
Thanx
Sam
Life is a journey, not a destination!
-
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
Thanx
Sam
Life is a journey, not a destination!
-
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).
Jeff Hunter
-
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).
Jeff Hunter
-
Sambavam :
you would like to see this 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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Thanx Jeff. That explains why. Also my thanx to all those who contributed their thoughts to the problem.
Sam
Thanx
Sam
Life is a journey, not a destination!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|