Full Database Backup Script

rem ######################################################################
rem Filename:   FullDBBackup.sql
rem Purpose:    Generate script to do a simple on-line database backup.
rem Notes:      Adjust the copy_cmnd and copy_dest variables and run from
rem             sqlplus. Uncomment last few lines to do the actual backup.
rem ######################################################################
set serveroutput on
set trimspool on
set line 500
set head off
set feed off
spool backup.cmd
declare
  copy_cmnd constant varchar2(30) := 'cp';            -- Use "ocopy" for NT
  copy_dest constant varchar2(30) := '/u02/backup/';  -- C:\BACKUP\ for NT
  dbname  varchar2(30);
  logmode varchar2(30);
begin
  select name, log_mode
  into   dbname, logmode
  from   sys.v_$database;

  if logmode <> 'ARCHIVELOG' then
    raise_application_error(-20000, 'ERROR: Database must be in ARCHIVELOG mode!!!');
    return;
  end if;
  dbms_output.put_line('spool backup.'||dbname||'.'|| to_char(sysdate, 'ddMonyy')||'.log');
  --Loop through tablespaces
  for c1 in (select tablespace_name ts from sys.dba_tablespaces where CONTENTS <> 'TEMPORARY')
  loop
    dbms_output.put_line('alter tablespace '||c1.ts||' begin backup;');
    --Loop through tablespaces' data files
    for c2 in (select file_name fil
               from   sys.dba_data_files
               where  tablespace_name = c1.ts)
      loop
        dbms_output.put_line('!'||copy_cmnd||' '||c2.fil||' '||copy_dest);
      end loop;
      dbms_output.put_line('alter tablespace '||c1.ts||' end backup;');
    end loop;
  -- Backup controlfile and switch logfiles
  dbms_output.put_line('alter database backup controlfile to trace;');
  dbms_output.put_line('alter database backup controlfile to '||''''||
  copy_dest||'control.'||dbname||'.'||
  to_char(sysdate,'DDMonYYHH24MI')||''''||';');
  dbms_output.put_line('alter system switch logfile;');
  dbms_output.put_line('spool off');
end;
/
spool off
set head on
set feed on
set serveroutput off
-- Unremark/uncomment the following line to run the backup script
-- @backup.cmd
-- exit

Grant Select/Execute on Database Objects

rem ##################################################################
rem Filename:   GrantSelectOnView.sql
rem Purpose:    Grant Select on Tables/View to the role
rem Date:       09-May-2010
rem Author:     Tamim Khan (Email: tamimdba@gmail.com)
rem ##################################################################
SELECT 'Grant Select on  '||object_name || ' to '||'<Role/User Name>' || ';'
FROM ALL_OBJECTS
Where object_type = '<VIEW/TABLE>'
  and OWNER = '<USER_NAME>';

rem ##################################################################
rem Filename:   GrantExecuteOnPackage.sql
rem Purpose:    Grant Execute on Package/Function/Procedure to the Role
rem Date:       09-May-2010
rem Author:     Tamim Khan (Email: tamimdba@gmail.com)
rem ##################################################################
SELECT 'Grant Execute on  '||object_name || ' to '||'<Role/User Name>' || ';'
FROM ALL_OBJECTS
Where object_type = '<PACKAGE/FUNCTION/PROCEDURE>'
  and OWNER = '<USER_NAME>';

Create Public Synonym

rem ##################################################################
rem Filename:   CreatePublicSynonym.sql
rem Purpose:    Create Public Synonym for a User Objects
rem Date:       09-May-2010
rem Author:     Tamim Khan (Email: tamimdba@gmail.com)
rem ##################################################################

SELECT 'Create or replace public synonym  '||object_name || ' for '
                               || OWNER || '.' || object_name || ';'
FROM ALL_OBJECTS
Where object_type  in ('VIEW','PACKAGE')
and OWNER = '<USER_NAME>';