if not able to create user with particular name and getting user or role exist

select role from DBA_roles;

->Take a backup of the role

-> Drop the role ( Which has the same name as user)

-> Create the user

Note : Roles are not listed in all_objects or dba_objects.

Script to clone one user and his privilages to another user

rem --------------------------------------------------------------------------------------------------------
rem Name                : CLONEUSER.SQL
rem
rem Compatible versions : 10.x and above
rem
rem Description         : This script will generate SQL file with DDLs for user creation, associated grants
rem                       and privilegs (roles, system privileges, object privileges and tablespace quotas)
rem
rem prerequisites       : - You must login with DBA role to execute this script
rem                       - Package DBMS_METADATA must be in valid status
rem
rem --------------------------------------------------------------------------------------------------------
rem
----------------------
-- Setting SQL prompt:
----------------------
whenever sqlerror exit;
set serveroutput on long 100000000 pagesize 60 linesize 150 heading off feedback off verify off echo off
--------------------------------------------------
-- Handling Exception for Insufficient privileges:
--------------------------------------------------
declare
        privusercnt     number;
begin
    select count(username)
    into   privusercnt
    from   user_role_privs
    where  granted_role = 'DBA'
    and    username = ora_login_user;
    if ( privusercnt = 0 ) then
      raise_application_error
         (-20001, 'ERROR !'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || 'INFO: Insufficient privileges. You must login with DBA role to execute this script'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || CHR (10)
          || 'Terminating the script...'
         );
    end if;
end;
/
-------------------------------------------------------
-- Handling Exception for Invaid DBMS_METADATA package:
-------------------------------------------------------
declare
        invalidstatcnt  number;
begin
    select count(status)
    into   invalidstatcnt
    from   dba_objects
    where  object_name = 'DBMS_METADATA'
    and    status <> 'VALID';
    if ( invalidstatcnt > 0 ) then
      raise_application_error
         (-20001, 'ERROR !'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || 'INFO: Package SYS.DBMS_METADATA is not in valid status. Please recompile'
          || '      the package and package body, and try again.'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || CHR (10)
          || 'Terminating the script...'
         );
    end if;
end;
/
column dbname noprint new_value db_name
column fdate noprint new_value datestamp

select name dbname,
       to_char(sysdate,'yyyymmddhh24miss') fdate
from   v$database
/
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt ATTENTION:
prompt
prompt (1) A NULL | ENTER for source user will abort the script.
prompt (2) A NULL | ENTER for target user will keep the same source user name as target user.
prompt (3) To abort the script execution, please use CTRL+d
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt
accept sourceuser prompt "Enter source user for generating DDL : "
----------------------------------------------
-- Handling Exception for Invalid source user:
----------------------------------------------
declare
        sourceusercount number;
begin
    select count(username)
    into   sourceusercount
    from   dba_users
    where  username = upper(trim('&&sourceuser'));
    if ( sourceusercount = 0 ) then
      raise_application_error
         (-20002, 'ERROR !'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || 'INFO: Invalid username specified. Please enter a valid username'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || CHR (10)
          || 'Terminating the script...'
         );
    end if;
end;
/
prompt
accept targetuser prompt "Enter target user for generating DDL : "
prompt

------------------------------------------------------
-- Setting Transform Parameters for the DDL Transform:
------------------------------------------------------
begin
    dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
    dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
------------------
-- Generating DDL:
------------------
spool user_ddl_&&sourceuser._&&db_name._&&datestamp..sql

prompt
prompt -- (1). DDL for user creation:
-------------------------------------
prompt
prompt -- Notes:
prompt
prompt -- (1) Please edit the password, default tablespace, temporary tablespace for target user.
prompt -- (2) If not, all these attributes will be the same as source user.
prompt -- (3) If you are using 11g database, please make sure that the encripted passoword dont have any space | line break
prompt
col userddl format a600
select trim(replace (dbms_metadata.get_ddl('USER',upper(trim('&&sourceuser')))
  ,'USER "'||upper(trim('&&sourceuser'))||'"','USER "'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"')) userddl
from dual
where exists
  (select 'x'
  from dba_users drp
  where drp.username = upper(trim('&&sourceuser')))
/
set linesize 110
prompt
prompt -- (2). DDL for tablespace quotas:
-----------------------------------------
prompt
prompt -- Note: - Please verify and edit the target tablespace names, quotas by checking contents.
prompt
select 'ALTER USER '
  ||replace(username,upper(trim('&&sourceuser')),upper(trim(nvl('&&targetuser','&&sourceuser'))))
  ||' QUOTA '
  ||decode(max_bytes,-1,'UNLIMITED',max_bytes)
  ||' ON '
  ||tablespace_name
  ||';' ddl
from dba_ts_quotas
where username = upper(trim('&&sourceuser'))
and  exists
  (select 'x'
  from dba_ts_quotas drp
  where drp.username = upper(trim('&&sourceuser')))
/
/*
----------------------------------------------------------------------------------------------------------
Note:
-----
The below SQL can be useed instead of the above TABLESPACE QUOTA generation command
this will generate the DDL in PL/SQL block format.

select replace (dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA',upper(trim('&&sourceuser')))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_ts_quotas drp
  where drp.username = upper(trim('&&sourceuser')))
/
----------------------------------------------------------------------------------------------------------
*/
prompt
prompt -- (3). DDL for roles:
-----------------------------
select replace (dbms_metadata.get_granted_ddl('ROLE_GRANT',upper(trim('&&sourceuser' )))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_role_privs drp
  where drp.grantee = upper(trim('&&sourceuser')))
/
-- DDL for alter user with default role
--
select replace (dbms_metadata.get_granted_ddl('DEFAULT_ROLE',upper(trim('&&sourceuser' )))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_role_privs drp
  where drp.grantee = upper(trim('&&sourceuser')))
/

prompt
prompt -- (4). DDL for system privileges:
-----------------------------------------
select replace (dbms_metadata.get_granted_ddl('SYSTEM_GRANT',upper(trim( '&&sourceuser')))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_sys_privs drp
  where drp.grantee = upper(trim('&&sourceuser')))
/

prompt
prompt -- (5). DDL for object privileges:
-----------------------------------------
select replace (dbms_metadata.get_granted_ddl('OBJECT_GRANT',upper(trim( '&&sourceuser')))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_tab_privs drp
  where drp.grantee = upper(trim('&&sourceuser')))
/

spool off

prompt
prompt Post steps:
prompt ~~~~~~~~~~~
prompt INFO: (1) Please review, edit and execute the script user_ddl_&&sourceuser._&&db_name._&&datestamp..sql in target database.
prompt INFO: (2) Please remove space | line break from the SQL file, before execution
prompt
------------------------
-- Resetting SQL prompt:
------------------------
prompt
prompt

undefine sourceuser targetuser db_name datestamp
whenever sqlerror continue;
set heading on feedback on verify on echo on
exit;

Script to drop all objects in a schema

drop_all_objects_of_selected_user.sql

 REM name: drop_objects_of_selected_user.sql
  REM usage:@drop_objects_of_selected_user.sql
  REM -------------------------------------------------------------------------
  REM requirements: dba-role
  REM -------------------------------------------------------------------------
  REM -------------------------------------------------------------------------
  REM Purpose:
  REM This script drops all objects (except DATABASE LINK) of a user, which must
  REM be typed in after this script was started. Only one user can be specified
  REM for each run. The user typed in must be different to SYS or SYSTEM.
  REM -------------------------------------------------------------------------
  REM -------------------------------------------------------------------------
select 'Attention ! This script will drop all objects of the user you type in !' ATTENTION from dual;
select 'If you are not sure to finish, so break with ctrl+d !' ATTENTION from dual;
accept user_to_clean prompt "Enter user to drop all his objects: "
set linesize 120 heading off termout off pagesize 2000 recsep 0 trimspool on
set feedback off echo off show off ver off
set serveroutput on
spool drop_all_objects_of_selected_user_l1.sql
declare
        cnt_user_objects number;
        ora_version number;
begin
        dbms_output.enable;
        select max(to_number(substr(version,1,1))) into ora_version from dba_registry;
        IF ora_version > 4 THEN
--              dbms_output.put_line('This is Oracle-version lower than version 10');
                dbms_output.put_line('set feedback off echo off show off ver off');
                dbms_output.put_line('set linesize 120 heading off termout off pagesize 2000 trimspool on');
                dbms_output.put_line('spool drop_all_objects_of_selected_user_l2.sql');
                dbms_output.put_line('select '||'''spool drop_all_objects_of_user_'''||'||'||'''&&user_to_clean'''||'||'||'''.log'''||' from dual;');
                dbms_output.put_line('select '||'''drop table "'''||'||'||'owner'||'||'||'''"."'''||'||'||'table_name'||'||'||'''" cascade constraints;'''||' from dba_tables where owner=upper('||'''&&user_to_clean'''||') AND owner not in ('||'''SYS'''||','||'''SYSTEM'''||');');
                dbms_output.put_line('select '||'''drop '''||'||'||'object_type'||'||'||''' "'''||'||'||'owner'||'||'||'''"."'''||'||'||'object_name'||'||'||'''";'''||' from dba_objects where owner=upper('||'''&&user_to_clean'''||') AND owner not in ('||'''SYS'''||','||'''SYSTEM'''||') AND object_type not in ('||'''TABLE'''||','||'''DATABASE LINK'''||','||'''INDEX'''||','||'''TRIGGER'''||','||'''PACKAGE BODY'''||','||'''LOB'''||');');
                dbms_output.put_line('select '||'''spool off'''||' from dual;');
                dbms_output.put_line('spool off');
                dbms_output.put_line('@@drop_all_objects_of_selected_user_l2.sql');
        ELSE
--              dbms_output.put_line('This is Oracle-version 10. Note: Objects are dropped with purge-option!');
                dbms_output.put_line('set feedback off echo off show off ver off');
                dbms_output.put_line('set linesize 120 heading off termout off pagesize 2000 trimspool on');
                dbms_output.put_line('spool drop_all_objects_of_selected_user_l2.sql');
                dbms_output.put_line('select '||'''spool drop_all_objects_of_user_'''||'||'||'''&&user_to_clean'''||'||'||'''.log'''||' from dual;');
                dbms_output.put_line('select '||'''drop table "'''||'||'||'owner'||'||'||'''"."'''||'||'||'table_name'||'||'||'''" cascade constraints purge;'''||' from dba_tables where owner=upper('||'''&&user_to_clean'''||') AND owner not in ('||'''SYS'''||','||'''SYSTEM'''||');');
                dbms_output.put_line('select '||'''drop '''||'||'||'object_type'||'||'||''' "'''||'||'||'owner'||'||'||'''"."'''||'||'||'object_name'||'||'||'''";'''||' from dba_objects where owner=upper('||'''&&user_to_clean'''||') AND owner not in ('||'''SYS'''||','||'''SYSTEM'''||') AND object_type not in ('||'''TABLE'''||','||'''DATABASE LINK'''||','||'''INDEX'''||','||'''TRIGGER'''||','||'''PACKAGE BODY'''||','||'''LOB'''||');');
                dbms_output.put_line('select '||'''spool off'''||' from dual;');
                dbms_output.put_line('spool off');
                dbms_output.put_line('@@drop_all_objects_of_selected_user_l2.sql');
        END IF;
        EXCEPTION
        WHEN OTHERS THEN
                dbms_output.put_line(SQLERRM);
END;
/
spool off
@@drop_all_objects_of_selected_user_l1.sql
set termout on
set serveroutput on
declare
        cnt_user_objects number;
begin
dbms_output.enable;
select count(*) into cnt_user_objects from dba_objects where owner=upper('&&user_to_clean');
        IF cnt_user_objects > 0
           THEN
                dbms_output.put_line('Please restart this script again ! There are objects left in schema '||'&&user_to_clean'||' !');
           ELSE
                dbms_output.put_line('All objects of user '||'&&user_to_clean'||' were dropped !');
        END IF;
        EXCEPTION
        WHEN OTHERS THEN
                dbms_output.put_line(SQLERRM);
END;
/
!rm drop_all_objects_of_selected_user_l1.sql
!rm drop_all_objects_of_selected_user_l2.sql

##########################
                                                                                V1-V3)
backup_abc.log
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > RMAN-06005: connected to target database: QMCRMDBP (DBID=1706730871)
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > RMAN-06008: connected to recovery catalog database
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 2>
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT >
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 19>
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT >
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 20>
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 23>
2013-01-07 02:48:16 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 02:53:31 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 02:58:46 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:04:01 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:09:16 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:14:31 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:19:46 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:25:01 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:30:15 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:35:30 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:40:45 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:46:00 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-20029: cannot make a snapshot control file
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-00571: ===========================================================
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-00571: ===========================================================
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-03002: failure of configure command at 01/07/2013 03:46:21
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-03014: implicit resync of recovery catalog failed
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-03009: failure of full resync command on default channel at 01/07/2013 03:46:21
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > ORA-00230: operation disallowed: snapshot control file enqueue unavailable

#######################

Solution:

SQL> SELECT vs.sid, vs.username, vs.program, vs.module, TO_CHAR(vs.logon_time, 'DD-MON-YYYY HH24:MI:SS')
FROM v$session vs, v$enqueue_lock vel
WHERE vs.sid = vel.sid AND vel.type = 'CF' AND vel.id1 = 0 AND vel.id2 = 2
  2    3    4
SQL> /
          SID USERNAME                       PROGRAM              MODULE                         TO_CHAR(VS.LOGON_TIME,'DD-MON
---------- ------------------------------ -------------------- ------------------------------ -----------------------------
        24 SYS                            rman@s96qmcr0d0 (TNS backup full datafile           24-NOV-2012 06:27:36
                                           V1-V3)


SQL> select SID,SERIAL#,USERNAME,COMMAND,PROCESS,PROGRAM from v$session where sid=24;
 
       SID    SERIAL# USERNAME                COMMAND PROCESS                  PROGRAM              LOGON_TIM
---------- ---------- -------------------- ---------- ------------------------ -------------------- ---------
        24      10203 SYS                           0 29489                    rman@s96qmcr0d0 (TNS 24-NOV-12V1-V3)
       
       
SQL> alter system kill session '24,10203';
alter system kill session '24,10203'
*
ERROR at line 1:
ORA-00031: session marked for kill

      SID    SERIAL# USERNAME                          COMMAND PROCESS                  PROGRAM              STATUS
---------- ---------- ------------------------------ ---------- ------------------------ -------------------- --------
        24      10203 SYS                                     0 29489                    rman@s96qmcr0d0 (TNS KILLED

SELECT P.SPID, S.SID, S.SERIAL#
FROM V$PROCESS P, V$SESSION S
WHERE P.ADDR = S.PADDR
AND S.SID =24
-> Kill the process id from os level

-> Resync catalog;
-> Triger fresh backup.

RMAN error 00230 and 00230 to backup.

Issue:


##########################
                                                                                V1-V3)
backup_abc.log
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > RMAN-06005: connected to target database: QMCRMDBP (DBID=1706730871)
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > RMAN-06008: connected to recovery catalog database
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 2>
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT >
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 19>
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT >
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 20>
2013-01-07 02:43:19 +0100( 22310. 0) SESSION  OUT > 23>
2013-01-07 02:48:16 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 02:53:31 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 02:58:46 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:04:01 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:09:16 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:14:31 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:19:46 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:25:01 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:30:15 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:35:30 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:40:45 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:46:00 +0100( 22310. 0) SESSION  OUT > RMAN-08512: waiting for snapshot control file enqueue
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-20029: cannot make a snapshot control file
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-00571: ===========================================================
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-00571: ===========================================================
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-03002: failure of configure command at 01/07/2013 03:46:21
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-03014: implicit resync of recovery catalog failed
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > RMAN-03009: failure of full resync command on default channel at 01/07/2013 03:46:21
2013-01-07 03:46:21 +0100( 22310. 0) SESSION  OUT > ORA-00230: operation disallowed: snapshot control file enqueue unavailable

#######################

Solution:
SELECT vs.sid, vs.username, vs.program, vs.module, TO_CHAR(vs.logon_time, 'DD-MON-YYYY HH24:MI:SS') FROM v$session vs, v$enqueue_lock vel WHERE vs.sid = vel.sid AND vel.type = 'CF' AND vel.id1 = 0 AND vel.id2 = 2



          SID USERNAME                       PROGRAM              MODULE                         TO_CHAR(VS.LOGON_TIME,'DD-MON
---------- ------------------------------ -------------------- ------------------------------ -----------------------------
        24 SYS                            rman@s96qmcr0d0 (TNS backup full datafile           24-NOV-2012 06:27:36
                                           V1-V3)


select SID,SERIAL#,STATUS,USERNAME,COMMAND,PROCESS,PROGRAM from v$session where sid=121;
 
       SID    SERIAL# USERNAME                COMMAND PROCESS                  PROGRAM              LOGON_TIM
---------- ---------- -------------------- ---------- ------------------------ -------------------- ---------
        24      10203 SYS                           0 29489                    rman@s96qmcr0d0 (TNS 24-NOV-12V1-V3)
       
       
SQL> alter system kill session '24,10203';
alter system kill session '24,10203'
*
ERROR at line 1:
ORA-00031: session marked for kill

select SID,SERIAL#,STATUS,USERNAME,COMMAND,PROCESS,PROGRAM from v$session where sid=121;

      SID    SERIAL# USERNAME                          COMMAND PROCESS                  PROGRAM              STATUS
---------- ---------- ------------------------------ ---------- ------------------------ -------------------- --------
        24      10203 SYS                                     0 29489                    rman@s96qmcr0d0 (TNS KILLED

SELECT P.SPID, S.SID, S.SERIAL#
FROM V$PROCESS P, V$SESSION S
WHERE P.ADDR = S.PADDR
AND S.SID =24
-> Kill the process id from os level

-> Resync catalog;

-> Trigger fresh backup.