LOBSEGMENT

数据库中有张表具有CLOB字段，引起一个LOBSEGMENT的segment大小57个G。查阅了一下MOS。

相关的文档如下

Master Note - RDBMS Large Objects (LOBs) [ID 1268771.1]

      修改时间 12-OCT-2011     类型 DIAGNOSTIC TOOLS     状态 PUBLISHED     

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
Information in this document applies to any platform.
--- Purpose ---

This Master Note is intended to provide a jumping off point for LOBs topics and issues in the Oracle RDBMS, including:

* LOB creation and storage
* LOB performance
* Known issues / problems with LOBs
* A list of relevant notes to progress issues, and helpful tips for filing Service requests if needed.

--- Concepts ---
Large Objects (LOBs) are a set of datatypes that are designed to hold large amounts of data.
LOBs can store much larger amounts of data (maximum size of a LOB is 8TB in 10gR2 or 128TB in 11g depending on the database block size). A table can have multiple LOB columns. LOB columns in a table can be of any LOB types BLOB, CLOB, NCLOB, BFILE.

Example:

SQL> CREATE TABLE LOB_TABLE
(ID NUMBER,
LOB_BLOB BLOB,
LOB_CLOB CLOB,
LOB_BFILE BFILE)
LOB (LOB_BLOB) STORE AS (TABLESPACE LOB_TBSP PCTVERSION 5)
LOB (LOB_CLOB) STORE AS (TABLESPACE LOB_TBSP ENABLE STORAGE IN ROW);

SQL> SELECT * FROM DBA_LOBS WHERE TABLE_NAME='LOB_TABLE';


SecureFiles are introduced in 11g to supplement the original LOBs implementation (BasicFile). A SecureFile can only be created in an automatic segment space management (ASSM) tablespace.

Example:

CREATE TABLE LOB_TABLE(
ID NUMBER,
LOB_CLOB CLOB)
LOB (LOB_CLOB) STORE AS SECUREFILE
(TABLESPACE AUTO_SEG_TBSP
STORAGE (INITIAL 6144)
CACHE);


Full documentation and coverage of Large Objects concept is covered in the following guides:

Oracle® Database Concepts 10g Release 2 (10.2)
Oracle® Database Application Developer's Guide - Large Objects 10g Release 2 (10.2)
Oracle® Database SecureFiles and Large Objects Developer's Guide 11g Release 1 (11.1)
Oracle® Database SecureFiles and Large Objects Developer's Guide 11g Release 2 (11.2)

--- How-To / Best Practices ---

There are good references for popular topics such as:

Note 66046.1 - Example SQL Demonstrating use of LOBs in Oracle
Note 66431.1 - LOBS - Storage, Redo and Performance Issues
Note 162345.1 - LOBS - Storage, Read-consistency and Rollback
Note 268476.1 - LOB Performance Guideline
Note 468160.1 - DB 11.1: Introduction to SecureFiles

--- Troubleshooting a LOB Issue ---

The LOB Master Note is provided to assist you in the solving of issues and logging of SRs for Large Object (LOB). The method provided below is based on Oracle Diagnostic Methodology (ODM) and helps you to avoid unnecessary trial and error by guiding you through a step-by step method. Diagnostic tools, examples and general questions are included in the document to assist in the different troubleshooting steps.

Step by step approach

Issue Clarification ==> Issue Verification ==> Cause Determination ==> Potential Solutions

In the issue clarification section it is important to describe the problem as best as possible. What is the problem you need to solve? At the end of the process you should be able to come back to this section to verify if the root cause was found and the solution was provided.

WHAT IS the problem we are looking at?

Examples of problem descriptions:

    Temporary lobs are not being released for reuse after COMMIT.  As a result ORA-1652 - Unable to extend temp segment error occurs.

    LOB segment (CLOB) will not shrink even though it is in an automatic segment space managed (ASSM) tablespace and row movement is enabled.  LOB tablespace on production system is running out of space.

    ORA-01555: snapshot too old: rollback segment number with name "" too small - occurs while selecting from a table containing a LOB column.  This prevents an important management report from running successfully.


Issue Clarification --> Issue Verification --> Cause Determination --> Potential Solutions

When you have a starting problem description it is time to collect facts in a structured way. First step would be to get an overview of all the facts we have. What information do we have already available? The answers to the questions below can potentially help you to solve the problem.

What is the problem / what is not a problem?

What are the symptoms?
What are the different errors generated?
What is the DDL (SQL definition) of the table containing the LOB segment?
What is the size of the LOB segment?
What Client and Server platforms reproduce the problem?
What Client and Server platforms work? (if any)
What 5-digit version of Oracle software reproduces the problem?
What 5-digit version of Oracle software works? (if any)
(For Windows platform confirm patch number bundle used)
Is the syntax correct? (Check the documentation set for examples/syntax)

When is the problem seen / when is the problem not seen?

Is the problem constant?
Is the problem reported intermittently?
Is there any pattern to the failure?
What resolves the problem?
Has this ever worked or is this a fresh installation / setup?
When did the problem start to show?
Can the problem been seen in Oracle tools like SQL*Developer?

Where is the problem / Where is the problem not?

Server side:
Is the problem limited to one database in the Oracle home?
Does the problem reproduce on other databases of the same version?
Does the problem reproduce on other databases of another version?

Client side:
What clients are affected by the problem?
Does it happen with ALL clients or only some?
Is the problem only occurring in one application?


Issue Clarification --> Issue Verification --> Cause Determination --> Potential Solutions

The facts listed in the ISSUE VERIFICATION are the starting point for the CAUSE DETERMINATION:
There are 3 main approaches to take here:
1. Use your experience to list possible causes. List the assumptions that need to be checked in this case.
2. Start searching for possible causes in My Oracle Support or other Oracle knowledge bases. Use the facts collected above to refine your search criteria.
3. Analyze the facts on differences between the working situation and non working situation: Depending on the answers from the questioning above and further investigation from the troubleshooting guides you should be able to list what is different, special, unique between the IS and the IS NOT and also see what is changed and when.


What changes happened around time the first failure / problem was reported?
What changed and could have an impact between the working situation and the current situation?
Any new patches applied to the client or server Oracle_Home?

Searching tips and tricks:

You can search or browse in our repository for issues.

For example:

Search on ORA-1652 + temporary lobs + space + released + commit

This would return:

Note 750209.1- Temporary LOB space not released after commit: ora-1652 being hit


Issue Clarification --> Issue Verification --> Cause Determination --> Potential Solutions

A brief description of the corrective actions that will remove the cause of the problem: in some cases there is only one solution linked to the cause. But in many cases, there are more. Example: install a patch to remove the bug from the system, avoid the problem by creating a different job setup, or avoid the problem by setting some parameters.

Determine the most probable cause and a relevant solution.


--- SR Creation ---

If you are blocked in the resolution process of your problem it is best to log a Service Request (SR).
When logging a SR please provide all the information you collected in the previous steps. The support engineer serving you will follow a very similar approach to come to the resolution of your problem. The more detailed you can describe the problem by using your issue clarification and verification, the better we will be able to solve you.

--- Diagnostic Tools ---

· Collection of the relevant information using selects from the dictionary views, showing the current states and used attributes.
· Alert.log, user trace files
· RDA report
· AWR report for performance issues with LOBs

--- Generic Diagnostics ---

Note 198160.1 - Summary Note Index for BasicFiles (LOB's/BLOB's/CLOB's/NCLOB's,BFILES) and SecureFiles
Note 846562.1 - Troubleshooting Guide (TSG) - Large Objects (LOBs)

** LOB Maintenance:
Note 386341.1 - How to determine the actual size of the LOB segments and how to free the deleted/unused space above
Note 369883.1 - How to Calculate Space Used by LOB Segments in the Database
Note 464558.1 - How to export/import data with LOB type from one user/schema to another?
Note 453186.1 - How to move LOB Data to Another Tablespace when the Table also contains a LONG column
Note 130814.1 - How to move LOB Data to Another Tablespace
Note 761388.1 - How To Move Or Rebuild A Lob Partition
Note 871203.1 - How to move a LOB index to another tablespace ?
Note 802059.1 - Shrink LOB Segment On Partitioned Table
Note 1151414.1 - How to Remove Lob Indexes
Note 339851.1 - Lob Index Stored In System Tablespace

** LOB Space issues
Note 48851.1 - OERR: ORA 1691 unable to extend lob segment %s.%s by %s in tablespace %s
Note 1014040.102 - How to Diagnose and Resolve ORA-1693: max # extents (%s) reached in lob segment %s.%s
Note 802897.1 - How to Release the Temp LOB Space and Avoid Hitting ORA-1652
Note 750209.1 - Temporary LOB space not released after commit: ora-1652 being hit

** LOB Performance:
Note 175825.1 - Slow Performance importing LOB columns
Note 740075.1 - 'enq HW - contention' For Busy LOB Segment
Note 837883.1 - LOB HWM CONTENTION :Using AWR Reports to Identify the Problem; Confirm and Verify the Fix
Note 268476.1 - LOB Performance Guideline
Note 66431.1 - LOBS - Storage, Redo and Performance Issues
Note 978045.1 - POOR PERFORMANCE WITH LOB INSERTS

** ORA-1555 on LOBs / LOB Corruption:
Note 846079.1 - LOBs and ORA-01555 troubleshooting
Note 293515.1 - ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors
Note 1206814.1 - Logical corruption of LOB data during recovery.
Note 253131.1 - Concurrent Writes May Corrupt LOB Segment When Using Auto Segment Space Management (ORA-1555)
Note 452341.1 - ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption.

** SecureFile:
Note 861344.1 - 11g Advanced Compression - How to Check Space Occupied by LOB Compression