Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • My Test Case On 21-OCT-2005 Oracle
  • Settting up get_vmstat.sh for colletinf CPU Usage. Oracle
  • Check_recovery.sh program to run sqlplus and return its values remotely. Linux/Unix
  • Kernel Parameters for Solaris Linux/Unix
  • Convert multiple rows to single column Oracle
  • Jai Shree Ram PHP/MYSQL/Wordpress
  • Gather Stats manually using DBMS_STATS after disabling DBMS_SCHEDULER jobs as previous entry Oracle
  • Korn Shell Arithmatic Linux/Unix
  • OEM-Commnds Oracle
  • Directory wise folder wise space usage Linux/Unix
  • Committing distributed transaction using commit force Oracle
  • Proc code Oracle
  • Getting started with notebook Python/PySpark
  • Important Solaris Commands Linux/Unix
  • Renaming Oracle Instance Name Oracle

Locally Managed Tablespace and Dictionary managed tablespace (LMT-DMT)

Posted on 19-Aug-2005 By Admin No Comments on Locally Managed Tablespace and Dictionary managed tablespace (LMT-DMT)

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

Oracle, SQL scripts

Post navigation

Previous Post: True Session Wait Activity in Oracle 10g Verygood
Next Post: Good Link from metalink 1

Related Posts

  • First Entry in RAC Oracle
  • How to specify 2 arch location to avoid any kind of DB hanging. Oracle
  • Drop all SPM baselines for SQL handle Oracle
  • _B_TREE_BITMAP_PLANS issue during 8.1.7 to 9.2.0.8 upgrade Oracle
  • UNderstand and eliminate Latch contention. Oracle
  • Oracle Data Direct to TAPE Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Error Handling in Proc Oracle
  • handling filenname with space Linux/Unix
  • Building Our Own Namespaces with “Create Context” Oracle
  • Global Unique Identifier Generation in Oracle 9.2 SYS_GUID() Oracle
  • Physical Standby switchover with session active Oracle
  • How to see which patches are applied. Oracle
  • To find explain plan for a statement that occurred in past. Oracle
  • Python class import from different folders Python/PySpark

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme