done-Unix-sysadmin-scripts.txt
*********Abbreviations******************
<CT>Means Customer of Custome Schema name,<PROD> means product short code eg IEX for collections
*******************Download Functions*******************************
#!/bin/sh
FNDLOAD $1 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct $2_FN.ldt FUNCTION
FUNCTION_NAME="$2"
****Usage
download_function.sh $1 <CT>_INT_RENT_FUN
download_function.sh $1 <CT>_<PROD>_LA_CONTRACT_TERMSCOND
download_function.sh $1 <CT>_<PROD>_AST_ADDL_INFO
********************Download Lookups********************************
#
#!/bin/sh
FNDLOAD $1 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct $2_LT.ldt FND_LOOKUP_TYPE
APPLICATION_SHORT_NAME="$3" LOOKUP_TYPE="$2"
*****Usage
download_lookup.sh $1 <CT>_<PROD>_TC_LINKS <PROD>
download_lookup.sh $1 <CT>_VSC_ROLE_ALLOCATION
*********************Download Message*****************************
#!/bin/sh
FNDLOAD $1 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct $2_MSG.ldt FND_NEW_MESSAGES
APPLICATION_SHORT_NAME="$3" MESSAGE_NAME="$2"
****Usage
download_message.sh $1 <CT>_<PROD>_EXIST_ACCOUNT_NUM <PROD>
download_message.sh $1 <CT>_<PROD>_VALID_ACCOUNT_NUM <PROD>
download_message.sh $1 <CT>_<PROD>_VALID_BUSIN_NAME <PROD>
download_message.sh $1 <CT>_INT_RENT_AMT_MSG BOLINF
download_message.sh $1 <CT>_INT_RENT_PERIOD BOLINF
download_message.sh $1 <CT>_INT_RENT_REQ_MSG BOLINF
************************Download Profiles*************************
#!/bin/sh
FNDLOAD $1 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct $2_PO.ldt PROFILE
PROFILE_NAME="$2" APPLICATION_SHORT_NAME="$3"
*****Usage
#download_profile.sh $1 <CT>_<PROD>_LA_ASSET_RG BOLINF
download_profile.sh $1 <CT>_<PROD>_VSC_UPDATABLE BOLINF
download_profile.sh $1 <CT>_VENDOR_COMM_STREAM BOLINF
download_profile.sh $1 <CT>_VENDOR_COMM_STREAM2 BOLINF
*****************************Download Programs*******************
#!/bin/sh
FNDLOAD $1 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct $2_CP.ldt PROGRAM
Page 1done-Unix-sysadmin-scripts.txt
APPLICATION_SHORT_NAME="$3" CONCURRENT_PROGRAM_NAME="$2"
*****Usage
#download_program.sh $1 <CT>_<PROD>_PAY_EVRGRN_DISB BOLINF
#download_program.sh $1 <CT>_EVERGREEN_BILLING BOLINF
#download_program.sh $1 <CT>_PAY_SERVICE_PASSTHROUGH
***************************Download Regions************************
#!/bin/sh
p=`tnsping $TWO_TASK| grep Attempting|awk '{print $4}'`
database_name='(DESCRIPTION='$p'(CONNECT_DATA=(SID='$TWO_TASK')))'
java oracle.apps.ak.akload apps $3 THIN "$database_name" DOWNLOAD $1.jlt GET CUSTOM_REGION $2 $1
******Usage
download_region.sh <CT>_<PROD>_RGP <PROD> $1
download_region.sh <CT>_SEARCH <PROD> $1
download_region.sh <CT>_CASES_ALL_R BOLINF $1
****************************Download Rule*************************
#!/bin/sh
FNDLOAD $1 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct $2_DFF.ldt DESC_FLEX
P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="<PROD>"
DESCRIPTIVE_FLEXFIELD_NAME="<PROD> Rule Developer DF" P_CONTEXT_CODE="$2"
****Usage
# interim rent
download_rule.sh $1 <CT>CNTINTRENT
download_rule.sh $1 <CT>VENINTRENT
***************************Download Value set************************
#!/bin/sh
FNDLOAD $1 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct $2_VS.ldt VALUE_SET
FLEX_VALUE_SET_NAME="$2"
*****Usage
download_valueset.sh $1 <CT>_NUMBER_38_2
#download_valueset.sh $1 <CT>_<PROD>_EGS_STY_TYPE
#download_valueset.sh $1 <CT>_PREPAYMENT_OFFSET
*******************************Replace strings in files*************
#!/bin/sh
# Perform a global search for string 'aaaaa' application ID and replace on each of several files
# File names listed explicitly
echo "Enter Application ID : "
read APPL_ID
for text_file in <CT>_ALL_CASES.jlt <CT>_EXISTING_CASES.jlt <CT>_UNASSIGNED_CASES.jlt
do
echo "Editing file $text_file"
sed -e s/aaaaa/$APPL_ID/g $text_file > temp
mv -f temp $text_file
done
******************************Upload dff*****************************
FNDLOAD $1 O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct $2
Page 2done-Unix-sysadmin-scripts.txt
******************************Upload form****************************
FNDLOAD $1 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct $2
*****************************Upload Functions************************
FNDLOAD $1 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct $2
*****************************Upload jtf grids************************
FNDLOAD $1 0 Y UPLOAD $JTF_TOP/patch/115/import/jtfgrid.lct $2
*****************************Upload Lookup***************************
FNDLOAD $1 O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct $2
*****************************Upload Messages*************************
FNDLOAD $1 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct $2
*****************************Upload Profiles*************************
FNDLOAD $1 O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct $2
*****************************Upload programs*************************
FNDLOAD $1 O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $2
*****************************Upload Region***************************
#!/bin/sh
p=`tnsping $TWO_TASK| grep Attempting|awk '{print $4}'`
database_name='(DESCRIPTION='$p'(CONNECT_DATA=(SID='$TWO_TASK')))'
java oracle.apps.ak.akload apps $1 THIN "$database_name" UPLOAD $2 UPDATE
AMERICAN_AMERICA.WE8ISO8859P1
*****************************Upload Valueset***************************
FNDLOAD $1 O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct $2
*****************************Upload WorkFlow Notification**************
#!/bin/sh
p=`tnsping $TWO_TASK| grep Attempting|awk '{print $4}'`
database_name='(DESCRIPTION='$p'(CONNECT_DATA=(SID='$TWO_TASK')))'
pp=`echo $p| awk '{gsub(/=/, " "); print}'| awk '{gsub(/)/, " "); print}'`
DB_HOST=`echo $pp|awk '{print $5}'`
DB_PORT=`echo $pp|awk '{print $7}'`
java oracle.apps.fnd.wf.WFXLoad -u apps $1 $DB_HOST:$DB_PORT:$TWO_TASK thin US $2
*****Usage
WFLOAD $1 0 Y FORCE $2
*********************SQL Loader Sample*************************************
LOAD DATA
INFILE '$XBOL_TOP/bin/TD9246.csv'
BADFILE '$XBOL_TOP/bin/TD9246.bad'
INSERT
INTO TABLE <CT>_9185_MJE_CRE
fields terminated by ',' trailing nullcols
(
REC_ID recnum,
Contract_Number,
Transaction_Number,
Description,
Amount ,
Line_Number,
line_Description,
Template_Name,
Page 3done-Unix-sysadmin-scripts.txt
Line_Amount,
Stream_Type,
Purpose )
***********saved in csv format with following sample data.*******************
001.60000656.0001 EVG001 Correct Evergreen Erroneous Expiry -101.56 1 Correct
Evergreen Erroneous Expiry Termination <CT> Evergreen Rent Accrual -101.56
******************Other sample of SQL loader script************************
LOAD DATA
INFILE '$XBOL_TOP/bin/TD8657.csv'
REPLACE
INTO TABLE <CT>_TD8657_STAGE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY'"'
TRAILING NULLCOLS
( CONTRACT_NUMBER CHAR
, MLA_CONTRACT_NUMBER CHAR NULLIF MLA_CONTRACT_NUMBER = BLANKS
"TRIM(:MLA_CONTRACT_NUMBER)"
, PRIVATE_LABEL CHAR NULLIF PRIVATE_LABEL = BLANKS "T RIM(:PRIVATE_LABEL)"
, STATUS_CODE CONSTANT 'L'
)
LOAD DATA
INFILE '$XBOL_TOP/bin/TD8982.csv'
REPLACE
INTO TABLE <CT>_VENDOR_AMENDMENT_TBL
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY'"'
TRAILING NULLCOLS
(
CUR_VENDOR_NO NULLIF (CUR_VENDOR_NO="NULL"),
CUR_VENDOR_NAME CHAR,
CUST_POST_CODE_FROM CHAR NULLIF CUST_POST_CODE_FROM = BLANKS,
CUST_POST_CODE_TO CHAR NULLIF CUST_POST_CODE_TO = BLANKS,
CONTRACT_NUMBER CHAR,
NEW_VENDOR_NO NULLIF (NEW_VENDOR_NO="NULL"),
NEW_VENDOR_NAME CHAR,
NEW_VENDOR_SITE CHAR,
NEW_VPA CHAR NULLIF NEW_VPA = BLA NKS,
CUSTOMER CHAR NULLIF CUSTOMER = BL ANKS,
CUSTOMER_ACCOUNT CHAR NULLIF CUSTOMER_ACCOUNT = BLANKS,
CUSTOMER_BILL_TO_SITE_ID NULLIF (CUSTOMER_BILL_TO_SITE_ID="NULL"),
PAYMENT_METHOD CHAR NULLIF PAYMENT_METHOD = BLANKS,
BANK_ACCOUNT_NAME CHAR NULLIF BANK_ACCOUNT_NAME = BLANKS,
INVOICE_FORMAT CHAR NULLIF INVOICE_FORMAT = BLANKS,
REVIEW_INVOICE CHAR NULLIF REVIEW_INVOICE = BLANKS,
REASON_FOR_REVIEW CHAR NULLIF REASON_FOR_REVIEW = BLANKS,
REVIEW_UNTIL_DATE CHAR NULLIF REVIEW_UNTIL_DATE = BLANKS,
STATUS CONSTANT 'L'
)
*********************************************Patch level***********************
select decode(bug_number,
2194408, '11i.<PROD>.A',
2269974, '11i.<PROD>.B',
2375622, '11i.<PROD>.C',
2420911, '11i.<PROD>.D',
2698797, '11i.<PROD>.E',
Page 4done-Unix-sysadmin-scripts.txt
3101250, '11i.<PROD>.F',
3981693, '11i.<PROD>.G',
4143569, 'GRUP.1',
4298372, 'GRUP.2',
4487651, 'GRUP.3',
4956746, 'GRUP.4',
4551977, '11i.<PROD>.H',
5088908, 'HRUP.1',
5196112, 'HRUP.2',
5350898, 'HRUP.3',
5701628, 'HRUP.4',
6370848, 'HRUP.5'),
to_char(bug_number)
, creation_date
from apps.ad_bugs
where bug_number in
('2194408','2269974','2375622','2420911','2698797','3101250','3981693','4143569','4298372','4487651','4956746','
4551977','5088908'
,'5196112','5350898','5701628','6370848')
order by creation_date;
*******************************************Fulfilment*********************
SELECT a.server_instance_id "Server Instance Id",
a.server_group_id "Server Group Id", a.dns_name "DNS Name",
a.status_code "statusCode",c.name "groupName"
FROM amf_server_instances_b a, amf_server_instances_tl b , amf_server_groups_tl c
WHERE (a.server_instance_id = b.server_instance_id
AND c.server_group_id = a.server_group_id
AND c.language(+) = userenv('lang')
AND b.language(+) = userenv('lang') AND a.status_code <> 'E' )
*********************Long Running*****************************************
. /pgecai/oracle/product/920/PGECAI_auohsgeca34.env
sqlplus -s <<!
olmuser/olmuser
spool longrun.txt
set feedback on
set linesize 1000
col RequestName format a35
select request_id,(d.user_concurrent_program_name) RequestName,
round((a.actual_completion_date - a.actual_start_date)* 86400/60)"Minutes",
to_char(actual_start_date,'DD-Mon-YYYY HH24:MI:SS')"Start Date",
to_char(actual_completion_date,'DD-Mon-YYYY HH24:MI:SS')"End
Date",status_code,a.phase_code,c.user_name
from apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.fnd_user
c,apps.fnd_concurrent_programs_vl d
where a.concurrent_program_id =b.concurrent_program_id
and (a.concurrent_program_id,a.request_id,a.actual_completion_date)
in(select concurrent_program_id,request_id,max(actual_completion_date) from apps.fnd_concurrent_requests
where to_char(actual_completion_date,'DD-Mon-YYYY HH24:MI:SS')
between to_char(sysdate-12/24,'DD-Mon-YYYY HH24:MI:SS')
and to_char(sysdate,'DD-Mon-YYYY HH24:MI:SS')
group by parent_request_id,concurrent_program_id,request_id)
and a.actual_start_date is not null
and a.actual_completion_date is not null
and a.requested_by = c.user_id
and b.concurrent_program_name = d.concurrent_program_name
and ((a.actual_completion_date - a.actual_start_date)* 86400/60) > 90
Page 5done-Unix-sysadmin-scripts.txt
order by actual_start_date;
spool off
exit;
!
if [ `cat longrun.txt|wc -l` -gt 0 ]
then
mailx -s "Requests Running for more than 1 1/2 hr" vaseem.mohammed@ge.com < longrun.txt
fi
rm longrun.txt
*****************************Profile changes***********************************
. /pgecai/oracle/product/920/PGECAI_auohsgeca34.env
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
spool profile_values.txt
set feed off
set linesize 150
set pagesize 1000
col ProfileLevel format A10
col Non-SiteDesc format A15
col UserName format A10
col ProfileOptionName format A30
col Value format A5
col LastUpdatedBy format A13
col LastUpdatedOn format A15
SELECT DECODE(level_id,10001,'Site',10002,'Application',10003,'Responsibility',10004,'USER') "ProfileLevel"
,
DECODE(level_id,10001,NULL,10002,fa.application_name,10003,fr.responsibility_name,10004,fu.user_name)
"Non-SiteDesc"
, fu.user_name "UserName"
, fpov.user_profile_option_name "ProfileOptionName"
, fpova.profile_option_value "Value"
, fu2.user_name "LastUpdatedBy"
, to_char(fpova.LAST_UPDATE_DATE,'mm/dd/yy hh24:mi') "LastUpdatedOn"
FROM apps.fnd_profile_options_vl fpov
, apps.fnd_profile_option_values fpova
, apps.fnd_application_tl fa -- table inclusion when looking at application joins
, apps.fnd_responsibility_tl fr -- table inclusion when looking at responsibility joins
, apps.fnd_user fu -- table inclusion when looking at user joins
, apps.fnd_user fu2
WHERE fpov.application_id = fpova.application_id
AND fpov.profile_option_id = fpova.profile_option_id
AND start_date_active <= SYSDATE
AND NVL(end_date_active,SYSDATE) >= SYSDATE
AND (site_enabled_flag = 'Y' OR app_enabled_flag = 'Y'
OR resp_enabled_flag = 'Y' OR user_enabled_flag = 'Y')
AND fpova.level_value = fa.application_id (+) -- join for application values
AND fpova.level_value = fr.responsibility_id (+) -- join for responsibility values
AND fpova.level_value = fu.user_id (+) -- join for user values
AND fpova.last_updated_by = fu2.user_id (+) -- join for update by user values
and trunc(fpova.LAST_UPDATE_DATE) = trunc(sysdate-1)
ORDER BY "ProfileOptionName", "ProfileLevel";
Page 6done-Unix-sysadmin-scripts.txt
spool off
exit
!
if [ `cat profile_values.txt|wc -l` -gt 0 ]
then
mailx -s " Profile Values updated in PGECAI on `date -d yesterday +%d-%m-%y` " glmoperationsteam@ge.com <
profile_values.txt
rm profile_values.txt
fi
**************************************Connected Users***********************************
. /pgecai/oracle/product/920/PGECAI_auohsgeca34.env
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
spool invalid_1.alert
select count(distinct(d.USER_NAME)) "users connected to production", to_char(sysdate,'MM-DD-YYYY
HH:MM:SS') "Time"
from apps.fnd_logins a,
gv\$session b, gv\$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);
spool off
exit;
!
if [`cat invalid_1.alert|wc -l` -gt 0 ]
then
cat invalid_1.alert >> invalid_2.alert
# mailx -s "Users information " vaseem.mohammed@ge.com < invalid_2.alert
fi
********************************************
. /pgecai/oracle/product/920/PGECAI_auohsgeca34.env
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool invalid_1.alert
select count(distinct(d.USER_NAME)) "users connected to production",TO_CHAR(SYSDATE, 'Dy DD-Mon-YYYY
HH24:MI:SS')"Current Time" from apps.fnd_logins a,gv\$session b, gv\$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
Page 7done-Unix-sysadmin-scripts.txt
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);
spool off
exit;
!
if [`cat invalid_1.alert|wc -l` -gt 0 ]
then
cat invalid_1.alert >> invalid_2.alert
rm invalid_1.alert
#mailx -s "Users information " glmgenpactappsdba@ge.com < invalid_1.alert
fi
*************************************
. /pgecai/oracle/product/920/PGECAI_auohsgeca34.env
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool invalid_1.alert
select count(distinct user_id) "users",TO_CHAR(SYSDATE, 'Dy DD-Mon-YYYY HH24:MI:SS')"Current Time"
from apps.icx_sessions where last_connect > sysdate - 2/24 and user_id != '-1';
spool off
exit;
!
if [`cat invalid_1.alert|wc -l` -gt 0 ]
then
cat invalid_1.alert >> invalid_2.alert
rm invalid_1.alert
#mailx -s "Users information " glmgenpactappsdba@ge.com < invalid_1.alert
fi
**************************************Temp TableSpace usage************************************
#!/bin/bash
ORACLE_HOME=/pgecai/oracle/product/920
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
rm temp_tablespace.alert
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool temp_tablespace.alert
select name from v\$database;
select current_users,TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,
FREE_BLOCKS, free_extents,used_extents from v\$sort_segment;
select to_char(sysdate,'mm-dd-yy hh24:mi:ss') from dual;
select tablespace_name, bytes_used/(1024*1024) "MBUsed", bytes_free/(1024*1024) "MBFree"
from v\$temp_space_header;
Page 8done-Unix-sysadmin-scripts.txt
exit;
!
if [`cat temp_tablespace.alert|wc -l` -gt 0 ]
then
cp temp_tablespace.alert /pgecai/backup/temp_tablespace.log
mailx -s "Temp TABLE SPACE USAGE report IN PGECAI in all nodes" balaji.s@ge.com <
/pgecai/backup/temp_tablespace.log
rm /pgecai/backup/temp_tablespace.log
fi
*************************************Tablespace usage******************************************
#!/bin/bash
ORACLE_HOME=/pgecai/oracle/product/920
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool temp_tablespace.alert
select name "Database Name" from v\$database;
column used_space format 99,999,999,999
column free_space format 99,999,999,999
column total_space format 99,999,999,999
column largest_extent format 99,999,999,999
column tablespace_name format a20
column pctfull format 999
select t.tablespace_name,t.total_space,
nvl(f.free_space,0) free_space,
nvl(f.largest_extent,0) largest_extent,
total_space-nvl(f.free_space,0) used_space,
(total_space-nvl(f.free_space,0))*100/total_space pctfull
from
(select tablespace_name, sum(bytes)/(1024*1024) total_space
from dba_data_files group by tablespace_name ) t,
(select tablespace_name, sum(bytes)/(1024*1024) free_space,
max(bytes)/(1024*1024) largest_extent
from dba_free_space group by tablespace_name) f
where t.tablespace_name=f.tablespace_name (+);
select current_users,TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,
FREE_BLOCKS, free_extents,used_extents from v\$sort_segment;
select to_char(sysdate,'mm-dd-yy hh24:mi:ss') "Date " from dual;
select tablespace_name, bytes_used/(1024*1024) "MBUsed", bytes_free/(1024*1024) "MBFree"
from v\$temp_space_header;
exit;
!
if [`cat temp_tablespace.alert|wc -l` -gt 0 ]
then
Page 9done-Unix-sysadmin-scripts.txt
mailx -s " TABLE SPACE USAGE report - IN PGECAI " glmgenpactappsdba@ge.com martin.hodge@ge.com <
temp_tablespace.alert
rm temp_tablespace.alert
fi
*************************************Misc*************************************
tablespace_usage.sh 0100700 0073471 0001747 00000003230
10604431157 013755 0 ustar c_bsanth
g999
#!/bin/bash
ORACLE_HOME=/pgecai/oracle/product/920
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool temp_tablespace.alert
select name "Database Name" from v\$database;
column used_space format 99,999,999,999
column free_space format 99,999,999,999
column total_space format 99,999,999,999
column largest_extent format 99,999,999,999
column tablespace_name format a20
column pctfull format 999
select t.tablespace_name,t.total_space,
nvl(f.free_space,0) free_space,
nvl(f.largest_extent,0) largest_extent,
total_space-nvl(f.free_space,0) used_space,
(total_space-nvl(f.free_space,0))*100/total_space pctfull
from
(select tablespace_name, sum(bytes)/(1024*1024) total_space
from dba_data_files group by tablespace_name ) t,
(select tablespace_name, sum(bytes)/(1024*1024) free_space,
max(bytes)/(1024*1024) largest_extent
from dba_free_space group by tablespace_name) f
where t.tablespace_name=f.tablespace_name (+);
select current_users,TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,
FREE_BLOCKS, free_extents,used_extents from v\$sort_segment;
select to_char(sysdate,'mm-dd-yy hh24:mi:ss') "Date " from dual;
select tablespace_name, bytes_used/(1024*1024) "MBUsed", bytes_free/(1024*1024) "MBFree"
from v\$temp_space_header;
exit;
!
if [`cat temp_tablespace.alert|wc -l` -gt 0 ]
then
mailx -s " TABLE SPACE USAGE report - IN PGECAI " glmgenpactappsdba@ge.com martin.hodge@ge.com <
temp_tablespace.alert
Page 10done-Unix-sysadmin-scripts.txt
rm temp_tablespace.alert
fi
temp_tablespace_usage.sh 0100700 0073471 0001747
00000001655 10601711012 014777 0 ustar
c_bsanth g999
#!/bin/bash
ORACLE_HOME=/pgecai/oracle/product/920
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
rm temp_tablespace.alert
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool temp_tablespace.alert
select name from v\$database;
select current_users,TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,
FREE_BLOCKS, free_extents,used_extents from v\$sort_segment;
select to_char(sysdate,'mm-dd-yy hh24:mi:ss') from dual;
select tablespace_name, bytes_used/(1024*1024) "MBUsed", bytes_free/(1024*1024) "MBFree"
from v\$temp_space_header;
exit;
!
if [`cat temp_tablespace.alert|wc -l` -gt 0 ]
then
cp temp_tablespace.alert /pgecai/backup/temp_tablespace.log
mailx -s "Temp TABLE SPACE USAGE report IN PGECAI in all nodes" balaji.s@ge.com <
/pgecai/backup/temp_tablespace.log
rm /pgecai/backup/temp_tablespace.log
fi
test.sh
0100700 0073471 0001747 00000000727 10607131650 011453 0
ustar c_bsanth g999
#!/bin/bash
ORACLE_HOME=/pgecai/oracle/product/920
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool test.alert
select name "Database Name" from v\$database;
exit;
!
Page 11done-Unix-sysadmin-scripts.txt
if [`cat test.alert|wc -l` -gt 0 ]
then
mailx -s " Test report - IN PGECAI " balaji.s@ge.com < test.alert
rm test.alert
fi
/**************************Begin Create user & update responsibilities***********************************/
use this script to enable a user and to enable all their administrator responsibilities.
This script can be run as many times for any given user.In case a user does not exist, then new user will be
created with default password being oracle123.You can set FND_GLOBAL variables by running the script below,
or by running fnd_global.apps_initialize
DECLARE
v_session_id INTEGER := userenv('sessionid') ;
BEGIN
dbms_application_info.set_client_info ( 101 ) ; --put ur org_id here
fnd_global.initialize
(
SESSION_ID=>v_session_id
,USER_ID =>18594
,RESP_ID =>4650125
,RESP_APPL_ID =>8402
,SECURITY_GROUP_ID =>0
,SITE_ID =>NULL
,LOGIN_ID =>3115003
,CONC_LOGIN_ID =>NULL
,PROG_APPL_ID =>NULL
,CONC_PROGRAM_ID =>NULL
,CONC_REQUEST_ID =>NULL
,CONC_PRIORITY_REQUEST =>NULL
) ;
commit ;
END ;
/
DECLARE
--By: First Last
--When Jun-2007
v_session_id INTEGER := userenv('sessionid');
v_user_name VARCHAR2(30) := upper('&Enter_User_Name');
result BOOLEAN;
v_user_id INTEGER;
FUNCTION check_fu_name(p_user_name IN VARCHAR2) RETURN BOOLEAN IS
CURSOR c_check IS
SELECT 'x' FROM fnd_user WHERE user_name = p_user_name;
p_check c_check%ROWTYPE;
BEGIN
OPEN c_check;
FETCH c_check
INTO p_check;
IF c_check%FOUND
THEN
/*Yes, it exists*/
Page 12done-Unix-sysadmin-scripts.txt
CLOSE c_check;
RETURN TRUE;
END IF;
CLOSE c_check;
RETURN FALSE;
END check_fu_name;
BEGIN
IF NOT (check_fu_name(p_user_name => v_user_name))
THEN
fnd_user_pkg.createuser(x_user_name => v_user_name
,x_owner => ''
,x_unencrypted_password => 'oracle123'
,x_session_number => v_session_id
,x_start_date => SYSDATE - 10
,x_end_date => SYSDATE + 100
,x_last_logon_date => SYSDATE - 10
,x_description => 'Anil Passi'
,x_password_date => SYSDATE - 10
,x_password_accesses_left => 10000
,x_password_lifespan_accesses => 10000
,x_password_lifespan_days => 10000
,x_employee_id => NULL /*Change this id by running below SQL*/
/*
SELECT person_id,full_name
FROM per_all_people_f
WHERE upper(full_name) LIKE '%' || upper('<ampersand>full_name') || '%'
GROUP BY person_id,full_name
*/
,x_email_address => ' last.first@gmail.com'
,x_fax => ''
,x_customer_id => ''
,x_supplier_id => '');
dbms_output.put_line ( 'FND_USER Created' ) ;
ELSE
fnd_user_pkg.updateuser(x_user_name => v_user_name
,x_owner => 'CUST'
,x_end_date => fnd_user_pkg.null_date
,x_password_date => SYSDATE - 10
,x_password_accesses_left => 10000
,x_password_lifespan_accesses => 10000
,x_password_lifespan_days => 10000);
dbms_output.put_line ( 'End Date removed from FND_USER ' ) ;
END IF;
SELECT user_id
INTO v_user_id
FROM fnd_user
WHERE user_name = v_user_name;
fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'FND'
,resp_key => 'FND_FUNC_ADMIN'
,security_group => 'STANDARD'
,description => 'last.first@gmail.com'
,start_date => SYSDATE - 1
,end_date => SYSDATE + 10000);
fnd_user_pkg.addresp(username => v_user_name
Page 13done-Unix-sysadmin-scripts.txt
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'last.first@gmail.com'
,start_date => SYSDATE - 1
,end_date => SYSDATE + 10000);
fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'FND'
,resp_key => 'FNDWF_ADMIN_WEB'
,security_group => 'STANDARD'
,description => 'last.first@gmail.com'
,start_date => SYSDATE - 1
,end_date => SYSDATE + 10000);
fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'FND'
,resp_key => 'APPLICATION_DEVELOPER'
,security_group => 'STANDARD'
,description => 'last.first@gmail.com'
,start_date => SYSDATE - 1
,end_date => SYSDATE + 10000);
fnd_user_pkg.addresp(username => v_user_name,
resp_app => 'ICX',
resp_key => 'PREFERENCES',
security_group => 'STANDARD',
description => 'last.first@gmail.com',
start_date => sysdate - 1,
end_date => null);
result := fnd_profile.save(x_name => 'APPS_SSO_LOCAL_LOGIN'
,x_value => 'BOTH'
,x_level_name => 'USER'
,x_level_value => v_user_id);
result := fnd_profile.save(x_name => 'FND_CUSTOM_OA_DEFINTION'
,x_value => 'Y'
,x_level_name => 'USER'
,x_level_value => v_user_id);
result := fnd_profile.save(x_name => 'FND_DIAGNOSTICS'
,x_value => 'Y'
,x_level_name => 'USER'
,x_level_value => v_user_id);
result := fnd_profile.save(x_name => 'DIAGNOSTICS'
,x_value => 'Y'
,x_level_name => 'USER'
,x_level_value => v_user_id);
result := fnd_profile.save(x_name => 'FND_HIDE_DIAGNOSTICS'
,x_value => 'N'
,x_level_name => 'USER'
,x_level_value => v_user_id);
COMMIT;
Page 14done-Unix-sysadmin-scripts.txt
END;
/
/**************************End Create user & update responsibilities***********************************/
No comments:
Post a Comment