BENEFITS of Locally Managed Tablespaces
—————————————
Locally managed tablespaces track all extent information in the tablespace
itself, using bitmaps, resulting in the following benefits:
* Improved concurrency and speed of space operations, because space allocations
and deallocations predominantly modify locally managed resources (bitmaps
stored in header files) rather than requiring centrally managed resources
such as enqueues
* Improved performance, because recursive operations that are sometimes
required during dictionary-managed space allocation are eliminated
* Readable standby databases are allowed, because locally managed temporary
tablespaces (used, for example, for sorts) are locally managed and thus do
not generate any undo or redo.
* Simplified space allocation when the AUTOALLOCATE clause is specified,
appropriate extent size is automatically selected
* Reduced user reliance on the data dictionary because necessary information
is stored in file headers and bitmap blocks
RESTRICTIONS of Having a Locally Managed SYSTEM Tablespace
———————————————————-
After the SYSTEM tablespace is created/migrated to locally managed:
1/ ANY dictionary-managed tablespaces in the database CANNOT be made READ WRITE.
If you want to be able to use the dictionary-managed tablespaces in
READ-WRITE mode, Oracle recommends that you first migrate these tablespaces
to locally managed before migrating the SYSTEM tablespace.
Example of an inescapable situation :
SQL> select t.tablespace_name, EXTENT_MANAGEMENT, ENABLED
2 from dba_tablespaces t, v$datafile d, dba_data_files f
3 where t.tablespace_name = f.tablespace_name
4 and f.file_id = d.file#;
TABLESPACE_NAME EXTENT_MAN ENABLED
—————————— ———- ———-
SYSTEM LOCAL READ WRITE
UNDOTBS1 LOCAL READ WRITE
USERS DICTIONARY READ ONLY
SQL> alter tablespace users read write;
alter tablespace users read write
*
ERROR at line 1:
ORA-12915: Cannot alter dictionary managed tablespace to read write
Now if you want to migrate the USERS tablespace to local management to
switch it to READ WRITE afterwards, it becomes impossible:
SQL> execute sys.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(‘USERS’);
BEGIN sys.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(‘USERS’); END;
*
ERROR at line 1:
ORA-03245: Tablespace has to be dictionary managed, online and permanent
to be able to migrate
ORA-06512: at “SYS.DBMS_SPACE_ADMIN”, line 0
ORA-06512: at line 1
SQL> select tablespace_name, extent_management, allocation_type
2 from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN ALLOCATIO
—————————— ———- ———
SYSTEM LOCAL USER
UNDOTBS1 LOCAL SYSTEM
TEMP LOCAL UNIFORM
USERS DICTIONARY USER
We should have migrated the USERS tablespace to LOCAL before the SYSTEM
tablespace.
2/ You cannot create new DICTIONARY managed tablespaces :
SQL> create tablespace USERS
2 datafile ‘/filer/9.0.2/djeunot/DB1/users01.dbf’ size 2M
3 extent management dictionary;
create tablespace USERS
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
3/ You cannot migrate a LOCALLY managed SYSTEM tablespace back to DICTIONARY:
SQL> execute sys.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL(‘SYSTEM’);
BEGIN sys.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL(‘SYSTEM’); END;
*
ERROR at line 1:
ORA-03251: Cannot issue this command on SYSTEM tablespace
ORA-06512: at “SYS.DBMS_SPACE_ADMIN”, line 0
ORA-06512: at line 1