My Oracle

Memory Parameters in oracle9i

2010-03-17T09:42:00.002-07:00

Oracle9i new features on Memory Management :

1. Oracle9i introduced new parameter PGA_AGGREGATE_TARGET to tune portion of the memory in PGA. This new parameter dynamically adjust sort_area_size, hash_area_size, create_bitmap_area_size and bitmap_merge_area_size. The WORK_AREA_SIZE_POLICY should be set to AUTO to enable this feature.

2. Prior to oracle9i, shared pool size was static parameter. DBA has to restart the instance if shared pool size parameter needs to be changed. But in oracle9i introduced a new capability to change the shared_pool_size parameter dynamically without restarting the instance. But this parameter size should be less than or equal to SGA_MAX_SIZE.

3. Oracle9i introduced new approach to determine how the buffer cache is being using and how much memory has to be added or released from the buffer cache for optimal performance. Here are the steps to enable to feature.

a) Set the DB_CACHE_ADVICE parameter to ON. It gather statistics on the buffer cache by setting the value of this parameter. This is dynamic paramter and can be changed by using ALTER SYSTEM command.

b) Display the statistics gathered by querying the new dynamic performance view, V$DB_CACHE_ADVICE. This view contains information about the physical reads for the different cache sizes.

select id, name, block_size,buffers_for_estimate, estd_physical_reads, estd_physical_read_factor from v$db_cache_advice

c) We can change the buffer cache size based on the observation in step b.

The source of this article is click

Here are other related link to this topic.

Oracle10g Memory feature
Oracle11g Memory feature
Oracle Memory components

Memory Parameter in Oracle10g

2010-03-17T09:42:00.001-07:00

Oracle10g new feature on Memory Management :

Oracle10g introduced memory management feature to make DBA's life much easy. Automatic shared memory management(ASMM) is another self management enhancement in oracle10g. In previous release of oracle, we had to manually configure the shared pool size, java pool size, large pool size and data base buffer cache. It was often challenge to optimally configure these components because sizing them too small could cause memory errors and sizing them too large could lead to waste of memory.

In oracle10g, you need to specify only the SGA_TARGET parameter, which specifies the total size of the SGA. Individual components of the SGA are automatically allocated by the database based on the workload and history information. so during the normal online operations,the buffer cache and java pool may be bigger. During the batch window, the database can automatically increase the large pool and reduce the buffer cache.

When ASMM is enabled, then the following memory pools are automatically sized:

1 .Buffer cache (DB_CACHE_SIZE)
2. Shared pool (SHARED_POOL_SIZE)
3. Large pool (LARGE_POOL_SIZE)
4. Java pool (JAVA_POOL_SIZE)

If above automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by ASMM.

1. DB_KEEP_CACHE_SIZE
2. DB_RECYCLE_CACHE_SIZE
3. DB_nK_CACHE_SIZE (non-default block size)
3. STREAMS_POOL_SIZE
4. LOG_BUFFER

For instance, SGA_TARGET is 400MB, LOG_BUFFER is 1M, DB_KEEP_CACHE_SIZE is 50M, then the memory available for automatically configured components is 349MB.

Questions and Answers :

1. How does ASMM work? ASMM automatically allocate the memory based on the work load and type of DB activities. We do not need to size the SGA parameters. The sga_target parameter will not dynamically manage all the sub component of the SGA memory. Some of the memory areas need to be sized by DBA as i explained above.

2. How do we switch the DB from non-ASMM to ASMM? Switching to ASSM can be done by changing SGA_TARGET parameter to non-zero value. STATISTICS_LEVEL should be TYPICAL or ALL. We need to allocate proper memory size for SGA_TARGET parameter. But this should not be greater then SGA_MAX_SIZE. Again, we need to reset the automatically tunable memory parameter to zero or minimum values. If we set minimum values for automatically tuned parameters, then oracle always maintain the minimum values all the time.

Here my DB is running in Non-ASMM. Let us switch this to ASMM.

SQL> select name,value from v$parameter where
2 name in('shared_pool_size','large_pool_size','java_pool_size','db_cache_size','sga_target');

NAME VALUE
------------------------------ ------------------------------
shared_pool_size 163577856
large_pool_size 4194304
java_pool_size 4194304
db_cache_size 432013312
sga_target 0

SQL> alter system set sga_target=1000M scope=both;

System altered.

SQL> alter system set shared_pool_size=500M scope=both;

System altered.

SQL> show sga

Total System Global Area 1048576000 bytes
Fixed Size 1252976 bytes
Variable Size 553648528 bytes
Database Buffers 486539264 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.

SQL> select target_size from v$sga_resize_ops
2 where component='shared pool';

TARGET_SIZE
-----------
528482304

SQL>

Now SGA parameters will be automatically tuned based on the workload and DB activities. But it always retain the minimum values, since we set shared_pool_size, large_pool_size, java_pool_size, db_cache_size set to non-zero values.

3. How do we disable ASMM? Disabling ASSM can be done by chaning SGA_TARGET parameter to zero value. But we need to set the below parameter with proper sizing when we disable ASMM

1. shared_pool_size
2. large_pool_size
3. java_pool_size
4. db_cache_size

SQL> alter system set sga_target=0 scope=both;

System altered.

4. When do we think that, ASMM should be disabled in oracle10g? We can disable ASMM if there is significant amount of memory resizing activity that cause the performance issue in database. This frequent memory resizing might happen when the environment is mixed of DSS and OLTP environment.

5. What is the best environment to enable the ASMM feature? Based on my knowledge, i would recommend to go for ASMM feature when DB is running on OLTP mode during the day time and DB is running on DSS more during the night time. In this scenario, ASMM feature will resize the memory based on the DB activities. DBA does not need to resize the memory for day time and night time differently.

Here are the relevant link to this topic.

Oracle9i Memory feature
Oracle11g Memory feature
Oracle Memory components

Memory Parameter in Oracle11g

2010-03-16T17:10:00.000-07:00

Oracle is enhancing the memory management in each version starting from oracle9i. Oracle9i introduced pga_aggregate_target to dynamically manage the memory for PGA. Oracle went one step further in 10g and introduced sga_target parameter to dynamically allocate the SGA memory. Oracle introduced memory_target parameter in oracle11g and dynamically allocate both SGA and PGA memory. This is one of exciting features that takes care of SGA as well as PGA in one parameter. We let Oracle to adjust the memory as the workload needs it.

I would like to write how this memory parameter works in oracle11g. How the memory is configured on each sub component when memory_target parameter is set to non zero?

Oracle11g introduced two memory parameters(memory_target, memory_max_target). oracle has become smart as in exchanging memory between SGA and PGA. This feature helps DBA to allocate chunk of memory to a particular instance without worrying about the subcateogary allocations of different components.

Question and Answers :

1. What is the advantage of memory_target parameter? I do not see any reason to not to use this memory management parameter. If we set this parameter, then we do not need to size all other sub component of this parameter. Sub component parameters are like SGA_TARGET, PGA_AGGREGATE_TARGET etc. These memory area can be managed dynamically.

2. What is the best place to leverage this feature? If your database running in mixed load, Oracle will adjust all your memory setting accordingly to DB load. Let us say, your database is running on OLTP mode in the day time, and running batch job during the night time. DBA can not adjust the different component of the memory according to Database activity. This memory_target parameter balance the memory between shared_pool_size, redolog_buffer, large_pool_size, java_pool_size etc. This is ideal scenario to use this feature.

3. How do we enable AMM feature ? This feature can be enabled by setting memory_target and max_memory_target to non-zero values. LOCK_SGA parameter should be set to FALSE to enable this feature.

4. How do we disable AMM feature ? This feature can be disabled by setting zero for memory_target parameter. Please remember, sga_target, pga_aggregate_target parameter should be sized according to DB activities.

5. Do we need to check the Shared memory in server before implementing AMM? Yes. We need to check the current size of your shared memory file system before we set the AMM feature. On linux, we need to use the below command to check the shared memory.

[oracle@rate9812~]$ df -k /dev/shm

Filesystem 1k-blocks Used Available Use% Mounted on tmpfs

2023256 1065000 958256 53% /dev/shm

[oracle@rate9812~]$

Here the shared memory is around 2GB. So the memory target can be more than 2GB. If it is more than 2GB, the we get the below error.

ORA-00845: MEMORY_TARGET not supported on this system

6. Do we have any new data dictionary views to monitor AMM? Yes. we do have new views. Here are some. These views helps to monitor the Memory utilization.

V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_DYNAMIC_COMPONENTS
V$MEMORY_RESIZE_OPS
V$MEMORY_TARGET_ADVICE

6. How does this feature differ from Oracle10g memory feature? This parameter covers both SGA and PGA. In Oracle10g, we use individual parameter for SGA and PGA. Now it is not required to define or size the memory for SGA and PGA seperately.

7. Do we need to take care of any memory parameters after AMM feature is enabled? Log buffer and the non-default block size caches not part of dynamic memory allocation. These memory should be taken care manually.

The new data dictionary V$MEMORY_DYNAMIC_COMPONENTS will give the list of components which is taken care by database when we enable AMM feature.

Here are relevant topic to this article.

Oracle9i Memory feature
Oracle10g Memory feature
Oracle Memory components

Oracle Memory Management

2010-03-09T19:43:00.001-08:00

It has been a few months since i added a new post in my blog. I moved to Austin, TX last December 2009 and since that time, i have been busy with my new job, settling down with new place, buying new home etc. I am going to discuss about Oracle Memory components and their function of each component. I am writing this article based on my experience, reading oracle websites, books and other blogs.

First let me explain about each memory component in oracle. Oracle Memory is divided into two portion. One is System Global Area(SGA) and another one is Program Global Area(PGA). Let us now discuss about SGA now. Then we will talk about PGA in the later portion of this article.

System Global Area consists of following Memory component.

1. Fixed size
2. Variable size
3. DB Buffer cache
4. Redo log buffer

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning option

SQL> show sga

Total System Global Area 1654013952 bytes
Fixed Size 2103048 bytes
Variable Size 1342179576 bytes
Database Buffers 301989888 bytes
Redo Buffers 7741440 bytes
SQL>

SGA = Fixed size + Variable size + DB buffers + Redo buffers

What is Fixed size?

The size of the fixed portion is constant for a release and a platform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters. This memory varies in size from platform to platform and release to release. This is something over which we have no control and it is generally very small. Think of this area as a bootstrap section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.

What is Variable size?

Variable size is sum of shared pool, java pool, large pool and streams pool. The streams pool is added in oracle10g.

Shared pool = library cache + dictionary cache

Variable size = Shared pool + Java pool + large pool + streams pool

Let us briefly talk about each component in variable size.

Shared pool : It consists of Library cache and Dictonary cache.

The library cache stores shared SQL, caching the parse tree and the execution plan for every unique SQL statement. If multiple applications issue the same SQL statement, each application can access the shared SQL area. This reduces the amount of memory needed and reduces the processing-time used for parsing and execution planning.

The dictionary cache stores dictionary information in order to parse the SQL statement. It also contains user information, integrity constraints defined for tables etc.

Java pool : It is used for caching parsed java programs.

Large pool : is used in shared/MTS server systems for session memory, by parallel execution for message buffers, and by RMAN backup processes for disk I/O buffers.

Stream pool : is used for streams.

What is DB buffer cache? It stores the recently executed database blocks. When other user fires the same query, oracle reads from buffer cache instead of accessing physical files.

What is Redo log buffer? It stores all the changes made in the database. It will be stored in archive log files for recovery purpose.

Please click to know more info about SGA

Program Global Area(PGA) A PGA is allocated by Oracle when a user connects to an Oracle database and a session is created. PGA contains session information and Private SQL area.

Content of PGA :

1. Session information. For instance, Logon info, session related info.

2. Private SQL area. Please remember, this is part of PGA only if the DB is dedicated server. This memory will be part of SGA if the DB is shared server. Private SQL area contains informatoin about the cursor, query execution status info, sql work area(sort area). Please click here to know more about PGA memory.

Oracle keep introducing new features in the memory management side to make DBA's life easy.

Let us talk about new oracle memory management features in each new version since from Oracle9i.

1. Oracle9i Feature

2. Oracle10g Feature

3. Oracle11g Feature

Dynamic Sampling in Oracle

2009-11-08T07:03:00.001-08:00

I started my career with oracle 7.0 and i remember, in oracle7.x/8.x version, optimizer use Rule based optimizer if one or all of the tables does not have any statistics in the join query. In oracle9i R2, dynamic sampling query is introduced and it has ability to sample the table and collect the better statistics dynamically during the hard parse. This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling.

Oracle optimizer use the default statistic values when table does not have any statistics and dynamic sampling query feature is disabled. The default statistics values are well documented in Performance Tuning Guide.

How do we set Dynamic sampling parameter? There are couple of ways, we can set the parameter.

1. We can set this in session level or instance level.
2. Dynamic sampling query hint in query level. (Hint)

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter session set OPTIMIZER_DYNAMIC_SAMPLING = 2;

Session altered.

SQL> alter system set OPTIMIZER_DYNAMIC_SAMPLING = 2;

System altered.

SQL> select /*+ dynamic_sampling(emp 0) */ * from scott.emp;

When does dynamic sampling is useful?
1. It is useful when table data is drastically changing and statistic becomes stale very often.
2. Global temporary table is good place to use dynamic sampling feature.

Dynamic sampling Levels? Optimizer Dynamic Sampling has 11 levels between 0 to 10. What are these levels? The answer is pretty straight forward and it is clearly documented in Oracle Performance Tuning Guide. In Oracle9i R2, the default setting for dynamic sampling level is 1. At this setting, the optimizer will tend to use dynamic sampling only in some cases when a table is unanalyzed. This includes the case of global temporary tables without statistics but doesn’t ensure that dynamic sampling will be used against all unanalyzed tables. In Oracle Database 10g Release 1 and above, the default setting is 2. This setting ensures that the optimizer will have a good estimate whenever it optimizes a query that accesses a table with no statistics.

The source of this Article is Asktom. Please read this link and this link has very pretty good information about Dynamic sampling.

Restoring old statistics

2009-11-04T09:06:00.000-08:00

I see some DBA's, they won't backup the statistics before collect the new statistics. If we don't have backup, and optimizer behaves differently with new statistics, then we are in trouble. If we have old statistic backup, then we can resolve the performance issue by restoring the old statistics. Oracle highly recommends to take the backup of current statistics before we collect new statistics.

Oracle10g has a new feature which will save the statistics before overwriting the new statistics.

There are couple of ways, we can backup the statistics.

1. We can export the statistics in a table and import back when ever we need. I discussed this approach in different thread. This approach is mostly useful when we transfer the statistics from one server to another server. Please refer this thread Post

2. Since Oracle10g, whenever statistics are modified, old version of statistics are saved automatically before overwriting the new statistics. However automatic saving of old statistics will not take place if you use ANALYZE in 10g for statistics collection.

I am going to discuss how the statistics are saved and how do we restore the old statistics in Approach 2.

Where does oracle store the statistics? Oracle DBA_OPTSTAT_OPERATIONS table contains a log history of statistics collected on the database at various level such as table, schema or database. We can restore older statistics anytime by making use of START_TIME and END_TIME values from DBA_OPTSTAT_OPERATIONS table.

How does Oracle maintain the Statistics History? GET_STATS_HISTORY_AVAILABILITY is a Function in DBMS_STATS package that returns oldest timestamp where statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp. Here is the query to find the statistics history starting timestamp.
SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
06-OCT-09 09.52.45.351000000 PM -05:00

SQL>

Oracle maintain the history statistics based on the retention value. Oracle Purge the statistics automatically if it exceeds the statistics retention setting. By defualt, oracle purge the statistics if it is older then 31 days. We can also alter this retention period by using DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure. PURGE_STATS is procedure in DBMS_STATS package that enables you to manually purge old statistics beyond a time stamp.

SQL> execute dbms_stats.alter_stats_history_retention(20);

PL/SQL procedure successfully completed.

As per the above code, oracle keep 20 days statistics history.

SQL> execute dbms_stats.purge_stats('06-OCT-09 09.52.45.351000000 PM -05:00');

PL/SQL procedure successfully completed.

SQL>

As per the above code, oracle purge the statistics prior to 06-OCT-09.

How do we recover the statistics? Oracle RESTORE_TABLE_STATS procedure restores statistics of a table as of a specified timestamp. It also restores the statistics of associated indexes and columns. Let me take EMP table in scott Schema and restore previous statistics.

Here are the steps to restore the previous timestamp statistics:

Step1: Let us check the past statistics and what date the statistics were collected.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select STATS_UPDATE_TIME from dba_tab_stats_history where table_name='EMP';

STATS_UPDATE_TIME
---------------------------------------------------------------------------
30-OCT-09 06.25.54.809000 PM -04:00
07-NOV-09 07.23.23.504000 AM -05:00
07-NOV-09 07.27.31.709000 AM -05:00
07-NOV-09 07.29.54.324000 AM -05:00

SQL> select last_analyzed from dba_tables where
2 table_name='EMP' and owner='SCOTT';

LAST_ANAL
---------
11-NOV-09

SQL> select sysdate from dual;

SYSDATE
---------
11-NOV-09

SQL>

Step2 : Let us restore the statistics which we collected on 30-OCT-2009.

SQL> execute dbms_stats.restore_table_stats('SCOTT','EMP','30-OCT-09 06.25.54.809000 PM -04:00');

PL/SQL procedure successfully completed.

SQL> select last_analyzed from dba_tables where
2 table_name='EMP' and owner='SCOTT';

LAST_ANAL
---------
30-OCT-09

SQL>

At what circumstances, do we restore the Statistics? We have performance issue in the database and DB was fine yesterday and since yesterday, there is no major change on database. Oracle10g automatic statistic gathering is turned on and last statistics gathering was close to the time that users started complanining about performance. At this circumstances, may be new statistics could be a culprit. We can restore the old statistics and see if we have any performance issue.

Restriction on restoring the statistics:

1. It does not have ability to restore user-defined statistics.

2. Old statistics will not be saved if we use ANALYZE command to gather statistics. Hence, we can recover the statistics.

Here is the input(Help) to write this topic.

Automatic Statistics Gathering in Oracle10g

2009-11-02T12:41:00.000-08:00

Oracle10g has one of the automated feature which is called Automatic Statistics gathering. Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly scheduled maintenance job. When the DB is created, job is automatically created and started to collect statistics on database object with missing or stale statistics. The job GATHER_STATS_JOB calls the procedure dbms_stats.gather_database_stats_job_proc to gather statistics during the maintenance window.

What does GATHER_DATABASE_STATS_JOB_PROC do? Unfortunately, the package body is wrapped, so without access to the original source code, the implementation details are obscured. This procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).

This is an internal procedure, but its operates in a very similar fashion to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The primary difference is that the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure prioritizes the database objects that require statistics, so that those objects which most need updated statistics are processed first. This ensures that the most-needed statistics are gathered before the maintenance window closes.

How do we enable this feature? Automatic statistics gathering is enabled by default when a database is created, or when a database is upgraded from an earlier database release. The following condition should be met to enable this feature.

1. GATHER_STATS_JOB should be enabled.
2. statistics_level should be TYPICAL or ALL.

We can enable the job as below when the job is already disabled for any reason.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> connect sys/password@orcl as sysdba
Connected.
SQL> BEGIN
2 DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');
3 END;
4 /

PL/SQL procedure successfully completed.

SQL>

What time does automatic statistic collection run? By default this job runs within a maintenance window between 10 P.M to 6 A.M week nights and all day on weekends. But in oracle11g, i believe, the maintenance window is not same as Oracle10g.

Do we need to collect statistics in other then the scheduled window? In few Scenario's, it would be required to gather statistics manually.

1. In some cases, we might need to collect the statistics in specific objects other than this maintenance window. If the table data are changed rapidly and optimizer behaves differently due to data changes, then we need to gather statistics manually. We can not wait till the maintenance window open.

2. If the table is bigger and it is failing in the maintenance window for some reason, then you can lock that table during the maintenance window and unlock the table and gather the statistics manually and lock back again.

3. If you have specific table and you want to gather statistics with different option, then you can lock those tables during the maintenance window and gather statistics manually during day time.

Should we change the parameter values in Automatic statistic collection? Oracle recommends not to change the default values for automatic statistics collection. In case, if you need to gather statistics something differently, then lock the table during the maintenance window and unlock the table and gather statistics manually with specfic parameter and lock the table back again.

We have get_param function to find out the parameter values. Also set_param procedure to change the default values for dbms_stats pacakge parameters.

Compute Oracle Index Statistics

2009-10-30T19:19:00.000-07:00

Many times, we forget to gather index statistics after creating the index. Optimizer will not use the index effectively if index does not have statistics.

Prior to Oracle9i, we have to issue two commands for creating index and gathering index statistics.

SQL> create index idx on test(object_name);

Index created.

SQL> exec dbms_stats.gather_index_stats(null, 'IDX');

PL/SQL procedure successfully completed.

SQL>

Starting in Oracle 9i, we have a compute statistics clause. We can both creating index and gathering index statistics in one command.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table test as select * from user_objects;

Table created.

SQL> create index idx on test(object_name);

Index created.

SQL> select table_name, num_rows, last_analyzed
2 from user_tables
3 where table_name ='TEST'
4 /

TABLE_NAME NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
TEST

SQL> drop index idx;

Index dropped.

SQL> create index idx on test(object_name) compute statistics;

Index created.

SQL> select table_name, num_rows, last_analyzed
2 from user_tables
3 where table_name ='TEST'
4 /

TABLE_NAME NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
TEST 184 31-OCT-09

SQL>

In Oracle10g, we do not need to use compute statistics clause at all. Oracle gather statistics while creating index automatically.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create index idx on test(object_name);

Index created.

SQL> select num_rows, last_analyzed from user_ind_statistics where index_name ='IDX';

NUM_ROWS LAST_ANAL
---------- ---------
50484 30-OCT-09

SQL>

Statistics Lock in Oracle10g

2009-10-30T11:06:00.000-07:00

Oracle10g has one of the useful feature that we can lock the table statistics. When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked. No one can gather statistics on a table when it is locked. But ofcourse, we can overwrite the statistics with force option. How does it useful for DBA's on day to day activities? At what circumstances, this feature is useful?

We can use this feature in the following circumstances...

1. There are tables where you want to setup gathering statistics manually. You can stop gathering statistics during the regular schedule by locking the statistics.

2. Some cases, Queries works fine with old statistics. You can avoid gathering statistics at this situation.

3. Some time, tables are bigger and automatic gathering statistics might fail silently. In this scenario, we might need to lock the table and collect the statistics seperately. Refer these links Post1, Post2, Post3

4. Sometime, gathering statistics, creating histograms takes very long time on bigger table and we can avoid such a bigger table while collecting statistics for all the tables in schema or DB level.

5. For some reason, if we want to use any specific parameter to gather statistics on particular table, then we can use this option to lock the statistics and gather statistics in different time.

How do we lock the statistics?

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> execute dbms_stats.lock_table_stats('SCOTT','EMP');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.lock_schema_stats('SCOTT');

PL/SQL procedure successfully completed.

SQL>

Index Creation on Locked tables : In oracle10g, when you create index, the statistics also will be generated automatically. (Please refer my another Post for gathering statistics while creating index) . When the table is locked, statistics will not be generated while creating the index. We need to use FORCE option to gather the statistics while creating index for locked objects.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table test as select * from dba_objects;

Table created.

SQL> exec dbms_stats.lock_table_stats(null, 'TEST');

PL/SQL procedure successfully completed.

SQL> create index idx on test(object_name);

Index created.

SQL> select num_rows, last_analyzed from user_ind_statistics where index_name ='IDX';

NUM_ROWS LAST_ANAL
---------- ---------

SQL> drop index idx;

Index dropped.

SQL> create index idx on test(object_name) compute statistics;
create index idx on test(object_name) compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

SQL> create index idx on test(object_name);

Index created.

SQL> exec dbms_stats.gather_index_stats(null, 'IDX');
BEGIN dbms_stats.gather_index_stats(null, 'IDX'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 10640
ORA-06512: at "SYS.DBMS_STATS", line 10664
ORA-06512: at line 1

SQL> exec dbms_stats.gather_index_stats(null, 'IDX',force=>true);

PL/SQL procedure successfully completed.

SQL> select num_rows, last_analyzed from user_ind_statistics where index_name ='IDX';

NUM_ROWS LAST_ANAL
---------- ---------
50484 30-OCT-09

SQL> alter index idx rebuild compute statistics;
alter index idx rebuild compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

SQL> alter index idx rebuild;

Index altered.

SQL> exec dbms_stats.gather_index_stats(null, 'IDX',force=>true);

PL/SQL procedure successfully completed.

SQL>

PLSQL_OPTIMIZE_LEVEL

2009-10-26T11:30:00.000-07:00

I came to Austin for interview and i am on my way to Newjersy. I am hanging in the Austin Airport and i have another three hours for my flight departure. I thought, i write about one of the new Oracle10g optimization parameter PLSQL_OPTIMIZE_LEVEL.

This parameter determine the optimization level to compile the PLSQL code. The higher setting, oracle use more efforts to compile the code. This parameter will eliminate the dead code and moving the code out of the loop which does the same thing for each iteration. This has three valid values, which are 0,1 and 2. But default value for this parameter is 2.

Let us discuss about each value for this parameter. Please note, Oracle has not provided any detail level example for each value of this parameter. So i can't demonstrate exactly what oracle does for each level. Indeed, we can see the performance improvement in each level.

PLSQL_OPTIMIZE_LEVEL = 0 The value 0 works some what as pre 10g release. Oracle documentation says it works better than 9i. Let me write a procedure and run in oracle10g with value 0.

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter session set plsql_optimize_level =0;

Session altered.

SQL> set serveroutput on
SQL> create or replace procedure test as
2 a integer;
3 b integer;
4 c integer;
5 d integer;
6 v_time integer;
7 begin
8 v_time := Dbms_Utility.GET_CPU_TIME();
9 for j in 1..10000000 loop
10 a:= 100;
11 b:= null;
12 c:= nvl(b,1)+a;
13 end loop;
14 Dbms_Output.Put_Line(Dbms_Utility.GET_CPU_TIME()-v_time);
15 end;
16 /

Procedure created.

SQL> execute test;
770

PL/SQL procedure successfully completed.

The above procedure runs in 770 mseconds in oracle10g with plsql_optimize_level=0.

PLSQL_OPTIMIZE_LEVEL = 1 It eliminates unnecessary computation and exceptions. Since oracle has not given any example for the each value, i guess, it removes the statement b:=NULL in the TEST procedure. It does not make any sense to assign NULL value for each iteration of the loop in the TEST Procedure.

SQL> alter procedure test compile plsql_optimize_level = 1;

Procedure altered.

SQL> execute test;
502

PL/SQL procedure successfully completed.

The above procedure executes in 502 seconds and it is better then the plsql_optimize_level=0.

PLSQL_OPTIMIZE_LEVEL = 2 It moves the unnecessary dead code relatively far from its original location. I guess, it moves the assignment statement out of loop. Since it assigns the same value for each iteration which is not meaningful. Be aware that, some time, oracle takes long time to compile the procedure when we have value 2. Since oracle has to rewrite the code during the compilation stage and not during the execution stage.

SQL> alter procedure test compile plsql_optimize_level = 2;

Procedure altered.

SQL> execute test;
301

PL/SQL procedure successfully completed.

SQL>

The above procedure runs in 301 seconds and performance is far better then the value 1.

How to use histogram in Oracle

2009-10-19T07:01:00.000-07:00

I would like to write about Oracle Histogram today. Histogram is very nice feature to help cost based optimizer to make right decision.

What is Histogram? Histograms are feature in CBO and it helps to optimizer to determine how data are skewed(distributed) with in the column. Histogram is good to create for the column which are included in the WHERE clause where the column is highly skewed. Histogram helps to optimizer to decide whether to use an index or full-table scan or help the optimizer determine the fastest table join order.

What are the advantage of Histogram? Histograms are useful in two places.

1. Histograms are useful for Oracle optimizer to choose the right access method in a table.

2. It is also useful for optimizer to decide the correct table join order. When we join multiple tables, histogram helps to minimize the intermediate result set. Since the smaller size of the intermediate result set will improve the performance.

Type of Histograms: Oracle uses two types of histograms for column statistics: height-balanced histograms and frequency histograms.

1. Height - balanced Histograms : The column values are divided into bands so that each band contains approximately the same number of rows. For instances, we have 10 distinct values in the column and only five buckets. It will create height based(Height balanced) histograms and it will evenly spread values through the buckets. A height-based histogram is when there are more distinct values than the number of buckets and the histogram statistics shows a range of rows across the buckets

2. Frequency Histograms : Each value of the column corresponds to a single bucket of the histogram. This is also called value based histogram. Each bucket contains the number of occurrences of that single value. Frequency histograms are automatically created instead of height-balanced histograms when the number of distinct values is less than or equal to the number of histogram buckets specified.

Method_opt Parameter: This is the parameter which tells about creating histogram while collecting the statistics. The default is FOR ALL COLUMNS SIZE AUTO in Oracle10g. But in oracle9i, the default is FOR ALL COLUMN SIZE 1 which will turn off the histogram collection.

FOR ALL [INDEXED HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] columnattribute [size_clause] [,columnattribute [size_clause]...]

size_clause is defined as size_clause := SIZE {integer REPEAT AUTO SKEWONLY}

integer : Number of histogram buckets. Must be in the range [1,254]

REPEAT : Collects histograms only on the columns that already have histograms.

AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns. We have a table called sys.col_usage$ that stores information about column usage. dbms_stats use this information to determine whether histogram is required for the columns.

SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.

Let me demonstrate how optimizer works with and without histogram as below two scenario. We take the emp table for this demonstration. The table has around 3.6 million records. The table emp_status column is highly skewed. It has two distinct values(Y,N). We have bitmap index on emp_status column.

Scenario 1 Let us generate the statistics without any histogram and see what kind of execution path optimizer is using. Without the histogram, oracle assume that, the data is evenly distributed and optimizer think that, we will have around 1.8 million record for emp_status Y and around another 1.8 million records for emp_status N.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*),emp_status from scott.emp
2 group by emp_status;

COUNT(*) E
---------- -
1 N
3670016 Y

SQL> execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'EMP',ESTIMATE_PERCENT =>
10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',CASCADE => TRUE);

PL/SQL procedure successfully completed.

SQL> select ename from scott.emp where emp_status='Y';

3670016 rows selected.

--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1832K 15M 5374 (5) 00:01:05
* 1 TABLE ACCESS FULL EMP 1832K 15M 5374 (5) 00:01:05
--------------------------------------------------------------------------

SQL> select ename from scott.emp where emp_status='N';

Conclusion: Optimizer is using full table scan for the query which returns 3670016 records as well as it using full table scan for query which returns just only one record. This is obvisouly incorrect. This problem will be resolved by collecting histogram. Let us see in the next scenario.

Scenario 2 : Let us generate the statistics with histogram and see what kind of execution path optimizer is using. FOR COLUMN SIZE 2 EMP_STATUS will create two bucket for column emp_status. If we are not sure the distinct number of values in the column, then we can use AUTO option to collect histogram. With this histogram, oracle optimizer knows that, the column emp_status is highly skewed and it has two bucket and one bucket has around 3.6 million records with emp_status Y and another bucket has only one record with emp_status N. Now depends upon the query, optimizer decides whether to use index or Full table scan.

SQL> execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'EMP',ESTIMATE_PERCENT =>
10, METHOD_OPT => 'FOR COLUMNS SIZE 2 EMP_STATUS',CASCADE => TRUE);

PL/SQL procedure successfully completed.

SQL> select ename from scott.emp where emp_status='Y';

3670016 rows selected.

--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 3681K 31M 5375 (5) 00:01:05
* 1 TABLE ACCESS FULL EMP 3681K 31M 5375 (5) 00:01:05
--------------------------------------------------------------------------

SQL> select ename from scott.emp where emp_status='N';

--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1 9 1 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID EMP 1 9 1 (0) 00:00:01
2 BITMAP CONVERSION TO ROWIDS
* 3 BITMAP INDEX SINGLE VALUE IDX_EMP
--------------------------------------------------------------------------

Conclusion: Optimizer is using full table scan for the query which returns 3670016 records. At the same time, optimizer is using index scan when for other query which returns one record. This scenario, the optimizer choose the right execution plan based on the query WHERE clause.

Data dictionary objects for Histogram:
user_histograms
user_part_histograms
user_subpart_histograms
user_tab_histograms
user_tab_col_statistics

Transferring statistics between database

2009-10-08T12:28:00.000-07:00

In general, development DB usually will have only portion of the data when we compared to Production database. In such a scenario, when we fix any production issues, obviously we make the changes in Dev DB and test the code and move to Prod DB. While testing the code in Dev DB, if we want to compare the execution plan between Dev and Prod, then we can copy the Prod DB statistics into Dev DB and forcast the optimizer behaviour in development server.

DBMS_STATS has an ability to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. This article is tested in oracle10g. Here are the steps to transfer the statistics.

Source database : orcl
Source schema : sales
Target database : oradev
Target schema : sales

Now our goal is to copy the statistics from sales@orcl to sales@ordev.

Let us follow the below steps to copy the statistics from source(production) to target(development). I am running all the steps in System Schema..

step1. First create a stat table in the source database. The statistics table is created in SYSTEM schema.

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> connect system/password@orcl
Connected.
SQL> EXEC DBMS_STATS.create_stat_table('SYSTEM','STATS_TABLE');

PL/SQL procedure successfully completed.

SQL>

Step2. Export the sales schema statistics.

SQL> EXEC DBMS_STATS.export_schema_stats('SALES','STATS_TABLE',NULL,'SYSTEM');

PL/SQL procedure successfully completed.

SQL>

Step3. Export the STATS_TABLE by using expdp or exp utility and move the dump file to target(ordev) server.

Step4. Import the dump file into target database by using impdp or imp utility. Here i imported the dump file in system schema at target server.

Step5. Import the statistics into application schema(sales@ordev). Please remember, previous step, we imported the stats_table content into system schema by using impdp method. But this step, we are importing the statistics into relevant data dictionary table by using dbms_stats pacakge.

SQL> EXEC DBMS_STATS.import_schema_stats('SALES','STATS_TABLE',NULL,'SYSTEM');

PL/SQL procedure successfully completed.

SQL>

Step6. Drop the stats_table in target server.

SQL> EXEC DBMS_STATS.drop_stat_table('SYSTEM','STATS_TABLE');

PL/SQL procedure successfully completed.

SQL>

Note : We can follow step1 and step2 to backup the statistics before we gather new statistics. It is always good to backup the statistics before we overwrite the new statistics. In case, if we see any performance problem with new statistics, then we can import the old statistics. This option is very useful to transfer the statistics from one DB to another DB.

In oracle10g, it automatically save the statistics for last 31 days by default. We can restore the past statistics within the database at any time. This option is useful to restore the statistics in the same database. Please refer this Restoring statistics

Refreshing Stale Statistics

2009-10-07T14:11:00.000-07:00

Oracle optimizer use the statistics information to choose the right path and execute the query efficiently. It is important to maintain the recent statistics to run the reports efficiently. Oracle highly recommends to use DBMS_STATS to gather statistics. Why oracle recommends to use DBMS_STATS? Click here to answer your question. This article is based on Oracle10g.

DBMS_STATS package has wonderful feature that capable of analyzing the stale statistics. I am going to discuss about collecting stale statistics in dbms_stats package.

In general, Gathering statistics consumes lot of resource and CPU time. Once we gathered statistics on a table, we do not need to collect the statistics on the same table until we make reasonable amount of data changes. Let us say, we have a schema called sales. This schema has lot of tables and many table has huge number of records. We schedule to analyze the entire schema every day at 2AM. In day to day DML activities, some of the tables are not having any changes or very minimum changes. In this scenario, we do not need to analyze the tables which are having very minimum changes or no changes. But scheduler automatically start analyzing all the tables in the schema at 2AM every day. This process unnecessarily consuming extra resource and degrade the server performance.

How do we stop analyzing the tables when there is no DML activity or very minimal DML activity? Yes... We can... Oracle introduced feature in DBMS_STATS package where oracle collect statistics on schema level or database level, only when the statistics are stale or out of date.

What is stale statistics? Oracle will record an approximate count of the number of rows that have been inserted,updated, and deleted in a table. The information will be recorded in user_tab_modifications view. When that count reaches a threshold percentage of the number of rows in the table , then the statistics are considered stale. The table monitoring should be enabled for recording the DML changes in user_tab_modification view. In oracle10g, Oracle automatically enable table monitoring and record the DML changes in user_tab_modifications view. Prior to oracle10g, we need to enable the table monitoring manually.

How do we enable table monitoring? In oracle10g, the table monitoring is default when statistic_level parameter is TYPICAL. Prior to Oracle10g, we need to enable table monitoring manually. Prior to Oracle10g, the below command is used to enable or disable the table monitoring. But since Oracle10g, the below command does not have any effect.

ALTER TABLE table_name MONITORING[NOMONITORING]

What is threshold percentage? Oracle automatically determines the threshold. Oracle doesn't officially document the threshold, so the threshold, and the entire algorithm, is subject to change over time.

Let me give an example how to analyze stale statistics :

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> BEGIN
2 DBMS_STATS.GATHER_SCHEMA_STATS (
3 ownname => 'SALES',
4 estimate_percent => 20,
5 block_sample => TRUE,
6 method_opt => 'FOR COLUMNS SIZE 10',
7 options => 'GATHER AUTO',
8 cascade => TRUE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>

Note : It is very important that, we should use GATHER AUTO or GATHER STALE to analyze the stale statistics. Also table monitoring is mandatory. Since oracle10g, the table monitoring is enabled by default. So we do not need to worry about table monitoring since oracle10g.

This feature is very useful for large and complex databases where refreshing statistics for all objects can cause a heavy drain on server resources.

Analyze Versus DBMS_STATS

2009-10-01T14:00:00.000-07:00

Cost based optimizer is preferred method for oracle optimizer. In order to make good use of the CBO, you need to create accurate statistics. Prior to oracle8i, we use ANALYZE command to gather statistics.

DBMS_STATS package is introduced in oracle8i. Since Oracle8i, Oracle highly recommeds to use DBMS_STATS instead of ANALYZE command. This article is written in oracle10g. I am going to address below topics in this thread....

1. Why oracle recommends to use DBMS_STATS package?
2. What are the advantages of DBMS_STATS compared to ANALYZE?
3. How do we use DBMS_STATS package to analyze the table?
4. What are new features in each version for DBMS_STATS?

Why oracle recommends to use DBMS_STATS since Oracle8i?

1. Gathering statistics can be done in Parallel. This option is not available in ANALYZE command.

2. It is used to collect the stale statistics. I discussed about collecting stale statistics in another topic. Please refer stale statistics to know more about collecting stale statistics.

3. DBMS_STATS is a PLSQL pacakge. So it is easy to call. But ANALYZE does not.

4. It is used to collect statistics for external tables. But ANALYZE does not.

5. DBMS_STATS used to collect system statistics. But ANLAYZE does not.

6. Some time, ANALYZE does not produce accurate statistics. But DBMS_STATS does.

7. We can not use ANLAYZE command to gather statistics for partition or sub partition level. But we can use DBMS_STATS to analyze any specific partition or sub partition. This is especially useful for partition table. We do not need to analyze the Historical data whenever we refresh the current partition.

8. We can transfer statistics from one DB to another DB when we collected statistics through DBMS_STATS. But it can not be done when we use ANALYZE command to collect the statistics. Please refer statistics transfer to know more about trasferring statistics.

What force you to use ANALYZE command in all Oracle versions? ANALYZE can be used to collect the statistics like CHAIN_CNT, AVG_SPACE, and EMPTY_BLOCKS. DBMS_STATS will not collect these statistics. We might need to use ANALYZE in case if we want to see chained rows, average space and empty blocks.

There are several parameter exists for collecting statistics on table level, schema level, database level and system level. But i do not want to explain all the parameters which are already in Oracle help. Still i would like to explain some parameters.

estimate_percent: Percentage of rows or blocks to estimate. The valid rage is 0.000001 to 100. when we pass NULL for this parameter, then it computes. Compute is same as 100% sample. For instance, if we pass 20%, then it takes roughly around 20% of rows or 20% blocks depends on the BLOCK_SAMPLE parameter. This Parameter is used for analyzing on table, index, schema level and database level.

block_sample: This determines whether or not to use random block sampling instead of random row sampling. Block sampling would be slightly less accurate in the case where rows have roughly the same lifecycle and, thus, values are spread non-uniformly throughout the table. In case if you want to drive in deep on this, David Aldridge has a nice article on block sampling. This Parameter is used for analyzing on table, schema level and database level.

method_opt: This parameter tells about histogram in table. It determine which column should have histogram and number of histogram created for the table columns. This Parameter is used for analyzing on table, schema level and database level. Please refer histogram to know more about Histogram in Oracle.

granularity:This parameter is useful when you want to gather statistics on specific partition or sub partition in a table. The valid parameters are ALL, AUTO, GLOBAL, GLOBAL AND PARTITION, PARTITION, SUBPARTITION. This Parameter is used for analyzing on table, index, schema level and database level only if the table or index is partitioned.

no_invalidate: Does not invalidate the dependent cursors or currently parsed SQL statement if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE to have Oracle decide when to invalidate dependent cursors. This Parameter is used for analyzing or deleting statistics on table, index, schema level, database level.

Degree: Degree of parallelism. It has three valid values.

NULL : Oracle takes the value which is specified in degree clause of create or alter table statement.

DBMS_STATS.DEFAULT_DEGREE : It takes the value based on number of CPU's and init parameters.

DBMS_STATS.AUTO_DEGREE : It determines the value automatically. It is either 1 or default degree according to the size of the object.

Options: This parameter is used only for analyzing the data on schema level or DB level. There are multiple values for this parameters. The valid value for this parameters are GATHER, GATHER AUTO, GATHER STALE, GATHER EMPTY, LIST AUTO, LIST STALE, LIST EMPTY. Let me explain these valid values in short. Since these values are important to gather statistics on schema level.

GATHER-Gather statistics for all the objects in a schema or database.

GATHER AUTO-Gather statistics when the statistics are stale or when there is no statistics. It does both GATHER STALE and GATHER EMPTY.

GATHER STALE-Gather statistics only when it is stale. Does not collect when there is no statistics.

GATHER EMPTY-Gather statistics only when no statistics.

LIST AUTO: Returns a list of objects to be processed with GATHER AUTO.

LIST STALE: Returns list of stale objects as determined by looking at the user_tab_modifications

LIST EMPTY: Returns list of objects which currently have no statistics.

Gathering_mode : This parameter is used only for gathering system statistics. The valid modes are NOWORKLOAD, INTERVAL,START and STOP. The default is NOWORKLOAD. The START and STOP is used to stop and start the system statistics.

Example for collecting statistics on table:

DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME => 'TANDEB',
TABNAME => 'CUSTOMER',
PARTNAME => 'PART092009'
GRANULARITY => 'PARTITION',
ESTIMATE_PERCENT => 10,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
CASCADE => TRUE,
NO_INVALIDATE => TRUE);

Example for collecting statistics on Schema:

DBMS_STATS.GATHER_SCHEMA_STATS(
OWNNAME => 'SCOMPPRD',
ESTIMATE_PERCENT => 10,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
OPTIONS => 'GATHER',
CASCADE => TRUE,
NO_VALIDATE => TRUE);

Example for collecting system statistics:

DBMS_STATS.GATHER_SYSTEM_STATS(
GATHERING_MODE => 'INTERVAL',
INTERVAL => 10);

Example for collecting database statistics:

DBMS_STATS.GATHER_DATABASE_STATS(
ESTIMATE_PERCENT => 10,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
CASCADE => TRUE,
NO_VALIDATE => TRUE,
GATHER_SYS => FALSE)

New feature in Oracle9i:

1. Introduced to gather system statistics. Such as I/O and CPU utilization.

2. It can direct the database to select the appropriate sample size to generate accurate statistics. A new value for the ESTIMATE_PERCENT parameter, DBMS_STATS.AUTO_SAMPLE_SIZE will let Oracle decide the sample size necessary to ensure generation of accurate statistics.

3. Oracle9i introduced new values for the size clause in the METHOD_OPT parameter automate the decisions regarding the columns on which histograms need to be created while letting administrators control the factors affecting such decisions. Besides specifying a numeric value for the size clause, administrators have the new options (AUTO, SKEWONLY, REPEAT)

4. Oracle9i introduced new feature to enable or disable the table monitoring in schema level or DB level in one command.

DBMS_STATS.alter_schema_tab_monitoring('MYSCHEMA', TRUE); DBMS_STATS.alter_schema_tab_monitoring('MYSCHEMA', FALSE);

DBMS_STATS.alter_database_tab_monitoring(TRUE); DBMS_STATS.alter_database_tab_monitoring(FALSE);

New feature in Oracle10g:

1. Oracle10g enable table monitoring automatically. Table monitoring is required to collect the stale statisics. We do not need to enable monitoring explicitly. This feature is disabled when statistics_level is BASIC. It enables the table monitoring feature when statistics_level is TYPICAL. ALTER TABLE [NO] MONITORING clauses as well as alter_schema_tab_monitoring and alter_database_tab_monitoring procedures of the dbms_stats package are now obsolete in oracle10g. But still it runs without any error. But there is no effect.

2. Oracle10g introduced two new values for Granularity parameter. These are AUTO and GLOBAL AND PARTITION. This parameter is applicable for analyzing partitioning tables.

AUTO : Oracle collect statistics GLOBAL level, Partition level, and sub-partition level only if sub partition method is LIST. If sub parition is not a LIST, then it collects only GLOBAL, Partition level.

GLOBAL AND PARTITION : Oracle gathers the global and partition level statistics. No sub-partition level statistics are gathered.

3. Oracle10g introduced new value DBMS_STATS.AUTO_DEGREE for Degree parameter. When you specify the auto_degree, Oracle will determine the degree of parallelism automatically. It will be either 1 (serial execution) or default_degree (the system default value based on number of CPUs and initialization parameters), according to the size of the object.

4. Oracle10g has ability to restore the previous statistics. Oracle saves last 31 days statistics by default. We can recover previous days statistics in case, optimizer behaves differently with current statistics. Please refer my another post restoring statistics

5. We can lock the table statistics. This would be helpful if you want to avoid gathering statistics during the maintenance window. Please refer my another post Locking statistics

6. Oracle10g has automatic statistics gathering feature. Oracle gather statistics for the entire database every day during the maintenance window. Please refer my another post automatic statistics gathering

7. The statistics will be collected automatically when we create index. In oracle9i, we need to use compute statistics clause to collect statistics while creating index. Please refer my another post compute index statistics

What is impact when we analyze the tables during the peak hours?

1. Oracle consume more resource when we gather statistics. This will slow down the overall performance in the sever.

2. When statistics are updated for a database object, Oracle invalidates any currently parsed SQL statements that access the object. The next time such a statement executes, the statement is re-parsed and the optimizer automatically chooses a new execution plan based on the new statistics. This will degrade the server performance during the peak hours. But we can control this by using the parameter NO_INVALIDATE. This has three values(TRUE, FALSE, DBMS_STATS.AUTO_INVALIDATE). TRUE will not invalidate the already parsed SQL statement. NO will invalidate parsed SQL statement immediately. AUTO_INVALIDATE decides when to invalidate the already parsed Statement.

Here is the oracle help to know more about dbms_stats procedure. Oracle Help

Resetting High Water Mark in Oracle10g

2009-09-29T07:42:00.000-07:00

I would like to write how to reset HWM in oracle10g. Prior to Oracle10g, resetting high water mark is painful procedure in busy environment. Oracle made our life easy to reset the High Water Mark in oracle10g. I am not going to discuss about what is HWM. Since i already discussed this in another topic. Please click to read about what is HWM and what are the various options available to reset the HMW.

We have traditional ways to reset the HWM prior to Oracle10g. We might need Table downtime when we use below traditional methods....

1. Imp/exp
2. Alter tablespace move
3. truncate and insert
4. user dbms_redefinition package to copy the table

Okay... Let us talk about resetting HWM in Oracle10g. The tablespace should be ASSM(Automatic segment space Management) enabled to leverge this feature. In oracle10g, we do not need table downtime to reset the HWM. It would be easy to reset the HWM in 24/7 environment.

What does Oracle do while using Oracle10g feature to reset the HMW?

Oracle split this process as two phase. Let me explain what is happening in each phase.

Phase I. Oracle move the rows which are located in the middle or at the end of a segment further more down to the beginning of the segment and make the segment more compact. This shrinking process is kind of delete and insert. But it is not really!!!. This process is moving the data row by row. It acquires a row level lock when the row is moved down to the begining of the segment. The corresponding index data will be handled like any other row level DML. So we do not need to worry about rebuilding the indexes for the row. Also row level lock will happen for very short moment. Before we start this phase, we need to enable row movement. Here is the command to complete the phase I. Here i am using the table called bookings.

SQL> alter table bookings enable row movement;

Table altered.

SQL> alter table bookings shrink space compact;

Table altered

Phase II This step will reset the high water mark. This will acquire table level lock for very short moment while resetting the HWM. Here is the command to accomplish this task.

SQL> alter table bookings shrink space;

Table altered

SQL> alter table bookings shrink space cascade; (it is for all dependent objects as well)

Table altered

If we want to reset the HWM in one go, then below command will accomplish the task. The below command moves the rows and reset the HWM.

SQL> alter table bookings shrink space;

Table altered

What are the advantages of using Oracle10g new feature to reset the HWM?

There are serveral advantages over traditional methods. Let me list the advantages here.

1. It can be done in online. There is a table level lock for very short moment. Traditional methods are not supporting to reset the HWM in online.

2. It does not take extra space while resetting the HWM. If we use traditional method, DBMS_REDEFINITION package use double the amount of space.

3. It does acquire only row level lock while performing majority of the shrinking(moving rows) work. It acquires table level lock only when it resets the HWM which is in phase II. But traditional methods requires table down time for resetting the HWM except using dbms_redefinition package.

4. Index will be maintained and remain usable. But in traditional methods, we need to rebuild the index. Especially when we use ALTER TABLESPACE MOVE command.

5. It can be made in one command(alter table emp shrink space). In traditional method, there are multiple steps.

6. If you are not sure that you can afford table level lock at specific time, then you can do the majority of the shriniking work and later we can reset the HWM. Since table level lock is required only while resetting the HWM. The whole process can be done in two steps as i explained above. This advantage is not available in traditional methods.

What are the restriction of using Oracle10g new feature to reset the HWM?

1. It is only possible in ASSM tablespace
2. Not supporting for clustered tables, tables with column data type LONG

What circumstances we can reset the HWM as two phase?

The table is not frequently used(insert/update/delete) and it can afford to have table level lock, then we can reset the HWM in one go. We can reset the HWM as two steps when the table is used by several people and it is always busy and it does not permit the table level lock even for short moment at specific time. This scenario, we can move the rows to shrink the table. Then in the night time or off peak hours, we can reset the HWM.

DBMS_PROFILER

2009-09-15T13:24:00.000-07:00

DBMS_PROFILER is introduced in oracle8i. It is powerful tool to find the PLSQL execution time and determine the bottleneck of the program unit. This tool provides information about how many times each line is executing and how much time it is spending to execute for each line of the code. The basic idea behind the profiling is, developers can understand where the code is taking most time and they can detect and optimize the PLSQL code. We use SQL trace to determine the bottleneck for SQL code. But for PLSQL code, we can use dbms_profiler utility to profile the run time behaviour. Steps might very for other oracle versions.

How do we set up dbms_profiler utility?

Please remember, the dbms_profiler setup is not part of Oracle installation. We need to setup manually if we want to profile the PLSQL code. There are five simple steps to configure the dbms_profiler. Let us start configure the profiler. This article is tested in oracle10g R2.

Step1. The dbms_profiler package can be loaded by running the $ORACLE_HOME/rdbms/admin/profload.sql script as the SYS user. Execute profload.sql in sys schema.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> connect sys/password@orcl as sysdba
Connected.
SQL> start c:/oracle/product/10.2.0/db_1/rdbms/admin/profload.sql

Package created.

Grant succeeded.

Synonym created.

Library created.

Package body created.

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

Step2. dbms_profiler package requires some schema objects which should be created in central schema or application schema. Create a new schema called profiler. We can either create a new schema or use existing schema. In this case, i created new schema, named Profiler.

SQL> create user profiler
2 identified by profiler;

User created.

SQL> grant create session,resource,connect to profiler;
Grant succeeded.

SQL>

Step3. Run the $ORACLE_HOME/rdbms/admin/proftab.sql file on the profiler schema to create some schema objects to store profiler information. This proftab.sql file creates below three tables with some other objects.

1.PLSQL_PROFILER_RUNS
2.PLSQL_PROFILER_UNITS
3.PLSQL_PROFILER_DATA

SQL> connect profiler/profiler@orcl
Connected.
SQL> start c:/oracle/product/10.2.0/db_1/rdbms/admin/proftab.sql
drop table plsql_profiler_data cascade constraints
*
ERROR at line 1:
ORA-00942: table or view does not exist

drop table plsql_profiler_units cascade constraints
*
ERROR at line 1:
ORA-00942: table or view does not exist

drop table plsql_profiler_runs cascade constraints
*
ERROR at line 1:
ORA-00942: table or view does not exist

drop sequence plsql_profiler_runnumber
*
ERROR at line 1:
ORA-02289: sequence does not exist

Table created.

Comment created.

Table created.

Comment created.

Table created.

Comment created.

Sequence created.

SQL>

Step4. Connect into profiler schema and grant the below privileges.

GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;

SQL> connect profiler/profiler@orcl
Connected.
SQL> GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;

Grant succeeded.

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;

Grant succeeded.

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;

Grant succeeded.

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;

Grant succeeded.

SQL>

Step5. Connect into sys schema and grant the below privileges for dbms_profiler package.

CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;
CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;
CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;
CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber;

SQL> connect sys/password@orcl as sysdba
Connected.
SQL> CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;

Synonym created.

SQL> CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;

Synonym created.

SQL> CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;

Synonym created.

SQL> CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber;

Synonym created.

SQL>

Once we are successful with five steps, we can start profiling the PLSQL code.

How do we profile the PLSQL Procedure?

Let us create sample procedure and profile the procedure.

1. start profiler
2. run the procedure
3. stop profiler
4. flush data from memory and save into table
5. Analyze the data and see where it is taking more time

Here i create a procedure called do_something in SCOTT schema. You can also profile the procedure which is existing in any schema in the database.

SQL> CREATE OR REPLACE PROCEDURE do_something (p_times IN NUMBER) AS
2 v_cnt NUMBER;
3 BEGIN
4 FOR i IN 1 .. p_times LOOP
5 SELECT count(*) + p_times
6 INTO v_cnt
7 FROM EMP;
8 END LOOP;
9 END;
10 /

Procedure created.

The below unnamed PLSQL code starts the profiler and call the procedure. Once the procedure is executed, it stop the profiler. It flush the data from memory and save into table.

SQL> DECLARE
2 l_result BINARY_INTEGER;
3 BEGIN
4 l_result := DBMS_PROFILER.start_profiler(run_comment => 'do_something: ' SYSDATE);
5 do_something(p_times => 100);
6 l_result := DBMS_PROFILER.stop_profiler;
7 dbms_profiler.flush_data;
8 END;
9 /

PL/SQL procedure successfully completed.

SQL>

Here is query to check the profiler result.

SQL> SET LINESIZE 200
SQL> SET TRIMOUT ON
SQL>
SQL> COLUMN runid FORMAT 99999
SQL> COLUMN run_comment FORMAT A50
SQL> SELECT runid,
2 run_date,
3 run_comment,
4 run_total_time
5 FROM plsql_profiler_runs
6 ORDER BY runid;

RUNID RUN_DATE RUN_COMMENT RUN_TOTAL_TIME
------ --------- -------------------------------------------------- --------------
4 15-SEP-09 do_something: 15-SEP-09 686370753

SQL> SELECT d.line#,
2 d.total_occur,
3 d.total_time
4 FROM plsql_profiler_units u
5 JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
6 WHERE u.runid = 4
7 and unit_name='DO_SOMETHING'
8 and unit_owner='SCOTT'
9 and unit_type='PROCEDURE'
10 ORDER BY u.unit_number, d.line#;

LINE# TOTAL_OCCUR TOTAL_TIME
---------- ----------- ----------
1 1 199466
4 101 2247771
5 100 513261322
9 1 85485

SQL> SELECT line ' : ' text
2 FROM all_source
3 WHERE owner = 'SCOTT'
4 AND type = 'PROCEDURE'
5 AND name = 'DO_SOMETHING';

LINE':'TEXT
----------------------------------------------------------------------------------------------------
1 : PROCEDURE do_something (p_times IN NUMBER) AS
2 : v_cnt NUMBER;
3 : BEGIN
4 : FOR i IN 1 .. p_times LOOP
5 : SELECT count(*) + p_times
6 : INTO v_cnt
7 : FROM EMP;
8 : END LOOP;
9 : END;

9 rows selected.

SQL>

Conclusion : The line number 4 runs 101 times and line number 5 runs 100 times. The procedure spends most of the time at line number 5. Now we figured out exactly where it is taking longer time. We can focus on tuning the line 5 in case if we want to....

Where do we use dbms_profiler? TKPROF and Explain plan helps to find where it is taking long time to complete the SQL. Dbms_profiler is useful, if we want to find out which line is consuming most time in entire PLSQL.

The source of this article is oracle-base.

Oracle SQL Questions for Newbies

2009-09-10T18:38:00.000-07:00

I thought, it would be useful if i post some sample SQL questions for newbies. These bunch of SQL questions would be helpful for beginners who wanted to learn Oracle SQL.

Here are some tables which needs to be created to practice SQL questions. Just for learning process, you could use SCOTT schema to practice SQL questions. I am posting 101 Oracle SQL questions with answers.

First step would be, we need to create the below tables and insert relevant data to practice the questions.

Create the below tables:

CREATE TABLE DEPT (
DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13));

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

ALTER TABLE DEPT ADD PRIMARY KEY(DEPTNO);

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));

ALTER TABLE EMP ADD CONSTRAINT FK_EMP_01
FOREIGN KEY(DEPTNO) REFERENCES DEPT;

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE SALGRADE(
GRADE NUMBER(2),
LOSAL NUMBER,
HISAL NUMBER);

INSERT INTO SALGRADE VALUES(1, 700,1200);
INSERT INTO SALGRADE VALUES(2, 1201,1400);
INSERT INTO SALGRADE VALUES(3, 1401,2000);
INSERT INTO SALGRADE VALUES(4, 2001,3000);
INSERT INTO SALGRADE VALUES(5, 3001,9999);

Question & Answers:

1) Display all the records in emp table?

select * from emp;

2) Display all the records in emp table where employee belongs to deptno 10?

select * from emp where deptno = 10

3) Display all the records in emp table where employee does not belong to deptno 30?

select * from emp where deptno != 30;

4) Display total number of records in Emp table?

select count(*) from emp;

5) Display emp table with salary descending order?

select * from emp order by sal desc

6) Display first five records in employee table?

select * from emp where rownum <= 5

7) Display all the records in emp table order by ascending deptno, descending salary?

select * from emp order by deptno asc, sal desc

8) Display all employees those who were joined in year 1981?

select * from emp where to_char(hiredate,'YYYY') = 1981;

9) Display COMM in emp table. Display zero in place of null.

select nvl(comm,0) from emp

10) Display the records in emp table where MGR in 7698,7566 and sal should be greater then 1500

select * from emp where mgr in(7698,7566) and sal > 1500

11) Display all employees where employees hired before 01-JAN-1981

select * from emp where hiredate < '01-JAN-1981'

12) Display all employees with how many years they have been servicing in the company?

select hiredate,round((sysdate-hiredate)/360) as years from emp

13) Display all employees those were not joined in 1981?

select * from emp where to_char(hiredate,'YYYY') != 1981;

14) Display all employees where their hiredate belongs to third quarter?

select * from emp where to_char(hiredate,'Q') = 3;

15) Display all employees where their salary is less then the Ford’s salary?

select * from emp where sal <(select sal from emp where ename='FORD');

16) Display all the records in EMP table along with the rowid?

select ename,rowid from emp;

17) Display all records in EMP table those were joined before SCOTT joined?

select * from emp where hiredate <(select hiredate from emp where ename='SCOTT')

18) Display all employees those who were joined in third quarter of 1981?

select * from emp where to_char(hiredate,'Q') = 3 and to_char(hiredate,'YYYY') = 1981

19) Add 3 months with hiredate in EMP table and display the result?

select hiredate, add_months(hiredate,3) from emp

20) Display the date for next TUESDAY in hiredate column?

select next_day(hiredate,'TUESDAY') from emp;

21) Find the date, 15 days after today’s date.

select sysdate+15 from dual

22) Write a query to display current date?

select sysdate from dual;
select current_date from dual;

23) Display distinct job from emp table?

select distinct job from emp

24) Display all the records in emp table where employee hired after 28-SEP-81 and before 03-DEC-81?

select * from emp where hiredate between '28-SEP-81' and '03-DEC-81'

25) Write a query that displays the employee’s names with the first letter capitalized and all other letters lowercase for all employees whose name starts with J, A, or M

select initcap(ename) from emp where ename like 'J%' or ename like 'A%' or ename like 'M%'

26) Display all jobs that are in department 10. Include the location of department in the output.

select job, loc from emp,dept where emp.deptno = dept.deptno and emp.deptno =10

27) Write a query to display the employee name, department name of all employees who earn a commission

select ename,dname from emp,dept where emp.deptno = dept.deptno and comm is not null;

28) Display the empno, ename, sal, and salary increased by 15%.

select empno, ename, sal actual_sal, (sal * 15/100) as Increased_sal from emp

29) Display ename, sal, grade. Use emp, salgrade table

select ename,sal,grade from emp,salgrade where sal between losal and hisal;

30) Display all employees and corresponding managers

select w.ename,w.sal,m.ename,m.sal from emp w, emp m where w.mgr = m.empno;

31) Display all the departments where employee salary greater then average salary of that department.

select ename,deptno, sal from emp a where sal > (select avg(sal) from emp where emp.deptno = a.deptno) order by deptno;

32) Display all employees whose salary greater then the manager salary?

select w.ename,w.sal,m.ename,m.sal from emp w, emp m where w.mgr = m.empno and w.sal > m.sal

33) Display employees where length of ename is 5

select * from emp where length(ename) =5

34) Display all employees where ename start with J and ends with S

select * from emp where ename like 'J%S'

35) Display all employees where employee does not belong to 10,20,40

select * from emp where deptno not in(10,20,40)

36) Display all employees where jobs does not belong to PRESIDENT and MANAGER?

select * from emp where job not in('PRESIDENT','MANAGER');

37) Display the maximum salary in the emp table

select max(sal) from emp

38) Display average salary for job SALESMAN

select avg(sal) from emp where job = 'SALESMAN'

39) Display all three figures salary in emp table

select * from emp where sal < = 999;

select * from emp where length(sal) = 3;

40) Display all records in emp table for employee who does not receive any commission

select * from emp where comm is not null

41) Display all ename where first character could be anything, but second character should be L?
select * from emp where ename like '_L%'

42) Display nth highest and nth lowest salary in emp table?

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

select distinct sal from (select ename,sal,dense_rank() over(order by sal desc) dr from emp) where dr = &x ;

43) Display all the departments where department has 3 employees?

select deptno from dept a where deptno in(select deptno from emp group by deptno having count(*)=3)

44) Display emp name and corresponding subordinates. Use CONNECT BY clause.

select lpad(' ',level+12)+ename from emp connect by prior empno = mgr start with mgr is null

Note: Please replace pipe symbol in the place of + sign for question 44. Pipe symbol is not displaying the blog. This is the reason, i used Plus sign here.

45) Display sum of salary for each department. The output should be in one record

select sum(decode(deptno,10,sal)) dept10, sum(decode(deptno,20,sal)) dept20, sum(decode(deptno,30,sal)) dept30, sum(sal) total_sal from emp

46) Display all department with Minimum salary and maximum salary?

select min(sal),max(sal) from emp;

47) Display all ename, sal, deptno,dname from emp, dept table where all department which has employees as well as department does not have any employees. This query should include non matching rows.

select dname,b.deptno, ename,sal from emp a, dept b where a.deptno(+) = b.deptno;

select dname,b.deptno, ename,sal from emp a right outer join dept b on a.deptno = b.deptno;

48) Display all ename, sal, deptno from emp, dept table where all employees which has matching department as well as employee does not have any departments. This query should include non matching rows.

Note: In the below query, employee will always have matching record in dept table. Emp, dept table may not be good example to answer this question.

select dname,b.deptno, ename,sal from emp a, dept b where a.deptno = b.deptno(+);

select dname,b.deptno, ename,sal from emp a left outer join dept b on a.deptno = b.deptno;

49) Display all ename, sal, deptno from emp, dept table where all employees which has matching and non matching department as well as all departments in dept table which has matching and non matching employees. This query should include non matching rows on both the tables.

Note: In the below query, employee will always have matching record in dept table. Emp, dept table may not be good example to answer this question.

select dname,b.deptno, ename,sal from emp a full outer join dept b on a.deptno = b.deptno

50) Display all ename, empno, dname, loc from emp, dept table without joining two tables

select * from emp,dept;

51) Display all the departments where department does not have any employees

select deptno from dept where not exists(select 1 from emp where emp.deptno = dept.deptno);

select deptno from dept where deptno not in(select deptno from emp);

52) Display all the departments where department does have atleast one employee

select * from dept a where exists(select 1 from emp b where b.deptno = a.deptno)

select * from dept a where deptno in(select deptno from emp b where a.deptno = b.deptno)

53) Display all employees those who are not managers?

select ename from emp a where not exists (select 1 from emp b where b.mgr = a.empno);

select ename from emp a where empno not in (select mgr from emp b where b.mgr = a.empno and mgr is not null);

54) Display ename, deptno from emp table with format of {ename} belongs to {deptno}

select ename+' belongs to '+deptno from emp

Note: Please replace pipe symbol in the place of + sign for question 44. Pipe symbol is not displaying the blog. This is the reason, i used Plus sign here.

55) Display total number of employees hired for 1980,1981,1982. The output should be in one record.

select
count(decode(to_char(hiredate,'YYYY'), 1980,hiredate)) total_hire_1980,
count(decode(to_char(hiredate,'YYYY'), 1981,hiredate)) total_hire_1981,
count(decode(to_char(hiredate,'YYYY'), 1982,hiredate)) total_hire_1982
from emp

56) Display ename, deptno from employee table. Also add another column in the same query and it should display ten for dept 10, twenty for dept 20, thirty for dept 30, fourty for dept 40

select ename,deptno, (case deptno
when 10 then 'Ten'
when 20 then 'Twenty'
when 30 then 'Thirty'
when 40 then 'fourty'
else 'others' end) as dept
from emp

57) Display all the records in emp table. The ename should be lower case. The job first character should be upper case and rest of the character in job field should be lower case.

select lower(ename) as ename, initcap(job) as job from emp

58) Display all employees those who have joined in first week of the month ?

select * from emp where to_char(hiredate,'W') = 1;

59) Display all empoyees those who have joined in the 49th week of the year?

select * from emp where to_char(hiredate,'WW') = 49;

60) Display empno, deptno, salary, salary difference between current record and previous record in emp table. Deptno should be in descending order.

SELECT empno,
ename,
job,
sal,
LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM emp;

61) Create table emp1 and copy the emp table for deptno 10 while creating the table

Create table emp1 as select * from emp where deptno=10

62) Create table emp2 with same structure of emp table. Do not copy the data

create table emp2 as select * from emp where 1=2

63) Insert new record in emp1 table, Merge the emp1 table on emp table.

insert into emp1 values(9999,'PAUL','MANAGER',7839,SYSDATE,8900,NULL,10);

MERGE
INTO emp tgt
USING emp1 src
ON ( src.empno = tgt.empno )
WHEN MATCHED
THEN
UPDATE
SET tgt.ename = src.ename,
tgt.job = src.job,
tgt.mgr = src.mgr,
tgt.hiredate = src.hiredate,
tgt.sal = src.sal,
tgt.deptno = src.deptno
WHEN NOT MATCHED
THEN
Insert(
Tgt.empno,
Tgt.Ename,
Tgt.Job,
Tgt.Mgr,
Tgt.Hiredate,
Tgt.Sal,
Tgt.Comm,
Tgt.Deptno)
values (src.empno,
src.ename,
src.job,
src.mgr,
src.hiredate,
src.sal,
src.comm,
src.deptno);

64) Display all the records for deptno which belongs to employee name JAMES?

select * from emp where deptno in(select deptno from emp where ename = 'JAMES')

65) Display all the records in emp table where salary should be less then or equal to ADAMS salary?

select * from emp where sal <= (select sal from emp where ename='ADAMS')

66) Display all employees those were joined before employee WARD joined?

select * from emp where hiredate < (select hiredate from emp where ename='WARD')

67) Display all subordinate those who are working under BLAKE?

Select ename from emp where mgr = (select empno from emp where ename='BLAKE')

68) Display all subordinate(all levels) for employee BLAKE?

select ename from emp start with empno = (select empno from emp where ename='BLAKE')
connect by prior empno = mgr

69) Display all record in emp table for deptno which belongs to KING's Job?

select * from emp where deptno in(select deptno from emp where job= (select job from emp where ename = 'KING'))

70) Display the employees for empno which belongs to job PRESIDENT?

select * from emp where empno in(select empno from emp where ename in(select ename from emp where JOB = 'PRESIDENT'));

71) Display list of ename those who have joined in Year 81 as MANAGER?

select * from emp where to_char(hiredate,'YYYY') = 1981 and job = 'MANAGER';

72) Display who is making highest commission?

select * from emp where comm = (select max(comm) from emp);

73) Display who is senior most employee? How many years has been working?

select * from emp where trunc(sysdate-hiredate)/365 = (select max(trunc(sysdate-hiredate)/365) from emp);

select * from emp where hiredate =(select min(hiredate) from emp)

74) Display who is most experienced and least experienced employee?

select * from emp where trunc(sysdate-hiredate)/365 = (select min(trunc(sysdate-hiredate)/365) from emp);

select * from emp where hiredate =(select max(hiredate) from emp)

75) Display ename, sal, grade, dname, loc for each employee.

select empno,ename,b.deptno,dname,grade from
emp a,dept b, salgrade c
where a.deptno = b.deptno
and sal between losal and hisal;

76) Display all employee whose location is DALLAS?

SELECT emp.ename, emp.JOB, emp.deptno
FROM emp
WHERE EXISTS
(SELECT 'x'
FROM dept d
WHERE d.DEPTNO = emp.DEPTNO
AND d.LOC = 'DALLAS') ;

select emp.ename, emp.job, emp.deptno
from emp
where deptno in(select deptno from dept where loc='DALLAS');

77) Display ename, job, dname, deptno for each employee by using INLINE view?

SELECT emp.ename,
emp.JOB,
emp.deptno,
dnames.dname
FROM emp
JOIN (select dname, deptno
from dept ) dnames ON emp.deptno = dnames.deptno

78) List ename, job, sal and department of all employees whose salary is not within the salary grade?

select ename, job, sal, dname
from emp, dept
where emp.deptno = dept.deptno
and not exists
(select ‘x’ from salgrade
where emp.sal between losal and hisal);

79) Use EMP and EMP1 table. Query should have only three columns. Display empno,ename,sal from both tables inluding duplicates.

select empno, ename, sal from emp
union all
select empno, ename, sal from emp1

80) Delete emp table for detpno 10 and 20.

delete emp where deptno in(10,20);

81) Delete all employees those are not getting any commission?

delete emp where comm is null;

82) Delete all employees those who employeed more then 28 years

delete emp where trunc(sysdate - hiredate)/365 > 28;

83) Add duplicate records in emp1 table. Delete the duplicate records in emp1 table.

insert into emp1 select * from emp1 where rownum <=1; commit; delete emp1 a where a.rowid <>(select min(b.rowid) from emp1 b where a.empno = b.empno);

84) Delete the employees where employee salary greater then average salary of department salary?

delete emp a where sal > (select avg(sal) from emp where emp.deptno = a.deptno);

85) Delete all employees those who are reporting to BLAKE?

Delete emp where ename in(Select ename from emp where mgr = (select empno from emp where ename='BLAKE'))

86) Delete all levels of employees those who are under BLAKE?

Delete emp where ename in(select ename from emp start with empno = (select empno from emp where ename='BLAKE')
connect by prior empno = mgr)

87) Delete all employees those who are only managers?

delete emp where ename in(select ename from emp a where empno in (select mgr from emp b where b.mgr = a.empno and mgr is not null))

88) Remove the department in dept table where dept does not have any employees?

delete dept where deptno not in(select deptno from emp where deptno is not null)

89) Remove all grade 2 employees in emp table?

delete emp where empno in(select empno from emp,salgrade where sal between losal and hisal and grade = 2)

90) Remove all the employees in SMITH's department

delete emp where deptno = (select deptno from emp where ename = 'SMITH')

91) Remove least paid employee who are reporting to BLAKE ?

delete emp where sal = (select min(sal) from emp where mgr =
(select empno from emp where ename = 'BLAKE')) and
ename in(select ename from emp where mgr =
(select empno from emp where ename = 'BLAKE'))

92) Remove all employees who were joined before SMITH joined?

delete emp where hiredate < (select hiredate from emp where ename='SMITH');

93) Rename the employee name JONES to ANDY

update emp set ename = 'ANDY' where ename = 'JONES'

94) Change the WARD's hiredate to one day ahead

update emp set hiredate = hiredate + 1 where ename = 'WARD'

95) Update MARTIN salary same as SMITH's salary

update emp set sal = (select sal from emp where ename = 'SMITH') where ename='MARTIN'

96) Increase the salary 5% for employee those who are earning commission less then 1000

update emp set sal = sal + (sal * (5/100)) where comm between 0 and 1000

97) Increase 250$ commission for BLAKE's team

update emp set comm = nvl(comm,0)+250 where mgr = (select empno from emp where ename='BLAKE');

98) Increase 100$ for employee who is making more then averge salary of his department?

update emp a set sal = sal + 150 where sal > (select avg(sal) from emp b where b.deptno = a.deptno)

99) Increase 1% salary for employee who is making lowest salary in dept 10

update emp set sal = sal + (sal* 1/100)
where
sal = (select min(sal) from emp where deptno = 10)
and deptno = 10

100) Reduce the commission amount from employee salary for each employee who were joined after ALLEN joined.

update emp set sal = sal - NVL(comm,0)
where empno in(select empno from emp where hiredate > (select hiredate from emp where
ename = 'ALLEN'))

101) Increase commission 10$ for employees those who are located in NEW YORK.

update emp a set comm = NVL(COMM,0) + 10
where deptno = (select deptno from dept where loc='NEW YORK');

Hope you enjoy all these questions... Please let me know if you have any comments or clarification... Feel free to click advertisement to maintain this blog. Thank you for visiting my Bog!!!.

Oracle11g OCP Exam Passed

2009-08-12T11:17:00.000-07:00

I am glad that, i just passed my Oracle11g OCP exam last week. I just wanted to share my preparation with other folks...

Here are the my preparation steps.

1. Read Oracle11g New features Ebook(Download the book here)
2. Read the oracle-base link. Click
3. Went through testking question (Please contact me if you need)
4. Ofcourse, My practical experience helped me to pass this exam.

At the end, i successfully passed 1ZO-050 exam with 99% score.

Hope it helps.

Where to download OCP Logo?

2009-08-12T11:01:00.000-07:00

Some of the folks, they customize the logo to upload in their resume without knowing the oracle registered logo. I thought, it would be useful to provide the links to download the logo. There are several source which we can use to download the logo.

Here is the google link to download the logo... Google Link

Hope you enjoy this link to download the OCP logo... Have a fun.

How do we use Ref Cursor?

2009-06-17T09:47:00.000-07:00

What is REF CURSOR? A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The main purpose of the ref cursor is, we can return the query result in the front end(Java, .Net, VB, reporting tools etc). For instance, if we want to return the query result set in Cognos reporting tool, then we can use the ref cursor to return the values. The primary use of ref cursor is to pass the result set back to calling application. Ref cursor can be passed as a variable to another subroutine. The powerful cabability in ref cursor is, cursor can be opened and passed to another block for processing, then returned to original block to be closed.

This article is tested in oracle9i and it should work in oracle10g and further versions... All the below PLSQL code in this article is successfully tested in oracle 9.2.0.8. I tested the code in Scott schema with emp and dept tables.

What is the difference between REF CURSOR and Normal Cursor?

1. The cursor concept is same between regular cursor(PLSQL cursor) and ref cursor. But normal cursor, we can declare and define the cursor in the declaration part. In the declaration itself, the select statement is tied up with the cursor. So the cursor structure is known in the compile time. It is static in definition.

In ref cursor, we just declare the variable as SYS_REFCURSOR data type. We are not tying with any select statement in the declaration. But inside the procedure, we can tie up the same ref cursor variable with any number of select statement. It is dynamic and dynamically opened in the procedure, based on the logic or condition.

Let us demonstrate this... I have stored procedure and i am passing input 1 or 2. If i pass 1, then i wanted to display emp table records. If i pass input 2, then i wanted to display dept table. I am demonstrating this in normal cursor and ref cursor.

Here is the way to accomplish this in Normal cursor.

create or replace procedure Test_refcursor(p_choice NUMBER) is
cursor c1 is select * from emp;
cursor c2 is select * from dept;
begin
if p_choice = 1 then
for i in c1 loop
dbms_output.put_line(i.ename);
end loop;
elsif p_choice = 2 then
for i in c2 loop
dbms_output.put_line(i.dname);
end loop;
end if;
END;
/

Here is the way to accomplish this in ref cursor.

Create or replace procedure test_refcursor(p_choice number) is
c1 sys_refcursor;
v_ename emp.ename%type;
v_dname dept.dname%type;
procedure gen_cur(chc IN number,b IN OUT sys_refcursor) is
str varchar2(1000);
begin
if chc = 1 then
str:= 'select ename from emp';
elsif chc = 2 then
str:= 'select dname from dept';
end if;
open b for str;
end;
begin
gen_cur(p_choice,c1);
if p_choice = 1 then
loop
fetch c1 into v_ename;
exit when c1%notfound;
dbms_output.put_line(v_ename);
end loop;
elsif p_choice = 2 then
loop
fetch c1 into v_dname;
exit when c1%notfound;
dbms_output.put_line(v_dname);
end loop;
end if;
end;
/

2. Ref cursor output can be returned to client(java, .Net, VB, reporting tool etc) application. But normal cursor(PLSQL cursor) output can not be returned to client application.

3. Normal cursor can be global. For example, we can declare the normal cursor in the package specification. It can be used in all procedure/functions in the same package as well as outside the package. But ref cursor can not be declared outside of the procedure.

4. Normal cursor can not be passed from one subroutine to another subroutine. But ref cursor can be passed from one subroutine to another subroutine.

How do we declare ref cursor? There are two type of ref cursor variable declaration. One is weak type declaration and another one is strong type declaration. Weak typed declaration does not tell us return data structure. Strong type declaration will tell us what type of data will be returned. Strongly typed cursor has less flexibilities and less prone to programming errors. Weakly typed cursors has more flexibilities and it can return different structure of of the data.

type emp_cursor is ref cursor; -- Weak typed declaration

type emp_cursor is ref cursor
returning emp%rowtype; -- Strong typed declaration

Once cursor type is defined, then cursor variable can be assigned to cursor type.
c1 emp_cursor;

Sample program for ref cursor... Pass the employee name as a input to procedure and return all subordinates.

CREATE OR REPLACE PROCEDURE testrefcursor(
p_ename IN VARCHAR2,
curename OUT SYS_REFCURSOR) IS
sql_text VARCHAR2(4000);
BEGIN
sql_text := 'SELECT ENAME
FROM EMP
WHERE ENAME != :1
START WITH ENAME = :2
CONNECT BY PRIOR EMPNO = MGR';
IF curename%ISOPEN THEN
CLOSE curename;
END IF ;
OPEN curename
FOR sql_text USING p_ename,p_ename;
END;
/

Let us accomplish the same above task through Function.

CREATE OR REPLACE FUNCTION testrefcursor(p_ename IN VARCHAR2)
RETURN SYS_REFCURSOR IS
sql_text VARCHAR2(4000);
curename SYS_REFCURSOR;
BEGIN
sql_text := 'SELECT ENAME
FROM EMP
WHERE ENAME != :1
START WITH ENAME = :2
CONNECT BY PRIOR EMPNO = MGR';
IF curename%ISOPEN THEN
CLOSE curename;
END IF ;
OPEN curename
FOR sql_text USING p_ename,p_ename;
RETURN curename;
END;
/

How do we display ref cursor result through SQL*PLUS? It is simple. Let us go with some sample code and demonstrate how to display the output in SQL*PLUS? Let us display the output for the above procedure testrefcursor.

SQL> DECLARE
2 refcursor SYS_REFCURSOR;
3 v_ename EMP.ENAME%TYPE;
4 BEGIN
5 testrefcursor('KING',refcursor);
6 loop
7 fetch refcursor into v_ename;
8 exit when refcursor%notfound;
9 dbms_output.put_line(v_ename);
10 end loop;
11 end;
12 /
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER

PL/SQL procedure successfully completed.

SQL>

Here is the way, we can display the above function output....

SQL> select testrefcursor('KING') from dual;

TESTREFCURSOR('KING'
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ENAME
----------
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER

13 rows selected.

We can also define SQL*PLus variables of type REFCURSOR...

SQL> create or replace function getemplist(dno in number)
2 return sys_refcursor
3 is
4 return_value sys_refcursor;
5 begin
6 open return_value for
7 select ename from emp where deptno = dno;
8 return return_value;
9 end;
10 /

Function created.

SQL> var rc refcursor
SQL> exec :rc := getemplist(30)

PL/SQL procedure successfully completed.

SQL> print rc

ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

6 rows selected.

SQL>

When and what circumstances we should use ref cursor? Ref cursor is classic option when we want to return the query output into any front end application. We can use ref cursor when you are not able to accomplish the task through normal cursor. REF CURSOR is flexibility feature, but not performance feature. Ref cursor is bad choice when performance is a concern and able to accomplish the task by using regular cursor. Ref cursor always consumes time to process the query compared to normal cursor. So ref cursor should be used only place where it is required.

Performance comparision between ref cursor and regular cursor? Ref cursor is not a good option if performance is a concern. but ofcourse, there are place you can not avoid ref cursor. Ref cursor is not an option when you want to process the records inside the PLSQL procedure. Ref cursor is always slower then explicit cursor and implicit cursor. Let me demonstrate how process time between ref cursor, explicit cursor and implicit cursor...

As per the below example, the ref cursor consumes more time then the explicit cursor and implicit cursor.... So in netshell, use ref cursor only if it is required. otherwise, try to use regular cursor..

Note : For some reason, in the below code, pipe symbol is not appearing in the blog. So i replaced the pipe symbol with # symbol. In case if you want to run the below code in your database, please replace back # with pipe sign.

SQL> CREATE OR REPLACE PROCEDURE stp_refcursor_comparison AS
2 l_loops NUMBER := 10000;
3 l_dummy dual.dummy%TYPE;
4 l_start NUMBER;
5 CURSOR c_dual IS
6 SELECT dummy
7 FROM dual;
8 l_cursor SYS_REFCURSOR;
9 BEGIN
10 -- Time explicit cursor.
11 l_start := DBMS_UTILITY.get_time;
12 FOR i IN 1 .. l_loops LOOP
13 OPEN c_dual;
14 FETCH c_dual
15 INTO l_dummy;
16 CLOSE c_dual;
17 END LOOP;
18 DBMS_OUTPUT.put_line('Explicit: ' #
19 (DBMS_UTILITY.get_time - l_start));
20 -- Time ref cursor.
21 l_start := DBMS_UTILITY.get_time;
22 FOR i IN 1 .. l_loops LOOP
23 OPEN l_cursor FOR SELECT dummy FROM dual;
24 FETCH l_cursor
25 INTO l_dummy;
26 CLOSE l_cursor;
27 END LOOP;
28 DBMS_OUTPUT.put_line('REF CURSOR: ' #
29 (DBMS_UTILITY.get_time - l_start));
30 -- Time implicit cursor.
31 l_start := DBMS_UTILITY.get_time;
32 FOR i IN 1 .. l_loops LOOP
33 SELECT dummy
34 INTO l_dummy
35 FROM dual;
36 END LOOP;
37 DBMS_OUTPUT.put_line('Implicit: ' #
38 (DBMS_UTILITY.get_time - l_start));
39 END stp_refcursor_comparison;
40 /

Procedure created.

SQL> set serveroutput on
SQL> execute stp_refcursor_comparison;
Explicit: 53
REF CURSOR: 67
Implicit: 35

PL/SQL procedure successfully completed.

SQL>

Star schema vs Snowflake Schema

2009-06-09T09:06:00.000-07:00

What is star schema? Star schema is data warehousing data model which resembles to star. There are one or more fact table connected with multiple dimensional tables. Center of the star consist of one or more fact table and fact is pointing to different dimension tables. Dimension tables have a simple primary key, while fact tables have a composit primary key consisting of the aggregate of relevant dimension keys.

Fact table consists of Measurements or facts of business process. It is centeralized table in star schema, called FACT. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys. Fact table contains the content of the Datawarehouse. A fact table might contain either detail level facts or facts that have been aggregated (fact tables that contain aggregated facts are often instead called summary tables). In the real world, it is possible to have a fact table that contains no measures or facts. These tables are called as Factless Fact tables.

Factless Fact means only the key available in the Fact and there are no measures available. Factless fact can have only keys or keys with count of occurrences/ events (For instance, no of accident in a month, no of policies has been closed in a month). It is used to support negative analysis report. For example a Store that did not sell a product for a given period. It is used to join the dimension tables.

Dimension table is a parent table which is connected with Fact table. Dimension has attributes which are normally descriptive and textual values. For instance, SALES table is fact table and possible dimension tables are TIME, PRODUCT, REGION, SALESPERSON, etc. Dimension never have foreign key in star schema data model.

Slowly changing dimension(SCD) applies to cases where the attribute for a record varies over time. We have three type of SCD, Type1, Type2 and Type3.

Type1: The new record replaces the original record. No trace of the old record exists

Type2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two people.

Type3: The original record is modified to reflect the change

To learn more about SCD, please click SCD

Here is sample data model for star schema. In the below data model, the dimensions are products, customers, time and locations. The fact table is sales and it is connected with all dimension. Now we can see the fact data with different dimensions. The fact table Foreign key will be connected with primary key of dimension table. We never connect the one dimension table to another dimension table. Oracle optimizer understand the star query and generate the different execution plan to improve the performance.

What is snowflake schema? The snowflake schema is an extension of the star schema, where each point of the star explodes into more points. In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy.

Here is sample data model for snowflake schema.. Here customer dimension is normalized into another lookup table customertype. The same way, we can also normalize the customer dimension into another lookup table as country. Location dimension is normalized into territory. Product dimension can also be normalized into another lookup table as supplier...

Advantages of Snowflake...

1. No redundancy and hence more easy to maintain and change.
2. When we normalize the dimension, the dimension will not be sparsed.
3. It reduce the disk space since there is no redundancy. But in real world scenario, dimension is small table and disk space is not a major issue.

Disadvantages of Snowflake...

1. You need to join more tables when we write the query. It is less easy to understand.
2. In snowflake model, query process time will increase, since we are joining multiple tables.

When should we go for snowflake schema? Dimension has many columns, and the data are very sparse, most of the fields has no data, also disk space is a concern, then we can think of snowflake. Since dimension tables hold less space, snow flake schema approach may be avoided in some organization. We can go for snowflake model when we have small data mart or datawarehouse. Star schema model is good for bigger data mart/warehouse. To learn more about snowflake schema, please click.

To learn more about dimensional data modeling, please see these links. Link1 and Link2

Temporary tablespace

2009-06-08T14:05:00.000-07:00

What is temporary tablespace?

Temporary tablespace is used to store temporary information which is used for sort operation. Data that is only used for the duration of a session is stored in a temporary tablespaces. Let us say, we are joining two large tables, then sorting may not be able to complete in memory. Apparently oracle use the temporary segment to complete the sorting. Here are some SQL statement which might require disk sorting.

CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc

How do we create temporary tablespace?

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP.DBF'
SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

TEMPFILEs are not recorded in the database's control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident. RMAN never backup the temporary tablespace data files.

How do we monitor temp tablespace?

Temp files are listed in V$TEMPFILE and DBA_TEMP_FILES. V$SORT_SEGMENT and V$SORT_USAGE can be used to find out who occupies the space in temporary Tablespaces. DBA_FREE_SPACE does not record free space for temporary tablespaces. We can use V$TEMP_SPACE_HEADER to find out the free space and used space.

Default temporary tablespace(Oracle9i feature)

When DBA create user, he/she should assign temporary tablespace. Prior to oracle9i, if DBA forgot to assign the temporary tablespace, then oracle use SYSTEM tablespace for sorting. To avoid overloading SYSTEM tablespace, oracle9i introduced default temporary tablespace. So every database has default temporary tablespace since Oracle9i. In case, if we forgot to assign temporary tablespace while creating user, then oracle use default temporary tablespace for sorting. Here is the way, we can assign default temporary tablespace for entire database.

SQL> alter database default temporary tablespace temp1;

To see the default temporary tablespace for a database, execute the following query:

SQL> select property_value from DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Restriction on Default temporary tablespace:

1. You can drop a default temporary tablespace until after you have
created new default temporary tablespace.

2. You can not take default temporary tablespace offline

3. You can not change a default temporary tablespace into a permanent tablespace

Temporary tablespace Groups(Oracle10g feature)

In nutshell, temporary tablespace group is a shortcut or synonym for a list of temporary tablespaces. A temporary tablespace group can have only temporary tablespaces as members. A temporary tablespace group consist of at least one temporary tablespace. A temporary tablespace group can not be empty. After last member of a temporary tablespace group has been dropped, the temporary tablespace group no longer exists. The temporary tablespace group is created when the first temporary tablespace is added to the group.

When ever a temporary tablespace can be referenced, a temporary tablespace group can be referenced as well. Therefore, temporary tablespace, temporary tablespace group share the same namespace. Temporary tablespace can not have the same name as a temporary tablespace group. The new view DBA_TABLESPACE_GROUPS shows the members of each temporary tablespace.

The advantage of this feature is, we can tailor user load to individual temporary tablespaces. We can allow large sort operations to span and consume multiple temporary tablespaces if needed. We can Reduce contention when multiple temporary tablespaces are defined.

How temporary tablespace group works?

Temporary tablespaces allow a single user with multiple session to potentially use a different temporary tablespace in each session. The user SCOTT is assigned the temporary tablespace group TEMPGRP consisting of TEMP1, TEMP2, TEMP3. The user SCOTT in session #1 may use actual temporary tablespace TEMP1, and user SCOTT in session#2 may use the actual temporary tablespace TEMP3. Not only does this prevent large tablespace operations from running out of temporary space, it also allows parallel operations within a single session to potentially use more than one actual temporary tablespace, even though in all the previous scenarios, SCOTT was assigned the TMPGRP temporary tablespace group. Logically the same temporary tablespace was used in every session.

Creating and Dropping temporary tablespace Groups:

CREATE TEMPORARY TABLESPACE temp1 TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP1.DBF' SIZE 20M;

CREATE TEMPORARY TABLESPACE temp3 TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP3.DBF' SIZE 20M;

CREATE TEMPORARY TABLESPACE temp4 TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP4.DBF' SIZE 20M;

alter tablespace temp1 tablespace group tempgrp;
alter tablespace temp3 tablespace group tempgrp;
alter tablespace temp4 tablespace group tempgrp;
alter database default temporary tablespace tempgrp;

You can not drop the temporary tablespace group, but however, we can drop one of the members of the group as below.

alter tablespace temp3 tablespace group '';

We can also create temporary tablespace and we can immediately add it to an existing group, or create new group with one member as below.

CREATE TEMPORARY TABLESPACE temp6 TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP6.DBF' SIZE 20M
tablespace group tempgrp2;

Assigning temporary tablespace group to users:

Assigning a temporary tablespace group to a user is identical to assigning a temporary tablespace to user.

Create user test identified by test
Default tablespace users
Temporary tablespace tempgrp;

Note that, if you did not specify a temporary tablespace for TEST, then we can still assign the temporary tablespace group.

Alter user test temporary tablespace tempgrp;

How do we resize the temporary data file?

In many database configurations, the DBA will choose to allow their temporary tablespace to auto extend. A bad query can easily chew up valuable space on the disk. The DBA will often want to resize the temporary tablespace to a more reasonable size in order to reclaim that extra space. The obvious action would be to resize the tempfiles using the following statement:

SQL> alter database tempfile
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP6.DBF' resize 250M;
alter database tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP6.DBF'
resize 250M
*
ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZE value

Ooops.... It did not work!!!. There are several other method exists to resize the temp files. But there is obvious method exists in all the version which is dropping and recreating temp files. Dropping and recreating the temp file is straight forward when the temporary tablespace is not a default temporary tablespace.

Since there are several method exists, i am going to discuss few ways about how to drop and recreate the temp files when temp file becomes bigger..... It is recommended to do this operation in off peak hours...

Method1a... Let us assume, some one ran the bad query and the temp file is bigger. Now how do we resize the temp file to smaller size. The temporary tablespace is default temporary tablespace. In this scenario, temporary tablespace is not part of tempoary tablespace group. The temporary tablespace name is TEMP.

Step1: create a new temporary tablespace with reasonable size.

CREATE TEMPORARY TABLESPACE tempspace TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMPSPACE.DBF' SIZE 20M;

Step2: Modify the newly created temporary tablepsace as default temporary tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPSPACE

Step3: Drop the bigger temporary tablespace. But before dropping, make sure, no one is using the temporary tablespace. Just make sure, the below query returns zero records.

SELECT COUNT(*) FROM v$sort_usage WHERE tablespace = 'TEMP'
SELECT COUNT(*) FROM v$tempseg_usage WHERE tablespace = 'TEMP'

Step4: Once the above(step3) query returns zero records, then drop the temporary tablespace.

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Method1b... Let us assume, some one ran the bad query and the temp file is bigger. Now how do we resize the temp file to smaller size. The temporary tablespace is default temporary tablespace. In this scenario, temporary tablespace is part of temporary tablespace group. The temporary tablespace name is TEMP and group name is TEMPGRP.

Step1: Unlink the bigger tablespace from temporary tablespace group.

alter tablespace temp tablespace group '';

Step2: Just make sure, no one is using temporary tablespace. Below query should return zero records.

SELECT COUNT(*) FROM v$sort_usage WHERE tablespace = 'TEMP'
SELECT COUNT(*) FROM v$tempseg_usage WHERE tablespace = 'TEMP'

Step3: Once the above(step3) query returns zero records, then drop the temporary tablespace.

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Step4: Create new tablespace and add the tablespace to group.

CREATE TEMPORARY TABLESPACE temp
TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP.DBF' SIZE 20M tablespace group tempgrp;

How do we recover the temporary data file?

When ever, we the temporary tablespace temp file is corrupted or deleted accidently, then it is easy to recover. RMAN never backup the temporary tablespace. Till oracle9i, we make the temp file offline and drop the temp file at the database level(alter database datafile 'c:/oracle/oradata/temp.dbf' offline drop). Once it is dropped, then drop the temporary tablespace and recreate new one. Oracle10g introduced new feature which we will create the temp file automatically when we restart the database.

Here is the steps in oracle10g. The database is running in windows OS.

Step1: Let us delete the temp file to simulate that the temp file is corrupted.

D:\oracle\product\10.2.0\oradata\orcl>dir *.dbf
Volume in drive D has no label.
Volume Serial Number is 70AE-6E52

Directory of D:\oracle\product\10.2.0\oradata\orcl

06/14/2009 08:55 PM 104,865,792 EXAMPLE01.DBF
06/14/2009 08:55 PM 251,666,432 SYSAUX01.DBF
06/14/2009 08:55 PM 503,324,672 SYSTEM01.DBF
06/14/2009 08:50 PM 20,979,712 TEMP01.DBF
06/14/2009 08:55 PM 31,465,472 UNDOTBS01.DBF
06/14/2009 08:55 PM 5,251,072 USERS01.DBF
6 File(s) 917,553,152 bytes
0 Dir(s) 15,348,064,256 bytes free

D:\oracle\product\10.2.0\oradata\orcl>del TEMP01.DBF

D:\oracle\product\10.2.0\oradata\orcl>dir *.dbf
Volume in drive D has no label.
Volume Serial Number is 70AE-6E52

Directory of D:\oracle\product\10.2.0\oradata\orcl

06/14/2009 08:55 PM 104,865,792 EXAMPLE01.DBF
06/14/2009 08:55 PM 251,666,432 SYSAUX01.DBF
06/14/2009 08:55 PM 503,324,672 SYSTEM01.DBF
06/14/2009 08:55 PM 31,465,472 UNDOTBS01.DBF
06/14/2009 08:55 PM 5,251,072 USERS01.DBF
5 File(s) 896,573,440 bytes
0 Dir(s) 15,369,043,968 bytes free

D:\oracle\product\10.2.0\oradata\orcl>

Step2: Let us restart the database. In oracle9i, the database will not open. But in oracle10g, when we start the database, it creates the temp file automatically and open the database. Just click to know more info.

D:\oracle\product\10.2.0\oradata\orcl>set oracle_sid=orcl

D:\oracle\product\10.2.0\oradata\orcl>sqlplus sys/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 14 21:41:51 2009

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1248552 bytes
Variable Size 88081112 bytes
Database Buffers 188743680 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
SQL>

Step 3: Database opened successfully. Let us check the DB directory...

D:\oracle\product\10.2.0\oradata\orcl>dir *.dbf
Volume in drive D has no label.
Volume Serial Number is 70AE-6E52

Directory of D:\oracle\product\10.2.0\oradata\orcl

06/14/2009 08:55 PM 104,865,792 EXAMPLE01.DBF
06/14/2009 08:55 PM 251,666,432 SYSAUX01.DBF
06/14/2009 08:55 PM 503,324,672 SYSTEM01.DBF
06/14/2009 09:42 PM 20,979,712 TEMP01.DBF
06/14/2009 08:55 PM 31,465,472 UNDOTBS01.DBF
06/14/2009 08:55 PM 5,251,072 USERS01.DBF
6 File(s) 917,553,152 bytes
0 Dir(s) 15,348,047,872 bytes free

D:\oracle\product\10.2.0\oradata\orcl>

Logical standby vs Physical standby

2009-06-01T15:13:00.000-07:00

Standby database was introduced in oracle8i for high availability solution. Oracle9i enhanced the standby database and introduced Data Guard. Generally there are two type of standby database. One is physical standby and another one is logical standby. This article is written as per the version Oracle10gR2.

1. What is physical standby database?
2. What is logical standby database?
3. What is the difference between logical standby and physical?
4. At what circumstances, physical standby is useful?
5. At what circumstances, logical standby is useful?

1. What is physical standby database?

Physical standby database is physically identical to the primary database. It is block by block copy of the primary images. The archived redo log files are shipped to standby database and applied the archived redo log files on the standby database. So standby database should be always in recovery mode. This is like, DBA is sitting in remote location and recovering the primary database in different server by applying the archived redo log files.

2. What is logical standby database?

Logical standby database is logically identical to the primary database. Oracle use the logminer technology to transforms standard archived redo logs(by default) or redo logs(if real-time apply enabled) into SQL statements and applies them to the logical stand by database. A logical standby database can remain open and the same time its tables are updated from the primary database, and those tables are simultaneously available for read access. Oracle9i introduced logical standby database.

3. What is the difference between logical standby and physical standby?

Physical standby database is always in managed recovery mode. We can only open the physical standby database in read only mode. But archive redo logs can not be applied while physical standby database is in read only mode.

Logical standby database is always in open mode. The SQL statements which is generated from primary database is applied on logical standby while the database is in open mode. We can have additional materialized views, indexes and tables added for faster performance in logical standby.

Physical standby schema matches exactly the source database. Logical standby database does not have to match the schema structure of the source database.

3. At what circumstances, physical standby is useful?

We can use Physical standby when we need to go for High availability solutions. Physical standby is the most commonly used for disaster recovery or failure.

5. At what circumstances, logical standby is useful?

We can use logical standby if we want to off-load some of the reporting overhead in primary database. The logical standby database is an excellent choice to do queries, summations, and reporting activities, thereby off-loading the primary database from those tasks and saving valuable CPU and I/O cycles.

Note: Oracle recommends not to apply by DML operations on logical standby tables maintained by SQL Apply. This will introduce deviations between the primary and standby databases that will make it impossible for the logical standby database to be maintained.

As per Oracle10gR2, Some of the SQL statments will not be shipped from primary database to logical standby database. Some sample SQL statements are......

1. CREATE or ALTER or DROP MATERIALIZED VIEW
2. CREATE or ALTER or DROP MATERIALIZED VIEW LOG
3. ALTER SESSION
4. CREATE PFILE
5. CREATE or DROP DATABASE LINK

Please click this link if you need to read more about standby DB.

Cloning Database through RMAN

2009-05-28T10:33:00.000-07:00

A wonderful feature in RMAN to clone or duplicate the database on the same server or remote server. This feature is useful when someone truncated or dropped the table and they wanted to recover the table. In that case, we can clone the database in different location and recover the database until point of time. This feature is very useful to recover the selected object from the RMAN backup rather than roll back a whole database. It is possible to create a duplicate database on a remote server with the same file structure or remote server with different file structure or the local server with a different file structure.

This article is tested in windows environment. Now I am going to clone the ORCL database and restore in different location on the same server. The new database name is called CLONEDB. Please remember, the directory, file names, Database names may varry between environment to environment. So you might need to change the scripts based on your database name, file structure when you follow the below steps.

Prerequisite: You should have target database and RMAN should be implemented to backup the target database. How do we implement RMAN backup? Please see this link.

Here is the Target DB and catalog DB info in my server.
Target DB = ORCL
CatalogDB = CATDB

Data file, redo log, control file directory location for target and catalog DB:
C:\oracle\product\10.2.0\oradata\orcl
C:\oracle\product\10.2.0\oradata\catdb

Tnsentry file location
C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN

Password file location
C:\oracle\product\10.2.0\db_1\database

SPFILE location:
C:\oracle\product\10.2.0\db_1\dbs

Here are the nine easy steps to clone the database. I have followed this steps in Oracle10gR2 in windows environment. So please test the below steps in your dev/test environment before proceed to production. The steps are pretty much same for unix environment except directory names and step5

Step1. First create the directory structures for the CLONEDB.

Create the folder clonedb in the following locations.
c:\oracle\product\10.2.0\oradata
C:\oracle\product\10.2.0\admin

Create folders(adump,bdump,cdump,dpdump,udump) in the following location.
C:\oracle\product\10.2.0\admin\clonedb

Step2. Create the password file for clonedb database. Orapwd is oracle utility and we can run this utility in command prompt for windows server. For unix environment, we need to run this in $ prompt.

orapwd file=C:\oracle\product\10.2.0\db_1\database\PWDclonedb.ora password=password entries=10

Step3. Add the appropriate entries into the tnsnames.ora and listener.ora files.

Add this entry in listener.ora file.

(SID_DESC =
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = CLONEDB))

After adding the above entry, reload the listener(lsnrctl reload)

Add the below entry in tnsnames.ora file..

CLONEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = EW_Training)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clonedb)
)
)

Step 4. Build init.ora file for the duplicate database. Since we are duplicating the database on the same server as the original, we must convert the file names, so there is no conflict. These entries needs to be modified in pfile for cloning database.

db_name='clonedb'
dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
audit_file_dest='C:\oracle\product\10.2.0\admin\clonedb\adump'
background_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\bdump'
control_files='C:\oracle\product\10.2.0\oradata\clonedb\control01.ctl',
'C:\oracle\product\10.2.0\oradata\clonedb\control02.ctl',
'C:\oracle\product\10.2.0\oradata\clonedb\control03.ctl'
core_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\cdump'
user_dump_dest='C:\oracle\product\10.2.0\admin\clonedb\udump'
db_file_name_convert=('C:\oracle\product\10.2.0\oradata\orcl','C:\oracle\product\10.2.0\oradata\clonedb')
log_file_name_convert=('C:\oracle\product\10.2.0\oradata\orcl','C:\oracle\product\10.2.0\oradata\clonedb')

The modified PFILE is located in C:\oracle\product\10.2.0\db_1\dbs\PFILE_CLONE.ORA

Step 5. Create the service for CLONEDB instance. Use ORDIM utility for windows environment. This command should run on OS Level.

ORADIM -NEW -SID clonedb

Creating service is only applicable for windows environment. We do not need to create any service in unix environment. In unix, starting the database spawns a services. We must ignore this step for unix environment.

Step 6. Create the spfile by using pfile which is build in step 4. Login as sys user in ORCL database and run this script.

create spfile = 'C:\oracle\product\10.2.0\db_1\dbs\SPFILECLONEDB.ORA'
from pfile='C:\oracle\product\10.2.0\db_1\dbs\PFILE_CLONE.ORA'

Step 7. Create file name called initclonedb.ORA in C:\oracle\product\10.2.0\db_1\database location. The file should have the below content.
SPFILE='C:\oracle\product\10.2.0\db_1/dbs/spfileclonedb.ora'

Step 8. Login as sys user in clonedb database. Start the clonedb instance without nomount.

C:\>set oracle_sid=clonedb

C:\>sqlplus sys/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 28 15:22:37 2009

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

Connected to an idle instance.

SQL> startup force nomount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
SQL>

Step 9. Connect as RMAN and clone the database. If you use any reserve word(like CLONE, TEST etc) for cloning database name, then you should use in double quotes. This case, i am using database name as clonedb. But in case, if we use name CLONE, then it should be quoted like this... (RMAN> duplicate target database to "CLONE";)

C:\>rman target=sys/password@orcl catalog=rman/rman@catdb AUXILIARY /

RMAN> duplicate target database to clonedb;

Now the ORCL database is successfully cloned and cloned database name is CLONEDB. In case if we want to clone the database to specific point of time, then we can use the below command to clone.

DUPLICATE TARGET DATABASE TO CLONEDB UNTIL TIME 'SYSDATE-4';

Questions and Answers....

Question1
I wanted to change the file names while cloning the database. How do we do that? Let us say, the file names in the target database are...

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF

While cloning the database, i wanted to change the file name as below. How can i achieve this?

C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSAUXCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\UNDOTBSCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSTEMCLONE01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE02.DBF

The directory structures are taken care by db_file_name_convert and log_file_name_covert parameters. The file name changes are not taken care by this parameters. Here is the below sample script to take care of this....

RMAN>run
{
SET NEWNAME FOR DATAFILE 1 TO 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\SYSTEMCLONE01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONEDB\USERSCLONE01.DBF';
duplicate target database to clonedb;
}

Question2 One of my programmer truncated the table accidently. I wanted to clone that database and recover that table. But the database size is 2TB. I do not want to clone the entire 2 TB data. Since i wanted to clone only the tablespace which blongs to my table. How do we exclude unwanted tablespaces? Please remember, we can not skip SYSTEM, SYSAUX, UNDO, TEMP and default permanent tablespace. The below script ingore the specific tablespaces.

Run
{
DUPLICATE TARGET DATABASE TO clonedb
SKIP TABLESPACE USERS, INVENTORY;
}

Question3 How do i skip the read only tablespace while cloning the database? The below script skip the read only tablespaces.

Run
{
DUPLICATE TARGET DATABASE TO clonedb
SKIP READONLY ;
}

Question4 Let us say, my target database is running in Host1. My catalog database is running in Host2. I wanted to clone the target database in different server which is called Host3. Where should i run the RMAN?

RMAN is client program that connects to the database. So it does not matter where you run the RMAN. We could actually run in Host4 if we want to... We should be good as long as we connect right database. Where the RMAN client runs is irrelevant.

Note : As per oracle10gR2, during the cloning, if any tablespaces are OFFLINE in target database, will not be cloned into new database.

Temp tablespace never be backed up through RMAN. Hence, it will not be cloned. At the end, cloning process add temp files for temporary tablespace automatically. This is pretty much same for restoring the database from RMAN backup. RMAN creates the temporary tablespace when we restore the database.

New features in RMAN since Oracle9i

2009-05-26T16:04:00.000-07:00

Oracle9i RMAN new features: Oracle9i introduced several new enhancements. Let us discuss some of the new enhancements in oracle9i.

1. Persistent Configuration:
Introduced new CONFIGURE command enable us to configure various backup, restore and recover parameters that are persistent across RMAN sessions.

CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\BK\bk%d_%s_%p'
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEFAULT DEVICE TYPE CLEAR;
CONFIGURE EXCLUDE FOR TABLESPACE users;
CONFIGURE EXCLUDE FOR TABLESPACE users CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE BACKUP OPTIMIZATION ON;

2. Backup of Archivelogs:

We can use new PLUS ARCHIVELOG clause to specify that archive logs that have not been backed up are included with the data backup.

BACKUP DATAFILE 2 PLUS ARCHIVELOG;

3. Restartable Backups:

Oracle9i has added another new clause to the BACKUP statement, NOT BACKED UP [SINCE TIME….]. This clause specifies that oracle should only back up files that have not been backed up since a particular date and time.

BACKUP DATABASE NOT BACKED UP SINCE TIME ‘sysdate-7’;

We can use this clause to restart the backup process when it fails after completing a part of the backup. This clause only backs up the files that were missed by the first backup that failed. This clause enables you to reduce the time required for the backup. Otherwise, you would have to perform a full backup again. If we omit the SINCE TIME option, files that have never been backed up become candidates for backup.

Oracle9i also optimize the restoration of files from backup, files whose file header match the headers of the files in the backup sets are not restored. This keeps us from restoring files that have already been restored in the case of restore operation that fails midway.

4. Block Media Recovery – BMR:

Data blocks can be corrupted due to various reasons during normal database operations. Oracle writes information about the data block corruption into the alert log file and the user trace files. Prior to oracle9i, we had to restore and recover the entire data file by applying the redo required from the archived log files, even through just one single block was corrupted. It would be time consuming to restore and recover the entire data file just for one corrupted block.

Oracle9i introduced BMR to enable us to restore and recover just those blocks that are corrupted. We can find the corrupted block from V$BACKUP_CORRUPTION and V$COPY_CORRUPTION views. We also do not have to bring the data file offline to recover the corrupted blocks. The DB availability is greatly enhanced with BMR feature.

BLOCKRECOVER DATAFILE d1 BLOCK b1,[b2,b3,b4…]
[DATAFILE d2 BLOCK b5,[b7,b8….]]…

--recover corrupt blocks in three datafiles
RMAN> BLOCKRECOVER DATAFILE 2 BLOCK 12, 13 DATAFILE 3 BLOCK 5, 98, 99 DATAFILE 4 BLOCK 19;

5. Trail Recovery:

Trail recovery tests the backup and identifies any problem in the backup before we really use this backup for any recovery. The difference between regular recovery and trail recovery is that, in trial recovery, the data is not written to the data files and changes are only recorded in the buffers. Trail recovery writes any errors encountered into the alert log file.

Once the trail recovery is completed, all the effects of trail recovery are nullified, and it writes all the errors in alert log. The database should be mounted stage while doing regular recovery as well as trail recovery. So users can not use the database during both trail and regular recovery.

Oracle9i introduced new feature to allow number of block corruptions during the recovery. If the corrupt blocks are few, you can perform a recovery that ignores the corrupt block but recovers the rest.

RECOVER DATABASE ALLOW 2 CORRUPTION;

Miscellaneous Enhancements:

1. Oracle9i has ability to report obsolete backups that are not required to keep the database unrecoverable with in a particular window of time. Here is the example.

REPORT OBSOLETE RECOVERY WINDOW OF 7 DAYS

We can also use DELETE OBSOLETE command to delete those obsolete backups. Also we can display files that must be backed up for number of days recovery window.

REPORT NEED BACKUP RECOVERY WINDOW OF 7 DAYS

2. We can use CROSSCHECK command to check if backup sets or file copies exists.

3. List command has been enhanced. Now we can specify either BY BACKUP or BY FILE.
LIST BACKUP;
LIST BACKUP BY FILE;

4. Introduced SHOW command to display all persistent parameters.
SHOW ALL

Oracle10g RMAN new features: Oracle10g introduced several new enhancements. Let us discuss some of the new enhancements in oracle10g.

1. Incrementally updated backups:

RMAN incremental backup method back up only datafile blocks that have changed since a specified previous backup. You can make incremental backups of databases, individual tablespaces or datafiles. The goal of an incremental backup is to back up only those data blocks that have changed since a previous backup.

Each data block in a datafile contains a system change number (SCN), which is the SCN at which the most recent change was made to the block. During an incremental backup, RMAN reads the SCN of each data block in the input file and compares it to the checkpoint SCN of the parent incremental backup. If the SCN in the input data block is greater than the checkpoint SCN of the parent, then RMAN copies the block.

Incremental backups can be either level 0 or level 1. A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.

RMAN> backup incremental level 0 tablespace system;
RMAN> backup incremental level 1 database;

A level 1 incremental backup can be either of the following types:

1. A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0

2. A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

2. Fast Incremental backup

During the incremental backup, oracle scans the whole data file and compare the SCN between the blocks in the data file and backup set files. It will be a time consuming tasks. Oracle introduced change tracking file to track the physical location of all database changes. During an incremental backup, RMAN uses the change tracking file to quickly identify only the blocks that have changed, avoiding the time consuming task of reading the entire data file to determine which blocks have changed. This is called Fast incremental backup. The new background process CTWR(Change tracking writer) is also required when using fast incremental backups.

SQL> alter database enable block change tracking using file
2 'C:/changetracking/chg01.dbf';

Database altered.
SQL>

The dynamic performance view V$BLOCK_CHANGE_TRACKING shows where block change tacking file is stored, whether it is enabled, and how large it is.

Please click the below link to read more about incremental backup in oracle10g. Click here

3. Dropping database through RMAN

We can drop the database through RMAN, it has additional benefits of dropping all backup copies and archived log files for the database if you include the INCLUDING BACKUPS clause.

RMAN> drop database including backups;

4. Automatic Channel Failover

If we are using multiple channels during an RMAN backup to either tape or disk and one of the channels fails, the backup job continues on the remaining channels. Any errors including channel errors are reported in the dynamic performance view V$RMAN_OUTPUT after the backup job is completed.

5. Duration, Throttling and Partial Backup Options

BACKUP......DURATION is a maximum duration or time frame for a backup operation, we can minimize the impact of the backup operation in a 24/7 environment. The new DURATION option, which replace RATE and READRATE in previous version of RMAN. For example, to spread out the backup of the USERS tablespace over a maximum of two hours, use the following command.

RMAN> BACKUP DURATION 2:00 TABLESPACE users;

By default, the backup will complete as fast as possible within the two-hour time frame.

BACKUP.....DURATION.....PARTIAL adding PARTIAL to the BACKUP command prevents an error message from being issued by RMAN in case the backup does not finish the operation in the specified amount of time. Any complete backup sets are available for recovery operations, and any partial or incomplete backup operations will have to be restarted.

RMAN> backup duration 2:00 partial
2> tablespace users
3> filesperset 1;

BACKUP.....DURATION.....MINIMIZE LOAD The MINIMIZE LOAD clause, used with disk backups only, automatically adjusts the throughput of the backup operation to complete the backup in the estimated completion time specified by DUATION.

RMAN> BACKUP DURATION 4:00 PARTIAL
2> MINIMIZE LOAD
3> DATABASE FILESPERSET 1;

BACKUP....DURATION....MINIMIZE TIME The MINIMIZE TIME clause, will complete the backup in the shortest possible time.

RMAN> backup duration 2:00 minimize time
2> tablespace users;

6. Creating Image copies

Oracle10g introduced new feature to create image copy of the data files. It is equivalent to conventional copy of oracle data files. Image copy is a bit-for-bit duplicate of a database file, identical to a copy made with an operating system command. When we take conventional backup, we need to put the tablespace in BEGIN BACKUP mode. But when we create image copy, it is not required. Another thing, when we take conventional backup, it will not be registered in RMAN catalog database. But RMAN image copy backup will be registered in catalog database.

What is the advantage of image copies? To answer this question, it would be useful for migrating the database to ASM. We can also use them to recover the database in non RMAN environment.

We have BACKUP AS COPY command in RMAN, which can copy entire database, multiple tablespaces, datafiles, archived log files.

RMAN> backup as copy database;
RMAN> backup as copy tablespace system, sysaux;
RMAN> backup as copy datafile 1;

RMAN automatically determines which datafiles belong to each tablespace and performs the image copy for each. In addition, the image copies generated by RMAN can be used directly in a recovery operation without using RMAN to extract a data file from previous backup set.

7. Full databaes backup

We may want to back up a previous backup to tape, rather than create a direct backup to tape, so as not to impact a database that is up and running. In RMAN, we can use the following command to backup a previous copy of the entire database to the default tape device.

RMAN> backup copy of database;

8. Compressed backup

This feature allows RMAN to perform binary compression of backupsets. The resulting backupsets do not need to be uncompressed during recovery. It is most useful in the following circumstances.

1. You are performing disk-based backup with limited disk space
2. You are performing backups across a network where network bandwidth is limiting
3. You are performing backups to tape, CD or DVD where hardware compression is not available

RMAN>BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
RMAN>BACKUP AS COMPRESSED BACKUPSET DATAFILE 1,5;

Compression requires additional CPU cycles which may affect the performance of the database. For this reason it should not be used for tape backups where hardware compression is available.

I have discussed some of the other topics which are related to this thread. Please go through below links in case if you are interested....

How do we enable archivelog in oracle? Click here

How do we set up RMAN in oracle? Click here

What is difference between traditional backup and RMAN backup? Click here

Different type of recovery scenario in RMAN? Click here

Instance Recovery

2009-05-25T11:09:00.001-07:00

Oracle performs instance recovery when the database is restarted due to instance failure or shutdown the database with ABORT option(shutdown abort) or startup the database with FORCE option(startup force). Instance recovery is taken care by SMON oracle background process automatically. Instance recovery consists of two steps. One is Roll forward, next is Roll backward.

Roll forward:

Changes being made to the database are recorded in the database buffer cache and buffer cache will be written into data files. At the same time, the changes are recorded in redo log buffer and redo log buffer will be written to redo log file. Oracle writes the data from database buffer cache to data file only when there is enough data in data buffer cache. It is not necessary that, every commit, oracle writes the data buffer cache into data file. When instance fails before committed data are written into data files, Oracle uses the data recorded in the online redo log files to recover the lost data when the associated database is re-started. This process is called Roll forward or cache recovery.

Roll backward:

When we make any changes in the database, the old image will be written into undo segment. Later undo segment will be used to rollback the data when we rollback the transaction. DBWR writes the buffer cache content into data file under different circumstances. It might be possible to write the uncommitted data from database buffer cache into data file. When instance fails and associated database is restarted, it rollback the uncommitted transaction in data file by using undo segment to maintain the read consistency. This is called roll forward or transaction recovery.

RMAN Recovery Scenarios

2009-05-21T17:24:00.000-07:00

Olden days, we use traditional backup(putting tablespace in hot backup mode) to backup the database. When we recover the database, we use traditional backup as a base and apply archive log files to recover the database. We have some issues when we dealt with traditional backup. Then oracle introduced RMAN backup to resolve some of the issues which we faced in traditional backup. Now you may ask, What are the issues in traditional backup and what are the convenience in RMAN backup? I discussed this in another thread.. Please see think link to get the answer.

In this thread, i am going to discuss about possible recovery scenario in RMAN. Before that, we need to setup the RMAN in oracle instance.. How do we set up RMAN in oracle instance? Here is the link to see how to setup RMAN in oracle.

Now we will discuss about different type of DB recovery scenarios.... The database name is ORCL. The catalog database name CATDB.... RMAN has setup and backup is running every day. The database is running in windows environment.

Scenario1

Kevin is DBA, he is working in Mission critical workshop. On Friday night, while taking RMAN backup, the machine is crashed and database is down. Kevin restarted the database and database is mounted successfully. But it is not opened. He realized that, some of the tablespaces are corrupted. Fortunately, redo log files and control files are intact. Kevin wanted to bring the DB as fast as possible.

SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'

SQL>
Here is the solution.... Since most of the tablespace datafiles are corrupted, he decided to do complete database recovery. First he mounted the database. He can not open the database until he completely recover the database.

Kevin connected the RMAN and recoverd the whole database as below.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 21:32:28 2009

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

connected to target database: ORCL (DBID=1215124933, not open)
connected to recovery catalog database

RMAN> run{
2> restore database;
3> recover database;
4> alter database open;
5> }

Starting restore at 21-MAY-09
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

creating datafile fno=5 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_0CKFJAS2_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_0CKFJAS2_1_1_%S_%P tag=TAG20090521T143649
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 21-MAY-09

Starting recover at 21-MAY-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:14

Finished recover at 21-MAY-09

database opened

RMAN>

Scenario2

Adita is DBA in banking firm. On Monday afternoon, due to media failure, one of the data file is corrupted. But all other data files are working fine. Anita restarted the database and one data file is complaining and DB is not restarted. Anita made the corrupted data file offine and opened the database. So users can use the DB while recoving the one data file.

SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF'
offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> select file_id from dba_data_files where file_name
2 ='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF';

FILE_ID
----------
5

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Here is the solution to recover the database..........

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 22:30:06 2009

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

connected to target database: ORCL (DBID=1215124933)
connected to recovery catalog database

RMAN> run
2> {
3> restore datafile 5;
4> recover datafile 5;
5> }

Starting restore at 21-MAY-09
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
creating datafile fno=5 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF
restore not done; all files readonly, offline, or already restored
Finished restore at 21-MAY-09

Starting recover at 21-MAY-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 21-MAY-09

RMAN> exit

Recovery Manager complete.

C:\>sqlplus sys/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 21 22:31:10 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF'
ONLINE;

Database altered.

SQL> select distinct status from dba_data_files;

STATUS
---------
AVAILABLE

SQL>

Scenario3

Scott is a DBA in trading firm. During the hard disk replacement in unix box, one of the application tablespace data files are corrupted. When Scott restart the database, it complained that, data file is corrupted and tablespace is not usable. But fortunately, other tablespaces datafiles are intact. Scott decided to make this tablespace offline and open the database. So that others can use the database while recovering the tablespace.

SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'
OFFLINE;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF'

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.\ORADATA\ORCL\USERS02.DBF'
OFFLINE;

Database altered.

SQL> alter database open;

Database altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

Scott made data files offline and opened the database. While others are using the database, he connected in RMAN and recovered only corrupted tablespace

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 22 09:36:52 2009

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

connected to target database: ORCL (DBID=1215124933)
connected to recovery catalog database

RMAN> run{
2> restore tablespace users;
3> recover tablespace users;
4> }

Starting restore at 22-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=128 devtype=DISK
creating datafile fno=5 name=C:\ORACLE\PRODUCT\10.2.\ORADATA\ORCL\USERS02.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_0CKFJA S2_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_0CKFJAS2_1_1_%S_%P tag=TAG20090521T143649
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 22-MAY-09
Starting recover at 22-MAY-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 12 is already on disk as file C:\ORACLE\PRODUCT\10
.2.0\DB_1\DATABASE\ARCHIVE\LOG_12_1_687344200.ARC
archive log thread 1 sequence 17 is already on disk as file C:\ORACLE\PRODUCT\10
.2.0\DB_1\DATABASE\ARCHIVE\LOG_17_1_687344200.ARC
archive log thread 1 sequence 18 is already on disk as file C:\ORACLE\PRODUCT\10
.2.0\DB_1\DATABASE\ARCHIVE\LOG_18_1_687344200.ARC
archive log thread 1 sequence 19 is already on disk as file C:\ORACLE\PRODUCT\10
.2.0\DB_1\DATABASE\ARCHIVE\LOG_19_1_687344200.ARC
media recovery complete, elapsed time: 00:01:23
Finished recover at 22-MAY-09

SQL> connect sys/password as sysdba
Connected.
SQL> alter database datafile 'c:\oracle\product\10.2.0\oradata\orcl\users01.dbf'
2 online;

Database altered.

SQL> alter database datafile 'c:\oracle\product\10.2.0\oradata\orcl\users02.dbf'
2 online;

Database altered.

SQL> connect scott/tiger@orcl
Connected.
SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL>

Scenario4

Jane is a DBA in telecommunication company. Unix admin had done some maintenance work on the server. Once they completed their work, Jane restarted the database. When Jane restart the DB, Jane realized that, SYSTEM tablespace is corrupted.. But others are intact. He decided to mount the database and recover this tablespace. This leads downtime for the database, since jane can not open the database without recovering the system tablespace.

SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
' OFFLINE;

Database altered.

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 22 10:21:04 2009

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

connected to target database: ORCL (DBID=1215124933, not open)
connected to recovery catalog database

RMAN> run
2> {
3> restore tablespace system;
4> recover tablespace system;
5> }

Starting restore at 22-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_0JKFLE
9Q_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_0JKFLE9Q_1_1_%S_%P tag=TAG20090522T094738
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 22-MAY-09
Starting recover at 22-MAY-09
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 22-MAY-09
RMAN> exit

Recovery Manager complete.

C:\>sqlplus sys/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 22 10:22:20 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' online;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.
SQL> select distinct status from dba_tablespaces;

STATUS
---------
ONLINE

SQL> select distinct status from dba_data_files;

STATUS
---------
AVAILABLE

SQL>

Scenario5

David is DBA for Energy company. David ended up restarting the database due to hardware maintenance. When David restarts the database, realized that all the redo log files are corrupted... RMAN backup runs every day at 11.30PM. David found this issue on 12.20PM. Now David needs to do incomplete recovery. Here is the steps David taken... Mount the database and run the RMAN backup. Just to make sure all the archived redo log files are backed up before start the recovery. Once RMAN backup is completed, David run the incomplete recovery and recovered until the last archived redo log.

SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'
SQL>exit
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 23 20:36:24 2009

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

connected to target database: ORCL (DBID=1215124933, not open)
connected to recovery catalog database

RMAN> backup archivelog all;

Starting backup at 23-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
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=2 recid=31 stamp=687541158
input archive log thread=1 sequence=3 recid=32 stamp=687542042
input archive log thread=1 sequence=4 recid=33 stamp=687542062
40 comment=NONE
Starting Control File and SPFILE Autobackup at 23-MAY-09
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215124933-20090523-0A comment=NONE
Finished Control File and SPFILE Autobackup at 23-MAY-09
RMAN> list backup of archivelog from time='sysdate-7';
List of Backup Sets
===================

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10609 8.10M DISK 00:00:02 23-MAY-09
BP Key: 10611 Status: AVAILABLE Compressed: NO Tag: TAG20090523T2235
40
Piece Name: C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\01KFPFLS_1_1

List of Archived Logs in backup set 10609
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 544522 23-MAY-09 550869 23-MAY-09
1 3 550869 23-MAY-09 550898 23-MAY-09

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10625 103.00K DISK 00:00:02 23-MAY-09
BP Key: 10630 Status: AVAILABLE Compressed: NO Tag: TAG20090523T2236
29
Piece Name: C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\03KFPFND_1_1

List of Archived Logs in backup set 10625
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 4 550898 23-MAY-09 550988 23-MAY-09

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10672 8.29M DISK 00:00:02 23-MAY-09
BP Key: 10676 Status: AVAILABLE Compressed: NO Tag: TAG20090523T2239
52
Piece Name: C:\RMANBACKUP\BACKUPORCL_DB_05KFPFTO_5_1

List of Archived Logs in backup set 10672
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 544522 23-MAY-09 550869 23-MAY-09
1 3 550869 23-MAY-09 550898 23-MAY-09
1 4 550898 23-MAY-09 550988 23-MAY-09
1 5 550988 23-MAY-09 551206 23-MAY-09

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10692 37.00K DISK 00:00:01 23-MAY-09
BP Key: 10697 Status: AVAILABLE Compressed: NO Tag: TAG20090523T2240
42
Piece Name: C:\RMANBACKUP\BACKUPORCL_DB_07KFPFVA_7_1

List of Archived Logs in backup set 10692
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 551206 23-MAY-09 551263 23-MAY-09

RMAN> restore database until sequence=6 thread=1 force;

Starting restore at 23-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\DB_1\DATA
BASE\02KFPFLV_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\02KFPFLV_1_1 tag=TAG20090523
T223542
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 23-MAY-09

RMAN> recover database until sequence=6 thread=1 ;

Starting recover at 23-MAY-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 4 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_687649381.ARC
archive log thread 1 sequence 5 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_687649381.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_6876
49381.ARC thread=1 sequence=4
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_6876
49381.ARC thread=1 sequence=5
media recovery complete, elapsed time: 00:00:03
Finished recover at 23-MAY-09

RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
RMAN>

The database is recovered successfully. But this is incomplete recovery. Prior to oracle10g, oracle strongly recommend to take the full database backup whenever there is incomplete recovery. But in oracle10g, it is optional. But still it is good to take the full database backup for safer side. Please read this link to know more about this...

Scenario6

Kathy is DBA for one of the finance company. On Monday, due to hardware issue, the server was crashed. Unfortunately, Kathy lost all data files, control files, redo log files. But luckily the current archived redo log files were intact. Here is the steps Kathy has taken to handle this crash recovery.

Kathy was not able to mount the database since she lost her control file. So she first recovered the control file from RMAN backup. Once control file is recovered, she mounted the database. After the database is mounted, she ran the RMAN backup to make sure, all the current archive log files are backed up. she recovered the data base until last archived log file. Finally she opened the the DB RESETLOGS option.

SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL>

C:\>set oracle_sid=orcl

C:\>rman catalog=rman/rman@catdb target=sys/password

Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:26:51 2009

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

connected to target database: orcl (not mounted)
connected to recovery catalog database

RMAN> restore controlfile;

Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\DB_1\DATA
BASE\C-1215492928-20090524-00
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215492928-20090524-00 tag
=TAG20090524T152409
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 24-MAY-09

RMAN>

Once the control file is recovered, the database is mounted.....

SQL> alter database mount;

Database altered.

SQL> select archivelog_change#-1 from v$database;

ARCHIVELOG_CHANGE#-1
--------------------
547010

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:29:33 2009

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

connected to target database: ORCL (DBID=1215492928, not open)
connected to recovery catalog database

RMAN> run{
2> set until scn 547010;
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }

executing command: SET until clause

Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_02KFRA
N7_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_02KFRAN7_1_1_%S_%P tag=TAG20090524T1523
19
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-MAY-09

Starting recover at 24-MAY-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_687712197.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_6877
12197.ARC thread=1 sequence=3
media recovery complete, elapsed time: 00:00:04
Finished recover at 24-MAY-09

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

Scenario7

Scott is the DBA for database. One of the DB progammer truncated the critical table around 3.45PM and he called Scott and explained the situation. Scott has to recover the truncated table. Here is the steps Scott followed....

SQL> select count(*) from employee;

COUNT(*)
----------
14

SQL> select to_char(sysdate,'DD-MM-YYYY:HH24:MI:SS')
2 from dual
3 /

TO_CHAR(SYSDATE,'DD
-------------------
24-05-2009:15:45:42

SQL> truncate table employee;

Table truncated.

SQL> select count(*) from employee;

COUNT(*)
----------
0

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL>

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:58:56 2009

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

connected to target database: ORCL (DBID=1215492928, not open)
connected to recovery catalog database

RMAN> run
2> {
3> set until time "to_date('24-05-2009:15:45:42','DD-MM-YYYY HH24:MI:SS')";
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_09KFRB
BR_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_09KFRBBR_1_1_%S_%P tag=TAG20090524T1534
19
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-MAY-09

Starting recover at 24-MAY-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 2 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_687713476.ARC
archive log thread 1 sequence 3 is already on disk as file C:\ORACLE\PRODUCT\10.
2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_687713476.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_6877
13476.ARC thread=1 sequence=2
media recovery complete, elapsed time: 00:00:05
Finished recover at 24-MAY-09

RMAN> exit

Recovery Manager complete.

C:\>sqlplus sys/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 24 16:01:31 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database open resetlogs;

Database altered.

SQL> connect scott/tiger@orcl
Connected.
SQL> select count(*) from employee;

COUNT(*)
----------
14

SQL>

I have discussed some of the other topics which are related to this thread. Please go through below links in case if you are interested....

How do we enable archivelog in oracle? Click here

What are the new features in RMAN since Oracle9i? Click here

Steps to Setup RMAN

2009-05-20T17:11:00.001-07:00

This article is tested in oracle10gR2. How do we setup the RMAN in oracle? There are couple of ways, we can setup the RMAN. We can use control file to store backup catalog info or we can have seperate database to store catalog info. Here i am using seperate database to store backup catalog information.

You might ask a question yourself, why would we need RMAN backup? Why do we need to setup RMAN, since my traditional backups are already running fine.... I already discussed this in another thread.. Please see this link to answer your question. Click

I am using windows OS. Please remember, the directories and folder might change based on the operating system and environment. But the below steps are pretty much same for any environmnet.

Here i am using ORCL as primary database and CATDB as catalog database.

Step1 Enable the archive log in ORCL database. I already discussed this in another thread. Please refer this link to enable the database to archive log mode.

Step2 Create the tablespace and user in catalog database to hold backup information.

SQL> CONNECT sys/password@catdb AS SYSDBA
Connected.

SQL> CREATE TABLESPACE RMAN
2 DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\RMAN01.DBF' SIZE 6208K REUSE
3 AUTOEXTEND ON NEXT 64K MAXSIZE 32767M
4 EXTENT MANAGEMENT LOCAL
5 SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> CREATE USER rman IDENTIFIED BY rman
2 TEMPORARY TABLESPACE temp
3 DEFAULT TABLESPACE rman
4 QUOTA UNLIMITED ON rman;

User created.

SQL> GRANT connect, resource, recovery_catalog_owner TO rman;

Grant succeeded.

SQL>

Step3 Create the recovery catalog in catalog database.

C:\>rman catalog=rman/rman@catdb

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 09:59:26 2009

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

connected to recovery catalog database

RMAN> create catalog tablespace "RMAN";

recovery catalog created

RMAN> exit

Recovery Manager complete.

C:\>

Step4 Register the database with Catalog database. Each database should be registered to catalog database to run RMAN backup.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 10:02:01 2009

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

connected to target database: ORCL (DBID=1215124933)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> exit

Recovery Manager complete.

C:\>

Step5 Configure the persistent parameters.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 19 18:46:40 2009

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

connected to target database: ORCL (DBID=1215054467)
connected to recovery catalog database

RMAN> configure retention policy to recovery window of 2 days;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure default device type to disk;

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure channel device type disk format 'C:\rmanbackup\Backup%d_DB_%U_%S
_%P';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\rmanbackup\Backup%d_DB_%U_%S_%P'
;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN>

Step 6 Take database full backup. The full database backup should be taken first time. Afterwards, archivelog backup will be taken.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 10:16:09 2009

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

connected to target database: ORCL (DBID=1215124933)
connected to recovery catalog database

RMAN> run{
2> backup database plus archivelog;
3> delete noprompt obsolete;
4> }

starting full resync of recovery catalog
full resync complete

Starting backup at 21-MAY-09
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
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=2 recid=1 stamp=687435222
input archive log thread=1 sequence=3 recid=2 stamp=687435237
input archive log thread=1 sequence=4 recid=3 stamp=687435270
input archive log thread=1 sequence=5 recid=4 stamp=687435279
input archive log thread=1 sequence=6 recid=5 stamp=687435420
channel ORA_DISK_1: starting piece 1 at 21-MAY-09
channel ORA_DISK_1: finished piece 1 at 21-MAY-09
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_01KFIRKU_1_1_%S_%P tag=TAG20090521T1017
01 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 21-MAY-09

Starting backup at 21-MAY-09
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=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DB
F
channel ORA_DISK_1: starting piece 1 at 21-MAY-09
channel ORA_DISK_1: finished piece 1 at 21-MAY-09
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_02KFIRLF_1_1_%S_%P tag=TAG20090521T1017
19 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 21-MAY-09

Starting backup at 21-MAY-09
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=7 recid=6 stamp=687435506
channel ORA_DISK_1: starting piece 1 at 21-MAY-09
channel ORA_DISK_1: finished piece 1 at 21-MAY-09
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_03KFIRNJ_1_1_%S_%P tag=TAG20090521T1018
27 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 21-MAY-09

Starting Control File and SPFILE Autobackup at 21-MAY-09
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215124933-20090521-00 com
ment=NONE
Finished Control File and SPFILE Autobackup at 21-MAY-09

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 2 days
using channel ORA_DISK_1
no obsolete backups found

RMAN> exit

Recovery Manager complete.

C:\>

Now the RMAN setup is completed successfully. Here are the info about RMAN.

Primary DB = ORCL
Catalog DB = CATDB
RMAN Backup location = c:\rmanbackup.

Now the full backup is taken. Every day, the below script should run and backup the new archive log files.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 10:25:40 2009

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

connected to target database: ORCL (DBID=1215124933)
connected to recovery catalog database

RMAN> run{
2> delete noprompt obsolete;
3> backup archivelog all;
4> }

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 2 days
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 devtype=DISK
no obsolete backups found

Starting backup at 21-MAY-09
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=2 recid=1 stamp=687435222
input archive log thread=1 sequence=3 recid=2 stamp=687435237
input archive log thread=1 sequence=4 recid=3 stamp=687435270
input archive log thread=1 sequence=5 recid=4 stamp=687435279
input archive log thread=1 sequence=6 recid=5 stamp=687435420
input archive log thread=1 sequence=7 recid=6 stamp=687435506
input archive log thread=1 sequence=8 recid=7 stamp=687435975
channel ORA_DISK_1: starting piece 1 at 21-MAY-09
channel ORA_DISK_1: finished piece 1 at 21-MAY-09
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_05KFIS68_1_1_%S_%P tag=TAG20090521T1026
15 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 21-MAY-09

Starting Control File and SPFILE Autobackup at 21-MAY-09
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215124933-20090521-01 com
ment=NONE
Finished Control File and SPFILE Autobackup at 21-MAY-09

RMAN> exit

Recovery Manager complete.

C:\>

How do we schedule the daily backup in Windows OS?

We need to write two script, one is batch file(named as daily_backup.bat) which should contain the following command.

rman catalog=rman/rman@catdb target=sys/password@orcl cmdfile daily_backup.sql

Another one is text file(named as daily_backup.sql) which should contain the following code.

run
{
backup archivelog all;
delete noprompt obsolete;
}

The batch file(daily_backup.bat) can be run manually, by double-clicking on it, or scheduled using the Scheduled Tasks Wizard (Start > Programs > Accessories > System Tools > Scheduled Tasks). The above two windows scripts are tested in windows environment and works well.

How do we schedule the daily backup in Unix?

Here we need to write shell script and schedule the shell script in unix scheduler(crontab).

The shell script content should be like this....... Please remember, the below shell script is not tested in unix environment. Please do test yourself in unix environment.... This is sample shell script...

export ORACLE_HOME=/usr/app/oracle/product/10.2.0
export ORACLE_SID=orcl
export ALIAS=orcl
cd =/usr/app/oracle/product/10.2.0/bin
rman catalog=rman/rman@catdb target=sys/password@orcl <<
run
{
backup archivelog all;
delete noprompt obsolete;
}
exit
EOF

The above shell script can be scheduled in crontab. To learn more about crontab, see this link.

If you want to read more on RMAN, Please click this link....

I have discussed some of the other topics which are related to this thread. Please go through below links in case if you are interested....

What are the new features in RMAN since Oracle9i? Click here

Different type of recovery scenario in RMAN? Click here

Benefits of RMAN Backup vs. Scripted Backups

2009-05-20T17:11:00.000-07:00

RMAN was introduced in oracle8i. Before oracle8i, we used traditional backup or scripted backup. Why did oracle move to RMAN backup? Why do we need to use RMAN to backup our database? We may already be doing online backup with some wonderfully crafted, home-grown scripts. We may ask this question ourself. Why should we start using RMAN when my scripted backups are working fine?

We are moving to RMAN backup for two reasons. One is, when backup scripts breaks, the database backup would fail. Another issue is, when script fails, some one has to fix the backup scripts.

There are number of other positive reason to move to RMAN backup. Here are these....

1. RMAN will detect corrupted blocks and report them to us. RMAN also can recover only corrupted blocks. This is called Block Media Recovery(BMR). In traditional recovery, we need to restore and recover the whole data file. But in RMAN, it is not required to restore and recover the entire data file for just few corrupted blocks.

2. RMAN can back up your database online without having to put the tablespace in hot backup mode. Thus, the additional redo generated during a hot backup is reduced.

3. RMAN will automatically track new datafiles and tablespaces for us. In scripted backup, we might need to change the backup script when new tablespace or data file is added in the database.

4. RMAN will only backup up used data blocks(up to the high water mark(HWM)). So RMAN backup images typically are smaller than those of traditional online back images.

5. RMAN provides easy, automated backup, restore and recovery operations. RMAN tracks all the backups needed to recover the database.

6. RMAN can work fairly seamlessly with third-party media management products.

7. RMAN supports incremental backup strategies.

8. With RMAN, we can actually test backups without restoring them. We can validate the backup and make sure backup will work at the time of restore.

9. If we use the repository, then RMAN provides a nice, centralized reporting facility.

I have discussed some of the other topics which are related to this thread. Please go through below links in case if you are interested....

How do we enable archivelog in oracle? Click here

How do we set up RMAN in oracle? Click here

What are the new features in RMAN since Oracle9i? Click here

Different type of recovery scenario in RMAN? Click here

Enable Archive log in Oracle10g

2009-05-19T06:25:00.002-07:00

How do we enable the archive log mode in oracle10g?

The below article is tested in oracle10g R2 version. Please remember, below steps directories and folders would change based on the environment and operating systems. Here the database name is ORCL.

Step 1 We need to build the pfile from spfile to add new entries. If you have already recent pfile, the you do not need to do this step.

Login as sys user and execute this to create pfile.

create pfile='C:\oracle\product\10.2.0\db_1\dbs\PFILE.ORA' from spfile;

Step 2 Once pfile is created, then edit the pfile and add the below two parameters.

log_archive_format=Log_%s_%t_%r.arc
log_archive_dest='C:\oracle\product\10.2.0\db_1\database\archive'

In case, if you already have the below two entries in the pfile, then we need to remove or comment this below two entires. Since we can not have this below two entry with above new two parameters.

db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'
db_recovery_file_dest_size=2147483648

Till oracle9i, we use log_archive_start=true in parameter file. Since from oracle10g, this parameter is deprecated. We should not add this entry in pfile from oracle10g. If we have this entry in oracle10g, we get the below error.

ORA-32004:obsolete and/or deprecated parameter specified.

Step 3 Once pfile is edited, then we need to create the spfile with modified pfile. Login as sys user and execute the below command.

create spfile = 'C:\oracle\product\10.2.0\db_1\dbs\XX.ORA' from pfile='C:\oracle\product\10.2.0\db_1\dbs\PFILE.ORA'

Step 4 Rename the original SPFILEORCL.ORA to different name. Then rename the XX.ORA to SPFILEORCL.ORA.

Step 5 Login as sys user and shutdown the database and follow the steps.

Mount the database
alter database archivelog
alter database open;

Here is the screen output....

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle\product\10.2.0\db_1\database\archive
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL>

I have discussed some of the other topics which are related to this thread. Please go through below links in case if you are interested....

How do we set up RMAN in oracle? Click here

What are the new features in RMAN since Oracle9i? Click here

What is difference between traditional backup and RMAN backup? Click here

Different type of recovery scenario in RMAN? Click here

Dropping database in oracle10g

2009-05-18T13:46:00.001-07:00

There are few ways, we can drop the database in oracle. This article is tested in oracle10gR2. Let us discuss these options here.

Option 1. We can use DBCA utility to drop the database. This approach will be a clean approach for dropping database. Since DBCA tool drop all the data files, control files, redolog files, spfile, password file and background dump trace files. This approach cleans up the tnsentry in tnsnames.ora file if we have tnsnames.ora file in server. It also deletes the entire folders relevant to that database. But this approach will not delete RMAN backup files, archived redo log files. If the database is running in RMAN and it is registered with catalog database, then DBCA utility will not unregister from catalog database automatically. So before we drop the database by using DBCA utility, we need to unregister the database by using RMAN UNREGISTER DATABASE command. If the database is using only control file for RMAN metadata, then this command is not necessary.

Option 2 In oracle10g, we have a command to drop the database. All the data files, control files, redo log files will be deleted automatically when we use drop database command in oracle10g. But it is not deleting the folders. It is not deleting dump files, alert log files, archived redo log files, RMAN backup files and password files relevant to the database. Also it is not cleaning the tnsentry in tnsnames.ora file if we have tnsnames.ora file in server. We need to clean these files and folders manually after we drop the database by using drop database command. If the database is running in RMAN with catalog database, we need to unregister the database manually from catalog before we drop the database. This option would not remove backup info in catalog DB.

Here are the steps to drop the database in oracle10g.

1. Connect as sys user
2. shutdown the database
3. Mount the database with restrict mode
4. Drop the database
5. Clean up the files manually(trace files, password file, folders, cleaning the tnsentry, archive redo log files, RMAN backup files)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict mount;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 155192196 bytes
Database Buffers 448790528 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

Option 3 When the database is running in RMAN, the database must be registered with catalog database. The above two options will not unregister the database from catalog DB. It will not remove the backup information from catalog database. If we use the above options, we need to unregister the database from catalog manually before we drop the database. Oracle10g introduced new feature that we can now use RMAN to drop a database and remove all its records from the RMAN CATALOG. Dropping database from RMAN has a additional benefit of dropping all backup copies and archived log files for the database. For further information pls also see Metalink Note: 251412.1

But still it is not deleting the dump files, alert log files, password file. It is not removing tnsentry in tnsnames.ora file if we have tnsnames.ora file in server. But it cleans data file, control file, redo log file, archive log file, RMAN backup files and removes backup info in CATALOG Database.

C:\>rman target / catalog rman/rman@catdb

Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 20 15:11:25 2009

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

connected to target database (not started)
connected to recovery catalog database

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes

RMAN> sql 'alter system enable restricted session';

sql statement: alter system enable restricted session

RMAN> drop database including backups;

database name is "ORCL" and DBID is 1215054467

Do you really want to drop all backups and the database (enter YES or NO)? YES

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
36 34 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_01KF
EGUK_1_1_%S_%P
37 35 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_02KF
EGUN_1_1_%S_%P
54 49 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_03KF
EGVS_1_1_%S_%P
64 62 1 1 AVAILABLE DISK C:\ORACLE\PRODUCT\10.2.0\DB_1\DA
TABASE\C-1215054467-20090519-00
133 130 1 1 AVAILABLE DISK C:\ORACLE\PRODUCT\10.2.0\DB_1\DA
TABASE\C-1215054467-20090519-01
261 255 1 1 AVAILABLE DISK C:\ORACLE\PRODUCT\10.2.0\DB_1\DA
TABASE\C-1215054467-20090519-02
328 324 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_07KF
EQ6L_1_1_%S_%P
329 325 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_08KF
EQ7P_1_1_%S_%P
356 350 1 1 AVAILABLE DISK C:\ORACLE\PRODUCT\10.2.0\DB_1\DA
TABASE\C-1215054467-20090519-03
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_01KFEGUK_1_1_%S_%P recid=1 stamp
=687293397
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_02KFEGUN_1_1_%S_%P recid=2 stamp
=687293400
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_03KFEGVS_1_1_%S_%P recid=3 stamp
=687293438
deleted backup piece
backup piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215054467-20090519
-00 recid=4 stamp=687293441
deleted backup piece
backup piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215054467-20090519
-01 recid=5 stamp=687297981
deleted backup piece
backup piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215054467-20090519
-02 recid=6 stamp=687301176
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_07KFEQ6L_1_1_%S_%P recid=7 stamp
=687302870
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_08KFEQ7P_1_1_%S_%P recid=8 stamp
=687302906
deleted backup piece
backup piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215054467-20090519
-03 recid=9 stamp=687302924
Deleted 9 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
26 1 3 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_3_1_687273734.ARC
33 1 4 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_4_1_687273734.ARC
103 1 5 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_5_1_687273734.ARC
104 1 6 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_6_1_687273734.ARC
128 1 7 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_7_1_687273734.ARC
129 1 8 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_8_1_687273734.ARC
164 1 9 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_9_1_687273734.ARC
165 1 10 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_10_1_687273734.ARC
166 1 11 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_11_1_687273734.ARC
167 1 12 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_12_1_687273734.ARC
249 1 13 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_13_1_687273734.ARC
287 1 1 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_1_1_687301150.ARC
288 1 2 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_2_1_687301150.ARC
289 1 3 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_3_1_687301150.ARC
290 1 4 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_4_1_687301150.ARC
319 1 5 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_5_1_687301150.ARC
384 1 6 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_6_1_687301150.ARC
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_6872
73734.ARC recid=1 stamp=687293394
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_6872
73734.ARC recid=2 stamp=687293436
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_6872
73734.ARC recid=3 stamp=687297267
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_6_1_6872
73734.ARC recid=4 stamp=687297285
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_7_1_6872
73734.ARC recid=5 stamp=687298507
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_8_1_6872
73734.ARC recid=6 stamp=687298515
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_9_1_6872
73734.ARC recid=7 stamp=687300567
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_10_1_687
273734.ARC recid=8 stamp=687300576
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_11_1_687
273734.ARC recid=9 stamp=687300608
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_12_1_687
273734.ARC recid=10 stamp=687300618
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_13_1_687
273734.ARC recid=11 stamp=687301152
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_1_1_6873
01150.ARC recid=12 stamp=687301663
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_6873
01150.ARC recid=13 stamp=687301679
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_6873
01150.ARC recid=14 stamp=687301699
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_6873
01150.ARC recid=15 stamp=687301706
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_6873
01150.ARC recid=16 stamp=687302866
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_6_1_6873
01150.ARC recid=17 stamp=687340879
Deleted 17 objects

database name is "ORCL" and DBID is 1215054467
database dropped

database name is "ORCL" and DBID is 1215054467
database unregistered from the recovery catalog

RMAN>

Conclusion: What is best choice to delete the database? Based on my experience, best choice would be, use DBCA to remove the Database. But before remove, unregister the database in CATALOG database if database is using RMAN with catalog.

In case, if you don't have DBCA, then we can go for removing DB in RMAN as below.

RMAN> drop database including backups;

After successfully dropped the database, we might need to remove dump files, password file manually.

Another option would be, using DROP DATABASE command. Again, you need to manually remove the files, backup info in CATALOG database.

Global Temporary tables

2009-05-14T08:59:00.000-07:00

During the application development, we use some staging table or temporary tables to store intermediate result which are complicated to complete in one pass. Normally we use regular table or PLSQL table(array) to capture the intermediate result. But oracle8i introduced global temporary table. We can use global temporary table instead of regular table.

GLOBAL TEMPORARY TABLE indicates that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table

Please remember, the below codes are tested in oracle10g R2.

What are the advantages of Global temporary table compared to regular table?

Let us call Global temporary table as GTT....

1. GTT does not generate REDO info by itself. But regular table does generate redo infomation. So it will be faster when we use GTT.

2. When we truncate the GTT, it truncates only session specific data. So others can still see their data. But regular table truncate will truncate the whole table.

3. Data in GTT is deleted when the session ends or commit/rollback the transaction. So GTT data is session specific. But regular table is not session specific. It is visible of all the session once the data are committed.

4. Index can be created on GTT. But scope of the index data is at the session level.

5. Views can be created against GTT and combination GTT and regular table

6. Like regular table, database triggers can be created on GTT

Restriction on Global temporary tables :

1. GTT can not be partitioned.

2. We can not create any Foreign key constraint on GTT

3. Parallel DML and parallel queries are not supported for temporary tables. Parallel hints are ignored

4. GTT cannot contain columns of nested table.

5. Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.

Creation of Global temporary tables

The session-specific rows in a global temporary table can be preserved for the whole session. The data will be deleted when we exit the session. The ON COMMIT PRESERVE ROWS clause indicates that the data should be deleted at the end of the session.

scott@orcl> CREATE GLOBAL TEMPORARY TABLE gtt(
2 column1 NUMBER,
3 column2 NUMBER
4 ) ON COMMIT PRESERVE ROWS;

Table created.

scott@orcl> insert into gtt values(1,1);

1 row created.

scott@orcl> commit;

Commit complete.

scott@orcl> select * from gtt;

COLUMN1 COLUMN2
---------- ----------
1 1

scott@orcl> connect scott/tiger@orcl
Connected.
scott@orcl> select * from gtt;

no rows selected

scott@orcl>

The transaction-specific rows in a global temporary table can be preserved until we commit or rollback the transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted when we commit or rollback the transaction.

scott@orcl> CREATE GLOBAL TEMPORARY TABLE gtt(
2 column1 NUMBER,
3 column2 NUMBER
4 ) ON COMMIT DELETE ROWS;

Table created.

scott@orcl> INSERT INTO gtt values(1,1);

1 row created.

scott@orcl> commit;

Commit complete.

scott@orcl> select * from gtt;

no rows selected

scott@orcl>

Constraint stats

2009-05-13T18:26:00.000-07:00

Table constraint can be enabled or disabled by using CREATE TABLE or ALTER TABLE statement. In addition, the VALIDATE or NOVALIDATE keywords can be used to alter the action of the state. This is introduced in oracle8i.

Here are the four type of constraint stats. These four constraint stats are applicable for all type of constraints(primary key, foreign key, check etc).

1. ENABLE VALIDATE
2. ENABLE NOVALIDATE
3. DISABLE VALIDATE
4. DISABLE NOVALIDATE

ENABLE VALIDATE is same as ENABLE. Constraint validate the data as soon as we entered in the table.

scott@ordb> CREATE TABLE MASTER(id NUMBER(10));

Table created.

scott@ordb> CREATE TABLE child(id NUMBER(10), master_id NUMBER(10));

Table created.

scott@ordb> ALTER TABLE master ADD PRIMARY KEY (id);

Table altered.

scott@ordb> ALTER TABLE child ADD CONSTRAINT fk_master
2 FOREIGN KEY (master_id)
3 REFERENCES master
4 ENABLE VALIDATE;

Table altered.

scott@ordb> INSERT INTO master VALUES(1);

1 row created.

scott@ordb> INSERT INTO child VALUES(12,12);
INSERT INTO child VALUES(12,12)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_MASTER) violated - parent key not
found

scott@ordb> COMMIT;

Commit complete.

scott@ordb>

ENABLE NOVALIDATE is not same as ENABLE. Constraint validates the new data or modified data. It would not validate the existing data in table.

scott@ordb> insert into master values(1);

1 row created.

scott@ordb> alter table child disable constraint fk_master;

Table altered.

scott@ordb> INSERT INTO child VALUES(12,12);

1 row created.

scott@ordb> commit;

Commit complete.

scott@ordb> alter table child enable constraint fk_master;
alter table child enable constraint fk_master
*
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_MASTER) - parent keys not found

scott@ordb> ALTER TABLE CHILD MODIFY CONSTRAINTS FK_MASTER ENABLE NOVALIDATE;

Table altered.

scott@ordb> INSERT INTO child VALUES(12,12);
INSERT INTO child VALUES(12,12)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_MASTER) violated - parent key not
found

scott@ordb> INSERT INTO child VALUES(1,1);

1 row created.

scott@ordb> commit;

Commit complete.

scott@ordb> select * from master;

ID
----------
1

scott@ordb> select * from child;

ID MASTER_ID
---------- ----------
1 1
12 12

scott@ordb>

DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked so data may violate the constraint.

scott@ordb> ALTER TABLE CHILD MODIFY CONSTRAINTS FK_MASTER DISABLE NOVALIDATE;

Table altered.

scott@ordb> INSERT INTO CHILD VALUES(88,88);

1 row created.

scott@ordb> COMMIT;

Commit complete.

scott@ordb> select * from master;

ID
----------
1

scott@ordb> select * from child;

ID MASTER_ID
---------- ----------
12 12
1 1
88 88

scott@ordb>

DISABLE VALIDATE means the constraint is not checked but disallows any modification of the constrained columns.

scott@ordb> ALTER TABLE CHILD MODIFY CONSTRAINTS FK_MASTER DISABLE VALIDATE;

Table altered.

scott@ordb> insert into master values(1);

1 row created.

scott@ordb> insert into child values(1,1);
insert into child values(1,1)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.FK_MASTER)
disabled and validated

scott@ordb>

Note : Couple of things needs to be noted down here.

1. Converting NOVALIDATE constraint to VALIDATE would take longer time, depends on how big the data in the table. Although conversion in the other direction is not an issue

2. Disabling primary key constraint will drop the index associated with primary key. Again, when we enable the primary key constraint, it will create the index on the primary key column.

What is the ideal place to use ENABLE NOVALIDATE option?

In a busy environment, some one disabled the constraint accidently or intentionally, and we have already bad data in that table. Now business requested you to load the new set of data, but business wanted to make sure that new set of data should be validated during the load. At this circumstances, we can use ENABLE NOVALIDATE option. This option will validate the new data and old data will not be validated.

What is the ideal place to use DISABLE VALIDATE option?

We disabled the constraint for some reason. We do not want to load any data until we fix the issue and enable the constraint. We can use DISABLE VALIDATE option here. This option would not let you load any data when the constraint is disabled.

Deferred Constraints

2009-05-11T16:49:00.000-07:00

Prior to Oracle8i, no way of entering child records without parent records, inserting duplicate records on primary key/unique key columns, deleting parent record before deleting child records. When we enter this kind of orphan records or bad records, it would fail right away and rollback the current transaction. Let us say, we entered records in parent table and entering records in child table which is not existing in parent table. Prior to oracle8i, it will fail and rollback the orphan records in child table. But parent table data will not be rolledback. This would lead into incomplete or partial data load.

To avoid all these issues, oracle8i introduced deferred constraints. Deferred constraint will let you load the data in reverse order(child first, parent next) and it will validate the data only at the time of commit. The data will be validated at the time of commit and rollback both parent and child at the same time if there is any orphan records. When we reload the same data again, we do not need to manually clean the partial data which we loaded previous time in the table.

For instance, we have parent table called MASTER and ten different child tables. Child tables are named as CHILD1,CHILD2,CHILD3... CHILD10. First we enter data in MASTER table and entering all the child tables. We are applying commit at the end of data load. Somehow, we entered orphan records in CHILD3 and CHILD7 tables. In regular non deferred constraints, data will be successfully loaded in all the tables except CHILD3 and CHILD7 tables. If we use deferred constraint, all the 11 tables(master and all child) transaction will be rolledback. There will not be any partial data load when we use deferred constraints.

Another thing, the loading time will be faster when we use deferred constraint. Because, it does not need to validate the constraint at the time of loading.

By default constraints are created as NON DEFERRABLE but this can be overidden using the DEFERRABLE keyword. If a constraint is created with the DEFERRABLE keyword, then it can act in one of two ways (INITIALLY IMMEDIATE, INITIALLY DEFERRED).

INITIALLY IMMEDIATE is same as non deferred regular constraint.
INITIALLY DEFERRED is deferred constraint which will validate at the time of commit.

The below cases are tested in oracle10gR2.

Case1. Let us create the deferred FK constraint and enter the orphan records. Deferred constraint validate at the time of commit and rollback both parent and child.

scott@orcl> CREATE TABLE MASTER(id NUMBER(10));

Table created.

scott@orcl> CREATE TABLE child(id NUMBER(10), master_id NUMBER(10));

Table created.

scott@orcl> ALTER TABLE master ADD PRIMARY KEY (id);

Table altered.

scott@orcl> ALTER TABLE child ADD CONSTRAINT fk_master
2 FOREIGN KEY (master_id)
3 REFERENCES master(id)
4 DEFERRABLE
5 INITIALLY DEFERRED;

Table altered.

scott@orcl> INSERT INTO child VALUES(1,2);

1 row created.

scott@orcl> INSERT INTO master VALUES(1);

1 row created.

scott@orcl> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.FK_MASTER) violated - parent key not
found

scott@orcl> select * from master;

no rows selected

scott@orcl> select * from child;

no rows selected

scott@orcl>

Case2. Let us create the deferred FK constraint and delete the parent without deleting child records. At the time of commit, it rollbacks the transaction in child table.

scott@orcl> CREATE TABLE MASTER(id NUMBER(10));

Table created.

scott@orcl> CREATE TABLE child(id NUMBER(10), master_id NUMBER(10));

Table created.

scott@orcl> ALTER TABLE master ADD PRIMARY KEY (id);

Table altered.

scott@orcl> ALTER TABLE child ADD CONSTRAINT fk_master
2 FOREIGN KEY (master_id)
3 REFERENCES master(id)
4 DEFERRABLE
5 INITIALLY DEFERRED;

Table altered.

scott@orcl> INSERT INTO child VALUES(1,1);

1 row created.

scott@orcl> INSERT INTO master VALUES(1);

1 row created.

scott@orcl> COMMIT;

Commit complete.

scott@orcl> delete master;

1 row deleted.

scott@orcl> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (SCOTT.FK_MASTER) violated - child record found

scott@orcl> select * from master;

ID
----------
1

scott@orcl> select * from child;

ID MASTER_ID
---------- ----------
1 1

scott@orcl>

Case3. Let us create deferred primary key and enter the duplicate records.

scott@orcl> create table master(no number);

Table created.

scott@orcl> alter table master add constraint pk_master
2 primary key(no)
3 deferrable
4 initially deferred;

Table altered.

scott@orcl> insert into master values(1);

1 row created.

scott@orcl> insert into master values(1);

1 row created.

scott@orcl> insert into master values(2);

1 row created.

scott@orcl> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SCOTT.PK_MASTER) violated

scott@orcl> select * from master;

no rows selected

scott@orcl>

How do we change the constraint from deferred to regular and vice versa?

We can change the regular constraint to deferred and deferred constraint to regular constraint at the session level as long as the constraint is deferrable.

The below command will change the constraints to deferred/immediate for all the deferrable
constraint at the session level.

ALTER SESSION SET CONSTRAINTS = DEFERRED;
ALTER SESSION SET CONSTRAINTS = IMMEDIATE;

We can also change the specfic constraint to deferred/immediate.

set constraint fk_master deferred;
set constraint fk_master immediate;

What is ideal place to use deferred constraint?

When we have large volume of transaction involving multiple dependencies, we have a constraint that either load all the dependent tables successfully or rollback all the dependent tables if there is any orphan records. This would be ideal place to use deferred constraints.

Reverse key Index

2009-05-10T08:03:00.000-07:00

Oracle introduced reverse key index to reduce the block contention(buffer busy waits) in index segment. Normal index stores the index column value and rowid in index segment. But reverse key index stores the reverse index column value and rowid. Ofcourse, oracle will not reverse the rowid. So we call this as reverse key index.

Let us say, the index column value is 1234. It will be converted as 4321 and stores in index segment. 1235 will be converted as 5321 and stored in index segment. In reverse key index, oracle will not store the actual column values in sequentail order. Instead, it reverse the index column value and will be spreaded across many index blocks. This would avoid index block contention.

Reverse key index would be useful when index column is populating from sequence and concurrent session inserting the data on the table.

What is ideal place to use Reverse key index?

1. In a single instance system, multiple sessions are trying to insert/update the index column at the same time, the index column value is extracting from sequence. Also we are deleting the rows and we are not doing any range scan on reverse key index column. This would be ideal place for reverse key index.

2. In RAC environment, if we have column populated by an increasing numbers, concurrent session inserting the rows from different RAC instance, the index block will have contention between nodes. The data are deleted time to time according to some rules which leave some old data undeleted in the table. Also there is not much range scan on this table. This would be ideal place for reverse key index.

If we use regular index for above two scenario, during the concurrent insert, oracle stores the index values in the sequence order and it would end up storing multiple sequential index values in the same block. This would lead into block contention when multiple insert happens at the same time with in the same block. Another thing, when we delete the old rows, the block will not be moved to free list until all rows are deleted in that block.

Reverse key index will resolve the above said issues.

When we use reverse key index, the empty space(empty space would happen when we delete old rows in the table) in the block will be refilled. Because, reverse key index stores the column value in reverse order. You will be able to use the empty space for different values with reverse key indexes.

When we use reverse key index, index block contention will be reduced. The reverse key index will scatter the entries accross different blocks during inserting and your many concurrent sessions will not have index block contention issues.

How do we create reverse key index?

scott@orcl> create index idx_rev
2 on employee(empno) reverse;

Index created.

scott@orcl>

How do we covert regular index to reverse key index?

scott@orcl> create index idx_rev on employee(empno);

Index created.

scott@orcl> alter index idx_rev rebuild reverse;

Index altered.

scott@orcl>

How do we covert reverse key index to regular index?

scott@ordb> alter index idx_rev rebuild noreverse;

Index altered.

scott@ordb>

Restriction on Reverse key index?

Reverse key index does not support range scan. Since the index column values are not stored in the sequential order.

Resumable space allocation

2009-05-08T21:35:00.000-07:00

Long running operations or batch process would fail due to insufficient space. When job fails, DBA has to allocate the space and restart the job manually. When DBA restarts the job, oracle has to run the job from the scratch again. Oracle9i introduced Resumable space allocation to resolve this kind of issues.

With this resumable space allocation feature, whenever job fails, it will be suspended until DBA fix the issues and job will be resumed instead of restarting from scratch.

In oracle9i, resumable space allocation feature must be turned on in the session level using the ALTER SESSION ENABLE RESUMABLE statement. In oracle10g, a new parameter RESUMABLE_TIMEOUT was introduced, where the resumable space allocation feature can be turned on at the database level. The default for this parameter is 0, which means the resumable timeout is not enabled.

Steps to setup Resumable space allocation:

Let us assume, we are enabling resumable job allocation for scott user.

Step1.

We need to grant RESUMABLE system privileges to schema.

GRANT RESUMABLE TO SCOTT;

Step2.

We need to setup the resumable timeout in seconds. The operation will be suspended for specified number of seconds for DBA to fix the problem.

There are several ways we can set timeout parameter.

ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;

EXECUTE Dbms_Resumable.Set_Timeout(3600);

ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'emp data loading';

We can set RESUMABLE_TIMEOUT parameter in init parameter file. This new parameter was introduced in oracle10g. Resumable space allocation feature will be disabled automatically when RESUMABLE_TIMEOUT parameter is zero. We can set this parameter in session level as well as database level.

alter session set resumable_timeout=3600;
alter system set resumable_timeout=3600;

What kind of issues can be suspended in resumable space allocation?

Out of space:

We have an out of space error when we can not get any more extents for various database objects in a tablespace.

Maximum extents reached:

Number of extents are exceeded the maximum extents which we specified for the object.

Space Quota Exceeded:

User exceeds the space quota for the tablespace. when we create the user, we set the space quota. Operation will be suspended when user exceeds the space quota on tablespace.

What kind of operations can fall into resumable space allocation?

1. Queries that run out of temporary space for sorting.
2. DML Insert, update statement
3. DDL Create table as select, alter table, create index, alter index,
create materialized view, create materialized view log etc
4. Import/export
5. SQL Loader

Let us test resumable space allocation on scott schema. The below PLSQL code runs without resumable space allocation. When we run this code, it is failed right away....

scott@orcl> alter session set resumable_timeout=0;

Session altered.

scott@orcl> begin
2 for i in 1 .. 5000
3 loop
4 insert into employee
5 select * from employee;
6 commit;
7 end loop;
8 end;
9 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.EMPLOYEE by 8 in tablespace USERSPACE
ORA-06512: at line 4

scott@orcl>

Let us test the same code with resumable space allocation. Now i set the timeout parmeter 60 seconds. The session suspends for 60 seconds and throw error. Since we did not fix the error during the 60 seconds.

scott@orcl> alter session set resumable_timeout=60;

Session altered.

scott@orcl> set time on
11:37:24 scott@orcl>
11:37:24 scott@orcl> begin
11:37:24 2 for i in 1 .. 5000
11:37:24 3 loop
11:37:24 4 insert into employee
11:37:24 5 select * from employee;
11:37:24 6 commit;
11:37:24 7 end loop;
11:37:24 8 end;
11:37:24 9 /
begin
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01653: unable to extend table SCOTT.EMPLOYEE by 8 in tablespace USERSPACE
ORA-06512: at line 4

11:38:26 scott@orcl>

Let us test the same PLSQL code with higher timeout seconds. Let me set 1000 seconds.

scott@orcl> ALTER SESSION ENABLE RESUMABLE TIMEOUT 1000 NAME 'emp data loading';

Session altered.

scott@orcl> begin
2 for i in 1 .. 10
3 loop
4 insert into employee
5 select * from employee;
6 commit;
7 end loop;
8 end;
9 /

The above PLSQL procedure is hanging... Let me connect another session and check the status.

scott@orcl> connect scott/tiger@orcl
Connected.
system@orcl> select name,sql_text,status,error_msg from dba_resumable;

NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
STATUS
---------
ERROR_MSG
--------------------------------------------------------------------------------
emp data loading
INSERT INTO EMPLOYEE SELECT * FROM EMPLOYEE
SUSPENDED
ORA-01653: unable to extend table SCOTT.EMPLOYEE by 8 in tablespace USERSPACE

scott@orcl>

It seems like, the session is suspended and waiting the space to load the data. Let me connect in another session and increase the space for the tablespace.

system@orcl> connect system/password@orcl
Connected.
system@orcl> alter database datafile
2 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERSPACE.DBF' resize 1000M;

Database altered.

system@orcl>

Again i will check the status of this job.

scott@orcl> connect scott/tiger@orcl
Connected.
scott@orcl> select name,sql_text,status,error_msg from dba_resumable;

NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
STATUS
---------
ERROR_MSG
--------------------------------------------------------------------------------
User SCOTT(57), Session 132, Instance 1
INSERT INTO EMPLOYEE SELECT * FROM EMPLOYEE
NORMAL

scott@orcl>

As per the above query, the status is back to normal. It means, the job is resumed. The error message is disappeared.

Again i went back and checked the job. The job is also successfully completed after increasing the space.

scott@orcl> begin
2 for i in 1 .. 10
3 loop
4 insert into employee
5 select * from employee;
6 commit;
7 end loop;
8 end;
9 /

PL/SQL procedure successfully completed.

scott@orcl>

Usage of AFTER SUSPEND TRIGGER:

It is impossible to check the suspended operations manually every time. Also suspended operation will not produce any error message. We can write the AFTER SUSPEND trigger to capture the suspended operations as well as error message. In AFTER SUSPEND trigger, we can write a code by using DBMS_SMTP package to notify DBA about suspended operations. We can also write the error message in log table by using autonomous transactions.

Here is the sample AFTER SUSPEND trigger skeleton.

CREATE OR REPLACE TRIGGER suspended_operations
AFTER SUSPEND
ON DATABASE
DECLARE
-- Declare any variables
BEGIN
-- Alter default timeout period.
Dbms_Resumable.Set_Timeout(3600);
-- Capture the error message into log table
-- Send email/page to DBA to make corrective action
END;
/

How do we see which operations are suspended in the database?

a) We can check the alert log
b) We can view USER_RESUMABLE or DBA_RESUMABLE

Resumable space parameters in imp/exp, SQL Loader:

We can use these below parameters to suspend the (SQL Loader, imp/exp) data load if there is space failure.

RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE

DBMS_RESUMABLE Package: This package is created for managing resumable space. SYS user should grant execute privileges to other schema to use this package.

ABORT(sessionID) - Ends the specified suspended session. Caller must be the owner of the session with sessionID, have ALTER SYSTEM privilege, or have DBA privileges.

GET_SESSION_TIMEOUT(sessionID) - Returns the timeout period in seconds for the specified session, or -1 if the session does not exist.

SET_SESSION_TIMEOUT(sessionID, timeout) - Sets the timeout in seconds of the specified session with immediate effect.

GET_TIMEOUT() - Returns the timeout period in seconds for the current session.

SET_TIMEOUT(timeout) - Sets the timeout in seconds of the current session with immediate effect.

DBMS_RESUMABLE.SPACE_ERROR_INFO(...) - Returns information about the space error when called from within an AFTER SUSPEND trigger.

Note : We use the below command to enable or disable resumable space allocation in oracle9i.

alter session enable resumable
alter session disable resumable

But in oracle10g, we do not need to use these command to enable or disable resumable allocation. Oracle10g introduced new parameter RESUMABLE_TIMEOUT, we use this parameter to enable or disable resumable space allocation.

alter session set resumable_timeout = 0 (Disable resumable space allocation)
alter session set resumable_timeout = 60 (enable resumable space allocation)

Flash Back Query

2009-05-06T06:09:00.000-07:00

Oracle9i introduced Flashback query feature. Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. We can use SCN or TIMESTAMP to read the past data. Oracle10g went one step further and introduced two new features on top of flashback query feature. Here are the use of flash back query feature......

1. Recover the lost data or undoing incorrect commit.
2. Comparing the current data with corresponding data in the past.

Let us talk about below three topics on this article. The below code in this thread is successfully tested in oracle10gR2.

1. Flashback query(Oracle9i feature)
2. Flashback version query(Oracle10g feature)
3. Flashback transaction query(Oracle10g feature)

Prerequisite to use Flash back query freature..

1. Automatic UNDO managment should be enabled. The following init parameter should be set.

UNDO_MANAGEMENT = AUTO
UNDO_TABLESPACE = undotablespace_name
UNDO_RETENTION = n

2. We need to grant below privilege to schema where we are using this feature. Let us assume, we are using scott schema.

grant execute on dbms_flashback to scott;

Flashback Query(Oracle9i feature)

Flashback query can be enabled or disabled by using dbms_flashback package. We can flashback by using SCN or specific time. Flashback query feature will allow users to see the data on specific time or SCN in the past.

Scenario 1

Let us use the timestamp and display the past data...

scott@orcl> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

scott@orcl> create table flashback as select * from user_objects
2 where rownum <6;

Table created.

scott@orcl> select object_name from flashback;

OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
CUSTOMERS_SEQ
DEPT
DEPT1

10:58:59 scott@orcl> update flashback set object_name=lower(object_name);

5 rows updated.

10:59:14 scott@orcl> commit;

Commit complete.

10:59:17 scott@orcl> select object_name from flashback;

OBJECT_NAME
--------------------------------------------------------------------------------
auditlog
audit_seq
customers_seq
dept
dept1

scott@orcl> SELECT OBJECT_NAME from flashback AS OF
2 TIMESTAMP TO_TIMESTAMP('06-MAY-2009 10:58:26');

OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
CUSTOMERS_SEQ
DEPT
DEPT1

scott@orcl>

Scenario 2

Recovering data based on specific SCN in the past...

scott@orcl> select current_scn from v$database;

CURRENT_SCN
-----------
19215382

scott@orcl> create table flashback as select * from user_objects
2 where rownum <6;

Table created.

scott@orcl> select current_scn from v$database;

CURRENT_SCN
-----------
19215408

scott@orcl> select * from flashback;

OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
BIN$IDl5ashFQMqE8ctrWU8K8Q==$0
BIN$dld0GhwqRlO4sMnrYyAKgQ==$0
CUSTOMERS_SEQ

scott@orcl> update flashback set object_name=lower(object_name);

5 rows updated.

scott@orcl> commit;

Commit complete.

scott@orcl> select * from flashback as of scn 19215382;
select * from flashback as of scn 19215382
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

scott@orcl> select * from flashback as of scn 19215408;

OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
BIN$IDl5ashFQMqE8ctrWU8K8Q==$0
BIN$dld0GhwqRlO4sMnrYyAKgQ==$0
CUSTOMERS_SEQ

scott@orcl> select * from flashback;

OBJECT_NAME
--------------------------------------------------------------------------------
auditlog
audit_seq
bin$idl5ashfqmqe8ctrwu8k8q==$0
bin$dld0ghwqrlo4smnryyakgq==$0
customers_seq

scott@orcl>

Note : when we enable flashback query, we provide either a timestamp or SCN. Timestamp is mapped to an SCN number every five minutes, the SCN offers a much finer level of precision for flashback.

Scenario 3

We can use the explicit cursor to read the past data by using flashback query feature.

scott@orcl> create table flashback as select * from user_objects
2 where rownum <11;

Table created.

scott@orcl> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN
-----------
TO_CHAR(SYSTIMESTAMP,'YYYY-MM-
---------------------------------------------------------------------------
19218732
2009-05-06 12:15:58

scott@orcl>

11:36:52 scott@orcl> delete flashback;

10 rows deleted.

11:37:10 scott@orcl> commit;

Commit complete.

11:37:11 scott@orcl>

I deleted the flashback table around 11.37AM. Now i wanted to revert the data through PLSQL cursor by using SCN or TIMESTAMP.

The below example reading through Timestamp.

scott@orcl> declare
cursor c1 is select * from flashback;
emprec c1%rowtype;
begin
dbms_flashback.enable_at_time('06-MAY-2009 11:36:52');
OPEN C1;
DBMS_FLASHBACK.DISABLE;
LOOP
FETCH c1 into emprec;
exit when c1%notfound;
insert into flashback(OBJECT_NAME)
values(emprec.object_name);
end loop;
close c1;
commit;
end;
/

PL/SQL procedure successfully completed.

scott@orcl> SELECT * FROM FLASHBACK;

OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
BIN$EkjOJm2AS66HXzkrAueJiA==$0
BIN$IDl5ashFQMqE8ctrWU8K8Q==$0
BIN$YUw9dPZ5SueBF5RDCMLJIQ==$0
BIN$dld0GhwqRlO4sMnrYyAKgQ==$0
CUSTOMERS_SEQ
DEPT
DEPT1
DO_SOMETHING

10 rows selected.

scott@orcl>

The below example reading flashback data through SCN.

scott@orcl> declare
cursor c1 is select * from flashback;
emprec c1%rowtype;
begin
dbms_flashback.Enable_At_System_Change_Number(19218732);
OPEN C1;
DBMS_FLASHBACK.DISABLE;
LOOP
FETCH c1 into emprec;
exit when c1%notfound;
insert into flashback(OBJECT_NAME)
values(emprec.object_name);
end loop;
close c1;
commit;
end;
/

PL/SQL procedure successfully completed.

scott@orcl> select * from flashback;

OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
BIN$EkjOJm2AS66HXzkrAueJiA==$0
BIN$IDl5ashFQMqE8ctrWU8K8Q==$0
BIN$YUw9dPZ5SueBF5RDCMLJIQ==$0
BIN$dld0GhwqRlO4sMnrYyAKgQ==$0
CUSTOMERS_SEQ
DEPT
DEPT1
DO_SOMETHING

10 rows selected.

scott@orcl>

Note: We can not do any operation in the database when database is in Flashback mode.

scott@orcl> declare
cursor c1 is select * from flashback;
emprec c1%rowtype;
begin
dbms_flashback.Enable_At_System_Change_Number(19218732);
OPEN C1;
--DBMS_FLASHBACK.DISABLE;
LOOP
FETCH c1 into emprec;
exit when c1%notfound;
insert into flashback(OBJECT_NAME)
values(emprec.object_name);
end loop;
close c1;
commit;
end;
/
declare
*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode
ORA-06512: at line 11

So far, we have seen oracle9i features. Oracle9i has ability to read the past data by using SCN or timestamp. But oracle10g has gone one step further and has ability to read the past data for specific time window. This feature is called as flashback version query. Also oracle10g can get extra information about the transactions listed by flashback version queries.

As a summary, oracle10g introduced two new features on top of flashback query (which is introduced in oracle9i).

1. Flashback version query
2. Flashback transaction query

Flashback Version query (Oracle10g feature)
Flashback version query display the past data on specific time window. This feature helps to find the detail level data changes during that window. Here i created table and apply three transaction. At the end, we are able to display all the changes made by the transaction during that time period.

scott@orcl> create table flashversion(name varchar2(20));

Table created.
scott@orcl> SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

TO_CHAR(SYSTIMESTAMP,'YYYY-MM-
---------------------------------------------------------------------------
2009-05-08 09:07:59

scott@orcl> insert into flashversion values('SCOTT');

1 row created.

scott@orcl> commit;

Commit complete.

scott@orcl> update flashversion set name='MANI';

1 row updated.

scott@orcl> commit;

Commit complete.

scott@orcl> update flashversion set name='JANI';

1 row updated.

scott@orcl> commit;

Commit complete.

scott@orcl> SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

TO_CHAR(SYSTIMESTAMP,'YYYY-MM-
---------------------------------------------------------------------------
2009-05-08 09:09:22

scott@orcl> SELECT versions_starttime,
2 name
3 FROM flashversion
4 VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2009-05-08 09:07:59', 'YYYY-MM-DD HH24:MI:SS')
5 AND TO_TIMESTAMP('2009-05-08 09:09:22', 'YYYY-MM-DD HH24:MI:SS');

VERSIONS_STARTTIME
---------------------------------------------------------------------------
NAME
--------------------
08-MAY-09 09.09.15 AM
JANI

08-MAY-09 09.08.41 AM
MANI

08-MAY-09 09.08.07 AM
SCOTT

We can use the below flashback pseudo columns with above query.

VERSIONS_STARTSCN => Starting SCN when row took on this value
VERSIONS_STARTTIME => Starting TIMESTAMP when row took on this value

Note : The value of NULL is returned if the row was created before the lower bound SCN or TIMESTAMP.

VERSIONS_ENDSCN => Ending SCN when row last contained this value
VERSIONS_ENDTIME => Ending TIMESTAMP when row last contained this value

Note : The value of NULL is returned if the value of the row is still current at the upper bound SCN or TIMESTAMP.

VERSIONS_XID => ID of the transaction that created the row in it's current state.
VERSIONS_OPERATION => Operation performed by the transaction ((I)nsert, (U)pdate or (D)elete)

Here is the query with all the pseudo column

SELECT
versions_startscn,
versions_starttime,
versions_endscn,
versions_endtime,
versions_xid,
versions_operation,
name
FROM flashversion
VERSIONS BETWEEN
TIMESTAMP TO_TIMESTAMP('2009-05-08 09:07:59', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2009-05-08 09:09:22', 'YYYY-MM-DD HH24:MI:SS');
/

Flashback Transaction Query(Oracle10g feature)

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries. The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view like:

scott@orcl> SELECT versions_xid,name
2 name
3 FROM flashversion
4 VERSIONS BETWEEN
5 TIMESTAMP TO_TIMESTAMP('2009-05-08 09:07:59', 'YYYY-MM-DD HH24:MI:SS')
6 AND TO_TIMESTAMP('2009-05-08 09:09:22', 'YYYY-MM-DD HH24:MI:SS');

VERSIONS_XID NAME
---------------- --------------------
010010007B0D0000 JANI
030028003A110000 MANI
01002E007B0D0000 SCOTT

scott@orcl> connect sys/password@orcl as sysdba
Connected.
sys@orcl> SELECT undo_sql
2 FROM flashback_transaction_query
3 WHERE xid = HEXTORAW('010010007B0D0000');

UNDO_SQL
--------------------------------------------------------------------------------
update "SCOTT"."FLASHVERSION" set "NAME" = 'MANI' where ROWID = 'AAANPLAAEAAAGOlAAA';

sys@orcl> SELECT undo_sql
2 FROM flashback_transaction_query
3 WHERE xid = HEXTORAW('030028003A110000');

UNDO_SQL
--------------------------------------------------------------------------------
update "SCOTT"."FLASHVERSION" set "NAME" = 'SCOTT' where ROWID = 'AAANPLAAEAAAGOlAAA';

Flashback_transaction_query is a view which is belonging to sys schema. If you need to access from different schema, then you need to grant necessary privileges to other schema.

Here is the query content for flashback_transaction_query view.

CREATE OR REPLACE FORCE VIEW SYS.FLASHBACK_TRANSACTION_QUERY
(XID,
START_SCN,
START_TIMESTAMP,
COMMIT_SCN,
COMMIT_TIMESTAMP,
LOGON_USER,
UNDO_CHANGE#,
OPERATION,
TABLE_NAME,
TABLE_OWNER,
ROW_ID,
UNDO_SQL
)
AS
SELECT xid,
start_scn,
start_timestamp,
DECODE (commit_scn,
0, commit_scn,
281474976710655, NULL,
commit_scn)
commit_scn,
commit_timestamp,
logon_user,
undo_change#,
operation,
table_name,
table_owner,
row_id,
undo_sql
FROM sys.x$ktuqqry;

Recyclebin

2009-05-04T13:47:00.000-07:00

Flashback drop table is one of the awesome feature in oracle10g. Flashback query feature was introduced in oracle9i and we were able to read the past data by using timestamp or SCN for existing table in database. Oracle9i flashback query does not have the ability to recover the dropped tables. But oracle10g introduced a flashback drop feature to recover the dropped tables.

Oracle10g introduced recyclebin to keep the dropped tables for longer time subject to space.

When recyclebin is enabled, any table you drop will not get dropped. Instead, it will rename the dropped tables and dependent object to system generated name that starts with BIN$.

Recyclebin is a logical structure within each tablespace that holds dropped tables and dependent object to that table. Dependent objects are index, triggers, constraints, LOB segments, nested_tables etc. The space associated with the dropped table is not immediately available, but shows up the DBA_FREE_SPACE. Free space in the tablespace that is not occupied by dropped tables. When space pressure occurs in the tablespace, objects in the recyclebin are deleted in First-in first-out(FIFO) fashion.

The dropped object still belongs to the owner and still counts againts the quota for the owner in the tablespace.

As long as a tablespace has no space pressure, dropped objects are available indefinitely for recovery. Dropped objects are removed automatically when there is a space pressure. Also dropped objects can be removed manually by PURGE command.

This article is tested on Oracle10g version.

Let us test the recyclebin features as multiple scenario.
Scenario 1

Here table is created with dependent objects(index, primary key, trigger, constraints, LOB segments, nested_tables etc). Let us drop the table and recover using recyclebin. When we recover this table, the dependent objects are also recovered. But the dependent object names never changed to original names.

scott@orcl> create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> alter table flashdrop
2 add primary key(table_name);

Table altered.

scott@orcl> create index idxx on flashdrop(tablespace_name);

Index created.

scott@orcl> create trigger trg_flashdrop
2 before insert on flashdrop
3 for each row
4 begin
5 null;
6 end;
7 /

Trigger created.

scott@orcl> select count(*) from flashdrop;

COUNT(*)
----------
5

scott@orcl> drop table flashdrop;

Table dropped.

scott@orcl> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHDROP BIN$jmJpbe5kSqOynybZkA+yfg==$0 TABLE 2009-05-04:18:37:20

scott@orcl> select object_name,original_name from recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$WcPD/4MlRLayVzQi3x/Vzw==$0 SYS_C005982
BIN$gXTgHi0nQPidAxbnCx5b+w==$0 TRG_FLASHDROP
BIN$jmJpbe5kSqOynybZkA+yfg==$0 FLASHDROP
BIN$4sBW2ND1RYmDYs3JerZodA==$0 IDXX

scott@orcl> flashback table flashdrop to before drop;

Flashback complete.

scott@orcl> select count(*) from flashdrop;

COUNT(*)
----------
5

scott@orcl> show recyclebin

scott@orcl> select object_name,original_name from recyclebin;

no rows selected

scott@orcl> select constraint_name from
2 user_constraints where table_name='FLASHDROP';

CONSTRAINT_NAME
------------------------------
BIN$OIrKaH31R+WaqLWxgMWBQw==$0
BIN$lWD5jjLRQjacvAJJN6i0pQ==$0

scott@orcl> select trigger_name from user_triggers
2 where table_name='FLASHDROP';

TRIGGER_NAME
------------------------------
BIN$gXTgHi0nQPidAxbnCx5b+w==$0

scott@orcl> select index_name from user_indexes
2 where table_name='FLASHDROP';

INDEX_NAME
------------------------------
BIN$WcPD/4MlRLayVzQi3x/Vzw==$0
BIN$4sBW2ND1RYmDYs3JerZodA==$0

Scenario 2

Tables can be dropped and recovered with different name.

scott@orcl>create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> select count(*) from flashdrop;

COUNT(*)
----------
5

scott@orcl>

scott@orcl> drop table flashdrop;

Table dropped.

scott@orcl> flashback table flashdrop to before drop
2 rename to old_flashdrop;

Flashback complete.

scott@orcl> select count(*) from flashdrop;
select count(*) from flashdrop
*
ERROR at line 1:
ORA-00942: table or view does not exist

scott@orcl> select count(*) from old_flashdrop;

COUNT(*)
----------
5

Scenario 3

The recyclebin may contain several versions of a dropped object. Oracle restores them in LIFO order. It restores most recent version of dropped object. We can restore older versions by repeatedly restoring until you get the version you want, or by using the correct version's BIN$... name directly.

scott@orcl>create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> drop table flashdrop;

Table dropped.

scott@orcl>create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> drop table flashdrop;

Table dropped.

scott@orcl>create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> drop table flashdrop;

Table dropped.

scott@orcl> select object_name, original_name,droptime
2 from recyclebin;

OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ -------------------------------- -------------------
BIN$QZwwUzhJQT2GZM/PIEW3pA==$0 FLASHDROP 2009-05-05:10:06:00
BIN$GR72mxnyQp+++qxOmVGQfg==$0 FLASHDROP 2009-05-05:10:05:25
BIN$ePj5BAIxTuuZy4oRO3d/oA==$0 FLASHDROP 2009-05-05:10:05:36

scott@orcl>

scott@orcl> flashback table flashdrop to before drop;

Flashback complete.

scott@orcl> select object_name, original_name,droptime
2 from recyclebin;

OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ -------------------------------- -------------------
BIN$GR72mxnyQp+++qxOmVGQfg==$0 FLASHDROP 2009-05-05:10:05:25
BIN$ePj5BAIxTuuZy4oRO3d/oA==$0 FLASHDROP 2009-05-05:10:05:36

scott@orcl>

In this scenario, Oracle recovered the table which is dropped recently.

Scenario 4

Recyclebin feature is enabled by default in oracle10g. But after turning the recyclebin feature off, we can recover the tables which are already existing in recyclebin.

scott@orcl>create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> drop table flashdrop;

Table dropped.

scott@orcl> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHDROP BIN$WXrd4IW+SKmYh9EGU8bg5Q==$0 TABLE 2009-05-05:10:24:59

scott@orcl> ALTER SESSION SET recyclebin = OFF;

Session altered.

scott@orcl> flashback table flashdrop to before drop;

Flashback complete.

scott@orcl> show recyclebin;

scott@orcl> select count(*) from flashdrop;

COUNT(*)
----------
5

scott@orcl>

Scenario 5

Let us say, we start updating on flashdrop table at 12PM. We complete all the updates around 2PM. After we realized that, the updates went wrong and we want to revert the table back to 12PM... We can achieve this using FLASHBACK TABLE feature.

scott@orcl>create table flashdrop as
2 select * from user_tables
3 where rownum < 6;

Table created.

scott@orcl> SELECT current_scn from v$database;

CURRENT_SCN
-----------
19220251

scott@orcl> delete flashdrop where rownum < 2;

scott@orcl> commit;

Commit complete.

scott@orcl> update flashdrop set object_name=lower(object_name);

4 rows updated.

scott@orcl> select object_name from flashdrop;

OBJECT_NAME
--------------------------------------------------------------------------------
audit_seq
bin$ekjojm2as66hxzkrauejia==$0
bin$hfdops9utc2qdmy9pamg6w==$0
bin$idl5ashfqmqe8ctrwu8k8q==$0

scott@orcl> flashback table flashdrop to scn 19220251;
flashback table flashdrop to scn 19220251
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

scott@orcl> alter table flashdrop enable row movement;

Table altered.

scott@orcl> flashback table flashdrop to scn 19220251;

Flashback complete.

scott@orcl> select object_name from flashdrop;

OBJECT_NAME
--------------------------------------------------------------------------------
AUDITLOG
AUDIT_SEQ
BIN$EkjOJm2AS66HXzkrAueJiA==$0
BIN$HFDoPs9uTC2QdMY9pAMG6w==$0
BIN$IDl5ashFQMqE8ctrWU8K8Q==$0

scott@orcl>

Flashback of table can also be performed using timestamps as below...

FLASHBACK TABLE flashdrop TO TIMESTAMP TO_TIMESTAMP('2004-05-06 10:00:00', 'YYYY-MM-DD HH:MI:SS');

How do we purge objects manully in recyclebin?

PURGE TABLE tablename; -- Specific table.
PURGE INDEX indexname; -- Specific index.
PURGE TABLESPACE ts_name; -- All tables in a specific tablespace.
PURGE TABLESPACE ts_name USER username; -- All tables in a specific tablespace for a specific user.
PURGE RECYCLEBIN; -- The current users entire recycle bin.
PURGE DBA_RECYCLEBIN; -- The whole recycle bin.

How do we bypass the recyclebin?

We can add PURGE statement with drop table command to bypass the recyclebin. The tables will be dropped without going into recycle bin. But when we drop tablespace(DROP TABLESPACE .... INCLUDING CONTENTS), tablespace is not placed in recyclebin. It drops associated recyclebin for that tablespace.

scott@orcl> DROP TABLE EMPTABLE PURGE;

Table dropped.

scott@orcl> FLASHBACK table emptable to before drop;
FLASHBACK table emptable to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

scott@orcl>

Naming convention for object in recyclebin:

When we drop the object, the dropped objects are renamed and moved to recyclebin. The names are globally unique and are used to identify the objects while they are in the recycle bin. The recyclebin name of an object is always 30 characters long. Dropped Object names are formed as follows: BIN$$globalUID$version

globalUID is a globally unique, 24 character long identifier generated for the object.
version is a version number assigned by the database.

Limitations on Recyclebin:

1. Only non system locally managed tablespace(LMT) can have a recycle bin. However, dependent objects in a dictionary managed tablespace are protected if the dropped object is LMT.

2. All dependent objects will be placed in recyclebin except bitmap join index, FK constraints, Materialized view logs)

3. Indexes are protected only if the table is dropped first. Explicitly dropping an index does not place in recyclebin.

4. There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.

5. Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.

6. Partitioned index-organized tables are not protected by the recycle bin.

Recyclebin Views:

We have two views, USER_RECYCLEBIN, DBA_RECYCLEBIN. For our convenience, synonym RECYCLEBIN is created which is pointing to user_recyclebin.

How do we disable the recyclebin?

The recyclebin feature is enabled in oracle10g by default. We can change the initialization parameter RECYCLEBIN to enable or disable this feature.

ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF;

SQL Loader Memory Parameters

2009-04-20T15:38:00.000-07:00

There are few command line SQLLoader parameters which are used for tuning direct data load. These parameters should be tuned only when we have any performance issue for loading the data through SQLLoader. Since these parameters are directly impacting client memory... This article is written in Oracle10g and these parameter might change in future Oracle Versions...

Here are the below parameters which are used only for Direct Path Data load.....

READSIZE is size of the buffer used to read the data from input data file. The default size is 1MB(1048576 bytes).

COLUMNARRAYROWS is number of rows in a two-dimensional array used to hold field information. This holds field information for every row. The default is 5000 rows.

STREAMSIZE is size of the buffer to send the data to server for loading into target table. The default is 256000 bytes.

MULTITHREADING is an option that allows concurrent execution of some SQL*Loader client operations with database server data loading. If multithreading is turned off, the SQL*Loader client will always wait until the server finishes a data load before continuing. If multithreading is turned on, some calls to the server will be executed by the client's "load thread" while the client's "main thread" continues converting data and building stream buffers.

When the MULTITHREADING command-line parameter is set to TRUE (the default on multi-CPU clients), SQL*Loader will overlap stream loading with stream conversion. If the main thread has converted data from a column array into a stream and the stream is filled before all data in the column array has been processed, the load thread will load that stream while the main thread continues to convert the column array into another stream buffer.

DATE_CACHE used in direct path load. This parameter will play a role when we load the date data from input file into Oracle table. When we have date value in input file, sqlloader has to convert the date string into Oracle DATE Format before load into target table. When we have large duplicate date string in input file, then sqlloader keeps the converted date value in date_cache first time and reuse every occurrence of the same date string in input file. So it would save time for date conversion when we have the same date string in the input file.

If you know the number of duplicate date strings is small or the number of unique date strings is very large, then you can disable the date cache by specifying DATE_CACHE=0 on the command line. The default value for DATE_CACHE is 1000. If number of unique date values in table greater then the size of the date cache, then DATE_CACHE will be disabled. All the date columns will share the same DATE_CACHE.

Please click this link if you need more info about DATE_CACHE.

Here is the process flow between these three memory parameters(READSIZE, COLUMNARRAYROWS and STREAMSIZE) for DIRECT Path data load...

Step1

SQLLoader reads data from input data file and load into memory buffer. This buffer size is controlled by READSIZE parameter.

Step2

SQLLoader parse every logical record from step1, and isolate the fields in COLUMNARRAY memory buffer based on field definition which is specified in SQLLoader control file. This process is called as "Field setting". The number of rows in the column array is controlled by COLUMNARRAYROWS parameter.

Step3

Step2 will continue untill COLUMNARRAY buffer is full.

Step4

SQLLoader will parse the columnarray data into STREAMBUFFER. This buffer size is controlled by STREAMSIZE parameter. There may be a possibility that, STREAMBUFFER might be getting full before it copies all the data from COLUMNARRAY buffer.

Step5

STREAMBUFFER data will be sent to server.

Step6

Server will parse STREAMBUFFER data and load into target table.

Step7

Once step6 is completed, then STREAMBUFFER will be set to empty.

Step8

If more data that needs to be loaded from COLUMNARRAYBUFFER, then continue from step4. If there is no data in COLUMNARRAYBUFFER, then continue step9.

Step9

If the COLUMNARRAY buffer data is completed(loaded into target table via streambuffer), then sqlloader will look for remaining data in READBUFFER. If there are more data in READBUFFER, then continue from step2. Otherwise, continue step10

Step10

If all the records in READBUFFER is processed, then load more records from input data file into READBUFFER which is apparently from step1.

Note : If you need more info on these DIRECT Load Tuning parameters, please click this link.

Integer Versus Number data type

2009-04-02T16:48:00.001-07:00

What is the difference betwen INTEGER and NUMBER? When should we use NUMBER and when should we use INTEGER? I just wanted to update my comments here...

NUMBER always stores as we entered. Scale is -84 to 127. But INTEGER rounds to whole number. The scale for INTEGER is 0. INTEGER is equivalent to NUMBER(38,0). It means, INTEGER is constrained number. The decimal place will be rounded. But NUMBER is not constrained.

INTEGER(12,2) => 12
INTEGER(12.5) => 13
INTEGER(12.9) => 13
INTEGER(12.4) => 12
NUMBER(12,2) => 12.2
NUMBER(12.5) => 12.5
NUMBER(12.9) => 12.9
NUMBER(12.4) => 12.4

INTEGER is always slower then NUMBER. Since integer is a number with added constraint. It takes additional CPU cycles to enforce the constraint. I never watched any difference, but there might be a difference when we load several millions of records on the INTEGER column. If we need to ensure that the input is whole numbers, then INTEGER is best option to go. Otherwise, we can stick with NUMBER data type.

New Enhancements in SQL* PLUS

2009-03-29T07:11:00.000-07:00

SQL* PLUS is a tool to edit the SQL command and format the SQL output. Here is the new enhancement in SQL*PLUS for oracle10g.

Enhancement 1
Prior to oracle10g if we describe the object that is invalid, the describe command will fail with error. But in oracle10g, DESCRIBE will try to validate the object first, and if the object is still invalid after validation, it gives the error message. If the validation is successful, then DESCRIBE command will also be successful.
Let us test this in oracle 10g & oracle9i and see the difference....

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

scott@orcl> create or replace procedure testproc is
2 v_cnt number;
3 begin
4 select count(*) into v_cnt
5 from temptable;
6 end;
7 /

Procedure created.

scott@orcl> desc testproc;
PROCEDURE testproc
scott@orcl> drop table temptable;

Table dropped.
scott@orcl> select status from user_objects
2 where object_name='TESTPROC';

STATUS
-------
INVALID
scott@orcl> create table temptable(no number);

Table created.

scott@orcl> select status from user_objects
2 where object_name='TESTPROC';

STATUS
-------
INVALID

scott@orcl> desc testproc;
PROCEDURE testproc

scott@orcl> select status from user_objects
2 where object_name='TESTPROC';

STATUS
-------
VALID

scott@orcl>

The above case, the procedure is recompiled when we describe the procedure...

Let us test the same scenario in oracle9i...
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create or replace procedure testproc is
2 v_cnt number;
3 begin
4 select count(*) into v_cnt
5 from temptable;
6 end;
7 /

Procedure created.

SQL>
SQL> desc testproc;
PROCEDURE testproc

SQL> drop table temptable;

Table dropped.

SQL>
SQL> select status from user_objects
2 where object_name='TESTPROC';

STATUS
-------
INVALID

SQL> create table temptable(no number);

Table created.

SQL>
SQL> select status from user_objects
2 where object_name='TESTPROC';

STATUS
-------
INVALID

SQL> desc testproc;
ERROR:
ORA-24372: invalid object for describe

SQL> select status from user_objects
2 where object_name='TESTPROC';

STATUS
-------
INVALID

SQL> alter procedure testproc compile;

Procedure altered.

SQL> desc testproc
PROCEDURE testproc

SQL>
The above case, the procedure is not recompiled when we describe the procedure...

Enhancement 2

The glogin.sql, login.sql files are profile files used to customize our SQL*Plus environment when we log in SQL*Plus. The glogin.sql file is site profile file and located in $ORACLE_HOME/sqlplus/admin directory. The login.sql file user profile and is executed after the glogin.sql file. Prior to oracle10g, this two files(glogin.sql, login.sql) are executed one after other only when we restart the SQL*Plus. But in oracle10g, these two files are executed one after other for every connect as well as every restart of SQL*Plus.

Let us test this in oracle10g... Our goal is to display username and instance name in the sql prompt. Let me add this below entry in glogin.sql file.

column global_name new_value gname
set termout off
select lower(user) '@'
INSTANCE_NAME global_name
from V$INSTANCE;
set sqlprompt '&gname> '
set termout on

Let us restart the SQL*Plus.

SQL*Plus: Release 10.1.0.2.0 - Production on Sun Mar 29 11:04:41 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
scott@orcl>

Now again we will connect to another user and see SQL prompt is changing...

scott@orcl> connect training/training@orcl
Connected.
training@orcl>

so in Oracle10g, glogin.sql is executing every connect as well as every restart of SQL* Plus.

Let us test the same in oracle9i.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

dwadba@invent> connect sales@invent
Enter password: ************
Connected.
dwadba@invent>

so in Oracle9i, glogin.sql is not executing for every connect. It executes only when we start the SQL*Plus.

Enhancement 3

oracle10g supports white space in filenames when we use commands like SPOOL, SAVE, RUN.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

scott@orcl> spool 'c:/test file.txt';
scott@orcl> select sysdate from dual;

SYSDATE
---------
29-MAR-09

scott@orcl> spool off

In oracle9i, it does not support space for filenames...

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> spool 'c:/test file.txt';
SP2-0333: Illegal spool file name: "'c:/test file.txt'" (bad character: ' ')

Enhancement 4

The spool command stores query result in a file. In oracle10g, SPOOL command includes the APPEND extension to add the contents of the buffer to the end of the file.

Let us test this in oracle10g.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

scott@orcl> spool c:/test.txt
scott@orcl> select sysdate from dual;

SYSDATE
---------
29-MAR-09

scott@orcl> spool off
scott@orcl> spool c:/test.txt append
scott@orcl> select sysdate from dual;

SYSDATE
---------
29-MAR-09

scott@orcl> spool off
scott@orcl>

Let us test the same in oracle9i.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> spool c:/test.txt
SQL> select sysdate from dual;

SYSDATE
---------
30-MAR-09

SQL> spool off
SQL> spool c:/test.txt append
SP2-0333: Illegal spool file name: "c:/test.txt append" (bad character: ' ')

Character and Byte Semantics in Oracle

2009-02-20T09:10:00.000-08:00

Historically Oracle character data type column have been defined by using number of bytes. This is good as long as total number of characters equal to total number of bytes. When we maintain multilingual version of application, the database needs to set as a multi byte character set. For instance, the column data type length is VARCHAR2(10). In this case, we can store 10 characters for single byte character set. When we moved to multi byte character set, we can not store 10 characters. Oracle9i has solved this problem with the introduction of character and byte length semantics. Oracle9i introduced NLS_LENGTH_SEMANTICS init parameter to solve this issue. NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics.

There are three different ways, we can declare CHAR/VARCHAR2 data type.
Let us take VARCHAR2(10) as an example here.

1. VARCHAR2(10)
2. VARCHAR2(10 BYTE)
3. VARCHAR2(10 CHAR)

What is the difference between VARCHAR2(10), VARCHAR2(10 BYTE) & VARCHAR2(10 CHAR).

VARCHAR2(10 BYTE)

When we declare the data type as VARCHAR2(10 BYTE), oracle stores only 10 bytes of data, regardless of how many characters this represents. This is perfect when the database has only single byte character set. Since total number of charater is equal to total number of bytes.. So this case, oracle stores 10 Characters.. But when database handles multilingual version of application, then oracle stores multi byte characters. In this case, oracle can store only 5 character.

VARCHAR2(10 CHAR)

This allows the specified number of characters to be stored in the column regardless of number of bytes this equates to.. Oracle can store 10 character regardless of single byte character set or multi byte character set.

VARCHAR2(10)

Oracle stores 10 Character or 10 Bytes depends on NLS_LENGTH_SEMANTICS parameter value. if it sets to BYTE, then we can store 10 bytes(if it is mutibyte character set, then we can store 5 characters). The default value is BYTE. When this parameter is CHAR, then we can store 10 characters regardless of single byte character set or multi byte character set. When we move the database to multibyte characterset, we can change NLS_LENGTH_SEMANTICS paramter to CHAR to resolve the storage issue. Existing columns will not be affected when we change the value for this parameter.

The default character semantics of the database or session can be altered using the NLS_LENGTH_SEMANTICS parameter like:

SQL> alter system set nls_length_semantics=char;

System altered.

SQL> alter system set nls_length_semantics=byte;

System altered.

SQL> alter session set nls_length_semantics=char;

Session altered.

SQL> alter session set nls_length_semantics=byte;

Session altered.

Note : NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics.

Bulk Binds

2008-12-18T17:24:00.000-08:00

Oracle use two engine to process PLSQL block and subprogram. All SQL code is processed by SQL engine and PLSQL code is processed by PLSQL engine. When you run SQL code in the loop, oracle has to switch between SQL engine and PLSQL engine for each iteration. So there is overhead associated for each context switch. Bulk Bind is introduced in oracle8i to reduce the context switch.. This feature is enhanced in further versions...

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> CREATE TABLE emp(
2 empno NUMBER(10),
3 empname VARCHAR2(50));

Table created.

SQL>
SQL> ALTER TABLE emp ADD (
2 CONSTRAINT emppk PRIMARY KEY(empno));

Table altered.

SQL>

The total time taken to insert the 50,000 records through FOR LOOP is .071 Minuts. Because, 50,000 context switch happens in the FOR LOOP.

SQL> set serveroutput on
SQL> DECLARE
2 TYPE emp_tab IS TABLE OF EMP%ROWTYPE;
3 t_tab emp_tab := emp_tab();
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 BEGIN
6 FOR i IN 1 .. 50000 LOOP
7 t_tab.extend;
8 t_tab(t_tab.last).empno := i;
9 t_tab(t_tab.last).empname := 'name:' To_Char(i);
10 END LOOP;
12 FOR i IN t_tab.first .. t_tab.last LOOP
13 INSERT INTO emp (empno, empname)
14 VALUES (t_tab(i).empno, t_tab(i).empname);
15 END LOOP;
16 COMMIT;
17 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
18 END;
19 /
Total time ... .071

PL/SQL procedure successfully completed.

SQL>

Here time taken to insert the 50,000 records through bulk binds is 0.015 Minuts. When we compared to above example, it is much faster.. since it is completed reduced the context switches between SQL engine to PLSQL engine.

SQL> TRUNCATE TABLE emp;

Table truncated.

SQL>
SQL> DECLARE
2 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
3 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
4 t_tab emp_tab := emp_tab();
5 BEGIN
6 FOR i IN 1 .. 50000 LOOP
7 t_tab.extend;
9 t_tab(t_tab.last).empno := i;
10 t_tab(t_tab.last).empname := 'name:' To_Char(i);
11 END LOOP;
12
13 FORALL i IN t_tab.first .. t_tab.last
14 INSERT INTO emp VALUES t_tab(i);
15 COMMIT;
16 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
17 END;
18 /
Total time ... .015

PL/SQL procedure successfully completed.

SQL>

Bulk binds can also improve the performance when loading collection from queries. BULK COLLECT INTO clause can collect the data into collections. In the below example, it took 0.021 Minuts to fetch 50,000 records with out bulk binds

SQL> DECLARE
2 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
3 t_tab emp_tab := emp_tab();
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 CURSOR c_data IS
6 SELECT *
7 FROM emp;
8 BEGIN
9 FOR cur_rec IN c_data LOOP
10 t_tab.extend;
11 t_tab(t_tab.last).empno := cur_rec.empno;
12 t_tab(t_tab.last).empname := cur_rec.empname;
13 END LOOP;
14 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
15 END;
16 /
Total time ... .021

PL/SQL procedure successfully completed.

In the below case, it took 0.004 Minutes to fetch the data into collection. When we compared to above case, it is faster.. since it is using BULK COLLECT to reduce the context switch.

SQL>
SQL> DECLARE
2 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
3 t_tab emp_tab := emp_tab();
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 BEGIN
6 SELECT empno,empname
7 BULK COLLECT INTO t_tab
8 FROM emp;
9 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
10 END;
11 /
Total time ... .004

PL/SQL procedure successfully completed.

SQL>

Oracle 9i Release 2 allows to use ROW key word to update the record. When we update, we can not use record type definition with bulk binds. The below case, we can not use bulk bind to improve the performance, since we are using record type definition. This is one restriction on this version(oracle9i R2).

SQL> DECLARE
2 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
3 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
4 t_tab emp_tab := emp_tab();
5 BEGIN
6 FOR i IN 1 .. 10000 LOOP
7 t_tab.extend;
9 t_tab(t_tab.last).empno := i;
10 t_tab(t_tab.last).empname := 'name' To_Char(i);
11 END LOOP;
13 FOR i IN t_tab.first .. t_tab.last LOOP
14 UPDATE emp
15 SET ROW = t_tab(i)
16 WHERE empno = t_tab(i).empno;
17 END LOOP;
18 COMMIT;
19 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
20 END;
21 /
Total time ... .012

PL/SQL procedure successfully completed.

SQL>
The below case, we are using bulk bind since, we are not using record type definition. The time took to update the 50,000 records is 0.007 Minuts. When we compare with above case, the performance is improved well.

SQL> DECLARE
2 TYPE empno_tab IS TABLE OF emp.empno%TYPE;
3 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 t_empno empno_tab := empno_tab();
6 t_tab emp_tab := emp_tab();
7 BEGIN
8 FOR i IN 1 .. 10000 LOOP
9 t_empno.extend;
10 t_tab.extend;
11 t_empno(t_empno.last) := i;
12 t_tab(t_tab.last).empno := i;
13 t_tab(t_tab.last).empname := 'name ' To_Char(i);
14 END LOOP;
16 FORALL i IN t_tab.first .. t_tab.last
17 UPDATE emp
18 SET ROW = t_tab(i)
19 WHERE empno = t_empno(i);
21 COMMIT;
22 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
23 END;
24 /
Total time ... .007

PL/SQL procedure successfully completed.

SQL>

When to use Bulk Binds: There is no univeral rule exists to dictate when to use Bulk binds. If PLSQL code reads only few hundered records, then you will not see significant performance improvement for bulk binds. When you read huge number of records and have multiple insert/update, then you can think of bulk binds. If you have the luxury of time, you can test your code both with and without bulk binds and decide to go for this feature.

Convert Non-Partitioned to Partitioned Table

2008-11-06T14:36:00.000-08:00

This article is written in oracle10g R2. In any database environment, if we need to covert any huge table to partition table, then first we need to think about two factor.

1. Partition key
2. What type of partition we need to use.

Once we decided the above key factor, then there are couple of ways, we can convert the table to partition table. We have two option to convert the regular table to partition table.
Option 1
This option requires table down time and it might fit if business allows the application down time. Here are the steps to follow.

1. Stop using the table, make sure no one is chaning the data in table.
2. Create the partition table with the same structure of regular table.
3. Copy the data from regular table to partition table.
4. Create the constriant, keys, indexes on partition table.
5. Drop the original table
6. Analyze the partition table.

Option 2

This is ideal option to convert the regular table to partition table. It does not require down time and every one can use the table during the conversion. This option is introduced in oracle9i and enhanced to 10g. We are using online redefinition(DBMS_REDEFINITION) to convert the table to partition table.

Some restriction of using DBMS_REDEFINITION.

1· Cannot belong to SYS or SYSTEM Schema.
2. The table to be redefined online should not be using User-defined data types
3· Should not be a clustered table.
4. Should not have MV Logs or MVs defined
5. You cannot move a table from one schema to another using Online Redefinition feature.

Here are the steps to convert to partition table.

Step 1
Check to make sure that the table can use the online redefintion feature

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProductionWith the Partitioning, OLAP and Data Mining options
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','EMP');
PL/SQL procedure successfully completed.

Step 2
Create the temporary partition table as same structure of original table.

SQL> Create table EMP_part
2 (EMPNO NUMBER(4),
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2))
10 partition by range (SAL)
11 (Partition p1 values less than (1000),
12 Partition p2 values less than (2000),
13 Partition p3 values less than (3000),
14 Partition p4 values less than (4000),
15 Partition max values less than (maxvalue))
16 tablespace users;

Table created.

Step 3
Start the online redefinition process.

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT','EMP','EMP_PART');

PL/SQL procedure successfully completed.

Step 4
Here is where oracle10g feature come into play. We do not need to copy any dependent objects to part_emp table. Dependent objects are like grants, synonym, triggers etc.

SQL> VARIABLE NUM_ERRORS NUMBER;
SQL> EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT','EMP','EMP_PART', 1,TRUE,TRUE,TRUE,FALSE,
:NUM_ERRORS,FALSE);

PL/SQL procedure successfully completed.

SQL> PRINT NUM_ERRORS

NUM_ERRORS
----------
0

Step 5
Resync the table

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT','EMP','EMP_PART');

PL/SQL procedure successfully completed.
Step 6

Complete the online redefinition

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','EMP','EMP_PART');

PL/SQL procedure successfully completed.

Step 7

Make sure emp table has all partitions as we created in emp_part table
SQL> Select partition_name, high_value from user_tab_partitions where table_name='EMP';

PARTITION_NAME HIGH_VALUE
------------------------------ ---------------------------------------------------------------------
MAX MAXVALUE
P1 1000
P2 2000
P3 3000
P4 4000

SQL> select partition_name, high_value from user_tab_partitions where table_name='EMP_PART';

no rows selected

Step 8
Make sure all the dependent objects are copied .

SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS
2 WHERE TABLE_NAME='EMP';

TRIGGER_NAME
------------------------------
EMPTRIG

SQL> select constraint_name from user_constraints
2 where table_name='EMP';

CONSTRAINT_NAME
------------------------------
PK_EMP
FK_DEPTNO

SQL>

Note : The only problem i see here is, if we have any index on the original table, it will convert to global index on partition table. If we need the index to be local index, then we have to drop and recreate the index.