sh: /u01/app/oracle/product/11.2.0/db_1/bin/clsecho: No such file or directory

Another annoying error but it's not a big issue.

[oracle@rac1 ~]$ asmcmd
Connected to an idle instance.
sh: /u01/app/oracle/product/11.2.0/db_1/bin/clsecho: No such file or directory
ASMCMD> ls
Can't exec "/u01/app/oracle/product/11.2.0/db_1/bin/clsecho": No such file or directory at /u01/app/oracle/product/11.2.0/db_1/lib/asmcmdshare.pm line 493, <STDIN> line 1.
Use of uninitialized value $buf in string ne at /u01/app/oracle/product/11.2.0/db_1/lib/asmcmdshare.pm line 497, <STDIN> line 1.
ASMCMD> 

The reason behind this is that the env for ASM is not setup properly. To set the proper env do the following

[oracle@rac1 ~]$ cat /etc/oratab | grep ASM
# a database or ASM Configuration Assistant while creating ASM instance.
+ASM1:/u01/app/11.2.0/grid:N          # line added by Agent
[oracle@rac1 ~]$ 

The very first thing we must do is check if the environment variables are correct.

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [RAC1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ asmcmd
ASMCMD>
ASMCMD>
ASMCMD> ls
DATA/
ASMCMD> 

It is working for you, great!!!

unable to add disk using oracleasm and getting Instantiating disk: failed

Unable to create the disk on RHEL6 using oracleasm

[root@racdr1 ~]# oracleasm createdisk ASM1 /dev/sdb1
Writing disk header: done
Instantiating disk: failed
Clearing disk header: done
[root@racdr1 ~]#

The reason behind this is under /etc/selinux/config file

make sure that it have following value

SELINUX=permissive

If not then set it and restart the system then issue the command again and there will be success.

[root@racdr1 ~]# cat  /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

[root@racdr1 ~]# oracleasm createdisk ASM1 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@racdr1 ~]# 

Visitor Number 20,000

Thanks for visiting my blog and I am hoping that it have helped you some way. Our official visitor counter have just ticked over 20,000 and the fortunate thing is that I know who is that lucky visitor.

Heath Christian from Charles Sturt University - Bathurst

 

RMAN create duplicate database using Oracle 11g on same host

Some times there is a need to create the duplicate database from the running database on same host. This will require following steps for the creation of the duplicate database.

1. Create spfile for the duplicate database and corresponding directories.
2. Create the password file.
3. Setup listener and tnsnames.ora for duplicate database.
4. Check the latest archive log sequence number from main database.
5. connect using RMAN and run script to duplicate database.

In this example I am duplicating from TDB (Test database) to DUPLI (Duplicate database).

1. First of all an initDUPLI.ora was created under ORACLE_HOME/dbs with below parameters:

*.audit_file_dest='/u01/app/oracle/admin/DUPLI/adump'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/DUPLI/control01.ctl','/arch/FRA2/DUPLI/control02.ctl'

*.db_file_name_convert='/u01/app/oracle/oradata/TDB/','/u01/app/oracle/oradata/DUPLI/'

*.log_file_name_convert='/u01/app/oracle/oradata/TDB/','/u01/app/oracle/oradata/DUPLI/'

*.db_block_size=8192

*.db_name='DUPLI'

*.db_recovery_file_dest='/arch/FRA2'

*.db_recovery_file_dest_size=10485760000

*.diagnostic_dest='/u01/app/oracle'

*.log_archive_format='%t_%s_%r.arc'

*.memory_max_target=500M

I have highlighted the important parameters.

mkdir -p /u01/app/oracle/admin/DUPLI/adump
mkdir -p /arch/FRA2/DUPLI/

Now to create spfile and start up database in nomount.

startup nomount
create spfile from pfile='?/dbs/initDUPLI.ora'
shutdown immediate
startup nomount

2. Create password file so that rman can connect 

orapwd file=orapwDUPLI password=********

3. Listener setup was done and a TNS entry was created.

DUPLI_TNS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hname.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DUPLI)
    )

  )

Check the status of the listener and make sure that you are getting 

Service "DUPLI" has 2 instance(s).
  Instance "DUPLI", status UNKNOWN, has 1 handler(s) for this service...

  Instance "DUPLI", status BLOCKED, has 1 handler(s) for this service...


4. Check the archivelog sequence number from main database which is TDB in our case.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Next log sequence to archive   8

Current log sequence           8


5. Log into RMAN and run following to create duplicate database.

$ rman 
RMAN> 
RMAN> connect target sys/********@tdb

RMAN> connect auxiliary sys/********@dupli_tns
RMAN> run {
2> set until sequence 8 thread 1;
3> duplicate target database to dupli;
4> }
RMAN> 


and let this finish and you are done.

Grant readonly access to PL/SQL (Function, procedures, packages etc) user logged in through SQL Developer

Sometimes developer need read only access to the code of you PL/SQL in oracle, this is not difficult to achieve. You can create a user

create user readonlyU identified by strongpassword default tablespace users temporary tablespace temp;

grant connect to readonlyU;
grant select on sys.dba_source to readonlyU;

This will work great but if you log in through SQL Developer you will not able to see the PL/SQL objects.

There are two solutions to this problem. After implementation of these solutions readonlyU should be able to view the source code in SQL Developer like the image below:

1. Grant select access to all tables under database and then set O7_DICTIONARY_ACCESSIBILITY=TRUE

This would allow user to have select access on all the tables including data dictionary tables. This could not be the best option.

grant select any table to readonlyU;
alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;
shutdown immediate
startup

2. SQL Developer us two dba views to show the PL/SQL objects under left navigation and those are dba_objects and dba_plsql_object_settings. Hence granting select on these two views will do the trick.

grant select on sys.dba_objects to readonlyU;
grant select on sys.dba_plsql_object_settings to readonlyU;

Tower of hanoi solution python

resl=0
count =0

def main():
    num_discs=3
    from_peg=1
    to_peg=3
    temp_peg=2
    resl=move_discs(num_discs, from_peg, to_peg, temp_peg)
    print ('All the pegs are moved!')
    print ('done in: ', resl ,' Moves !')

def move_discs(num, from_peg, to_peg, temp_peg):
    global count
    count +=1
    print ('inside 1: ', count)
    if num >0:
        print ('inside 2: ', count)
        move_discs(num -1, from_peg, to_peg, temp_peg)
        print('Move a disc from peg', from_peg, 'to_peg', temp_peg)
        print ('inside 3: ', count)
        move_discs(num -1, temp_peg,to_peg,from_peg)
        print ('inside 4: ', count)
   
    print ('a',count)
    return count
    #else:
        #print(count)
main()

My RAC have lots of room for the improvement and learning

Below is the example on how the raccheck utility will run and what kind of output we get out of it. This is amazing.

[oracle@node01 Desktop]$ ./raccheck -a

CRS stack is running and CRS_HOME is not set. Do you want to set CRS_HOME to /u01/app/11.2.0/grid?[y/n][y]y

Checking ssh user equivalency settings on all nodes in cluster

Node node02 is configured for ssh user equivalency for oracle user


Searching for running databases . . . . .

.
List of running databases registered in OCR
1. RAC
2. None of above

Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1].1
. .


Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
-------------------------------------------------------------------------------------------------------
                                                 Oracle Stack Status                          
-------------------------------------------------------------------------------------------------------
Host Name  CRS Installed  ASM HOME       RDBMS Installed  CRS UP    ASM UP    RDBMS UP  DB Instance Name
-------------------------------------------------------------------------------------------------------
node01      Yes             Yes             Yes             Yes        Yes      Yes      RAC1    
node02      Yes             Yes             Yes             Yes        Yes      Yes      RAC2    
-------------------------------------------------------------------------------------------------------

91 of the included audit checks require root privileged data collection . If sudo is not configured or the root password is not available, audit checks which  require root privileged data collection can be skipped.


1. Enter 1 if you will enter root password for each  host when prompted

2. Enter 2 if you have sudo configured for oracle user to execute root_raccheck.sh script

3. Enter 3 to skip the root privileged collections

4. Enter 4 to exit and work with the SA to configure sudo  or to arrange for root access and run the tool later.

Please indicate your selection from one of the above options[1-4][1]:- 1

*** Checking Best Practice Recommendations (PASS/WARNING/FAIL) ***



Log file for collections and audit checks are at
/home/oracle/Desktop/raccheck_030313_004245/raccheck.log

=============================================================
                    Node name - node01                              
=============================================================
Collecting - ASM DIsk I/O stats
Collecting - ASM Disk Groups
Collecting - ASM Diskgroup Attributes
Collecting - ASM disk partnership imbalance
Collecting - ASM diskgroup attributes
Collecting - ASM initialization parameters
Collecting - Active sessions load balance for RAC database
Collecting - Archived Destination Status for RAC database
Collecting - Cluster Interconnect Config for RAC database
Collecting - Database Archive Destinations for RAC database
Collecting - Database Files for RAC database
Collecting - Database Instance Settings for RAC database
Collecting - Database Parameters for RAC database
Collecting - Database Properties for RAC database
Collecting - Database Registry for RAC database
Collecting - Database Sequences for RAC database
Collecting - Database Undocumented Parameters for RAC database
Collecting - Database Workload Services for RAC database
Collecting - Dataguard Status for RAC database
Collecting - Files not opened by ASM
Collecting - Log Sequence Numbers for RAC database
Collecting - Percentage of asm disk  Imbalance
Collecting - Process for shipping Redo to standby for RAC database
Collecting - Redo Log information for RAC database
Collecting - Standby redo log creation status before switchover for RAC database
Collecting - CPU Information
Collecting - CRS active version
Collecting - CRS oifcfg
Collecting - CRS software version
Collecting - CSS Reboot time
Collecting - CSS disktimout
Collecting - Cluster interconnect (clusterware)
Collecting - Clusterware OCR healthcheck
Collecting - Clusterware Resource Status
Collecting - Huge pages configuration
Collecting - Kernel parameters
Collecting - Linux module config.
Collecting - Maximum number of semaphore sets on system
Collecting - Maximum number of semaphores on system
Collecting - Maximum number of semaphores per semaphore set
Collecting - Memory Information
Collecting - OS Packages
Collecting - Operating system release information and kernel version
Collecting - Oracle Executable Attributes
Collecting - Patches for Grid Infrastructure
Collecting - Patches for RDBMS Home
Collecting - Shared memory segments
Collecting - Table of file system defaults
Collecting - Voting disks (clusterware)
Collecting - number of semaphore operations per semop system call
Preparing to run root privileged commands  node01.  Please enter root password when prompted.
root@node01's password:
Collecting - CRS user limits configuration
Collecting - CRS user time zone check
Collecting - Custom rc init scripts (rc.local)
Collecting - Interconnect interface config
Collecting - Network interface stats
Collecting - OCFS2 disks
/tmp/root_raccheck.sh: line 34: /sbin/mounted.ocfs2: No such file or directory
Collecting - Root Open File Limit
Collecting - ocsf status
/tmp/root_raccheck.sh: line 44: /etc/init.d/o2cb: No such file or directory
Collecting - root time zone check


Data collections completed. Checking best practices on node01.
--------------------------------------------------------------------------------------


 WARNING => OCR is NOT being backed up daily
 INFO =>    $CRS_HOME/log/hostname/client directory has too many older log files.
 INFO =>    user_dump_dest has trace files older than 30 days for RAC
 INFO =>    background_dump_dest has files older than 30 days for RAC
 INFO =>    At some times checkpoints are not being completed for RAC
 WARNING => Open files limit (ulimit -n) for current user is NOT set to recommended value >= 65536 or unlimited
 INFO =>    audit_file_dest has audit files older than 30 days for RAC
 WARNING => Controlfile is NOT multiplexed for RAC
 WARNING => One or more redo log groups are NOT multiplexed for RAC
 WARNING => /tmp is NOT on a dedicated filesystem
 WARNING => kernel.shmmax parameter is NOT configured according to recommendation
 WARNING => Open file limit for root user (ulimit -n) is NOT >= 65536 or unlimited
 WARNING => pam_limits NOT configured properly for shell limits
 WARNING => NIC bonding is NOT configured for public network (VIP)
 WARNING => OSWatcher is not running as is recommended.
 INFO =>    Jumbo frames (MTU ~9000) are not configured for interconnect
 FAIL =>    Database parameter DB_BLOCK_CHECKSUM is NOT set to recommended value on RAC1 instance
 FAIL =>    Database parameter DB_LOST_WRITE_PROTECT is NOT set to recommended value on RAC1 instance
 INFO =>    OS parameter vm.swappiness is NOT set to the recommended value
 WARNING => Database parameter DB_BLOCK_CHECKING on PRIMARY is NOT set to the recommended value. for RAC
 FAIL =>    Flashback on PRIMARY is not configured for RAC
 FAIL =>    No one high redundancy diskgroup configured for RAC
 INFO =>    Operational Best Practices
 INFO =>    Consolidation Database Practices
 INFO =>    Network failure prevention best practices
 INFO =>    Computer failure prevention best practices
 INFO =>    Data corruption prevention best practices
 INFO =>    Logical corruption prevention best practices
 INFO =>    Storage failures prevention best practices
 INFO =>    Database/Cluster/Site failure prevention best practices
 INFO =>    Client failover operational best practices
 WARNING => fast_start_mttr_target has NOT been changed from default on RAC1 instance

 INFO =>    IMPORTANT: Oracle Database Patch 13466801, 13343438, 13348650, 13696251 13919095 or 14275572 PSU is NOT applied to RDBMS Home /u01/app/oracle/product/11.2.0/db_1
 WARNING => kernel parameter rp_filter is set to 1.
 INFO =>    Information about hanganalyze and systemstate dump
 FAIL =>    Your Configuration is NOT in Compliance with Oracle Security Alert for CVE-2012-1675 /u01/app/11.2.0/grid
 FAIL =>    Your Configuration is NOT in Compliance with Oracle Security Alert for CVE-2012-1675 /u01/app/oracle/product/11.2.0/db_1
 INFO =>    Software maintenance best practices
 WARNING => Database Parameter memory_target is not set to the recommended value on RAC1 instance
 INFO =>    Database failure prevention best practices
 WARNING => Database Archivelog Mode should be set to ARCHIVELOG for RAC
 FAIL =>    Primary database is NOT protected with Data Guard (standby database) for real-time data protection and availability for RAC
 WARNING => avahi-daemon process is running


Best Practice checking completed.Checking recommended patches on node01.
---------------------------------------------------------------------------------


Collecting patch inventory on  CRS HOME /u01/app/11.2.0/grid
Collecting patch inventory on ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1
---------------------------------------------------------------------------------
1 Recommended CRS patches for 112030 from /u01/app/11.2.0/grid on node01
---------------------------------------------------------------------------------
Patch#   CRS  ASM    RDBMS RDBMS_HOME                              Patch-Description                          
---------------------------------------------------------------------------------
14275572  no          no  /u01/app/oracle/product/11.2.0/db_1GRID INFRASTRUCTURE PATCH SET UPDATE 11    
---------------------------------------------------------------------------------


---------------------------------------------------------------------------------
1 Recommended RDBMS patches for 112030 from /u01/app/oracle/product/11.2.0/db_1 on node01
---------------------------------------------------------------------------------
Patch#   RDBMS    ASM     type                Patch-Description                      
---------------------------------------------------------------------------------
14275572  no             merge               GRID INFRASTRUCTURE PATCH SET UPDATE 11.
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------


---------------------------------------------------------------------------------
              Clusterware patches summary report
---------------------------------------------------------------------------------
Total patches  Applied on CRS Applied on RDBMS Applied on ASM
---------------------------------------------------------------------------------
1              0              0                0            
---------------------------------------------------------------------------------


---------------------------------------------------------------------------------
              RDBMS homes patches summary report
---------------------------------------------------------------------------------
Total patches  Applied on RDBMS Applied on ASM ORACLE_HOME  
---------------------------------------------------------------------------------
 1              0              0                /u01/app/oracle/product/11.2.0/db_1
---------------------------------------------------------------------------------


=============================================================
                    Node name - node02                              
=============================================================
Collecting - CPU Information
Collecting - CRS active version
Collecting - CRS oifcfg
Collecting - CRS software version
Collecting - Cluster interconnect (clusterware)
Collecting - Huge pages configuration
Collecting - Kernel parameters
Collecting - Linux module config.
Collecting - Maximum number of semaphore sets on system
Collecting - Maximum number of semaphores on system
Collecting - Maximum number of semaphores per semaphore set
Collecting - Memory Information
Collecting - OS Packages
Collecting - Operating system release information and kernel version
Collecting - Oracle Executable Attributes
Collecting - Patches for Grid Infrastructure
Collecting - Patches for RDBMS Home
Collecting - Shared memory segments
Collecting - Table of file system defaults
Collecting - number of semaphore operations per semop system call
Preparing to run root privileged commands  node02.  Please enter root password when prompted.
root@node02's password:
Collecting - CRS user limits configuration
Collecting - CRS user time zone check
Collecting - Interconnect interface config
Collecting - Network interface stats
Collecting - OCFS2 disks
/tmp/root_raccheck.sh: line 29: /sbin/mounted.ocfs2: No such file or directory
Collecting - Root Open File Limit
Collecting - ocsf status
/tmp/root_raccheck.sh: line 39: /etc/init.d/o2cb: No such file or directory
Collecting - root time zone check


Data collections completed. Checking best practices on node02.
--------------------------------------------------------------------------------------


 INFO =>    $CRS_HOME/log/hostname/client directory has too many older log files.
 INFO =>    user_dump_dest has trace files older than 30 days for RAC
 INFO =>    background_dump_dest has files older than 30 days for RAC
 WARNING => Open files limit (ulimit -n) for current user is NOT set to recommended value >= 65536 or unlimited
 INFO =>    audit_file_dest has audit files older than 30 days for RAC
 WARNING => /tmp is NOT on a dedicated filesystem
 WARNING => kernel.shmmax parameter is NOT configured according to recommendation
 WARNING => Open file limit for root user (ulimit -n) is NOT >= 65536 or unlimited
 WARNING => pam_limits NOT configured properly for shell limits
 WARNING => NIC bonding is NOT configured for public network (VIP)
 WARNING => OSWatcher is not running as is recommended.
 INFO =>    Jumbo frames (MTU ~9000) are not configured for interconnect
 FAIL =>    Database parameter DB_BLOCK_CHECKSUM is NOT set to recommended value on RAC2 instance
 FAIL =>    Database parameter DB_LOST_WRITE_PROTECT is NOT set to recommended value on RAC2 instance
 INFO =>    OS parameter vm.swappiness is NOT set to the recommended value
 WARNING => Database parameter DB_BLOCK_CHECKING on PRIMARY is NOT set to the recommended value. for RAC
 WARNING => fast_start_mttr_target has NOT been changed from default on RAC2 instance

 INFO =>    IMPORTANT: Oracle Database Patch 13466801, 13343438, 13348650, 13696251 13919095 or 14275572 PSU is NOT applied to RDBMS Home /u01/app/oracle/product/11.2.0/db_1
 WARNING => kernel parameter rp_filter is set to 1.
 FAIL =>    Your Configuration is NOT in Compliance with Oracle Security Alert for CVE-2012-1675 /u01/app/11.2.0/grid
 FAIL =>    Your Configuration is NOT in Compliance with Oracle Security Alert for CVE-2012-1675 /u01/app/oracle/product/11.2.0/db_1
 WARNING => Database Parameter memory_target is not set to the recommended value on RAC2 instance
 WARNING => avahi-daemon process is running


Best Practice checking completed.Checking recommended patches on node02.
---------------------------------------------------------------------------------


Collecting patch inventory on  CRS HOME /u01/app/11.2.0/grid
Collecting patch inventory on ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1
---------------------------------------------------------------------------------
1 Recommended CRS patches for 112030 from /u01/app/11.2.0/grid on node02
---------------------------------------------------------------------------------
Patch#   CRS  ASM    RDBMS RDBMS_HOME                              Patch-Description                          
---------------------------------------------------------------------------------
14275572  no          no  /u01/app/oracle/product/11.2.0/db_1GRID INFRASTRUCTURE PATCH SET UPDATE 11    
---------------------------------------------------------------------------------


---------------------------------------------------------------------------------
1 Recommended RDBMS patches for 112030 from /u01/app/oracle/product/11.2.0/db_1 on node02
---------------------------------------------------------------------------------
Patch#   RDBMS    ASM     type                Patch-Description                      
---------------------------------------------------------------------------------
14275572  no             merge               GRID INFRASTRUCTURE PATCH SET UPDATE 11.
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------


---------------------------------------------------------------------------------
              Clusterware patches summary report
---------------------------------------------------------------------------------
Total patches  Applied on CRS Applied on RDBMS Applied on ASM
---------------------------------------------------------------------------------
1              0              0                0            
---------------------------------------------------------------------------------


---------------------------------------------------------------------------------
              RDBMS homes patches summary report
---------------------------------------------------------------------------------
Total patches  Applied on RDBMS Applied on ASM ORACLE_HOME  
---------------------------------------------------------------------------------
 1              0              0                /u01/app/oracle/product/11.2.0/db_1
---------------------------------------------------------------------------------




---------------------------------------------------------------------------------
                      CLUSTERWIDE CHECKS
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------

Detailed report (html) - /home/oracle/Desktop/raccheck_RAC_030313_004245/raccheck_RAC_030313_004245.html


UPLOAD(if required) - /home/oracle/Desktop/raccheck_RAC_030313_004245.zip




[oracle@node01 Desktop]$

ORA-01450: maximum key length (3215) exceeded

I was getting the error while rebuilding the index online


alter index INDEX$id rebuild online
                       *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded


The issue was fixed with rebuilding the index without online.

More than 10,000 views of my blog

I am very pleased to see that my blog is viewed more than ten thousand time. I would encourage you all that if you have a question or a feedback then feel free to comment on the post. We learn every day, may be I can learn something new from you.

If there is any issue you are facing then you can also comment on this post with an example and I will pick it and create a new blog and try to help you. 

ORA-01658: unable to create INITIAL extent for segment in tablespace

I was getting the following error while trying to create the index.

create index harvey_1647 on harvey_table( id ) tablespace INDEXES;

ERROR at line 1:

ORA-01658: unable to create INITIAL extent for segment in tablespace INDEXES

The message clearly says that oracle is unable to create the INITIAL extent for the segment in the tablespace INDEXES.

This means that the datafile(s) of INDEXES tablespace are unable to extend because the disk(s) are full.

To fix this move the datafile(s) to the disk which have space or add another datafile which is on the disk having space and it will fix the issue straight way.

Data consistency

This morning I had a confusion regarding the update and insert statement. If we have a table with one million rows (The rows can be any). Table structure

SQL> desc myt
 Name      Null?    Type
 --------- -------- ------------------------
 NO                 NUMBER(8)
 NAME               VARCHAR2(10CHAR)

this table is populated with 

begin
 for i in 1..1000000 loop
   insert into myt values(1,'harvey');
   end loop;
  commit;
end;
/

Means each row have no = 1 and name = harvey.

14:49:07 SQL> select count(*) from myt;
        COUNT(*)
----------------
       1,000,000
Elapsed: 00:00:00.01

Session 1:
if I run update

14:49:56 SQL> update myt set no=2 where no=1;

Session 2:
before this update in session 1 finishes run insert from second session 

14:48:53 SQL> insert into myt values(1,'harvey');
1 row created.
Elapsed: 00:00:00.01
14:50:06 SQL> commit;
Commit complete.
Elapsed: 00:00:00.14
14:50:07 SQL> 

Question: what will be the number of rows updated by session 1.

1,000,000
or 
1,000,001

The correct answer is 1,000,000

14:49:56 SQL> update myt set no=2 where no=1;
1000000 rows updated.
Elapsed: 00:00:17.7114:50:22 SQL> 

and total number of rows in myt will be 1,000,001 because I have commited one row from session 2.


15:09:37 SQL> select count(*) from myt;

        COUNT(*)
----------------
       1,000,001

Elapsed: 00:00:01.33
15:09:45 SQL> 

ORA-23404: refresh group "OWNER"."HONDA_MV" does not exist

I was getting the following message while executing the job to refresh the Matarialized view, I was trying to refresh with following statement

EXEC DBMS_REFRESH.REFRESH('HONDA_MV');
ORA-12012: error on auto execute of job 46
ORA-23404: refresh group "OWNER"."HONDA_MV" does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REFRESH", line 23
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

The issue was fixed by using the following:

EXEC DBMS_MVIEW.REFRESH('HONDA_MV');