Archive

Archive for the ‘Database Script’ Category

Kill all oracle sessions

February 23, 2011 Leave a comment
rem ##################################################################
rem Filename:   kill_all_oracle_sessions.sql
rem Purpose:    Kill all oracle sessions for a user
rem Date:       23-Feb-2011
rem Author:     Tamim Khan (Email: tamimdba@gmail.com)
rem ##################################################################

set pagesize 0
spool kill_all.sql
select 'alter system kill session '''||sess.sid||', '||sess.serial#||''';' from v$session sess where username = '<User Name>';
spool off
spool kill_all.log
@kill_all.sql
spool off

To run the script connect oracle as a system user then run the file kill_all_oracle_sessions.sql

@kill_all_oracle_sessions.sql

How to use trigger to check database constant

August 4, 2010 Leave a comment

We can use trigger to check database constant and customize error message. For example we have two tables name Department and Employee. I have written two trigger on Update and Delete on Department table, which will responsible to check referential integrity constraint and customize the error message.

Creating Tables Department and Employee

CREATE TABLE DEPARTMENT
  (
    Department_Id          Number(*,0) Not Null Enable,
    Department_Name        Varchar2(45 Byte) Not Null Enable,
    Department_Description Varchar2(500 Byte),
    Primary Key (Department_Id)
  );

CREATE TABLE EMPLOYEE
  (
    Employee_Name        Varchar2(45 Byte) Not Null Enable,
    Employee_Ssn         Varchar2(45 Byte) Not Null Enable,
    Employee_Phone       Varchar2(45 Byte) Not Null Enable,
    Employee_Cellular    Varchar2(45 Byte) Not Null Enable,
    Employee_Description Varchar2(500 Byte),
    Department_Id        Number(*,0) Not Null Enable,
    Primary Key (Employee_Ssn) ,
    Foreign Key (Department_Id) References Department (Department_Id) Enable
  );

Insert some data on Department and Employee Table

Insert Into Department (Department_Id, Department_Name, Department_Description)
Values ('1001', 'Tecnical', 'Tecnical Department');
Insert Into Department (Department_Id, Department_Name, Department_Description)
Values ('1002', 'Merketing', 'Merketing Department');

Insert Into EMPLOYEE (Employee_Name, Employee_Ssn, Employee_Phone, Employee_Cellular, Department_Id)
Values ('Tamim', '100001', '880175307713', '880175307713', '1001');

Trigger on Delete of Department

Create Or Replace Trigger Td_Department
  After Delete on Department
  FOR EACH row
    DECLARE numrows INTEGER;
  BEGIN
    SELECT COUNT(*) INTO numrows
    FROM Employee
    WHERE  Employee.Department_ID = :old.Department_ID;
    IF (numrows> 0) THEN
      raise_application_error( -20001, 'Cannot DELETE Department because Employee exists.' );
    END IF;
  END;
  /

Test a Delete SQL Statement on Department Table

  Delete
  From Department
  Where Department_Id = 1001 

  /*
  SQL Error: ORA-20001: Cannot DELETE Department because Employee exists.
  */

Trigger on Update of Department

Create Or Replace Trigger Tu_Department
  After Update On Department
  For Each Row
    Declare Numrows Integer;
  Begin
    IF (:old.Department_ID <> :new.Department_ID) THEN
      Select Count(*) Into Numrows   
      FROM Employee
      Where Employee.Department_Id = :Old.Department_Id;
      IF (numrows > 0) THEN
        raise_application_error( -20005, 'Cannot UPDATE Department because Employee exists.' );
      END IF;
    END IF;
  END;
  /

Test a Update SQL Statement on Department Table

  Update Department
  Set Department_Id = 1003
  Where Department_Id = 1001 

  /*
  SQL Error: ORA-20005: Cannot UPDATE Department because Employee exists.
  */

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>';

How to Delete All Objects for a User in Oracle

March 3, 2010 Leave a comment

If you don’t have system level access(Sys or System), and want to clean your schema, the following sql will produce a series of drop statments, which can then be executed.

select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';') from user_objects

Then, I normally purge the recycle bin to really clean things up. To be honest, I don’t see a lot of use for oracle’s recycle bin, and wish i could disable it… but anyway:

purge recyclebin;

Following Script can run the full System

SET feedback off
SET verify off
SET echo off
PROMPT Number of objects IN the schema:
SELECT COUNT(*) FROM user_objects;
PROMPT Finding objects TO DROP

SET termout OFF
SET pages 80
SET heading OFF
SET linesize 120

PURGE RECYCLEBIN;

SPOOL c:\cleanup_schema.sql
SELECT 'SPOOL c:\cleanup_schema.log' FROM dual;
SELECT 'DROP '||object_type||' '|| object_name||  DECODE(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') FROM user_objects ORDER BY object_id DESC;
SELECT 'PURGE RECYCLEBIN;' FROM dual;
SELECT 'PROMPT After cleanup, number of objects remaining in schema' FROM dual;
SELECT 'SELECT COUNT(*) FROM user_objects;' FROM dual;
SELECT 'SPOOL OFF' FROM dual;
SELECT 'EXIT;' FROM dual;

SPOOL OFF

SET termout ON
PROMPT Dropping Objects now ...
-- Execute the sql file created earlier
--@c:\cleanup_schema.sql
EXIT
/

Reff: http://forums.oracle.com/forums/message.jspa?messageID=1057359