PeopleSoft Maintenance Scripts

PeopleSoft Maintenance Script


Grant_sysnonym.sh

export ORACLE_SID=fsprd91
export ORACLE_DIR=/fsprd_adm/fsprd91/scripts
export ORACLE_HOME=/u01/app/oracle/product/11.2
PATH=$ORACLE_HOME/bin:$PATH;export PATH

cd $ORACLE_DIR
date > grants.log

rm $ORACLE_DIR/readonly_grants.sql
rm $ORACLE_DIR/public_synonyms.sql
rm $ORACLE_DIR/recompile_invalid_synonyms.sql

sqlplus sysadm/password@fsprd91 @$ORACLE_DIR/create_readonlyrole_grants.sql
date >> grants.log
sqlplus sysadm/password@fsprd91 @$ORACLE_DIR/create_public_synonyms.sql
date >> grants.log
sqlplus / as sysdba @$ORACLE_DIR/create_recompile_invalid_synonyms.sql
date >> grants.log
mailx -s "FSPRD91 Read Only Grants and Public Synonyms Done" primedba < grants.log

create_readonlyrole_grants.sql

set heading off
set echo off
set feedback off
set pages 0
set lines 100
spool /fsprd_adm/fsprd91/scripts/readonly_grants.sql
select 'grant select on sysadm.' || table_name || ' to readonlyrole;' from user_tables;
select 'grant select on sysadm.' || view_name || ' to readonlyrole;' from user_views;
revoke select on PSWEBPROFNVP from readonlyrole;
select 'grant select on PS_PLD_GEO_TREE_VW to prologisone;' from dual;
select 'grant select on PS_PLD_FUNDNAME_VW to prologisone;' from dual;
spool off;
set echo on
start /fsprd_adm/fsprd91/scripts/readonly_grants.sql
exit;

 
create_public_synonyms.sql
set heading off
set echo off
set feedback off
set pages 0
set lines 100
spool /fsprd_adm/fsprd91/scripts/public_synonyms.sql
select 'CREATE PUBLIC SYNONYM ' || table_name || ' FOR SYSADM.' || table_name || ';'  from user_tables;
select 'CREATE PUBLIC SYNONYM ' || view_name || ' FOR SYSADM.' || view_name || ';'  from user_views;
spool off;
set echo on
start /fsprd_adm/fsprd91/scripts/public_synonyms.sql
exit;

create_recompile_invalid_synonyms.sql
set heading off
set echo off
set feedback off
set pages 0
set lines 100
spool /fsprd_adm/fsprd91/scripts/recompile_invalid_synonyms.sql
SELECT 'ALTER PUBLIC SYNONYM ' || SYNONYM_NAME || ' COMPILE;'
  FROM ALL_SYNONYMS S
  JOIN ALL_OBJECTS O
  ON S.OWNER = O.OWNER
  AND S.SYNONYM_NAME = O.OBJECT_NAME
  WHERE O.OBJECT_TYPE = 'SYNONYM'
  AND S.OWNER = 'PUBLIC'
  AND O.STATUS <> 'VALID';
spool off;
set echo on
start /fsprd_adm/fsprd91/scripts/recompile_invalid_synonyms.sql
exit;