locally managed tablespaces in oracle8 i l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Locally Managed Tablespaces in Oracle8 i PowerPoint Presentation
Download Presentation
Locally Managed Tablespaces in Oracle8 i

Loading in 2 Seconds...

play fullscreen
1 / 20

Locally Managed Tablespaces in Oracle8 i - PowerPoint PPT Presentation


  • 164 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Locally Managed Tablespaces in Oracle8 i' - sandro


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
locally managed tablespaces in oracle8 i

LocallyManaged TablespacesinOracle8i

Raghav

Vinjamuri

rvinjamuri@yahoo.com

questions
Questions
  • Extents
    • Size of Individual Extent in a Segment
      • Matters/Does Not Matter?
    • Number of Extents
      • Matters/Does Not Matter?
questions and answers
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.
questions and answers4
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?
overview
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
extent management
Extent Management
  • How to implement Fixed Size Extentsin an Oracle database?
    • Pre-Oracle8
    • Oracle8
    • Oracle8i
extent management7
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.
extent management8
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
extent management9
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
locally managed tablespaces
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
locally managed tablespaces11
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;
locally managed tablespaces12
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
locally managed tablespaces13
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
locally managed tablespaces14
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.

locally managed tablespaces15
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!!

locally managed tablespaces16
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.
locally managed tablespaces17
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';
slide18
These slides (and, a white paper!) will be posted on the NOCOUG web site, athttp://www.nocoug.org