Database objects, segments and extents

Summary

Symptom

1. What types of database objects exist?

2. How can I determine the type of database objects?

3. What are extents and segments?

4. Can extents or segments extend across several data files or tablespaces?

5. How is the size of extents defined?

6. What happens if there is no more space available for a new extent in a tablespace?

7. What does external fragmentation mean?

8. How can adjacent freespace fragments be added together?

9. How is the maximum number of extents defined for each segment?

10. What happens if the MAXEXTENTS limit is reached in a segment?

11. How can I adjust storage parameters?

12. How does the SAP system determine the tablespace and storage parameters for objects to be created?

13. What do I have to bear in mind in connection with the technical settings in the BW environment?

14. How can I adjust these SAP settings?

15. What errors may occur if an incorrect tablespace or incorrect storage parameters are defined in SAP?

16. How does Oracle determine the tablespace and the storage parameters for objects to be created?

17. How can I change default Oracle values for tablespaces and storage parameters?

18. What happens if the defined default tablespace of a partitioned segment no longer exists?

19. What pitfalls and exceptions are associated with the mechanisms for specifying the tablespace and storage parameters?

20. Can a large number of extents result in performance problems?

21. How does "brconnect -f next" determine the NEXT size?

Other terms

FAQ, frequently asked questions

Solution

1. What types of database objects exist?

              The most important types of database objects are as follows:

• CLUSTER: Oracle clusters

• These are not used by SAP (not to be confused with SAP cluster tables).

• Some clusters exist by default in the Oracle dictionary (for example, C_COBJ#).

• INDEX: Indexes

• Indexes are used for faster access to data in a table.

• They are used extensively by Oracle and SAP.

• INDEX PARTITION: index partitions

• These divide an index into certain data areas.

• They are only used in the BW environment.

• LOB: LOBs

• LOBs are used to save large volumes of data (see Note 500340).

• PACKAGE: Packages

• A package is a related number of procedures and functions (specification).

• SAP only uses packages in a few cases (for example, DBMS_STATS package, see Note 588668).

• PACKAGE BODY: Package bodies

• A package body is a related number of procedures and functions (implementation).

• PACKAGE and PACKAGE BODY form one unit.

• PROCEDURE: Stored procedures

• SAP only uses stored procedures in rare cases (for example, to determine the memory quality in transaction DB02).

• SEQUENCE: Sequences

• Sequences are used for the unique assignment of integer values.

• They are only used in rare cases in the SAP environment (for example, for the buffer synchronization, see Note 185821).

• SYNONYM: Synonyms

• Synonyms are used to access objects under another name.

• They are required for accessing the views of Oracle dictionaries.

• They are also partially used in the SAP environment (for example, SAPUSER synonym for OPS$ connect -> Note 400241; upgrade with shadow instance).

• TABLE: Tables

• Tables are the central objects of every database.

• They contain the actual data.

• TABLE PARTITION: Table partitions

• Table partitions subdivide a table into certain data areas.

• They are only used in the BW environment.

• VIEW: Views

• They provide a defined view of one or more tables and subviews.

• They are used to access information from the Oracle dictionary and runtime information (for example, DBA and V$ views).

• They are also used in the SAP environment.

              In addition, there is also a complete range of other object types such as CONSUMER GROUP, EVALUATION CONTEXT, FUNCTION, LIBRARY, OPERATOR, QUEUE, RESOURCE PLAN, TRIGGER, TYPE or TYPE BODY.

2. How can I determine the type of database objects?

              You can determine the type of database objects in the OBJECT_TYPE field of the DBA_OBJECTS view.

              The following SELECT determines the type of an <object> object:

SELECT OBJECT_TYPE FROM DBA_OBJECTS
WHERE OBJECT_NAME = '<object>';

              The following SELECT determines all object types contained in a <tablespace> tablespace:

SELECT DISTINCT(OBJECT_TYPE) FROM DBA_OBJECTS
WHERE TABLESPACE_NAME = '<tablespace>';

3. What are extents and segments?

              An extent is a connected area of a data file that is exclusively assigned to a segment.

              A segment consists of one or more extents and contains the physical data.

              Segments can have different types that more or less correlate with certain object types. The most important types are CLUSTER, INDEX, INDEX PARTITION, LOBINDEX, LOBSEGMENT, ROLLBACK, TABLE, TABLE PARTITION and TYPE2 UNDO.

4. Can extents or segments extend across several data files or tablespaces?

              An extent can only appear in exactly one data file and therefore also only in exactly one tablespace.

              A segment can extend across several data files of a tablespace. However, it is always explicitly assigned to ONE tablespace.

5. How is the size of extents defined?

              The size of the extent is determined in a different way depending on the tablespace type used:

• LMTS UNIFORM (Note 214995)

           When a tablespace is created, all extents have the standard defined extent size. This can be determined as follows:

SELECT NEXT_EXTENT FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = '<tablespace>';

• LMTS AUTOALLOCATE

           The extent sizes are determined using an internal algorithm (see Note 599694).

• Dictionary Managed Tablespace (previous standard)

           The sizes of the extent can be specified when you create the segments within the STORAGE clause. INITIAL determines the size of the first extent, while NEXT specifies the size of the next extent. In addition, PCTINCREASE must be set to 0 to prevent the extents from being increased in each case by a certain percentage.

           Example (creating a table with an extent size of 2 MB):

CREATE TABLE ... STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0);

           The current storage parameters of a segment can be determined using the following query:

SELECT INITIAL_EXTENT, NEXT_EXTENT FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = '<segment>';

           You should regularly check and adjust the extent sizes of segments in DMTS using "BRCONNECT -f next". This prevents a segment that is rapidly increasing in size from allocating a large number of extents (due to an extent size that is too low) and consequently approaching the MAXEXTENTS limit at some stage.

6. What happens if there is no more space available for a new extent in a tablespace?

              If there is no more free space available for a new extent to be allocated, and if the automatic data file enhancement is not activated by AUTOALLOCATE, the current transaction terminates and the system issues an error message. If this is an SAP update transaction, the SAP system update is deactivated. For more information about these errors and how to solve them, see Note 3155.

7. What does external fragmentation mean?

              External fragmentation means that the freespace in a tablespace is not contiguous; instead, there are several freespace fragments that appear between the allocated extents. Due to external fragmentation, overflow errors may occur even though sufficient freespace apparently still exists for a new extent. You can use the following statement to determine the existing freespace fragments in a tablespace:

SELECT BYTES FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = '<tablespace>'
ORDER BY BYTES ASC;

              The lowest value specifies the highest freespace fragment that exists in the tablespace. An overflow error may occur if Oracle tries to allocate an extent in this tablespace that is higher than the highest freespace fragment.

8. How can adjacent freespace fragments be added together?

              In the case of LMTS, adjacent freespace fragments are automatically recognized as being connected in the LMTS bitmap.

              In the case of DMTS, there is no automated mechanism for adding adjacent areas of freespace together. In fact, Oracle automatically adds them together if required - for example, if there is no sufficiently large freespace fragment available for an extent that is to be allocated. This process of adding freespaces together is also known as tablespace COALESCE. You can manually trigger this action as follows:

ALTER TABLESPACE <tablespace> COALESCE;

9. How is the maximum number of extents defined for each segment?

              In the case of LMTS, there is no restriction concerning the number of extents for each segment.

              In the case of DMTS, the maximum number is defined by the MAXEXTENTS storage parameter. This storage parameter is defined and queried in the same way as the INITIAL and NEXT storage parameters described above.

              For example: Example: Creating a table where MAXEXTENTS = 500:

CREATE TABLE ... STORAGE (... MAXEXTENTS 500);

              You can determine the current MAXEXTENTS value of a segment as follows:

SELECT MAX_EXTENTS FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = '<segment>';

10. What happens if the MAXEXTENTS limit is reached in a segment?

              In this case, the active transaction terminates and the system issues an error message. You can solve this problem by manually increasing MAXEXTENTS. Note 533455 contains possible error messages and also the troubleshooting procedure.

11. How can I adjust storage parameters?

              You can manually adjust storage parameters for a <segment> segment (for example, INDEX, TABLE) as follows:

ALTER <segment> STORAGE (NEXT <new_next>);
ALTER <segment> STORAGE (MAXEXTENTS <new_maxextents>);
ALTER <segment> STORAGE (PCTINCREASE <new_pctincrease>);

              After this, you can no longer adjust INITIAL.

              Alternatively, you can adjust the storage parameters using BRCONNECT as described in Note 855187.

              The MINEXTENTS and OPTIMAL storage parameters are also particularly relevant for rollback segments (see Note 3807).

12. How does the SAP system determine the tablespace and storage parameters for objects to be created?

              In various situations, database objects are created from R/3, for example:

• Table conversions

• Transports of new objects into the system

• Manual creation of new objects (transactions SE14, SE11)

              If errors occur when you create objects (such as the MAXEXTENTS limit is reached, for example) or if the objects are created in an incorrect tablespace, you must determine the basis on which the storage parameters and the target tablespace are defined. For this, the R/3 system proceeds with the following steps:

a) User-specific settings

• If user-specific settings exist (for example, because you specified data in SE14 using the "For new creation" button), these are used to create the new object.

• The user-specific settings are stored in the DDSTORAGE table.

b) Current database settings

• If the affected object already existed at database level (for example, for a table conversion) and user-specific settings do not exist, the previous settings are transferred. This means that the new object is in the same tablespace and has the same storage parameters as the object that previously existed.

c) Technical settings

• If the two conditions above are not fulfilled, the technical settings take effect. They are in transaction SE13 and consist of the "Data Class" (TABART) and the "Size Category" (TABKAT). The TABART is used to define the tablespace used while the TABKAT determines the size of the storage parameters.

• The TAORA (tables) or IAORA (indexes) tables contain the 'TABART -> tablespace' assignment.

• The TGORA (tables) or IGORA (indexes) tables contain the 'TABKAT -> storage parameter' assignment.

• The TSORA table contains the 'table tablespace -> index tablespace' assignment.

• The DD09L table contains TABKAT and TABART for all objects with technical settings.

• The DDART and DARTT tables contain information about the available TABARTs.

              The system temporarily stores DDL statements in the TATAF table within the SAP transport system. These statements also contain components, such as tablespace names, that were determined based on the settings mentioned above.

13. What do I have to bear in mind in connection with the technical settings in the BW environment?

              Some temporary BW objects (naming convention /BI0/0*) use the tablespace and the storage parameters of the dummy table QUERY_TABL_MODEL (for example, /BI0/06*, /BI0/0P*; in some cases /BI0/01*, /BI0/02*). For more information, also see Note 568632.

              Sometimes, temporary BW objects are created without an explicit specification of a tablespace. This is the reason why these can then be found in the default tablespace. The tablespace may also be defined dynamically with individual methods.

              By default, fact tables use the technical settings stored in the model table RSDMFACTAB. By default, dimension tables use the technical settings stored in the model table RSDMDIMTAB (Note 443767). For more BW-specific model tables, see the type group RSDG in transaction SE11 (for example, RSDMODSTAB for ODS).

14. How can I adjust these SAP settings?

              SAPDBA and BRSPACE make the necessary settings automatically (for example, if the target tablespace is missing during a table reorganization). For more information, see Note 154193.

              Note 771191 describes how you can move BW objects into another tablespace.

              Note 568632 describes how you can change the settings for temporary BW objects if problems occur.

              Note 46272 describes how you can include a new data class in the technical settings.

              You can correct entries for individual objects in DD09L by adjusting the technical settings in transaction SE14. Under "For new creation" in transaction SE14, you can adjust the DDSTORAGE entries for individual objects.

              You can use transaction SE16 to change entries from TAORA, IAORA, TGORA, IGORA and TSORA. Note that this is only possible if it is permitted by the settings for the system change option (transaction SE06) and client change option (transaction SCC4).

              If you cannot use transaction SE16 or if you want to change a large number of entries in DDSTORAGE or DD09L, you can update the tables directly at Oracle level, while taking the necessary precautions.

              You can only change entires in TATAF manually (or by activating the object in transaction SE11).

              Note that the above tables are generally buffered in the R/3 system. Therefore, changes made with SQLPLUS or other R/3 external tools are only recognized by R/3 if the instance is restarted or if buffering is invalidated for the tables. For this invalidation, use the report "RSDBBUFF -> Edit -> Reset buffer".

15. What errors may occur if an incorrect tablespace or incorrect storage parameters are defined in SAP?

• If SAP finds a tablespace that no longer actually exists in the user-specified or technical settings, the following error occurs when you use SAP tools to create the object (for example, as part of a conversion):
  
  ORA-00959: Tablespace <tsp> does not exist

           This problem can occur even if the entries in TAORA, IAORA and TSORA are correct after an online reorganization with BRSPACE, because the SAP buffer for these tables is not automatically invalidated at the same time. In this case, you can perform a manual invalidation using the report RSDBBUFF.

           If the problem persists even though you have corrected TAORA/IAORA, TATAF may contain obsolete entries that you must correct.

• If BRCONNECT detects a difference between TAORA / IAORA and the actual existing SAP tablespaces, the errors described in Note 655162 occur.

• Incorrect TAORA/IAORA entries may cause dumps when you create partitions in BW (see Note 539757).

• If, for an object, you select a TABKAT that is too low and therefore NEXT and MAXEXTENTS parameters that are too small, this may cause "maxextents reached" errors as described in Note 533455.

• In addition, extent sizes that are too small may lead to performance problems due to space transactions.

• If the partitions of partitioned objects are in different tablespaces (because the tablespace to be used for new partitions changed in the meantime), the process of 'dropping' the affected tablespaces fails with an ORA-14404. In general, you should ensure that the partitions of an object are in one tablespace only.

16. How does Oracle determine the tablespace and the storage parameters for objects to be created?

              Generally, SAP explicitly specifies tablespaces and storage parameters. These values then undergo a 1:1 conversion by Oracle. If tablespaces and storage parameters are not explicitly specified, Oracle proceeds according to the following rules:

• If a tablespace is not specified for tables or indexes, the object is created in the default tablespace of the database user who is logged on to the system. You can determine the default tablespaces for the database users as follows:
  
  SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS;

• If the tablespace specification is missing for partitions, the default setting is used for the partition. You can determine this for tables or indexes as follows:
  
  SELECT DEF_TABLESPACE_NAME FROM DBA_PART_TABLES
  WHERE TABLE_NAME = '<table_name>';
  
  SELECT DEF_TABLESPACE_NAME FROM DBA_PART_INDEXES
  WHERE INDEX_NAME = '<index_name>';
  

• Oracle determines non-specified storage parameters for tables or indexes from the default values of the relevant tablespaces. You can determine these as follows:
  
  SELECT INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, PCT_INCREASE
  FROM DBA_TABLESPACES
  WHERE TABLESPACE_NAME = '<tablespace_name>';

• Oracle determines non-specified storage parameters for partitions from the default storage parameters for the partitions of the relevant object. You can determine these for tables or indexes as follows:
  
  SELECT DEF_INITIAL_EXTENT, DEF_NEXT_EXTENT, DEF_MAX_EXTENTS,
    DEF_PCT_INCREASE
  FROM DBA_PART_TABLES
  WHERE TABLE_NAME = '<table_name>';
  
  SELECT DEF_INITIAL_EXTENT, DEF_NEXT_EXTENT, DEF_MAX_EXTENTS,
    DEF_PCT_INCREASE
  FROM DBA_PART_INDEXES
  WHERE INDEX_NAME = '<index_name>';

17. How can I change default Oracle values for tablespaces and storage parameters?

              Generally, you do not have to adjust the Oracle default values. However, if you believe this may be useful in individual cases, you can use the following commands:

• Changing the default tablespaces for a user:
  
  ALTER USER <user_name> DEFAULT TABLESPACE <new_default_tablespace>;

• Changing the tablespace defaults of the storage parameters:
  
  ALTER TABLESPACE <tsp> DEFAULT STORAGE (INITIAL <new_initial>);
  ALTER TABLESPACE <tsp> DEFAULT STORAGE (NEXT <new_next>);
  ALTER TABLESPACE <tsp> DEFAULT STORAGE (MAXEXTENTS <new_maxextents>);
  ALTER TABLESPACE <tsp> DEFAULT STORAGE (PCTINCREASE <new_pctincrease>);

• Changing the partition defaults for tables:
  
  ALTER TABLE "<table_name>" MODIFY DEFAULT ATTRIBUTES
    TABLESPACE <tablespace_name>;
  ALTER TABLE "<table_name>" MODIFY DEFAULT ATTRIBUTES
    STORAGE (INITIAL <new_initial>);
  ALTER TABLE "<table_name>" MODIFY DEFAULT ATTRIBUTES
    STORAGE (NEXT <new_next>);
  ALTER TABLE "<table_name>" MODIFY DEFAULT ATTRIBUTES
    STORAGE (MAXEXTENTS <new_maxextents>);
  ALTER TABLE "<table_name>" MODIFY DEFAULT ATTRIBUTES
    STORAGE (PCTINCREASE <new_pctincrease>);

• Changing the partition defaults for indexes:

           The same as tables, except with:

ALTER INDEX "< index_name>" ...

18. What happens if the defined default tablespace of a partitioned segment no longer exists?

              If, during a reorganization, you delete a tablespace, for example, which is defined as DEF_TABLESPACE_NAME in DBA_PART_TABLES or DBA_PART_INDEXES, the tablespace name is replaced by a placeholder such as _$deleted$7$0. If you now perform an ADD PARTITION, it leads to the following errors:

ORA-00959: tablespace '_$deleted$8$0' does not exist

              You can correct this by adjusting the tablespace name using MODIFY DEFAULT ATTRIBUTES as described above.

              BRSPACE performs this adjustment automatically during the reorganization.

19. What pitfalls and exceptions are associated with the mechanisms for specifying the tablespace and storage parameters?

              Even though it seems as though you have set up user-specific values and technical settings correctly, a number of SAP and Oracle patterns of behavior may result in the wrong tablespace or storage parameters being used:

• Depending on the release and patch status, PSA tables in BW (naming convention: /BI*/B*) follow their own rules with regard to tablespace and storage parameters. For more information, see Notes 639930 and 639941.

• New partitions for locally partitioned indexes in BW are automatically created if the relevant table gets new partitions. The tablespace in which the new index partitions are created cannot be explicitly specified. Instead, the default tablespace specified when the index is created is always used. Alternatively, if a default tablespace was not specified, the table tablespace is used. This may cause problems after moving indexes to another tablespace, for example, if the default tablespace was not also adjusted (see the BRSPACE bug from Note 822936, for example).

           You can use the following command to check whether a default tablespace was defined for the index:

SELECT DEF_TABLESPACE_NAME FROM DBA_PART_INDEXES
WHERE INDEX_NAME = '<index_name>';

           If the field is empty or if an incorrect tablespace is specified, you can adjust the default value as follows:

ALTER INDEX "<index_name>"
MODIFY DEFAULT ATTRIBUTES TABLESPACE <tablespace_name>;

• If a TRUNCATE is executed on a table or partition, Oracle resets the value for NEXT to the NEXT value of the last deleted extent. Therefore, if you had created a table or partition with an unfavorable NEXT value (for example, very small 8K), but you corrected this later (after some extents were already allocated), Oracle resets NEXT to the original NEXT value (in the case of a TRUNCATE). In particular, this may cause problems in BW because automatic TRUNCATEs are executed there in some situations.

           To solve the problem, you must manually correct NEXT directly after a TRUNCATE.

• If you have used a tool such as BRSPACE to move tables to another tablespace, the changes to tables such as TAORA or IAORA may be lost when you upgrade or import Support Packages because the SAP dictionary is "officially" unaware of the changes. If you carry out a system copy later on, segments may be placed in the original tablespace again. Note 778784 describes how you can avoid this problem. See also Note 777615.

20. Can a large number of extents result in performance problems?

              Using an extent size that is too low, and the resulting necessity for a number of extents that are too high, may cause serious performance problems in DMTS due to space transaction enqueues (ST enqueues) if extents are allocated or released (for example, within INSERTs or TRUNCATEs). For more information, refer to the "enqueue" section in Note 619188.

              In LMTS, the presence of a large number of extents is clearly less critical. However, here you should also avoid increasing the number of extents of a segment into five-digit areas.

21. How does "brconnect -f next" determine the NEXT size?

              To determine the NEXT size, BRCONNECT looks at the TGORA/IGORA entries, the object category, the next_max_size parameter, the freespace available in the tablespace, the current NEXT value, the next_special parameter and the block size. The exact algorithm is described in SAP's online documentation.