Space Management-Srinivas Maddali

At database level – space management by resizing files associated with database

You can

(1) resize datafiles
(2) resize tempfiles

You can not
(1) resize redo log files but can drop and add log file/s and group/s

You do not need to think of as oracle cares of the extension of expansion
(1) controlfiles

data file example:

–to resize a data file of 512M to 1024M
— upto 9i
alter database datafile ‘<file_path_with_name>’ resize 1024M;

–from 10g
alter database datafile ‘<file_path_with_name>’ resize 1G;
–to resize a data file of 512M to 256M – this relases space to the OS
alter database datafile ‘<file_path_with_name>’ resize 256M;

Note and caution
– this command sometimes may fail if the high water mark for the file could not be reset

(b) temp file example:
–to resize a temp file of 512M to 1024M
alter database tempfile ‘<file_path_with_name>’ resize 1024M;

–to resize a temp file of 512M to 256M – this relases space to the OS
alter database tempfile ‘<file_path_with_name>’ resize 256M;

Note and caution
– this command sometimes may fail if the high water mark for the file could not be reset

(c) online redolog files
to resize the members of a group, flush the redo and make them inactive and then drop the existing –group and add new group.

— add new group and 2 members

alter database add logfile group <group_number> (‘<logfile_path_and_member_name>’,'<logfile_path_and_member_name>’) size 512M;

— drop logfile members
alter database drop logfile member ‘<logfile_path_and_member_name>’;
— drop logfile group
alter database drop logfile group <group_number>;
— to add another member to a group
alter database add logfile member ‘<logfile_path_and_member_name>’ to group <group_number>;
At tablespace level – space management of files associated with tablespace/s

to add a new data file to a tablespace with tables/indexes:

alter tablespace <tablespace_name>
add datafile ‘<datafile_path_and_name>’
size 1024M
autoextend on next 10k
maxsize 100k;

to drop a data file with no data:

alter tablespace <tablspace_name> drop datafile ‘<datafile_path_and_name>’;

to add a new data file to a temporary tablespace

alter tablespace <temp_tablespace_name>
add tempfile ‘<datafile_path_and_name>’
size 512m
autoextend on;

At table level – space management of files associated with table/s

The physical_attributes_clause lets you change the value of the PCTFREE, PCTUSED, and INITRANS parameters and storage characteristics. Please refer to physical_attributes_clause and storage_clause for a full description of these parameters and characteristics.

Restrictions on Altering Table Physical Attributes

Altering physical attributes is subject to the following restrictions:

You cannot specify the PCTUSED parameter for the index segment of an index-organized table.

If you attempt to alter the storage attributes of tables in locally managed tablespaces, then Oracle Database raises an error. However, if some segments of a partitioned table reside in a locally managed tablespace and other segments reside in a dictionary-managed tablespace, then the database alters the storage attributes of the segments in the dictionary-managed tablespace but does not alter the attributes of the segments in the locally managed tablespace, and does not raise an error.

For segments with automatic segment-space management, the database ignores attempts to change the PCTUSED setting. If you alter the PCTFREE setting, then you must subsequently run the DBMS_REPAIR.SEGMENT_FIX_STATUS procedure to implement the new setting on blocks already allocated to the segment.

Cautions on Altering Tables Physical Attributes

The values you specify in this clause affect the table as follows:

For a non-partitioned table, the values you specify override any values specified for the table at create time.

For a range-, list-, or hash-partitioned table, the values you specify are the default values for the table and the actual values for every existing partition, overriding any values already set for the partitions. To change default table attributes without overriding existing partition values, use the modify_table_default_attrs clause.

For a composite-partitioned table, the values you specify are the default values for the table and all partitions of the table and the actual values for all subpartitions of the table, overriding any values already set for the subpartitions. To change default partition attributes without overriding existing subpartition values, use the modify_table_default_attrs clause with the FOR PARTITION clause
table_compression
The table_compression clause is valid only for heap-organized tables. Use this clause to instruct Oracle Database whether to compress data segments to reduce disk and memory use. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression.
Note:

The first time a table is altered in such a way that compressed data will be added, all bitmap indexes and bitmap index partitions on that table must be marked UNUSABLE.
allocate_extent_clause

Use the allocate_extent_clause to explicitly allocate a new extent for the table, the partition or subpartition, the overflow data segment, the LOB data segment, or the LOB index.

Restriction on Allocating Table Extents

You cannot allocate an extent for a temporary table or for a range- or composite-partitioned table.

deallocate_unused_clause

Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the table, partition or subpartition, overflow data segment, LOB data segment, or LOB index and make the space available for other segments in the tablespace.

shrink_clause

The shrink clause lets you manually shrink space in a table, index-organized table or its overflow segment, index, partition, subpartition, LOB segment, materialized view, or materialized view log. This clause is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space immediately.

Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.

COMPACT

If you specify COMPACT, then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE … SHRINK SPACE statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step.

For an index or index-organized table, specifying ALTER [INDEX | TABLE] … SHRINK SPACE COMPACT is equivalent to specifying ALTER [INDEX | TABLE … COALESCE. The shrink_clause can be cascaded (please refer to the CASCADE clause, which follows) and compacts the segment more densely than does a coalesce operation, which can improve performance. However, if you do not want to release the unused space, then you can use the appropriate COALESCE clause.
CASCADE

If you specify CASCADE, then Oracle Database performs the same operations on all dependent objects of table, including secondary indexes on index-organized tables.

Restrictions on the shrink_clause, 10gR1

1. You cannot specify this clause for a cluster, a clustered table, or any
object with a LONG column.
2. Segment shrink is not supported for LOB segments even if CASCADE is
specified.
3. Segment shrink is not supported for tables with function-based indexes.
4. This clause does not shrink mapping tables or overflow segments of
index-organized tables, even if you specify CASCADE.
5. You cannot shrink a table that is the master table of an ON COMMIT
materialized view. Rowid materialized views must be rebuilt after the
shrink operation.
6. Table with a domain index is not supported.

Restrictions on the shrink_clause, 10gR2

1. You cannot specify this clause for a cluster, a clustered table, or any
object with a LONG column.
2. Segment shrink is not supported for tables with function-based indexes or
bitmap join indexes.
3. This clause does not shrink mapping tables of index-organized tables,
even if you specify CASCADE.
4. You cannot specify this clause for a compressed table.
5. You cannot shrink a table that is the master table of an ON COMMIT
materialized view. Rowid materialized views must be rebuilt after the
shrink operation.
6. Table with a domain index is not supported.

Enhancements in 10.2.0.3.0

alter < segment_type> < segment_name> shrink space parallel < degreee>;
or
alter < segment_type> < segment_name> shrink space compact parallel < degreee>;

Allocate new extent

ALTER TABLE employees ALLOCATE EXTENT (SIZE 5K);

How to reset HWM on a table?

(a) By shrinking
(b) By moving table segments and rebuilding index segments

Before shrinking any table for releasing space enable the row movement of the table.

1. Enable row movement for the table.
ALTER TABLE <owner>.<table_name> ENABLE ROW MOVEMENT;
2. Shrink table but don’t want to shrink HWM (High Water Mark).
ALTER TABLE <owner>.<table_name> SHRINK SPACE COMPACT;
3. Shrink table and HWM too.
ALTER TABLE <owner>.<table_name> SHRINK SPACE;
4. Shrink table and all dependent index too.
ALTER TABLE <owner>.<table_name> SHRINK SPACE CASCADE;
5. Shrink table under MView.
ALTER TABLE <owner>.<table_name> SHRINK SPACE;
6. Shrink Index only.
ALTER INDEX <owner>.<index_name> SHRINK SPACE;
7. Disable the row movement
ALTER TABLE <owner>.<index_name> DISABLE ROW MOVEMENT;
Availability

Segment shrink is done online, thereby it increases the availability of the object. While conventional DML operations can coexist with segment shrink, parallel DMLs cannot.

During segment shrink, data will be moved as part of the compaction phase. During compaction locks will be held on individual rows and/or blocks containing the data. This will cause the concurrent DMLs like updates and deletes to serialize on the locks. The compaction will be done in units of smaller transactions, so the availability of the object will not be impacted significantly.

However during certain phases of segment shrink (when the HWM is adjusted), the segment will have to be locked in exclusive mode.

This phase is for a very short duration and should impact the availability of the object less significantly.

Move the table and partition segments and rebuild the index and index partition segments

ALTER TABLE <owner>.<table_name> MOVE [TABLESPACE <tablespace_name>] [PCTUSED xx] [PCTFREE xx] [INITRANS xx];

Which is FASTER?

The ALTER TABLE MOVE command is indeed faster than the ALTER TABLE SHRINK command.

The shrink algorithm starts from the bottom of the segment and starts moving those rows to the beginning of the segment. Shrink is a combination of delete/insert pair for every row movement and this generates many UNDO and REDO blocks.

Therefore, it is normal for a shrink operation to take longer than a move operation which is simply a move of data blocks to another location without any delete/inserts .

The alter table xxx move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same. The alter table move syntax also preserves the index and constraint definitions.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:492636200346818072

As data is placed into the table over time and more blocks get used, the HWM rises. If we delete some (or even all) of the rows in the table, we might have many blocks that no longer contain data, but they are still under the HWM, and they will remain under the HWM until the object is rebuilt, truncated, or shrunk (shrinking of a segment is a new Oracle 10g feature that is supported only if the segment is in an ASSM tablespace).

The HWM is relevant since Oracle will scan all blocks under the HWM, even when they contain no data, during a full scan. This will impact the performance of a full scan especially if most of the blocks under the HWM are empty. To see this, just create a table with 1,000,000 rows (or create any table with a large number of rows), and then execute a SELECT COUNT(*) from this table. Now, DELETE every row in it and you will find that the SELECT COUNT(*) takes just as long (or longer, if you need to clean out the block! Refer to the ‘Block Cleanout’ section of Chapter 9) to count 0 rows as it did to count 1,000,000. This is because Oracle is busy reading all of the blocks below the HWM to see if they contain data. You should compare this to what happens if you used TRUNCATE on the table instead of deleting each individual row. TRUNCATE will reset the HWM of a table back to ‘zero’ and will truncate the associated indexes on the table as well. If you plan on deleting every row in a table, TRUNCATE¿if it can be used would be the method of choice for this reason.

In an MSSM tablespace, segments have a definite HWM. In an ASSM tablespace, however, there is an HWM and a low HWM. In MSSM, when the HWM is advanced (e.g., as rows are inserted), all of the blocks are formatted and valid, and Oracle can read them safely. With ASSM, however, when the HWM is advanced Oracle doesn’t format all of the blocks immediately they are only formatted and made safe to read upon their first use. So, when full scanning a segment, we have to know if the blocks to be read are ‘safe’ or unformatted (meaning they contain nothing of interest and we do not process them). To make it so that not every block in the table need go through this safe/not safe check, Oracle maintains a low HWM and a HWM. Oracle will full scan the table up to the HWM¿and for all of the blocks below the low HWM, it will just read and process them. For blocks between the ‘low HWM’ and the HWM, it must be more careful and refer to the ASSM bitmap information used to manage these blocks in order to see which of them it should read and which it should just ignore.