Guenadi N Jilevski's Oracle BLOG


Segments Shrink in Oracle 10g

Segments Shrink in Oracle 10g

Shrink segments online and in real time.

Segments that undergo significant data manipulation language (DML) activity, such as UPDATE and DELETE operations, can become sparsely populated, with chunks of free space within their data blocks. Besides simply wasting space, sparsely populated segments can also lead to poor performance, because operations such as a full table scan will need to scan more blocks than necessary to retrieve data.

Before Oracle Database 10g, you could reclaim the free segment space by dropping the table, re-creating it, and then reloading the data. You could also use the ALTER TABLE MOVE command to move the table to a different tablespace. Both of these processes, however, must occur with the table offline. Online table reorganization is another way to reclaim space, but it requires significant disk space.

Now Shrink

With Oracle Database 10g, you can now shrink segments directly, without taking the tablespaces or the objects offline. The process of shrinking a segment includes two key phases:

  • Segment data is compacted. Through a series of INSERT and DELETE statements (during which DML-compatible locks are held on individual rows or blocks of the table), the segment data is moved as far to the beginning of the segment as possible. Given that rowids change, you must enable row movement and also disable any triggers based on rowid for table segments you want to shrink.
  • High-water mark (HWM) is adjusted to an appropriate location (exclusive locks are held on the data at this point), and unused space is deallocated from the segment, so it is available for the tablespace to reallocate to other objects as needed.

The shrink capability is implemented in Oracle Database 10g as an optional SHRINK SPACE clause on the ALTER… SQL statements for the associated object. The SHRINK SPACE clause performs both phases of the shrink process. You can also use the optional COMPACT clause in conjunction with the SHRINK SPACE clause to perform just the first phase—the compacting—by itself, to defer the locking of the second phase, for example, and then issue the SHRINK SPACE clause (without COMPACT) later to complete the process, as in



and later, run



But how do you identify which segments to shrink? And how do you make this process a regular part of maintaining system performance? Fortunately, with Oracle Database 10g, segment usage data is captured, by default, along with all the other statistical data captured by the Automatic Workload Repository (AWR) infrastructure. The segment usage information is easy to obtain with the advisor infrastructure (DBMS_ADVISOR)—specifically, the Segment Advisor, a simple-to-use new feature provided with Oracle Database 10g that identifies which segments have significant free space, and, therefore, are good candidates for segment shrinking.

Getting Started with the Oracle Database 10g Segment Advisor

You can run the Segment Advisor against specific objects (tables, indexes, and materialized views), against an entire tablespace, or against multiple tablespaces.

As with the other advisors provided by Oracle Database 10g, you can launch the Segment Advisor by using Oracle Enterprise Manager Database Control or the DBMS_ADVISOR built-in PL/SQL package.

You can launch the Segment Advisor from several places in the Enterprise Manager Database Control (or Grid Control) browser, such as Advisor Central (from the Enterprise Manager home page), or from the specific database object management page (the Tables or Index page, for example) by selecting Run Segment Advisor from the drop-down menu, with the specific table, index, or materialized view selected.

You can proactively run the Segment Advisor against the specific table you suspect may have a segment usage issue. For example, you might run the Segment Advisor if you’ve just purged 5,000 old accounts from a 25,000-customer table or run it against a complete tablespace you use as working storage, to stage or cleanse data for a data warehouse.

Whether you launch the Segment Advisor from Advisor Central or within the context of a specific object or tablespace, you initiate a four-page sequence of configuration pages in which you define a Segment Advisor task for submission to the job subsystem.

The four pages of the Segment Advisor wizard step you through the settings for defining the Segment Advisor task’s parameters, including the following:

Advisor mode. Can be run in Limited or Comprehensive mode. In Comprehensive mode, when the task runs, the Segment Advisor samples the objects being analyzed, in addition to using already gathered statistics on the objects from the AWR. Limited mode relies on existing statistics only—so if your statistics aren’t current (or don’t exist at all), Segment Advisor will generate no recommendations.

Time. Limited or unlimited time for analysis by the Segment Advisor task.

Schedule. Whether to run the task immediately or schedule it for later, such as during a maintenance window (this is the default), and whether the task repeats. You can also change the system-generated task name on this page.

Review. Includes a list of your Segment Advisor settings and a Submit button to submit the new task to the job scheduler.

The task is also stored as an object in the AWR (the default retention is for 30 days), so you can rerun it anytime later.

Shrink the Segment

Once the task completes its analysis, you can review the advisor’s findings on the Recommendations page, available from Advisor Central. The page lists all the segments (table, index, and so on) that constitute the object under review. The default view (“View Segments Recommended to Shrink”) lists any segments that have free space you can reclaim.

In the example the Segment Advisor recommends shrinking several segments from different tablespaces (owned by different users). The page shows the segment name, the space allocated to the segment, the used space, the reclaimable space, and recommendations such as “Perform shrink, estimated savings is 14284326 bytes.”

Two shrink options are available on this page. In this example, with an index segment selected, “Compact Segments and Release Space” is equivalent to:


and “Compact Segments” is equivalent to



You can choose to implement the recommendation directly from this page, selecting as many tables, indexes, or other listed objects as you like and then clicking on the Schedule Implementation button.

The next page lets you set the time for shrinking the segment or segments selected. Your selection then goes to the job subsystem as a series of SQL statements executed immediately or per your schedule.

Whether you implement a recommendation or simply select Shrink Segment from the drop-down menu on another page of Enterprise Manager, the appropriate SQL statements are submitted to the job subsystem to shrink the segment.

You can also select “View other Segments” to view the status of all other segments and obtain this same level of detail, but in the case of segments in which the advisor doesn’t find extra space, recommendations include information such as “The free space in the object is less than the size of the last extent” or “The object has less than 1% free space, it is not worth shrinking.”

Using the DBMS_ADVISOR Built-in Package

The Segment Advisor wizard provided by Enterprise Manager Database Control (and Grid Control) uses the functionality of the DBMS_ADVISOR built-in PL/SQL package of the Oracle database. If you prefer, you can call the various subroutines of this package from the command line or by using scripts. Listing 1 shows a script that calls DBMS_ADVISOR and creates a Segment Advisor task.

The DBMS_ADVISOR built-in PL/SQL package, new with Oracle Database 10g, lets you create the complete array of advisor tasks, such as SQL Tuning Advisor and SQL Access Advisor, many of which previous Talking Tuning columns have discussed. The Segment Advisor is another subsystem (or set of procedures) available in that package.

Code Listing 1: Script for creating a Segment Advisor task on a table









NAME := ” ;









Whether you use Enterprise Manager or the DBMS_ADVISOR package, the Segment Advisor is easy to run and lets you quickly find areas in your database where you can reclaim space. It finds the pockets of reclaimable space in segments and then, rather than making you unload the table and rebuild it offline, lets you shrink segments online and in place. No additional storage is needed, because the operation effectively does an INSERT and a DELETE right in the object itself.

May 24, 2008 Posted by | oracle | 2 Comments

Database duplication with RMAN on remote host

Database duplication with RMAN on remote host

In this scenario I will duplicate database on a remote host with the same directory structure as of original database. In this example original database is dbase and the hostname of the original database is zeus while the duplicate database name will be dbdup and the hostname of the duplicate database is hera.

The following steps are required to perform the operation.

A)Backup the original database. Here original database is dbase on host zeus.

i)SQL> host rman TARGET /

Recovery Manager: Release – Production on Tue May 13 02:50:23 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)


Starting backup at 13-MAY-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=21 stamp=654577550
channel ORA_DISK_1: starting piece 1 at 13-MAY-08
channel ORA_DISK_1: finished piece 1 at 13-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030550_42lhghq3_.bkp tag=TAG20080513T030550 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 13-MAY-08

Starting backup at 13-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata2/data1/dbase/system01.dbf
input datafile fno=00003 name=/oradata2/data1/dbase/sysaux01.dbf
input datafile fno=00008 name=/oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf
input datafile fno=00002 name=/oradata2/data1/dbase/undotbs01.dbf
input datafile fno=00004 name=/oradata2/data1/dbase/users01.dbf
input datafile fno=00005 name=/oradata2/data.dbf
input datafile fno=00006 name=/oradata2/data1/data02.dbf
input datafile fno=00009 name=/oradata2/data_test.dbf
input datafile fno=00007 name=/oradata2/6.dbf
channel ORA_DISK_1: starting piece 1 at 13-MAY-08
channel ORA_DISK_1: finished piece 1 at 13-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_nnndf_TAG20080513T030552_42lhgk10_.bkp tag=TAG20080513T030552 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 13-MAY-08
channel ORA_DISK_1: finished piece 1 at 13-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_ncsnf_TAG20080513T030552_42lhjxkf_.bkp tag=TAG20080513T030552 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 13-MAY-08

Starting backup at 13-MAY-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=4 recid=22 stamp=654577630
channel ORA_DISK_1: starting piece 1 at 13-MAY-08
channel ORA_DISK_1: finished piece 1 at 13-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030710_42lhjzro_.bkp tag=TAG20080513T030710 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 13-MAY-08

B)Copy the backup set of datafile and archived redo log files to hera as to the same path as it was in recorded in control file in terminus.

$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030550_42lhghq3_.bkp oracle@hera:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/

o1_mf_annnn_TAG20080 100% |***********************************************| 25088 00:00
$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_nnndf_TAG20080513T030552_42lhgk10_.bkp oracle@hera:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/

o1_mf_nnndf_TAG20080 100% |***********************************************| 564 MB 01:03
$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030710_42lhjzro_.bkp oracle@hera:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/

o1_mf_annnn_TAG20080 100% |***********************************************| 7680 00:00
$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030409_42lhdrxt_.bkp oracle@hera:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/

o1_mf_annnn_TAG20080 100% |***********************************************| 90210 KB 00:10
$ scp /oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/
o1_mf_annnn_TAG20080513T030409_42lhf17z_.bkp oracle@hera:/oradata2/flash_recovery_area/DBASE/backupset/2008_05_13/

o1_mf_annnn_TAG20080 100% |***********************************************| 5331 KB 00:00

B)Prepare the auxiliary instance. Auxiliary instance will be created in hera host and name of the auxiliary instance is dbdup.

i)Create pfile with parameter db_name=dbdup . If you want to rename control file set control_files parameter in pfile.

$ vi /oradata2/initdbdup.ora

ii)Set the Oracle sid in hera to dbdup.
$ export ORACLE_SID=dbdup

iii)Create spfile from pfile.
$ sqlplus / as sysdba
SQL*Plus: Release – Production on Tue May 13 03:31:02 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile=’/oradata2/initdbdup.ora’;

ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes

SQL> CREATE SPFILE FROM PFILE=’/oradata2/initdbdup.ora’;

File created.

iv)Start the auxiliary instance with spfile.
SQL> startup force nomount
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes

C)Connect to the hera machine(auxiliary database) RMAN and issue following command.

$ rman target sys/a@dbase AUXILIARY /

Recovery Manager: Release – Production on Tue May 13 04:23:45 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: DBASE (DBID=1509380669)
connected to auxiliary database: DBDUP (not mounted)

4> }

database opened
Finished Duplicate Db at 13-MAY-08

D)now your database duplication is ok. Work and Test with that.

RMAN> exit
Recovery Manager complete.

$ sqlplus / as sysdba
SQL*Plus: Release – Production on Tue May 13 05:02:17 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release – Production
With the Partitioning, OLAP and Data Mining options

SQL> select open_mode from v$database;




May 17, 2008 Posted by | oracle | Leave a comment

Automatic storage management (ASM) FAQ for Oracle 10g and 11g R1

Automatic storage management (ASM) FAQ for Oracle 10g and 11g R1

I have tried to consolidate list of Frequently Asked Questions (FAQ) on ASM. Please feel free to ask any other question or contribute to this FAQ by using comment section

Frequently Asked Question on ASM

1) What is ASM?

Automatic storage Management (ASM) is a new type of filesystem which was introduced with Oracle 10g. ASM is recommended filesystem for RAC and Single instance ASM for storing database files. This provides direct I/O to the file and performance is comparable with that provided by RAW Devices. Oracle creates a separate instance for this purpose.

2) How do we identify if we are connected to Normal Instance or ASM instance?

Issue following command to identify this

SQL> show parameter instance_type


———————————— ———– ——————————

instance_type string asm

In case you are connected to ASM instance, it will display value as asm, otherwise it will display value as RDBMS.

3) What are Diskgroup’s and Failuregroups?

Diskgroup is a terminology used for logical structure which holds the database files. Each Diskgroup consists of Disks/Raw devices where the files are actually stored. Any ASM file is completely contained within a single disk group. However, a disk group might contain files belonging to several databases and a single database can use files from multiple disk groups.

Failuregroups are used when using Normal/High Redundancy. They contain the mirrored ASM extents and must be containing different disks and preferably on separate disk controller.

4)Can ASM be used as replacement for RAID?

ASM is supposed to stripe the data and also mirror the data (if Using Normal, High Redundancy). So this can be used as a alternative for RAID 0+1 solutions

5) How does ASM provides Redundancy?

When you create a disk group, you specify an ASM disk group type based on one of the following three redundancy levels:

  • Normal for 2-way mirroring – When ASM allocates an extent for a normal redundancy file, ASM allocates a primary copy and a secondary copy. ASM chooses the disk on which to store the secondary copy in a different failure group other than the primary copy.
  • High for 3-way mirroring. In this case the extent is mirrored across 3 disks.
  • External to not use ASM mirroring. This is used if you are using Third party Redundancy mechanism like RAID, Storage arrays.

6) Can we change the Redundancy for Diskgroup after its creation.

No, we cannot modify the redundancy for Diskgroup once it has been created. To alter it we will be required to create a new Diskgroup and move the files to it. This can also be done by restoring full backup on the new Diskgroup. Following metalink note describes the steps

Note.438580.1 – How To Move The Database To Different Diskgroup (Change Diskgroup Redundancy)

7) I am unable to open the ASM instance. What is the reason?

ASM instance does not have open stage. It has got only two options

  • Nomount- This starts the ASM instance
  • Mount- At this stage, Diskgroup defined in ASM_DISKGROUPS parameter are mounted

When you try to open the ASM instance , you get following error

SQL> alter database open;

alter database open


ERROR at line 1:

ORA-15000: command disallowed by current instance type

8) Can ASM instance and database (rdbms) be on different servers?

ASM instance and Database (rdbms) have to be present on same server. Otherwise it will not work.
9)Can we see the files stored in the ASM instance using standard unix commands.

No, you cannot see the files using standard unix commands like ls. You need to use utility called asmcmd to do this. This is present in 10.2 and above.e.g

You can use help command to see the options.

Note: – You can use asmcmd for 10.1 database also. For this you can copy the asmcmdcore and asmcmd file from 10.2 ORACLE_HOME to 10.1 ORACLE_HOME. Else you can download the files from Metalink Note:332853.1

10) What kind of files can be stored on ASM Diskgroup.

You can store the following file types in ASM disk groups:

  • Control files
  • Datafiles, temporary datafiles, and datafile copies
  • Online redo logs, archive logs, and Flashback logs
  • RMAN backups
  • Disaster recovery configurations
  • Change tracking bitmaps
  • Data Pump dumpsets

Note: Oracle executables and ASCII files, such as alert logs and trace files, cannot be stored in ASM disk groups.

11)Can we use ASM for storing Voting Disk/OCR in a RAC instance?

No. You cannot use ASM for storing the voting disk and OCR. It is due to the fact that Clusterware starts before ASM instance and it should be able to access these files which is not possible if you are storing it on ASM. You will have to use raw devices or OCFS or any other shared storage.

12)Does ASM instance automatically rebalances and takes care of hot spots?

No. This is a myth and ASM does not do it. It will initiate automatic rebalance only when a new disk is added to Diskgroup or we drop a disk from existing Diskgroup.

13) What is ASMLIB?
ASMLIB is the support library for the ASM. ASMLIB allows an Oracle database using ASM more efficient and capable access to diskgroups. The purpose of ASMLIB, is to provide an alternative interface to identify and access block devices. Additionally, the ASMLIB API enables storage and operating system vendors to supply extended storage-related features. You can read more about ASMLIB in


ASMLIB for linux can be downloaded from following link

14) What is SYSASM role?
Starting from Oracle 11g, SYSASM role can be used to administer the ASM instances. You can continue using SYSDBA role to connect to ASM but it will generate following warning messages at time of startup/shutdown, create Diskgroup/add disk ,etc

Alert entry

WARNING: Deprecated privilege SYSDBA for command ‘STARTUP’

15) How can we copy the files from/to ASM?

You can use RMAN or DBMS_FILE_TRANSFER.COPY_FILE procedure to copy the files to/from ASM from/to Filesystem. Starting from Oracle 11g, you can use cp command in asmcmd to perform the same between ASM Diskgroups and also to OS Filesystem.

16) Can we use BCV to clone the ASM Diskgroup on same host?

Diskgroup Cloning is not supported on the same host using BCV. You have no other option to use except RMAN DUPLICATE. You can find more detail on BCV and ASM in below whitepaper

Click to access asm-on-emc-5_3.pdf

17) Can we edit the ASM Disk header to change the Diskgroup Name?

No. This cannot be done.

18) Whats is Kfed?

kfed is a utility which can be used to view the ASM Disk information. Syntax for using it is

kfed read devicename

19) Can we use block devices for ASM Disks?

Yes. Starting from Oracle Database 10.2 block devices can be used directly for ASM Disks in Linux. This is not true for other Unix based systems where block devices are not supported yet.

Along with this it is recommended to use a Device mapping functionality so that disk mapping is preserved after disk failure. This is important when you have devices as /dev/sda,/dev/sdb,/dev/sdc and due to some reason the devices are not detected at next reboot (say /dev/sdb), the system will map the incorrect device (i.e /dev/sdc will be marked as /dev/sdb). You can use following methods for preserving disk names

-udev – the role of udev is to provide device persistency and naming consistency.This is especially important for the Oracle Cluster Registry (OCR) and Voting disks required by Oracle Clusterware.

– ASMLIB will provide device management specifically for ASM disk devices.

Refer to following Whitepaper for more details

Click to access device-mapper-udev-crs-asm%20rh4.pdf

20)Is it mandatory to use disks of same size and characteristics for Diskgroups?

No, it is not mandatory to use the disks of same size and characteristics for Diskgroups though it is a Recommended Practice.

Same size disk for Failuregroups in Normal/High redundancy will prevent issues like ORA-15041 as the file extents needs to be mirrored across the disks. Also as Oracle distributes data based on capacity, so larger disk will have more data stored in it and which will result in higher I/O to disk and eventually can lead to sub-optimal performance.

Moreover having disks of different characteristic like varying disk speed can impact the performance.

When managing disks with different size and performance capabilities, best practice is to group them into disk groups according to their characteristics. So you can use higher speed disks for your database files while other disks can be part of Diskgroup used for Flash Recovery Area.

21)Do we need to install ASM and Oracle Database Software in different ORACLE_HOME?

No. Again installing ASM and Oracle Database Software in different ORACLE_HOME is not mandatory but a best practice. This is useful in cases when we need to have multiple databases using same ASM instance and you need to patch only one of them. E.g You need to apply a CBO patch to one of 10.2 database while your other 10.1 database using different installation does not require it. In this case having a ASM_HOME separate from 10.2 ORACLE_HOME will allow your 10.1 database to keep running. Thus this approach is useful for High Availability.

22)What is the maximum size of Disk supported by ASM?

ASM supports disks upto 2Tb, so you need to ensure that lun size should be less then 2Tb. 10g database will give error if you try to create a diskgroup with ASM disks having disk size >2Tb.

23)I have created Oracle database using DBCA and having a different home for ASM and Oracle Database. I see that listener is running from ASM_HOME. Is it correct?

This is fine. When using different home for ASM, you need to run the listener from ASM_HOME instead of ORACLE_HOME.

Additional Links for Further Reading

Click to access device-mapper-udev-crs-asm%20rh4.pdf

Note 266028.1 – ASM Using Files Instead of Real Devices on Linux

May 7, 2008 Posted by | oracle | 2 Comments