Oracle DBA Scripts

Below are some Oracle scripts used in day to day activities note that below script will generate sql statements and then we need to run those statements

Script to capture object level privileges for schema

set head off

set verify off

set feedback off

set echo off

Select 'grant '||privilege||' on '||owner||'."'||table_name||'" to '||grantee||';' from dba_tab_privs where grantor in ('&SCHEMA_NAME');

Script to capture system level privileges for schema

select 'grant '||privilege||' to '||grantee||';' from dba_sys_privs where grantee in ('&SCHEMA_NAME');

Compile INVALID objects in particular schema

select 'alter '||object_type||' ' ||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and owner in (‘&SCHEMA_NAME’);

To refresh SELECT Role

select 'Grant select on &SCHEMA_NAME.'||object_name|| ' to SELECT_ROLE_NAME;' from dba_objects where owner='&SCHEMA_NAME' and object_type in ('TABLE','VIEW','SYNONYM','MATERIALIZED VIEW');

To refresh UPDATE role

select 'Grant insert,update,delete on SCHEMA_NAME.'||object_name|| ' to UPDATE_SCHEMA_NAME;' from dba_objects where owner=’SCHEMA_NAME' and object_type in ('TABLE','VIEW','SYNONYM','MATERIALIZED VIEW')

union

select 'Grant execute on SCHEMA_NAME.'||object_name|| ' to UPDATE_ROLE_NAME;' from dba_objects where owner='SCHEMA_NAME' and object_type in ('PROCEDURE','FUNCTION','PACKAGE');

To Enable and Disable constraints on a table

select 'alter table ' || owner || '.' ||'"'|| TABLE_NAME||'"'|| ' disable constraint ' ||'"'|| CONSTRAINT_NAME ||'"'|| ';' from dba_constraints where OWNER='SCHEMA_NAME';

select 'alter table ' || owner || '.' ||'"'|| TABLE_NAME||'"'|| ' enable constraint ' ||'"'|| CONSTRAINT_NAME ||'"'|| ';' from dba_constraints where OWNER='SCHEMA_NAME';

To truncate tables of a schema

select 'truncate TABLE ' ||owner||'.'||object_name||';' from dba_objects where owner='SCHEMA_NAME' and object_type='TABLE'  order by 1 desc;

To kill the sessions

db Level

select 'alter system kill session '''||sid ||','||serial#||''';' from v$session where sid in (2331,1552,261);

OS level

select 'kill -9 '||p.spid from v$session s, v$process p where s.paddr=p.addr and s.sid in (2331,1552,261);

Synonym creation on all the tables of particular schema

select 'create synonym '||object_name||' for ' ||owner||'.'||object_name||';' from dba_objects where owner in ('SCHEMA_NAME') and object_type in ('TABLE');

I will keep this article updated with new scripts. Keep eye on this.