Oracle® Fusion Middleware Administrator's Guide for Oracle SOA Suite and Oracle Business Process Management Suite 11g Release 1 (11.1.1.7) Part Number E10226-17 |
|
|
PDF · Mobi · ePub |
This chapter describes how to develop a database growth management strategy, including determining the optimal profile or size for the database; monitoring disk space usage, hardware resources, and database performance; understanding growth management challenges and testing strategies; and understanding space management.
This chapter includes the following sections:
Section 9.2, "Identifying the Profile or Size of the Database"
Section 9.3, "Monitoring Space Usage, Hardware Resources, and Database Performance"
Section 9.4, "Understanding Growth Management Challenges and Testing Strategies"
For information about managing database growth with the purge scripts and component table partitioning, see Chapter 10, "Managing Database Growth."
For information about troubleshooting, see Section B.2, "Parallel Purging and Table Partitioning Issues."
Note:
This chapter is intended for database administrators.
An Oracle SOA Suite 11g installation presents several challenges for database administrators, including managing the growth of the Oracle SOA Suite database. Underestimating the importance of managing the database can lead to issues when the database is moved to a production environment. This chapter helps you determine an appropriate strategy and highlights the need for capacity planning, testing, and monitoring.
The recommended strategies are based on the profile or the predicted size of the Oracle SOA Suite installation. The tools and techniques that implement the strategies are straight forward, which helps to simplify the recommendations. However, this does restrict administrators to a set of tools that must be implemented as designed.
Testing and monitoring are required to determine the effectiveness and resource requirements of a growth management strategy. Thorough testing ensures that the tools, the database, and hardware resources all come together to meet the needs of current and future database growth estimates.
This section describes how to identify the profile or size of the Oracle SOA Suite database to determine an optimal growth management strategy. For more information, see Section 9.4, "Understanding Growth Management Challenges and Testing Strategies."
The calculations detailed in this section to approximate disk space usage are not a replacement for conducting a thorough space capacity plan. However, the estimates are sufficient to draw conclusions on the appropriate growth management strategy, while highlighting the need for disk space planning.
Note:
This section provides frequent references to Section 9.3, "Monitoring Space Usage, Hardware Resources, and Database Performance" for details about space calculation instructions.
Table 9-1 provides a profile of small, medium, and large installations based on the composite space persisted daily and the minimum retention space. These two metrics are related in an and/or condition because the retention policy may not retain more than several days of data, but composite inflow may be high.
Table 9-1 Oracle SOA Suite Database Profiles
Database Profile | Composite Space Persisted Daily | Minimum Retention of Space |
---|---|---|
Small |
< 10 GB |
< 100 GB |
Medium |
10-30 GB |
100-300 GB |
Large |
> 30 GB |
> 300 GB |
The rate of composite inflow and space usage is best understood as an average derived after many days of load testing. This allows for allocated space (segment extents) to be better utilized. To understand inflow, the following data points are investigated:
Section 9.2.1.1, "Identifying the Number of Composites Produced Daily."
Section 9.2.1.2, "Identifying the Disk Space Used by Each Composite."
Section 9.2.1.3, "Identifying the Composite Space Persisted Daily."
Section 9.2.1.4, "Analyzing Space Distribution of Oracle SOA Suite Segments."
Use the following formula to identify the number of composites produced daily:
Daily-inflow-composite = (Composite Total / Period)
For example, calculate the average number of composites produced daily based on 5 days of load testing with a total composite count of 100,000:
(20,000 = (100,000 / 5))
For more information, see Section 9.3.1.1, "Determining the Growth Trend of Components."
Use the following formula to identify the disk space used by each composite:
Inflow-space-per-composite = (SOA Schema Size /Composite Total)
For example, calculate the average disk space used by each composite with a total of 100,000 composites and an Oracle SOA Suite schema size of around 200 GB:
(2MB = (200 GB / 100,000))
For more information, see Section 9.3.1.2, "Determining the Growth Trend of the Oracle SOA Suite Schema."
Use the following formula to identify the composite space persisted daily:
Daily-inflow-composite-space = (Daily-inflow-composite * Inflow-space-per-composite)
For example, calculate the average disk space used by composites daily with 20,000 composites daily of 2 MB each:
(40 GB = (20,000 * 2 MB))
Alternatively:
Daily-inflow-composite-space = (SOA Schema Size / Period)
Analyze the shape of the Oracle SOA Suite schema so that the distribution of segment space (tables, indexes, and large objects (LOBs)) is understood and segments that may be problematic are identified:
Determine the growth trend of the components. For more information, see Section 9.3.1.1, "Determining the Growth Trend of Components."
Collect component statistics after each day of load testing so that growth averages can be determined.
Determine the growth trend of the Oracle SOA Suite schema. For more information, see Section 9.3.1.2, "Determining the Growth Trend of the Oracle SOA Suite Schema."
Collect schema sizing statistics after each day of load testing so that growth averages can be determined.
Determine the largest segments. For more information, see Section 9.3.1.3, "Determining the Largest Segments."
Determine the growth trend of tables and indexes. For more information, see Section 9.3.1.4, "Determining the Growth Trend of Tables and Indexes."
The growth statistics for table and indexes are automatically gathered through the Automatic Workload Repository (AWR).
For more information, see Section 9.3.1.1, "Determining the Growth Trend of Components."
You must consider how long composites are retained in the database. This affects the size of the Oracle SOA Suite schema and the performance of the purge scripts. The following factors drive the retention policy:
Legal requirements
Line of business requirements
Overall company policy on retention of data
The longer the retention policy, the greater the volume of data that must be stored and, correspondingly, the higher the disk capacity requirements.
Use the following formula to identify the minimum retained disk space. For information about identifying Daily-inflow-composite-space
, see Section 9.2.1.3, "Identifying the Composite Space Persisted Daily."
Min-space-retain = Daily-inflow-composite-space * Retention Period in days
For example, calculate the minimum retained disk space with 40 GB of composite space persisted daily and a retention period of 10 days:
(400 GB = (40 GB * 10))
Use the following formula to identify the minimum number of retained composites. For information about identifying Daily-inflow-composite
, see Section 9.2.1.1, "Identifying the Number of Composites Produced Daily."
Min-composites-retain = Daily-inflow-composite * Retention Period in days
For example, calculate the minimum number of retained composites with 20,000 composites created daily and a retention period of 10 days:
(200,000 = (20,000 * 10))
From this, it can be stated that 200,000 composites retain on average 400 GB of data based on a 10 day retention policy.
Note:
The above calculation defines an approximation on the minimum disk space usage. The actual minimum disk space usage is more accurately determined through quality assurance testing. For more information, see Section 9.4.2, "Quality Assurance Testing."
The outflow of composites is a measurement of the number of composites that are deleted. It is not a factor when determining the database profile, but is an important metric when evaluating the effectiveness of the growth management strategy.
The goal of measuring outflow is to ensure the following:
That the inflow can be deleted.
A steady state for space usage, hopefully just above the minimum retained disk space usage.
For more information, see Section 9.2.2.1, "Determining the Minimum Retained Disk Space."
There are two cycles that must first be defined:
The purge cycle: Refers to a period that may involve multiple executions of the purge scripts.
The maintenance cycle: Refers to the number of days required to perform all space management operations. For more information, see Section 9.5.1, "Introduction to the Components of a Data File."
The appropriate growth management strategy may include both purging and partitioning. Therefore, outflow should not be measured until eligible partitions have been dropped. The dropping of partitions is a maintenance operation. Therefore, it makes little sense to measure outflow metrics until the maintenance cycle has finished.
The following metrics can be captured before and after the completion of the maintenance cycle to understand the effectiveness of the strategy and to re-evaluate space distribution:
Determine the growth trend of the components. For more information, see Section 9.3.1.1, "Determining the Growth Trend of Components."
Determine the growth trend of the Oracle SOA Suite schema. For more information, see Section 9.3.1.2, "Determining the Growth Trend of the Oracle SOA Suite Schema."
Determine the largest segments. For more information, see Section 9.3.1.3, "Determining the Largest Segments."
Determine the growth trend of tables and indexes. For more information, see Section 9.3.1.4, "Determining the Growth Trend of Tables and Indexes."
Note:
If a steady state has been reached, then the difference between the before and after metrics for components and schema growth should be near zero or negative. Otherwise, the purge strategy may not be coping or the inflow of data may have increased.
Long running composites are composites that remain open beyond the retention period. These composites do not have significant implications for the purge scripts, but their impact is felt when table partitioning is implemented. (Section 9.4, "Understanding Growth Management Challenges and Testing Strategies" recommends that table partitioning be considered for medium and large installations.)
The partitioned tables are (should be) excluded from the purge scripts because their space is reclaimed through the database ALTER
TABLE
…
DROP
PARTITION
command. Long running composites remain open beyond the desired retention period, preventing the partition from being dropped and the space from being reclaimed. Therefore, when estimating the size of the partitioned tables, the retention period that is used should equal the longest running composite:
Retention period = Longest Running Composite.
Understanding space implications of long running transactions and the size of the partitioned tables is best understood through quality assurance testing, as described in Section 9.4, "Understanding Growth Management Challenges and Testing Strategies." However, use the calculations in the following sections to estimate the space used by each table that is partitioned.
It is best to load the tables over a period of several days to produce reasonable averages. Table 9-2 provides details.
Table 9-2 Table Partitioning Recommendations
Action | Command |
---|---|
Determine the average number of rows generated daily for each table. |
(Total rows / period) |
Estimate the number of rows based on the longest running composite. |
((Total rows / period) * Longest running composite in days) |
Estimate space usage for the table and index based on row calculations. |
See Section 9.3.1.5, "Estimating Table Size" and Section 9.3.1.6, "Estimating Index Size." |
Note:
As of Oracle SOA Suite Release 11g R1 (11.1.1.6), a new set of scripts called row migration have been provided. These scripts can move long running composites from one partition to another, thereby facilitating the removal of the partition. However, it is a best practice to plan for the space requirements of the partition tables and not rely on the row migration scripts.
The following sections describe how to monitor space usage:
Section 9.3.1.1, "Determining the Growth Trend of Components"
Section 9.3.1.2, "Determining the Growth Trend of the Oracle SOA Suite Schema"
Section 9.3.1.4, "Determining the Growth Trend of Tables and Indexes"
The following sections describe how to monitor hardware resources and database performance:
Monitoring space is an essential task of database and system administrators to avoid unplanned outages. This section provides advice to help with capacity planning and determine the effectiveness of the growth management strategy. It is not meant to be a comprehensive guide on all aspects of monitoring and capacity planning.
For more information on capacity planning and threshold monitoring, see Oracle Database Administrator's Guide for more information.)
The component master tables record the creation date of each row to use to determine growth trends over a given period:
COMPOSITE_INSTANCE
: CREATED_TIME
CUBE_INSTANCE
: CREATION_DATE
MEDIATOR_INSTANCE
: CREATED_TIME
However, querying the component master tables is limited as purging constantly removes data, making trend predictions difficult. To determine a trend over a given period requires data to be regularly collected with an associated time stamp. This can be achieved with a simple component history table that is populated each day. This table should be sufficient to determine the effectiveness of the growth management strategy and growth trends.
For performance reasons, it may be best to create a history table per component, as shown in Example 9-1.
Example 9-1 History Table Creation
SQL> CREATE TABLE SOA_FABRIC_HIST (SFH_DATE TIMESTAMP, SFH_COUNT NUMBER); SQL> INSERT INTO SOA_FABRIC_HIST SELECT SYSTIMESTAMP, COUNT(*) FROM COMPOSITE_INSTANCE GROUP BY SYSTIMESTAMP; SQL> CREATE TABLE SOA_BPEL_HIST (SBH_DATE TIMESTAMP, SBH_COUNT NUMBER); SQL> INSERT INTO SOA_BPEL_HIST SELECT SYSTIMESTAMP, COUNT(*) FROM CUBE_INSTANCE GROUP BY SYSTIMESTAMP;
and so on.
An alternate to counting the rows in each component table is to query the NUM_ROWS
column from USER_TABLES
as long as the tables are analyzed.
The objects (table, index, and LOBs) created for the Oracle SOA Suite schema may be spread over many tablespaces, but all have the same owner (*soainfra
) to use to group space usage. When trying to determine the space requirements of each composite, measuring the whole schema allows for better space metrics because indexes and LOB segments are taken into account.
To monitor the growth of the Oracle SOA Suite schema, a simple history table can be populated daily. This table should be sufficient to determine the effectiveness of space management. For more information, see Section 9.5, "Understanding Space Management."
Create a schema growth table based on object type, as shown in Example 9-2.
Optionally, a history table by object type (for example, table, index, and LOB) may be helpful when coupled with the largest segment to verify which tables may need partitioning or lack space management. Example 9-3 provides details.
Example 9-3 History Table Creation
SQL> CREATE TABLE SOA_SIZE_TYPE_HIST (SSCH_DATE TIMESTAMP, SSCH_TYPE
VARCHAR2(18), SSCH_SUM_MB NUMBER);
SQL> INSERT INTO SOA_SIZE_TYPE_HIST SELECT SYSTIMESTAMP, SEGMENT_TYPE,
SUM(BYTES)/1024/1024 FROM DBA_SEGMENTS WHERE OWNER='SOA_OWNER' GROUP BY
SYSTIMESTAMP, SEGMENT_TYPE;
Understanding which segments are the largest in the schema helps to determine the tables that may be better managed as range partitioned tables. In addition, monitoring the largest segments may uncover missing space management operations or inappropriate audit settings.
Identify the largest 20 segments for the Oracle SOA Suite schema, as shown in Example 9-4,
The largest segments may be LOB segments, as shown in Example 9-5.
Example 9-5 LOB Segments are Largest Segments
SQL> SELECT L.TABLE_NAME, S.SEGMENT_NAME, (SUM(BYTES)/1024/1024) MB_SIZE FROM
DBA_LOBS L, DBA_SEGMENTS S WHERE S.OWNER='<SOA_OWNER>' AND S.SEGMENT_TYPE =
'LOBSEGMENT' AND S.SEGMENT_NAME = L.SEGMENT_NAME AND S.OWNER='SOA_OWNER''
GROUP BY L.TABLE_NAME, S.SEGMENT_NAME;
or
SQL> SELECT L.TABLE_NAME FROM DBA_LOBS L WHERE L.OWNER ='<SOA_OWNER>' AND
L.SEGMENT_NAME = 'SEGMENT_NAME';
To determine the size of an individual segment:
SQL> SELECT SEGMENT_NAME, (SUM(BYTES)/1024/1024) MB_SIZE FROM DBA_SEGMENTS WHERE OWNER='SOA_OWNER' AND SEGMENT_NAME = 'TABLE_NAME' GROUP BY SEGMENT_ NAME;
Use the OBJECT_GROWTH_TREND
function of the DBMS_SPACE
package to show the trend in space growth for a table. For more information, see Oracle Database Administrator's Guide.
The OBJECT_GROWTH_TREND
function of the DBMS_SPACE
package produces a table of one or more rows, where each row describes the space use of the object at a specific time. The function retrieves the space totals from the AWR or computes current space and combines it with historic space changes retrieved from AWR.
The following example displays the growth in used and allocated space over time for the COMPOSITE_INSTANCE
table.
SQL> SELECT TIMEPOINT, SPACE_USAGE, SPACE_ALLOC, QUALITY FROM TABLE (DBMS_ SPACE.OBJECT_GROWTH_TREND ('DEV_SOAINFRA','COMPOSITE_INSTANCE','TABLE'));
Example 9-6 shows the complete syntax for the dbms_space.object_growth_trend
procedure:
Example 9-6 DBMS_SPACE.OBJECT_GROWTH_TREND Procedure
dbms_space.object_growth_trend ( object_owner in varchar2, object_name in varchar2, object_type in varchar2, partition_name in varchar2 default null, start_time in timestamp default null, end_time in timestamp default null, interval in dsinterval_unconstrained default null, skip_interpolated in varchar2 default 'false', timeout_seconds in number default null, single_datapoint_flag in varchar2 default 'true')
Table 9-3 describes the object_growth_trend
function parameters.
Table 9-3 OBJECT_GROWTH_TREND Function Parameters
Parameter | Description |
---|---|
|
The schema containing the object. |
|
The name of the object. |
|
The type of the object. |
|
The name of the table or index partition, if relevant. Otherwise, specify |
|
A time stamp value indicating the beginning of the growth trend analysis. |
|
A time stamp value indicating the end of the growth trend analysis. The default is |
|
The interval (yes) or not (no). This setting is useful when the result table is displayed as a table rather than a chart. This is because you can see more clearly how the actual recording interval relates to the requested reporting interval. The function returns a table, each row of which provides space use information on the object for one interval. If the return table is very large, the results are pipelined so that another application can consume the information as it is being produced. |
|
Specify whether to skip interpolation of missing values ( |
|
The timeout value for the function in seconds. |
|
Specify whether in the absence of statistics to sample the segment. |
Returned values are shown in Example 9-7.
Example 9-7 Returned Values
TYPE object_growth_trend_row IS RECORD( timepoint timestamp, space_usage number, space_alloc number, quality varchar(20));
Table 9-4 describes the object_growth_trend_row
return values.
Table 9-4 OBJECT_GROWTH_TREND_ROW Return Values
Parameter | Description |
---|---|
|
A timestamp value indicating the time of the reporting interval. Records are not produced for values of |
|
The number of bytes actually being used by the object data. |
|
The number of bytes allocated to the object in the tablespace at that time. |
|
A value indicating how well the requested reporting interval matches the actual recording of statistics. This information is useful because there is no guaranteed reporting interval for object size use statistics, and the actual reporting interval varies over time and from object to object.
|
The create_table_cost
procedure of the dbms_space
package allows the size of a table to be estimated using the predicted number of rows for an existing table or based on a table column definition. The size of tables can vary widely based on the tablespace storage attribute that is accounted for by this procedure. There are two overloads of this procedure:
The first procedure takes the column information of the table.
The second procedure takes the average row size of the table.
Because the Oracle SOA Suite tables already exist, it is the second variant that is most useful. The following example estimates the size of the CUBE_INSTANCE
table with 10.000 rows, an average row length of 360, and a PCT
FREE
0f 10.
Execute the following command. The average row length and current PCT_FREE
is derived from DBA_TABLES
after ensuring that the table has been analyzed:
SQL> SELECT AVG_ROW_LEN, PCT_FREE FROM DBA_TABLES WHERE TABLE_NAME = 'CUBE_INSTANCE' AND OWNER = 'DEV_SOAINFRA';
Execute the dbms_space.create_table_cost
procedure:
set serverout on declare v_used number(10); v_alloc number(10); begindbms_space.create_table_cost ('SOA1_SOAINFRA',360,10000,10, v_used,v_Alloc); dbms_output.put_line('used bytes: ' || to_char(v_used)); dbms_output.put_line('allocated bytes: ' || to_char(v_alloc)); end; /
Example 9-8 estimates the space usage of a table based on its column definition.
Example 9-8 Space Usage Estimates
set serveroutput on declare v_used_bytes number(10); v_allocated_bytes number(10); v_type sys.create_table_cost_columns; begin v_type := sys.create_table_cost_columns ( sys.create_table_cost_colinfo('number',9), sys.create_table_cost_colinfo('varchar2',50), sys.create_table_cost_colinfo('varchar2',15), sys.create_table_cost_colinfo('date'f,null), sys.create_table_cost_colinfo('date'f,null) ); dbms_space.create_table_cost ('users',v_type,10000,7,v_used_bytes,v_allocated_bytes); dbms_output.put_line('used bytes: ' || to_char(v_used_bytes)); dbms_output.put_line('allocated bytes: ' || to_char(v_allocated_bytes)); end; /
Complete syntax for the dbms_space
.create_table_cost
procedure is shown in Example 9-9 and Example 9-10.
First overload:
Example 9-9 First Overload
dbms_space.create_table_cost (tablespace_name in varchar2, colinfos in create_table_cost_columns, row_count in number, pct_free in number, used_bytes out number, alloc_bytes out number); create type create_table_cost_colinfo is object (col_type varchar(200), col_size number);
Second overload:
Both variants require the input values shown in Table 9-5 and Table 9-6.
Table 9-5 CREATE_TABLE_COST Procedure Parameters
Parameter | Description |
---|---|
|
The tablespace in which the object is created. The default is the |
|
The anticipated number of rows in the table. |
|
The percentage of free space you want to reserve in each block for future expansion of existing rows due to updates. In addition, the first variant also requires as input a value for The second variant also requires for each anticipated column values for |
Table 9-6 CREATE_TABLE_COST Procedure Return Values
Parameter | Description |
---|---|
|
The actual bytes used by the data, including overhead for block metadata, |
|
The amount of space anticipated to be allocated for the object taking into account the tablespace extent characteristics. |
The create_index_cost
procedure of the dbms_space
package enables you to estimate the space use cost of creating an index on an existing table. Use this to determine the cost of adding an index to the Oracle SOA Suite schema.
Example 9-11 estimates the size of a new index based on the data definition language (DDL).
Example 9-11 New Index Size
set serveroutput on declare v_used_bytes number(10); v_allocated_bytes number(10); begin dbms_space.create_index_cost( 'create index cube_index on cube_instance(cikey)' v_used_bytes, v_allocated_bytes); dbms_output.put_line('used bytes: ' || to_char(v_used_bytes)); dbms_output.put_line('allocated bytes: '|| to_char(v_allocated_bytes));end; /
The complete syntax for DBMS_SPACE.CREATE_INDEX_COST
procedure is as follows:
dbms_space.create_index_cost (ddl in varchar2, used_bytes out number, alloc_bytes out number, plan_table in varchar2 default null);
The procedure requires the input values shown in Table 9-7.
Table 9-7 DBMS_SPACE.CREATE_INDEX_COST Values
Parameter | Description |
---|---|
|
The |
|
(Optional]) The name of the plan table to use. The default is
|
Usage notes:
The table on which the index is created must already exist.
The computation of the index size depends on statistics gathered on the segment.
It is imperative that the table has been analyzed recently.
In the absence of correct statistics, the results may be inaccurate.
The Oracle database provides a means of monitoring indexes to determine whether they are used. If an index is not used, it can be dropped, eliminating unnecessary statement overhead.
Enter the following statement to start monitoring the usage of an index:
SQL> ALTER INDEX INDEX_NAME MONITORING USAGE;
Enter the following statement to stop monitoring.
SQL> ALTER INDEX INDEX_NAME NOMONITORING USAGE;
The view V$OBJECT_USAGE
can be queried for the index being monitored to see if the index has been used. The view contains a USED
column whose value is YES
or NO
, depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a MONITORING
column (YES
/NO
) to indicate if usage monitoring is currently active. Each time that you specify MONITORING
USAGE
, the V$OBJECT_USAGE
view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify NOMONITORING
USAGE
, no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next ALTER INDEX...MONITORING
USAGE
statement is issued, the view information is left unchanged.
The hardware resource must be able to support the tools selected to implement the growth strategy. It is important to monitor hardware resource during online transaction processing (OLTP) and maintenance periods and especially when these periods overlap.
To monitor CPU, disk, memory, and network O/S resources, Oracle provides OSWatcher Black Box, which must be installed on all tiers in the Oracle SOA Suite installation.
OSWatcher Black Box is a collection of UNIX shell scripts intended to collect and archive operating system and network metrics to aid Oracle Support Services in diagnosing performance issues. OSWatcher Black Box operates as a set of background processes on the server and gathers OS data on a regular basis, invoking such UNIX utilities as vmstat
, netstat
, and iostat
.
The OSWatcher Black Box User Guide and product can be downloaded through Metalink Support note 301137.1.
https://support.oracle.com
Oracle provides AWR and the Automatic Database Diagnostic Monitor (ADDM) to gather and analyze database performance statistics.
AWR can be configured to automatically collect performance statistical snapshots at regular intervals, or it can be manually triggered. The ADDM utility can then analyze the statistics between two snapshot intervals to produce a performance summary report that includes the following:
CPU bottlenecks
Undersized memory structures
I/O capacity issues
High load SQL statements
High load PL/SQL execution and compilation issues, and high-load Java usage
Oracle Real Application Clusters specific issues
Suboptimal use of the Oracle database by the application
Database configuration issues
Concurrency issues
Hot objects
The AWR and ADDM utilities can be executed manually or through Oracle Enterprise Manager Database Control. For more information, see Oracle Database 2 Day + Performance Tuning Guide.
A complete Oracle SOA Suite I/O design plan is beyond the scope of this chapter. However, good advice can be found in the Oracle Database Performance Tuning Guide. A well designed I/O plan considers capacity, availability, and performance.
The AWR reports identify segments, data files, and tablespaces that are accessed frequently. OSWatcher identifies lengthy I/O queues. The combination of these tools identify the disk devices and files of concern. This may identify a need to redistribute files to balance I/O or to modify the redundant arrays of inexpensive disks (RAID) levels on slow volumes.
The I/O calibration feature (Oracle Orion) of the Oracle database issues random I/O using the Oracle data files to access the storage media. This produces results that closely match the actual performance of the database. Oracle Orion is expressly designed for simulating Oracle database I/O workloads using the same I/O software stack as Oracle. Oracle Orion can also simulate the effect of striping performed by Oracle Automatic Storage Management. For more information, see Oracle Database Performance Tuning Guide.
This section provides recommendations on the appropriate tools to manage the growth of the Oracle SOA Suite database. The provided advice is for new installations or existing installations whose current strategy may lack procedures such as monitoring and space management. For additional advice, see Section B.2, "Parallel Purging and Table Partitioning Issues."
It is advisable to read Section 9.3, "Monitoring Space Usage, Hardware Resources, and Database Performance" before this section to become familiar with the Oracle SOA Suite purging and partitioning tools.
The challenges described in this section provide background by describing situations that have contributed to the recommended strategies.
When an ineffective purging strategy has been implemented, the Oracle SOA Suite tables may grow very large. This leads to an urgent need to reclaim space. The larger the tables, the harder it is to delete rows and reclaim space.
The performance of the parallel purge script relies on factors such as CPU resources and the speed of disk I/O. However, very large tables have proven to be challenging. The challenge is due to the amount of data that must be parsed to determine composites that can be deleted. This parsing can monopolize the elapsed time of the entire purge script. For more information, see Section B.2.1.3, "Using Parallel Query Slaves."
This situation highlights the need to constantly monitor the effectiveness of the growth management strategy and to take corrective actions as soon as possible before the tables become very large.
To help the performance of the parallel purge script, tables with long elapsed times can be range partitioned and excluded from the script. However, this raises concerns about downtime, if table partitioning is necessary in a live production environment. While tables can be partitioned online through the redefinition packages, this operation may not complete in a reasonable time when presented with a very large table.As of Oracle SOA Suite Release 11g R1 (11.1.1.6), the granularity of tables that can be partitioned has improved. It is now possible to partition high volume tables with only their master table. This should make partitioning more attractive and feasible. For more information, see Section 10.4.8, "Partial Partitioning of Components."
Long running composites are composites that remain open beyond the retention period. This has implications for Oracle SOA Suite table partitions because they cannot be dropped until all composites they contain are closed. Only a few open composites can prevent a partition from being dropped and space reclaimed. This situation has given rise to the row migration scripts that are available in Oracle SOA Suite Release 11g R1 (11.1.1.6). For more information, see Section 10.4.7, "Moving Active, Long Running Instances to a Different Partition."
Plan for sufficient disk space to accommodate partitioned tables that include the retention period of the longest running composites. The row migration scripts are recommended for corrective actions when there is an urgent need to reclaim space.
The movement of composite data by the row migration script raises the following concerns:
The scripts were written with an assumption that around 5% of the data in a partition be moved to avoid performance concerns with the script.
To maintain the requirements of equipartitioning requires the movement of 5% of the rows across many tables. For more information about equipartitioning, see Section B.2.2.1, "Referential Integrity and Equipartitioning."
The arbitrary figure of 5% does not convey the size of the partition, which depends on inflow rate and partition period (daily, weekly, and monthly).
Repeated migration of long running composites to either the latest partition or to a partition specifically created to pool active composites may lead to an accumulation.
A given partition may become very large and require purging that has other implications. For more information, see Section B.2.2.5, "Partition Pruning."
Testing the growth management strategies is essential to ensure that tools and techniques are practiced and understood.The hardware resources available for quality assurance testing are unlikely to be identical to a production environment. Therefore, administrators must make conservative predictions when projecting results onto the production host.
The following topics are described:
Section 9.4.2.1, "Reviewing Metalink Support Note 1384379.1"
Section 9.4.2.2, "Configuring the Production Audit Level Setting"
Section 9.4.2.3, "Creating an Oracle SOA Suite Schema - Test Environment"
Section 9.4.2.4, "Executing the Parallel or Single Threaded Script and Reclaiming Space"
Section 9.4.2.6, "Partitioning the Tables Causing a Bottleneck"
Section 9.4.2.7, "Repeating Purge Testing and Review and Excluding the Partitioned Table"
Review support note Doc ID 358.1 Maintenance and Administration Advisor: Oracle Fusion Middleware (FMW) SOA 11g Infrastructure Database.
https://support.oracle.com
Ensure that audit settings are appropriate for a production environment. For more information, see Section B.2.3, "Reducing Audit Levels."
Though difficult, it is important to create a test environment that is comparable to a production environment. The performance of the purge scripts are adversely affected by the amount of data that requires parsing. Therefore, a well-sized test environment is important to understand purge performance. For more information, see Section B.2.1.3, "Using Parallel Query Slaves."
The workload mix that populates the schema must resemble a production environment in payload size and number of composite invocations. The data must simulate many days of loading, so that space averages and size estimates can be better determined.
During the loading of data, take time to determine the space used per table and respective space distribution. For more information, see Section 9.2.1, "Identifying the Inflow of Data" for calculations on inflow.
Determine the growth trend of the components. For more information, see Section 9.3.1.1, "Determining the Growth Trend of Components."
Determine the growth trend of the Oracle SOA Suite schema. For more information, see Section 9.3.1.2, "Determining the Growth Trend of the Oracle SOA Suite Schema."
Collect metrics with partitioning in mind:
Determine the largest segments. For more information, see Section 9.3.1.3, "Determining the Largest Segments."
Determine the growth trend of tables and indexes. For more information, see Section 9.3.1.4, "Determining the Growth Trend of Tables and Indexes."
Estimate the table size. For more information, see Section 9.3.1.5, "Estimating Table Size."
Estimate the index size. For more information, see Section 9.3.1.6, "Estimating Index Size."
There are two cycles that must first be understood:
The purge cycle: Refers to a period that may involve multiple executions of the purge scripts.
The maintenance cycle: Refers to the number of days required to perform all space management operations. For more information, see Section 9.5.1, "Introduction to the Components of a Data File."
The goal is to execute the purge scripts and maintenance operations to determine the optimal cycle for each, ensuring that the purge has room to grow. The purge script may need to be executed multiple times per day and maintenance operations may span multiple days.
The following sections describe how to tune, monitor, and reclaim space:
Parallel purge. For more information, see Section B.2.1, "Executing the Parallel Purge Script."
Hardware and database monitoring. For more information, see Section 9.3.2, "Monitoring the Hardware Resources and Database."
Space management operations. For more information, see Section 9.5.1, "Introduction to the Components of a Data File."
The goals should be as follows:
Determine if the purge can delete and reclaim space, either equal to or above the inflow. For more information, see Section 9.2.2, "Developing a Retention Policy" and Section 9.2.3, "Identifying the Outflow of Data."
Determine how many times the purge must be executed and the total elapsed time of the purge cycle to meet purging requirements. This purge cycle must complete within 80% of the time allocated for its execution.
Determine the bottleneck tables. For more information, see Section B.2.1.4, "Debugging and Tracing Purging Operations."
Review database and O/S reports for resource contention and waits. For more information, see Section 9.3.2, "Monitoring the Hardware Resources and Database."
Large installations should consider partitioning the tables identified as difficult to purge. These tables may also be the largest tables. Table partitioning is a proven method of removing bulk data. The Oracle SOA Suite schema is instrumented with a partition key to facilitate range partitioning.
As of Oracle SOA Suite Release 11g R1 (11.1.1.6), the granularity of tables that can be partitioned has improved. It is now possible to partition high volume tables with only their master table. This should make partitioning more attractive and feasible:
Partial partitioning. For more information, see Section 10.4.8, "Partial Partitioning of Components."
Long running composites. For more information, see Section 9.2.4, "Identifying Long Running Composites and Table Partitioning."
Repeat the purge testing and test results review described in Section 9.4.2.4, "Executing the Parallel or Single Threaded Script and Reclaiming Space" and Section 9.4.2.5, "Reviewing the Testing Results." Exclude the partitioned table.
The following recommended growth management strategies are based on the Oracle SOA Suite database profile:
Section 9.4.3.1, "Recommendations for Large Database Profiles."
Section 9.4.3.2, "Recommendations for Medium Database Profiles."
Section 9.4.3.3, "Recommendations for Small Database Profiles."
The strategies require testing to determine their effectiveness. The testing must ensure the following:
Purge scripts have room to grow.
Partitioned tables have sufficient disk space.
Growth is monitored.
Maintenance is performed with minimal impact to OLTP performance.
Installations with large database profiles must seriously consider table partitioning as part of their strategy. Table range partitioning is a proven method for managing large tables:
Execute the parallel purge excluding the tables that are partitioned. For more information, see Section 10.3.2, "Looped Purge in Parallel Script with dbms_scheduler."
Partition the bottleneck tables to the purge script. For more information, see Section 10.4, "Partitioning Component Tables."
Reclaim space as part of the maintenance cycle. For more information, see Section 9.5.1, "Introduction to the Components of a Data File."
Drop eligible partitions as part of the maintenance cycle. For more information, see Section 10.4.5, "Running the Verification Script."
Monitor the inflow and outflow. For more information, see Section 9.2.2, "Developing a Retention Policy" and Section 9.2.3, "Identifying the Outflow of Data."
Monitor database and hardware resources. For more information, see Section 9.5.2, "Reclaiming Segment and Data File Space."
Installations with medium database profiles have the potential to grow large. Therefore, the recommendations are the same as for large profiles. For more information, see Section 9.4.3.1, "Recommendations for Large Database Profiles."
It is important in medium installations to perform rigorous quality assurance testing and monitoring because table partitioning in a live production environment may require downtime.
The only additional recommendation is that the table recreation script (TRS script) may be an alternative to purging. This depends on the size of the database being truly medium and the availability of downtime during the maintenance windows.
For information about the TRS script, see Section 10.6, "Recreating Tables with Open Composite Instances and Reclaiming Database Space."
Small installations with limited CPU, memory, and disk space should start with the single threaded purge and then move to the parallel purge:
Execute the single threaded purge. For more information, see Section 10.3.1, "Looped Purge Script."
Execute the parallel purge. For more information, see Section 10.3.2, "Looped Purge in Parallel Script with dbms_scheduler."
If the single threaded purge is not performing, then the parallel purge should be tested.
Monitor the inflow and outflow. For more information, see Section 9.2.2, "Developing a Retention Policy" and Section 9.2.3, "Identifying the Outflow of Data."
Monitor database and hardware resources. For more information, see Section 9.5.2, "Reclaiming Segment and Data File Space."
The only additional recommendation is that the TRS script may be an alternative to purging. This depends on the size of the database being truly small and the availability of downtime during the maintenance windows.
Much of the information in this section can be found in the various Oracle Database administration guides. The space management concepts and commands summarized in this section address a common misconception that the Oracle SOA Suite purge scripts reclaim space. To reclaim space, database maintenance operations must be executed.
By default, the Oracle SOA Suite schema is created on locally managed tablespaces with automatic segment space management (ASSM). Therefore, all advice is limited by this situation. This section is not meant as a comprehensive guide to all database space management features.
A data file scan can be divided into the following components:
Segment: Contains a specific type of database object. That is, a table is stored in a table segment, and an index is stored in an index segment.
Extent: A contiguous set of data blocks within a segment. The Oracle database allocates space for segments in units of one extent.
Data block: Also called a database block, this is the smallest unit of I/O-to-database storage. An extent consists of several contiguous data blocks.
Tablespace: Consists of one or more physical data files. A locally managed tablespace maintains a bitmap in the data file header to track free and used space in the data file body. Each bit corresponds to a group of blocks. When space is allocated or freed, the Oracle database changes the bitmap values to reflect the new status of the blocks.
Segments, extents, and data blocks are all logical structures.
Figure 9-1 shows the relationship between logical and physical storage.
To manage space, the Oracle database tracks the state of blocks in the segment. The high water mark (HWM) is the point in a segment beyond which data blocks are unformated and have never been used. When the blocks between the HWM and low HWM are full, the HWM advances to the right and the low HWM advances to the location of the old HWM. Figure 9-2 provides details. As the database inserts data over time, the HWM continues to advance to the right, with the low HWM always trailing behind it. Unless you manually rebuild, truncate, or shrink/deallocate the object, the HWM never retreats.
The following sections provide instructions on common techniques used to reclaim segment and data file space.
The purge scripts delete rows from database segments (tables and indexes) and release space within the data blocks for reuse, but may also cause fragmentation with some space too small for reuse. The space can be defragmented and the extents reclaimed by performing an online segment shrink. The shrink operation consolidates free space below the HWM and compacts the segment. After this, it then moves the HWM and deallocates the space above the HWM.
Data manipulation language (DML) can still be issued during the data movement phase of segment shrink. However, DML operations are blocked for a short time at the end of the shrink operation when the space is deallocated. Indexes are maintained during the shrink operation and remain usable.
The Segment Advisor can identify segments that benefit from online segment shrink. However, after constant purging most Oracle SOA Suite segments should be candidates for online segment shrink operations. For more information on the Segment Advisor, see Oracle Database Administrator's Guide.
The general online segment shrink steps are as follows:
Before executing the shrink command, row movement must be enabled:
SQL> ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;
To shrink a segment:
SQL> ALTER TABLE TABLE_NAME SHRINK SPACE;
The COMPACT
clause lets you divide the shrink segment operation into two phases. When you specify COMPACT
, the segment space is defragmented and rows are compacted, but it postpones the resetting of the HWM and the deallocation of space. Dividing the operations into two phases is useful for large tables and reduces the impact on the blocking of DML during the deallocation phase.
You can reissue the shrink space without the COMPACT
clause during off-peak hours to complete the second phase:
SQL> ALTER TABLE TABLE_NAME SHRINK SPACE COMPACT;
The CASCADE
clause extends the segment shrink operation to all dependent segments of the object. For example, if you specify CASCADE
when shrinking a table segment, all indexes of the table are also shrunk:
SQL> ALTER TABLE TABLE_NAME SHRINK SPACE COMPACT CASCADE;
For very large tables, it is advisable to perform the shrink in two phases and not to use the CASCADE
clause. Perform the COMPACT
operation first, perhaps even compact the basic LOBs before that, then execute the normal shrink command to reclaim the unused space.
All segment types are eligible for online segment shrink, except the following:
Index-organized table (IOT) mapping tables
Tables with row ID-based materialized views
Tables with function-based indexes
Secure file LOBs
Compressed tables
Table 9-8 provides online segment shrinking examples.
Table 9-8 Online Segment Shrink Examples
Scenario | Example |
---|---|
Shrink a large table in two phases. |
SQL> ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT; SQL> ALTER TABLE TABLE_NAME SHRINK SPACE COMPACT; SQL> ALTER TABLE TABLE_NAME SHRINK SPACE; |
Shrink a table and all its dependent segments (including |
SQL> ALTER TABLE TABLE_NAME SHRINK SPACE CASCADE;
|
Shrink a |
SQL> ALTER TABLE TABLE_NAME MODIFY LOB (LOB_NAME) (SHRINK SPACE); |
Shrink a single partition of a partitioned table. |
SQL> ALTER TABLE TABLE_NAME MODIFY PARTITION P1 SHRINK SPACE; |
The DEALLOCATE
UNUSED
command can be used to manually deallocate unused space. This command frees unused space above the HWM. The online segment shrink also releases space above HWM.
SQL> ALTER TABLE TABLE_NAME DEALLOCATE;
Use the optional KEEP
clause to specify the amount of space retained in the segment of table, index, or cluster:
SQL> ALTER TABLE TABLE_NAME DEALLOCATE UNUSED KEEP INTEGER; SQL> ALTER INDEX INDEX_NAME DEALLOCATE UNUSED KEEP INTEGER;
Note:
The UNUSED_SPACE
procedure of the DBMS_SPACE
package returns information about the position of the HWM and the amount of unused space in a segment. For segments in locally managed tablespaces with ASSM, use the SPACE_USAGE
procedure for more accurate information on unused space. (Use the DBA_FREE_SPACE
view to verify the deallocated space.)
Whether to rebuild BTREE indexes is a point of contention for database administrators. Unfortunately, over time the Oracle SOA Suite purge scripts fragment most of the Oracle SOA Suite BTREE indexes in a manner that requires them to be rebuilt to maintain SQL performance.
The purge scripts delete only closed composites, leaving the open ones in each index data block. Because many of the Oracle SOA Suite index keys are monotonically increasing, the free space in the data block is not reused.
SQL> ALTER INDEX INDEX_NAME REBUILD OR COALESCE
Table 9-9 describes the costs and benefits of coalescing or rebuilding indexes.
Table 9-9 Costs and Benefits of Coalescing or Rebuilding Indexes
Rebuild Index | Coalesce Index |
---|---|
Quickly moves index to another tablespace. |
Cannot move index to another tablespace. |
Higher costs: Requires more disk space. |
Lower costs: does not require more disk space. |
Creates new tree and shrinks height, if applicable. |
Coalesces leaf blocks within the same branch of the tree. |
Enables you to quickly change storage and tablespace parameters without having to drop the original index. |
Quickly frees up index leaf blocks for use. |
One method to combat the need for index rebuilding is to convert them to global hash indexes. Hashing monotonically increasing keys distributes them randomly across data blocks, thereby improving space reuse. There are other improvements that include a reduction in buffer busy waits for hot index blocks. However, not all Oracle SOA Suite indexes are good candidates for conversion. See the Oracle FMW 11g R1 SOA with Oracle Database Real Application Clusters Assessment:
http://www.oracle.com/technetwork/database/availability/maa-fmw-soa-racanalysis-427647.pdf
Table partitions can be dropped to remove table data in bulk and reclaim space. Within Oracle SOA Suite, do not drop the partitions unless identified as eligible. For more information, see Section 10.4.5, "Running the Verification Script."
SQL> ALTER TABLE TABLE_NAME DROP PARTITION P1;
Although the DROP
PARTITION
operation takes longer, it is worth specifying the UPDATE
INDEXES
clause to avoid the need to rebuild indexes.
Many table maintenance operations on partitioned tables invalidate (mark UNUSABLE
) the corresponding indexes or index partitions. You must then rebuild the entire index or, for a global index, each of its partitions. The database lets you override this default behavior if you specify UPDATE
INDEXES
in your ALTER
TABLE
statement for the maintenance operation. Specifying this clause tells the database to update the indexes at the time it executes the maintenance operation DDL statement. This provides the benefits described in this section.
The following operations support the UPDATE
INDEXES
clause:
ADD
PARTITION
COALESCE
PARTITION
DROP
PARTITION
EXCHANGE
PARTITION
MERGE
PARTITION
MOVE
PARTITION
SPLIT
PARTITION
TRUNCATE
PARTITION
Under certain circumstances, the partition operations shown in Table 9-10 may be required to assist with issues.
Table 9-10 Partition Operations
Partition Operation | Description |
---|---|
Shrink table partition |
SQL> ALTER TABLE TABLE_NAME MODIFY PARTITION P1 SHRINK SPACE; |
Truncate table partition |
SQL> ALTER TABLE ... TRUNCATE PARTITION |
Compress table partition |
SQL> ALTER TABLE TABLE_NAME MOVE PARTITION PART_NAME TABLESPACE TABLESPACE_NAME NOLOGGING COMPRESS FOR OLTP; Notable restrictions:
|
Secure files are a LOB storage architecture that provides performance benefits that many factor as faster and better than traditional LOB access. Secure files are a complete rewrite of the original LOB storage architecture, now called basic files.
Secure files support the following advanced features:
Deduplication: Stores only one copy of identical secure file data.
Compression: Reduces storage, I/O, redo log, and encryption overhead. The online segment shrink command is not supported for secure file LOBs due to compression.
Encryption
As secure files are a relatively new feature introduced in the Oracle 11g database, it is recommended that the highest available database patch set be applied to avoid known problems.
The following settings are required to use secure files.
Set the COMPATIBLE
initialization parameter higher than 11.0.0.0.0.
SQL> show parameter COMPATIBLE;
The DB_SECUREFILE
initialization parameter controls the default action of the database with regards to LOB storage (default if PERMITTED
):
SQL> ALTER SYSTEM SET DB_SECUREFILE = 'ALWAYS'; SQL> SHOW PARAMETER DB_SECUREFILE
Parameter | Description |
---|---|
|
All LOBs in ASSM tablespaces are created as secure file LOBs. LOBs in non-ASSM tablespaces are created as basic file LOBs unless explicitly specified as secure files. Basic file storage options are ignored, and secure file default storage options are used for any unspecified options. |
|
All LOBs are created as secure file LOBs. If the LOB is being created in a non-ASSM tablespace, an error is thrown. Basic file storage options are ignored, and secure file default storage options are used for any unspecified options. |
|
The default setting that enables secure file LOB storage when the |
|
Secure file LOBs are not permitted. |
|
Prevents creation of secure file LOBs, and ignores any errors associated with secure file storage options. |
Configure the tablespace to support ASSM:
SQL> SELECT SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME
= 'TABLESPACE_NAME';
The Repository Creation Utility (RCU) that creates the Oracle SOA Suite tables by default also creates basic files. However, secure file LOBs can also be created at the time of SOA schema creation. The process below is only for the time of creation. Otherwise, see Section 9.5.2.5.3, "Migrating Secure Files."
Ensure the requirements are met and the DB_SECUREFILE
database initialization parameter is set to ALWAYS
or FORCE
. For more information about requirements, see Section 9.5.2.5.1, "Secure File Requirements."
Run the appropriate Oracle SOA Suite RCU utility to create the schema. Although the LOBs are defined as basic, they are created as secure files. The basic file LOB storage parameters are ignored.
The advanced features of compression, deduplication, and encryption are not enabled by default.
Enter the following command to determine if the advanced features are enabled for the secure file LOB column:
SQL> SELECT TABLE_NAME, COLUMN_NAME, SECUREFILE, RETENTION, ENCRYPT, COMPRESSION, DEDUPLICATION FROM DBA_LOBS;
The ALTER
command to enable the advanced features must be performed immediately after using RCU:
SQL> ALTER TABLE LOB_TABLE MODIFY LOB(LOB_COLUMN) (COMPRESS); SQL> ALTER TABLE LOB_TABLE MODIFY LOB(LOB_COLUMN) (DEDUPLICATE);
Note:
Oracle recommends that you enable compression, deduplication, or encryption through the CREATE
TABLE
statement and/or online redefinition. For existing data, if you enable these features through the ALTER
TABLE
statement, all secure file LOB data in the table is read, modified, and written. This causes the database to lock the table during a potentially lengthy operation. Therefore, the ALTER
table command is not recommended for converting populated tables, and causes locking.
The online redefinition is the recommended online method for converting to secure file LOBs. However, there are offline methods:
Create Table as Select (CTAS) script
Insert Table as Select (ITAS)
Export/import
TRS script
For information, see Section 10.6, "Recreating Tables with Open Composite Instances and Reclaiming Database Space."
Advantages of online redefinition:
No requirement to take the table or partition offline.
Can be performed in parallel.
Disadvantages of online redefinition:
Additional storage equal to the entire table or partition and all LOB segments must be available.
Global indexes must be rebuilt.
For best practices about executing the online REDFINITON
package, including preventing redo generation and parallel execution, see Section "Migrating Columns from BasicFiles LOBs to SecureFiles LOBs" of the Oracle Database SecureFiles and Large Objects Developer's Guide.
This section describes additional methods available for managing database growth.
The TRS script only selects open composites and therefore by default drops all closed composites beyond a given retention period. The process of recreating the table and indexes also reorganizes and reclaims space. For more information, see Section 10.6, "Recreating Tables with Open Composite Instances and Reclaiming Database Space."
The truncate statement removes all rows from a table. Therefore, this is unlikely to be performed or required in a Oracle SOA Suite production environment. For more information, see Section 10.5, "Removing Records from the Runtime Tables Without Dropping the Tables."
To first avoid application errors and the need for manual intervention when a tablespace runs out of space, set the data files to AUTOEXTEND
.
To determine whether a data file is auto-extensible, query the DBA_DATA_FILES
view column AUTOEXTENSIBLE
:
SQL> SELECT AUTOEXTENSIBLE FROM DBA_DATA_FILES
Specify automatic file extension by specifying an AUTOEXTEND
ON
clause when the data file is created or altered:
SQL> ALTER TABLESPACE TABLESPACE_NAME ADD DATAFILE '/U01/DATAFILE.DBF' SIZE 10M AUTOEXTEND ON; SQL> ALTER DATABASE DATAFILE '/U01/DATAFILE.DBF' AUTOEXTEND ON;
If the initial allocation of the data files was excessive or segments were allowed to grow excessively, the data file can be resized.
Resize the data file. However, this is assuming that the space is not used by segments.
SQL> ALTER DATABASE DATAFILE '/U01/DATAFILE.DBF'' RESIZE 50M;
It is not always possible to decrease the size of a file to a specific value. In which case, the database returns the following error.
ORA-03297: file contains used data beyond requested RESIZE value