Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • To seee semaphores and shared memory segments in Solaris Linux/Unix
  • Sort with ASCII order and Numeric Order Linux/Unix
  • How to connect to Oracle Database with Wallet with Python. Oracle
  • How to change hostname in Linux Linux/Unix
  • Pending Transaction Neighbors Script Oracle
  • ORA-00064: object is too large to allocate on this O/S during startup Oracle
  • First Entry in RAC Oracle
  • Good Oracle Architecture In Short and point to point Oracle
  • ORA-4031 issue and solution on 09-MAY-2008 Oracle
  • Good Link from metalink 1 Oracle
  • tblwopk.sql /* Find Tables Without PK */ Oracle
  • Good notes for shared pool Oracle
  • Does DBMS_JOB recompute the NEXT_DATE interval after or before Oracle
  • ORACLE_SID in sqlplus Oracle
  • Oracle 11g Environment Setup 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

  • AWR license Oracle
  • Create type and Grant on it. Oracle
  • Caching sequence in Memory Oracle
  • ext#.sql Oracle
  • PLSQL Table Syntax 1 Oracle
  • DBMS_SQL for alter session. 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 (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • 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
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Disbaling DBA_SCHEDULER_JOBS Oracle
  • Find average Row Length and other table size calculation. metalink notes Oracle
  • TRUNCATE table and disabling referential constraints. Oracle
  • Find Plan Hash value fphv.sql Oracle
  • Reclaim temp tablespace for oracle 8, 8i Oracle
  • before_trunc.sql Before Truncate table needs to execute following: Oracle
  • find_longsql.sql Oracle
  • My FTP Job Scheduling for www.pvmehta.com PHP/MYSQL/Wordpress

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme