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:
Related Articles:
Our work-support plans provide precise options as per your project tasks. Whether you are a newbie or an experienced professional seeking assistance in completing project tasks, we are here with the following plans to meet your custom needs:
Name | Dates | |
---|---|---|
Oracle DBA Training | Nov 12 to Nov 27 | View Details |
Oracle DBA Training | Nov 16 to Dec 01 | View Details |
Oracle DBA Training | Nov 19 to Dec 04 | View Details |
Oracle DBA Training | Nov 23 to Dec 08 | View Details |
Ravindra Savaram is a Technical 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.