*********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
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
******************************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,
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 "TRIM(: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 = BLANKS,
CUSTOMER CHAR NULLIF
CUSTOMER = BLANKS,
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',
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
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";
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
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;
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
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
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;
!
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
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*/
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
,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;
END;
/
/**************************End
Create user & update responsibilities***********************************/
No comments:
Post a Comment