Locally Managed Tablespaces in Oracle8i

1
LocallyManaged TablespacesinOracle8i
Raghav Vinjamuri rvinjamuri_at_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
• QA

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
• PCTINCREASE0
• 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
• PCTINCREASE0
• 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 of integer
• eliminates unwarranted extent size
  rounding/trimming

9
Extent Management

• Dictionary Managed Tablespaces 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
  LOCAL AUTOALLOCATE
• Create Tablespace TESTLMT datafile lmt01.dbf
  size 1000M reuse EXTENT MANAGEMENT
  LOCAL UNIFORM 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 DBAs
• 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 DBAs 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 LMTs can only use UNIFORM SIZE, and
  not AUTOALLOCATE.

15
Locally Managed Tablespaces

• Summary Notes for DBAs 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 DBAs 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 DBAs 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, at http//www.nocoug.or
  g

19
Questions ?

20
Good Luck!