1 / 20

Locally Managed Tablespaces in Oracle8 i

Locally Managed Tablespaces in Oracle8 i. Raghav Vinjamuri rvinjamuri@yahoo.com. Questions. Extents Size of Individual Extent in a Segment Matters/Does Not Matter? Number of Extents Matters/Does Not Matter?. Questions ( AND Answers). Extents Size of Individual Extent in a Segment

amato
Download Presentation

Locally Managed Tablespaces in Oracle8 i

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. LocallyManaged TablespacesinOracle8i Raghav Vinjamuri rvinjamuri@yahoo.com

  2. Questions • Extents • Size of Individual Extent in a Segment • Matters/Does Not Matter? • Number of Extents • Matters/Does Not Matter?

  3. Questions ( AND Answers) • Extents • Size of Individual Extent in a Segment • Matters/Does Not Matter? • if small, can lead to numerous extents. • Number of Extents • Matters/does not matter? • Due to overheads of dictionary maintenance, May not be for full table scan performance.

  4. Questions ( AND Answers) • Extent Management • Extents must therefore be • Fixed Size - Appropriately Large, • Fixed in Number • How to implement/manage Fixed Size Extents in an Oracle database?

  5. Overview • Extent Management • Dictionary Managed Tablespaces - “DMT” • Locally Managed Tablespaces - “LMT” • Features/“Bugs”/“Usage Quirks” of LMTs in Oracle8i Rel 8.1.6.0 • Benefits of LMTs over DMTs • Summary & References • Q&A

  6. Extent Management • How to implement Fixed Size Extentsin an Oracle database? • Pre-Oracle8 • Oracle8 • Oracle8i

  7. Extent Management • Pre-Oracle8 • DEFAULT STORAGE • INITIAL and NEXT are set equal, and • PCTINCREASE=0 • Caveats • Extent Sizes Rounded Up. • Nearest Multiple of 5. • MAYBE more (up to 4 additional blocks) if there are fewer than 5 blocks remaining in the unallocated portion. • Coalescing of Free Extents by SMON. • Extent Trimming After Parallel Operations.

  8. Extent Management • Oracle8 • DEFAULT STORAGE • INITIAL and NEXT are set equal, and • PCTINCREASE=0 • MINIMUM EXTENT integer • the minimum size of an extent in a tablespace. • controls free space fragmentation in tablespaces • ensures every used/free extent size in a tablespace is at least as large as, and is a multiple ofinteger • eliminates unwarranted extent size rounding/trimming

  9. Extent Management • Dictionary ManagedTablespaces “DMT” • Free/Used Extent information of tablespaces tracked via Data Dictionary Tables (FET$ and UET$) • differentiates from Locally Managed Tablespaces in Oracle8i

  10. Locally Managed Tablespaces • New in Oracle8i • EXTENT MANAGEMENT LOCAL clause of CREATE TABLESPACE statement • EXTENT MANAGEMENT DICTIONARY still is Default. • Allows Fixed Extent Size(s) • AUTOALLOCATE or “System Determined” Sizes • UNIFORM Size

  11. Locally Managed Tablespaces • Create Tablespace TESTLMT datafile ‘lmt01.dbf’ size 1000M reuse EXTENT MANAGEMENT LOCALAUTOALLOCATE; • Create Tablespace TESTLMT datafile ‘lmt01.dbf’ size 1000M reuse EXTENT MANAGEMENT LOCALUNIFORM Size 10M;

  12. Locally Managed Tablespaces • AUTOALLOCATE • is default • can specify initial extent and Oracle determines the size of the rest, with a minimum of 64k • UNIFORM • default is 1MB

  13. Locally Managed Tablespaces • Summary Notes for DBA’s 1. LMTs and DMTs can co-exist in same DB. 2. EXTENT MANAGEMENT DICTIONARY is default. 3. Currently, a locally managed SYSTEM tablespace (?? For ROLLBACK SEGMENT usage) cannot be created (True in 8.1.7) 4. EXTENT MANAGEMENT LOCAL • CREATE TABLESPACE statement - for permanent tablespaces • CREATE TEMPORARY TABLESPACE statement - for temporary tablespaces

  14. Locally Managed Tablespaces • Summary Notes for DBA’s … 2 • CREATE TEMPORARY TABLESPACE statement - for temporary tablespaces CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'temp.dbf' SIZE 256M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K; 5. When creating an LMT, AUTOALLOCATE is default. 6. Temporary LMT’s can only use UNIFORM SIZE, and not AUTOALLOCATE.

  15. Locally Managed Tablespaces • Summary Notes for DBA’s … 3 7. Parameters • TEMPORARY, • MINIMUM EXTENT, or • DEFAULT storage_clause, • NEXT, • PCTINCREASE, • MINEXTENTS, • MAXEXTENTS are not valid for locally managed tablespaces. Could get an error or be silently ignored!!

  16. Locally Managed Tablespaces • Summary Notes for DBA’s … 4 8. DBMS_SPACE_ADMIN package provides • Defect Diagnosis and Repair Functionality for LMT • Migration of DMT to LMT or vice-versa 9. LMTs can be altered for many of the same reasons as a dictionary-managed tablespace. However, • altering storage parameters is not an option and • coalescing free extents is unnecessary • an LMT cannot be altered to a locally managed temporary tablespace.

  17. Locally Managed Tablespaces • Summary Notes for DBA’s … 5 10. To find out which tablespaces are LMTs • SELECT tablespace_name, extent_managementFROM dba_tablespacesWHERE extent_management = 'LOCAL';

  18. These slides (and, a white paper!) will be posted on the NOCOUG web site, athttp://www.nocoug.org

  19. Questions ?

  20. Good Luck!

More Related