Imp scripts

 Below SQL to alert us 10 days before for PSAPPS password is about to expire.

select OPRID,LASTPSWDCHANGE from sysadm.psoprdefn where oprid='PSAPPS' and sysdate-LASTPSWDCHANGE>=80;

To Find the PeopleSoft concurrent users:

SELECT OPRID, (to_char (LOGINDTTM, 'yyyy-mm-dd hh24:mi:ss.ff3')) AS  "LOGINDTTM",

to_char(LOGINDTTM, 'D') as DAY

FROM PSACCESSLOG

WHERE OPRID NOT IN ('PSADMIN', 'PRCS', 'PSLDAP', 'PUBUSER', 'RUNCONTROLHR', 'RUNCONTROLTIMS', 'RUNCONTROLPAT', 'RUNCONTROLSEC')

AND LOGINDTTM >'18-JUL-19' AND LOGINDTTM <'19-JUL-19'

AND LOGIPADDRESS !='UNKNOWN'

ORDER BY LOGINDTTM ASC;

Database Size :

SQL> select round((sum(bytes)/1048576/1024),2) from v$datafile;

Tablespace Utilization :

#####################################################################################

## CHECK TABLESPACE SIZE  ##

####################################################################################

#!/bin/ksh

sqlplus -s <<!

oracle/$1@$2

set feed off

set linesize 100

set pagesize 200

spool tablespace.alert

SELECT F.TABLESPACE_NAME,

       TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",

       TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",

       TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",

       TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE

FROM   (

       SELECT       TABLESPACE_NAME, 

                    ROUND (SUM (BLOCKS*(SELECT VALUE/1024

                                        FROM V\$PARAMETER 

                                        WHERE NAME = 'db_block_size')/1024)

                           ) FREE_SPACE

       FROM DBA_FREE_SPACE

       GROUP BY TABLESPACE_NAMEa

       ) F,

       (

       SELECT TABLESPACE_NAME,

       ROUND (SUM (BYTES/1048576)) TOTAL_SPACE

       FROM DBA_DATA_FILES

       GROUP BY TABLESPACE_NAMEa

       ) T

WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;

spool off

exit

!

if [ `cat tablespace.alert|wc -l` -gt 0 ]

then

          cat tablespace.alert -l tablespace.alert > tablespace.tmp

          mailx -s "TABLESPACE ALERT for ${2}" PBhaskaran@xfg.com < tablespace.tmp

fi

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

Script to create the synonyms :

-- Run Dynamic SQL to create synonym script

SET HEA OFF;

SET PAGESIZE 0;

SET LINES 150;

SPOOL SYNONYM.SQL

select 'create public synonym '||object_name||' for '||owner||'.'||object_name||';'

from sys.dba_objects where owner = 'SYSADM' and object_type in ('TABLE', 'VIEW');

SPOOL OFF;

-- Run Dynamic SQL to create grant scripts

SET HEA OFF;

SET PAGESIZE 0;

SET LINES 150

SPOOL GRANT.SQL

select 'grant select, update, insert, delete on '||object_name||' to VOLT_SUID;'

from sys.dba_objects where owner = 'SYSADM' and object_type in ('TABLE', 'VIEW');

SPOOL OFF;

prompt

prompt Manually run synonym script 

prompt Enable spool to generate log

prompt @SYNONYM.SQL

prompt

prompt Manually run grant scripts

prompt Enable spool to generate log

prompt @GRANT.SQL