backup details script

---> Sort by Backup Type Values (ARCH,INC,REC) in GB


set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col END_TIME for a20
col START_TIME for a20
col DOW for a15
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp, x.device_type,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:Ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:Ss') end_time,
  (j.output_bytes/1024/1024/1024) output_gbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp, d.device_type,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp, d.device_type) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
and j.input_type like '%&BACKUP_TYPE%'
order by j.start_time
/

ASM Installation

  How To:Setup Oracle ASM 11gR2 on Oracle Enterprise Linux
  ---------------------------------------------------------

Software Used
---------------
Vmware
Oracle Enterprise Linux:
Oracle Database 11g Release2(11.2.0.4.0) For Linux x86-64
Oracle Grid Infracture 11g Release 2(11.2.0.4.0) for Linux x86-64

What Will We do?
---------------
Setup Oracle grid Infracture for standalone "ASM"                                -Under User grid
Setup Oracle Database                                                            -Under User oracle


Packages
----------
We will need to install Prerequisites packages for oracle database               -Under User root

We will need to install Prerequisites packages for ASM for Oracle Linux 6         -Under User root

get them from:
http://www.oracle.com/technetwork/server-storage/linux/downloads

You have to choose the right rpm for your kernel

--Check your Kernel
uname -rm

Asm Packages
------------
--oracleasm-support
--oracleasm
--oracleasmlib

rpm -ivh "package name"



--Create groups for database
groupadd dba
groupadd oinstall
groupadd oper
------
--Create groups for ASM
groupadd asmadmin
groupadd asmdba
groupadd asmoper


--Create Users
useradd -g oinstall -G dba,oper,asmdba -d /home/oracle oracle                   --Oracle User
useradd -g oinstall -G dba,asmadmin,asmdba,asmoper -d /home/grid grid           --Grid User

--Set passwords for users created above
passwd oracle            
passwd grid

--Make directory structures for database and grid infracture installation
mkdir -p /u01/app/oracle/product/11.2.0/db_home1
mkdir -p /u01/app/grid/product/11.2.0/grid

--Change ownership for /u01 mount point to oracle and grid for installation
chown -R oracle:oinstall /u01
chown -R grid:oinstall /u01/app/grid/product/11.2.0/grid

--Change permision to /u01 to read,write,execute
chmod -R 775 /u01



--Set profile in .bash_profile from oracle user
export ORACLE_SID=ORCL
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/11.2.0/db_home1
export PATH=$ORACLE_HOME/bin:$PATH

--Set profile in .bash_profile from grid user
export ORACLE_SID=+ASM
export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=/u01/app/grid/product/11.2.0/grid
export PATH=$ORACLE_HOME/bin:$PATH

--Create Linux Hard disk using VMware "Adding 3 Hard Disk minimum of 5 GB"

--To avoid rebooting the machine to mount the new Hard disks
echo "- - -" > /sys/class/scsi_host/host0/scan
echo "- - -" > /sys/class/scsi_host/host1/scan
echo "- - -" > /sys/class/scsi_host/host2/scan
echo "- - -" > /sys/class/scsi_host/host3/scan


--If still not dected reboot the server

--List the new Hard diske
fdisk -l

Disk /dev/sdb: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xc9c7323b

Disk /dev/sdc: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xdac64d98


--Partition the newly added disk to  primary partitions          
fdisk /dev/sdb
fdisk /dev/sdc
fdisk /dev/sdd


--List the partitioned disks
fdisk -l

--Change the ownership to user grid on the partitioned disks
chown grid:asmadmin /dev/sdb1
chown grid:asmadmin /dev/sdc1
chown grid:asmadmin /dev/sdd1

--Change the permissions on the partitioned disks
chmod 660 /dev/sdb1
chmod 660 /dev/sdc1
chmod 660 /dev/sdd1


--Configure ASM and Create ASM Disks

--Configure
ll /dev/sd*
/etc/init.d/oracleasm configure
/etc/init.d/oracleasm scandisks
/etc/init.d/oracleasm listdisks
/etc/init.d/oracleasm status

Usage: /etc/init.d/oracleasm {start|stop|restart|enable|disable|configure|createdisk|deletedisk|querydisk|listdisks|scandisks|status}


--Create ASM Disk from the partioned disks
/etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
/etc/init.d/oracleasm createdisk VOL2 /dev/sdc1
/etc/init.d/oracleasm createdisk VOL3 /dev/sdd1


---Check once the disks are created
[grid@oracle1 grid]$ ls -lrt /dev/oracleasm/disks/*
brw-rw----. 1 grid asmadmin 8, 17 Jan 19 18:59 /dev/oracleasm/disks/VOL1
brw-rw----. 1 grid asmadmin 8, 33 Jan 19 18:59 /dev/oracleasm/disks/VOL2
brw-rw----. 1 grid asmadmin 8, 49 Jan 19 19:00 /dev/oracleasm/disks/VOL3





============================================================================================================================

Display issue:
--------------

1> execute runinstaller and select no
2>new terminal type xhost+
3>new terminal execute runinstaller again


--> If still error make the below entry in hosts file with the servername

Server name:oracle1

[root@oracle1 ~]# cat /etc/hosts
127.0.0.1   oracle1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         oracle1 localhost localhost.localdomain localhost6 localhost6.localdomain6


=================================================================================================================================

Executing sql script to run in background

lvpu7:/opt/oraprd # cat rebuild_index.sh

#!/bin/ksh

sqlplus "/ as sysdba" << EOF

@/opt/oraprd/rebulid.sql

exit;

EOF

lvpu7:/opt/oraprd # cat rebulid.sql

spool index_rebuild.log

set echo on time on timing on

alter index ELCC.CK_CLIENT_MEMBER_ID rebuild nologging;

alter index ELCC.CLGDR_X1 rebuild nologging;

alter index ELCC.CLNT_CONFIDENCE_SCORE rebuild nologging;

spool off

nohup sh rebuild_index.sh &

==================

#!/bin/ksh

sqlplus <<-eof o:p="">

/ as sysdba

set echo on timing on

exec dbms_stats.gather_schema_stats(ownname=>'CASHPOOL',options=>'gather auto',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>6);

EOF

nohup sh gather.sh &

SH

Hi,

Shift : Morning

Handover by : Harsha

Takeover by : Sayeed

1) Incidents Ticket Details:

No. of WIP Ticket(s) : 5

No. of SLEEP Ticket(s) : 0

No. of Solved Ticket(s) : 0

No. of Escalated Ticket(s) in Bin : 45

No. of Customer Ticket(s) : 1

No. of tickets with priority 1,2 : 0

Remarks:

TT 18524622 - SR 3-6350571721 - Priority 2 - Please monitor.

TT 18518010 - Weekly copy failed,Restore the database with Rman.Please refer TT.

2) Task Ticket Details:

No. of ticket(s) in progress :TT0018519980 -> Backup is in progress Reffer TT log for details

No. of escalated Ticket(s)in Bin :

Remarks:

3) Vendor cases:

4) Additional Information:

5) Escalation to L2 group:

Incident tickets :

Task tickets :

Problem tickets :

Remarks:

6) Message to MOD group:Nil

　

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