How To Check If There Is a Dictionary-Managed Tablespace On Oracle Database(Extent Management Control)

dbtut

6 years ago

Extent Management Control can be LOCAL or Dictionary Based.

If local, the information in the bitmaps in the datafile is used.

If Dictionary Based, empty extents are queried in FET$ and UET$ views in the data dictionary.

Dictionary Based causes performance loss. It has become local default at 9i.

We use the following script to check for tablespaces that are not set to LOCAL.

SELECT tablespace_name, extent_management
FROM dba_tablespaces
WHERE EXTENT_MANAGEMENT != 'LOCAL';

SELECT tablespace_name, extent_management

FROM dba_tablespaces

WHERE EXTENT_MANAGEMENT != 'LOCAL';

We can change it as follows.

EXECUTE dbms_space_admin.tablespace_migragte_to_local('tablespacename');

1	EXECUTE dbms_space_admin.tablespace_migragte_to_local('tablespacename');