Locally managed tablespace track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits:
Want To Get DBA Training From Experts? Enroll Now For Free Demo On Oracle DBA Training.
All tablespaces, including the system tablespace, can be locally managed. The dbms_space_admin package provides maintenance procedures for locally managed tablespaces.
Create a locally managed tablespace by specifying local in the extent management clause of the create tablespace statement. You can have the database manage extents for you automatically with the autoallocate clause (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (uniform).
Autoallocate is also a good choice if it is not important for you to have a lot of control over space allocation and deallocation, because it simplifies tablespace management. Some space may be wasted with this setting, but the benefit of having oracle database manage your space most likely outweighs this drawback.
When you do not explicitly specify the type of extent management, oracle database determines extent management as follows:
[Related Article: Control File in Oracle]
The following statement creates a locally managed tablespace named lmtbsb and specifies autoallocate:
Sql> create tablespace lmtbsb datafile ‘/u02/oracle/data/lmtbsb01.dbf’ size 50m Extent management local autoallocate;
Autoallocate causes the tablespace to be system managed with a minimum extent size of 64k.
The alternative to autoallocate is uniform. Which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the size clause of uniform. If you omit size, then the default size is 1m.
Sql> create tablespace lmtbsb datafile ‘/u02/oracle/data/lmtbsb01.dbf’ size 50m Extent management local uniform size 128k;
You cannot specify the default storage clause, minimum extent, or temporary when you explicitly specify extent management local. If you want to create a temporary locally managed tablespace, use the create temporary tablespace statement.
[Related Article: Oracle DBA Interview Questions]
In a locally managed tablespace, there are two methods that oracle database can use to manage segment space: automatic and manual. Manual segment space management uses linked lists called “freelists” to manage free space in the segment, while automatic segment space management uses bitmaps. Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.
Automatic segment space management delivers better space utilization than manual segment space management. It is also self-tuning, in that it scales with increasing number of users or instances. In addition, for many standard workloads, application performance with automatic segment space management is better than the performance of a well-tuned application using manual segment space management.
The following statement creates tablespace lmtbsb with automatic segment space management:
[Related Article: Oracle Rename Tablespace]
Sql> create tablespace lmtbsb datafile ‘/u02/oracle/data/lmtbsb01.dbf’ size 50m Extent management local Segment space management auto;
The segment space management manual clause disables automatic segment space management.
The segment space management that you specify at tablespace creation time applies to all segments subsequently created in the tablespace. You cannot change the segment space management mode of a tablespace.
Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!
You cannot alter a locally managed tablespace to a locally managed temporary tablespace, nor can you change its method of segment space management. Coalescing free extents is unnecessary for locally managed tablespaces. However, you can use the alter tablespace statement on locally managed tablespaces for some operations, including the following:
Ravindra Savaram is a Content Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.