Chitika Add

Friday, November 23

Useful script on Oracle alert History

select
distinct AAHV . *
FROM apps.ALR_ALERTS al ,
APPS.ALR_ACTION_HISTORY AAH ,
APPS.ALR_OUTPUT_HISTORY AOH ,
APPS.ALR_ACTIONS_V ALV ,
apps.ALR_ALERT_HISTORY_VIEW AAHV
WHERE al . alert_name ='HOLX_ADDR_CHG_FIELD_ENGR' --'XX_ALERTS_SAMPLE'
AND al . alert_id = aah . alert_id
AND aah . check_id = aoh . check_id
and ALV . ALERT_ID = AAH . ALERT_ID
and AAHV . ALERT_NAME = AL . ALERT_NAME
order by aahv.alert_check_date desc;

Monday, September 17

Concurrent Program and Executable Details

The following SQL query provide you executable file name ,top name , etc...

--Oracle Applications Query to get the actual concurrent program file executable if we know the concurrent program name

SELECT b.user_concurrent_program_name, b.concurrent_program_name,
a.user_executable_name,
DECODE (a.execution_method_code,
'I', 'PL/SQL Stored Procedure',
'H', 'Host',
'S', 'Immediate',
'J', 'Java Stored Procedure',
'K', 'Java concurrent program',
'M', 'Multi Language Function',
'P', 'Oracle reports',
'B', 'Request Set Stage Function',
'A', 'Spawned',
'L', 'SQL*Loader',
'Q', 'SQL*Plus',
'E', 'Pearl concurrent Programm',
'Unkown Type'
) TYPE,
a.execution_file_name, a.execution_file_path, a.application_name,
c.basepath
FROM fnd_executables_form_v a,
fnd_concurrent_programs_vl b,
fnd_application c
WHERE a.application_id = c.application_id
AND a.executable_id = b.executable_id
AND a.application_id = b.application_id
AND a.executable_id > 4
AND b.user_concurrent_program_name LIKE '%Sales%Bill%Out%'

Thursday, June 28

File Vesions

Select * from ad_file_versions where file_id in (select file_id from ad_files 
where filename = 'APXVDVSR.rdf ');

Wednesday, June 13

O2C life cycle

Convert number to characters..

select USER_ID  ,   (TO_CHAR (TO_DATE(USER_ID,'j'), 'Jsp'))   user_id_string
from FND_USER where USER_NAME = 'JKUMAR'  ;

Tracing techniques in oracle applications

Hi,This is one of the good documents from metalink..good reference document when you want to do tracing....
WHAT TO SET UP BEFORE GENERATING THE TRACE

These steps must be performed by the DBA on the database server.

1. Set TIMED_STATISTICS to TRUE.
For performance issues, make sure TIMED_STATISTICS is turned on,
before attempting to generate the trace.
Set the following in the init.ora file:
TIMED_STATISTICS=TRUE
OR
in SQL*Plus:
ALTER SYSTEM SET TIMED_STATISTICS=TRUE;


2. Set the location of the trace output.
Set the following in the init.ora file:
USER_DUMP_DEST = <preferred directory for the trace output>


3. Create the PLAN_TABLE to hold the output of the explain plan. Run
the SQL script called UTLXPLAN.SQL to create this in the apps schema.
This script is usually in $ORACLE_HOME/rdbms/admin.


4. If the init.ora file has been updated, you must shut down and restart the database before the changes will take effect.
TYPES OF TRACE - HOW TO TURN TRACE ON
Regardless of the type of trace file you create, make sure you note the time
that you create it.


1. Form Trace

Toggle trace on/off on the form, to trace specific application functions.
Make sure you go in fresh (sign off/on to the application), since somequeries are cached and may not be executed on subsequent visits to theform.


From the menu, select Help..Diagnostics..Trace (Release 11i) to turn
trace on (when checked, it is on).
Release 11 and 10.7 GUI/NCA --> Help..Tools..Trace
Perform the action to be traced.
From the menu, turn trace off, by selecting Help..Diagnostics..Trace(it should now be unchecked).Release 11 and 10.7 GUI/NCA --> Help..Tools..Trace

2. Concurrent Program Trace

A. This will turn trace on for each execution of this program.
In Release 11.0 and lli, check the Enable Trace checkbox for the
concurrent program on the Concurrent Programs form. After running the
program to be traced, make sure you uncheck the Enable Trace checkbox.
Select the System Administrator responsibility.
Navigation =
Concurrent -> Programs -> Define. Query the concurrent program you
want to trace. Check the Enable Trace checkbox and save.
OR
B. How to generate a raw trace file with binds and/or waits
for 11.5.10:
1. Log into applications as System Administrator and Navigate to the
System Profile Values Form. Select the profile called
Concurrent: Allow Debugging and change the value to Yes at the appropriate level.
2. Allow pop-ups on your browser.
3. Navigate to the Submit a New Request form and select a job and
enter all parameters for that job
4. Select the Debug Options Button and this will take you to Create
Debug Rule in Oracle Application Manager
5. Select the appropriate Debug Option Value for SQL Trace only and then check the box
6. Hit OK twice and then Submit the job
7. Raw trace file with options selected will be located in the
appropriate directory.


3. Database Level Trace
This will turn trace on for all processes that are running in the
instance and should only have to be used in Release 10.7, for
concurrent programs. (This has to be done by the DBA.)
Set the following in the init.ora file:SQL_TRACE=TRUE
Shut down and restart the database.After generating the trace file, shut down and restart the database with the original init.ora.

.
4. Report Trace
If you are on 10.7 and need to trace an Oracle Report, you can modify the report to turn trace on for that session.
a. Convert the report from rdf to rex:
$ORACLE_HOME/bin/r25convm batch=yes userid=<apps username/pwd>> stype=rdffile source=REPORT_NAME.rdf dtype=rexfile overwrite=yes
b. Edit the rexfile and search for the beforerep trigger in the report.
Locate the following code:
IF (:p_trace_switch = 'Y') THEN
SRW.DO_SQL('alter session set sql_trace TRUE');
END IF;
Comment out the IF and END IF lines.
c. Save the report.
d. Convert the report from rex to rdf:
$ORACLE_HOME/bin/r25convm batch=yes userid=<apps username/pwd> \
> stype=rexfile source=REPORT_NAME.rex dtype=rdffile overwrite=yes


5. Self Service page (like a Forms trace, but for self service web apps)
a. Set the FND:Diagnostics profile:
Responsibility = System Administrator
Navigation: Profile > System
User: Enter User name
Query the Profile: 'FND:Diagnostics'
Set the 'FND:Diagnostics' profile to Yes at User level

b. Login to Self Service under the same user the profile was set for.
c. Turn Trace on:
Click the Diagnostic link at the top of the page
It shows two options: Show Log and Set Trace Level
Select 'Set Trace Level'
Click Go.
Select one of the following options:
Disable Trace - used to end the trace
Trace (Regular) - just like a forms trace
Trace with Binds - record the bind variables in the trace
Trace with waits - Good for performance issues
Trace with binds and waits - combines both of the above
Click Save.
d. Perform the action to be traced in Self Service.
Multiple trace files may be generated in the usual trace directory.
e. Turn trace off:
Select the Diagnostic link
Click on option: Set Trace Level
Note all of the trace numbers listed
Click Go
Select: Disable Trace
Click Save


WHAT TO DO AFTER GENERATING THE TRACE FILE
These steps should be performed by the DBA, on the database server.

1. Find the trace directory.
Get the location of user_dump_dest.
Log into SQL*Plus as the apps user.

select value from V$PARAMETER where name = 'user_dump_dest'
2. Find the trace file for your process.
Go to the directory you found in step 1 (in UNIX, use cd).
Look for a file (.trc) that was created at the time you started
your process (in UNIX, use ls -ltr).

3. Run tkprof with explain plan.
Go to a directory in which you have write privilege (in UNIX, use cd).

Run tkprof:tkprof <full path to trace file> <output file name> explain=<apps username/apps password>


HOW TO GET AN EXPLAIN PLAN FOR A SQL STATEMENT
Sometimes you may have a need to get an explain plan for a specific SQL
statement. If you have the sql statement, you can get the explain plan for
it.
This should be run on the same instance that the sql statement came from.
In your apps account ---
Run the following script:
delete from plan_table
where statement_id = 'tmp'

explain plan
set statement_id = 'tmp'
for
<put sql statement here>
/
set pages 100
col operation format a36
col options format a11
col object_name format a30

select lpad(' ',2*(level-1))operation operation,

options, object_name
from plan_table
where statement_id = 'tmp'
connect by prior id = parent_id
and statement_id = 'tmp'
start with id = 1
and statement_id = 'tmp'
order by id
/
delete from plan_table
where statement_id = 'tmp'
/
commit;

Concurrent Programs with trace enabled

Shows which programs are defined with Trace enabled. Generally, you should enable trace for a program while you are debugging, but then you should turn it off when you're done.

SELECT A.CONCURRENT_PROGRAM_NAME "Program Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User Program Name",
SUBSTR(B.USER_NAME,1,15) "Last Updated By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;

Friday, May 11

FND_LOAD commands

Profile Options:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt
PROFILE PROFILE_NAME="XXPRNAME" APPLICATION_SHORT_NAME="PN"
Target:
1. FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt
2. FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscprof.lct
XXPRNAME.ldt PROFILE PROFILE_NAME=" XXPRNAME" APPLICATION_SHORT_NAME="PN"
==================================================================

Forms:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFRMNAME.ldt FORM
APPLICATION_SHORT_NAME="PN" FORM_NAME="XXFRMNAME"
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct XXFRMNAME.ldt

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

Functions:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFUNNAME.ldt
FUNCTION FUNC_APP_SHORT_NAME="PN" FUNCTION_NAME="XXFUNNAME"
Target:
FNDLOAD apps/apps O Y UPLOAD @FND:patch/115/import/afsload.lct XXFUNNAME.ldt

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

Responsibilities:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt
FND_RESPONSIBILITY RESP_KEY="XXRESNAME"
Target:
1. FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt
2. FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscursp.lct
XXRESNAME.ldt FND_RESPONSIBILITY RESP_KEY="XXRESNAME" APPLICATION_SHORT_NAME="PN"
==================================================================
Request Groups:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt
REQUEST_GROUP REQUEST_GROUP_NAME="XXRQGNAME" APPLICATION_SHORT_NAME="PN"
Target:
1. FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt
2. FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpreqg.lct
XXRQGNAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XXRQGNAME" APPLICATION_SHORT_NAME="PN"
==================================================================

Request Sets:
Source:

Step1:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt
REQ_SET REQUEST_SET_NAME="XXRQSNAME"
Step2:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt
REQ_SET_LINKS REQUEST_SET_NAME="XXRQSNAME"
Target:
Step1:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt
Step2:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt
==================================================================

Lookups:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt
FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="PN" LOOKUP_TYPE="XXLKPNAME"
Target:
1. FNDLOAD apps/apps 0 Y UPLOAD aflvmlu.lct XXLKPNAME.ldt
2. FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/aflvmlu.lct
XXLKPNAME.ldt FND_LOOKUP_TYPE LOOKUP_TYPE="XXLKPNAME" APPLICATION_SHORT_NAME="PN"
==================================================================

Value Sets:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt
VALUE_SET FLEX_VALUE_SET_NAME="XXVALSNAME"
Target:
1. FNDLOAD apps/apps 0 Y UPLOAD afffload.lct XXVALSNAME.ldt
2. FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afffload.lct
XXVALSNAME.ldt VALUE_SET FLEX_VALUE_SET_NAME="XXVALSNAME" APPLICATION_SHORT_NAME="PN"

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

Descriptive Flex-fields:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXDFFNAME.ldt
DESC_FLEX P_LEVEL='COL_ALL:REF_ALL:CTX_ONE:SEG_ALL' APPLICATION_SHORT_NAME="PN"
DESCRIPTIVE_FLEXFIELD_NAME="PN_LEASE_DETAILS" P_CONTEXT_CODE="Global Data Elements"
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct XXDFFNAME.ldt

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

Key Flex-fields:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXKFFNAME.ldt
KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’
APPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure
name”
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct XXKFFNAME.ldt

==================================================================
Concurrent Programs:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt
PROGRAM APPLICATION_SHORT_NAME="PN" CONCURRENT_PROGRAM_NAME="XXCPNAME"

Target:
1. FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afcpprog.lct XXCPNAME.ldt
2. FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpprog.lct
XXCPNAME.ldt PROGRAM CONCURRENT_PROGRAM_NAME="XXCPNAME" APPLICATION_SHORT_NAME="PN"
==================================================================

Form Personalization:
Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt
FND_FORM_CUSTOM_RULES function_name="XXFPNAME"
Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt

==================================================================
FND Users:
Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct ./XXUSERNAME.ldt
FND_USER USER_NAME='XXUSERNAME'
Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct ./ XXUSERNAME.ldt

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

Printer Styles:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct XXPRSTYLE.ldt STYLE
PRINTER_STYLE_NAME="XXPRSTYLE"
Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcppstl.lct XXPRSTYLE.ldt

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

Some useful Meta-link Notes related to FNDLOAD:
1. For NLS Language using FNDLOAD:
          Note: 434724.1
2. Troubleshooting Incorrect translation with FNDLOAD
          Note: 299080.1
Note:
1. Test FNDLOAD commands multiple times in multiple instances before running into PROD
instances.
2. UPLOAD_PARTIAL is used to modify existed programs.
3. Execution sequence is important ex: To create a responsibility Create Form
Create Function Create Menu Create Responsibility


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


Oracle WEB ADI:
First step
Identify the pieces that must be moved across using fndload. These can be:-
Integrators
Layouts
Mappings
Contents
This article covers the above listed components one by one.
Second step
Download the above web adi attributes into various ldt files. Basically we will create one ldt file for each of the above four web adi entities.
Third step
Upload those ldt files into the new environment. For this we will run the fndload in upload mode.
=====Find the Integrator codes=====
SELECT integrator_code, application_id
FROM bne_integrators_vl vl
WHERE user_name IN
('XX Request for further references', 'XX Sorry Interview did not work') ;

This will return two internal codes, both in application PER (Application ID 800). Lets say the two internal codes are HR_101_INTG & HR_41_INTG.

Now download these as below:-


FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bneint.lct
XX_HR_101_INTG.ldt BNE_INTEGRATORS INTEGRATOR_ASN="PER" INTEGRATOR_CODE="HR_101_INTG"

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bneint.lct
XX_HR_41_INTG.ldt BNE_INTEGRATORS INTEGRATOR_ASN="PER" INTEGRATOR_CODE="HR_41_INTG"

In order to upload these
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bneint.lct
XX_HR_101_INTG.ldt

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bneint.lct XX_HR_41_INTG.ldt

=====Find the Layouts from layout names=====
SELECT LAYOUT_CODE
FROM bne_layouts_vl vl
WHERE user_name IN ('Offer Letter for Job', 'Denial of Job')
==============
Above SQL can return values say XX_C_O_F_T & XX_CODE
To download these layouts
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bnelay.lct XX_C_O_F_T.ldt
BNE_LAYOUTS LAYOUT_ASN="PER" LAYOUT_CODE="XX_C_O_F_T"

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bnelay.lct XX_CODE.ldt
BNE_LAYOUTS LAYOUT_ASN="PER" LAYOUT_CODE="XX_CODE"

Now in order to upload these into new environment, use below commands

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnelay.lct XX_C_O_F_T.ldt

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnelay.lct XX_CODE.ldt


=====Find the MAPPING CODES from integrator names==
SELECT mapping_code, integrator_code
FROM bne_mappings_vl
WHERE integrator_code IN
(SELECT integrator_code
FROM bne_integrators_vl vl
WHERE application_id = 800
AND user_name IN ('XX HR Reference letter', 'XX HR Sorry Cant offer'))
ORDER BY last_update_date DESC;

Lets say this SQL returns HR_101_MAP & HR_86_MAP
--Now do the download

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bnemap.lct
XX_HR_101_MAP.ldt BNE_MAPPINGS MAPPING_ASN="PER" MAPPING_CODE="HR_101_MAP"

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bnemap.lct
XX_HR_86_MAP.ldt BNE_MAPPINGS MAPPING_ASN="PER" MAPPING_CODE="HR_86_MAP"

To upload these files into a new environment, ftp the ldt files and run below commands on the new environment

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnemap.lct XX_HR_101_MAP.ldt

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnemap.lct XX_HR_86_MAP.ldt

Question : Is FNDLOAD of Web ADI contents similar to above?
Answer : Its slightly different, as to recognize the contents I had to use the date range
to pick all the content codes configured in Web ADI during the past 90 days.
=====================
SELECT CONTENT_CODE
FROM bne_content_cols_vl
WHERE last_update_date > SYSDATE - 90
group by CONTENT_CODE ;
=====================
CONTENT_CODE
--------------
HR_101_CNT
HR_41_CNT
For each content code returned by SQL above, we will now do FNDLOAD as below

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bnecont.lct
XX_HR_101_CNT.ldt BNE_CONTENTS CONTENT_ASN="PER" CONTENT_CODE="HR_101_CNT"

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bnecont.lct
XX_HR_41_CNT.ldt BNE_CONTENTS CONTENT_ASN="PER" CONTENT_CODE="HR_41_CNT"

Obviously to upload Web ADI contents, use the below commands

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnecont.lct
XX_HR_101_CNT.ldt

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnecont.lct XX_HR_41_CNT.ldt



Monday, February 6

Usefull Run Commands For Windows XP

Usefull Run Commands For Windows XP


Here is a list of commands that you can run off from the Run Command prompt in XP:


Program Run Command


Accessibility Controls = access.cpl
Accessibility Wizard = accwiz
Add Hardware Wizard = hdwwiz.cpl
Add/Remove Programs = appwiz.cpl
Administrative Tools = control admintools
Adobe Acrobat ( if installed ) = acrobat
Adobe Distiller ( if installed ) = acrodist
Adobe ImageReady ( if installed ) = imageready
Adobe Photoshop ( if installed ) = photoshop
Automatic Updates = wuaucpl.cpl
Basic Media Player = mplay32
Bluetooth Transfer Wizard = fsquirt


Calculator = calc
Ccleaner ( if installed ) = ccleaner
C: Drive = c:
Certificate Manager = cdrtmgr.msc
Character Map = charmap
Check Disk Utility = chkdsk
Clipboard Viewer = clipbrd
Command Prompt = cmd
Command Prompt = command
Component Services = dcomcnfg
Computer Management = compmgmt.msc
Compare Files = comp
Control Panel = control
Create a shared folder Wizard = shrpubw


Date and Time Properties = timedate.cpl
DDE Shares = ddeshare
Device Manager = devmgmt.msc
Direct X Control Panel ( if installed ) = directx.cpl
Direct X Troubleshooter = dxdiag
Disk Cleanup Utility = cleanmgr
Disk Defragment = dfrg.msc
Disk Partition Manager = diskmgmt.msc
Display Properties = control desktop
Display Properties = desk.cpl
Display Properties (w/Appearance Tab Preselected ) = control color
Dr. Watson System Troubleshooting Utility = drwtsn32
Driver Verifier Utility = verifier


Ethereal ( if installed ) = ethereal
Event Viewer = eventvwr.msc
Files and Settings Transfer Tool = migwiz
File Signature Verification Tool = sigverif
Findfast = findfast.cpl
Firefox = firefox
Folders Properties = control folders
Fonts = fonts
Fonts Folder = fonts
Free Cell Card Game = freecell


Game Controllers = joy.cpl
Group Policy Editor ( xp pro ) = gpedit.msc
Hearts Card Game = mshearts
Help and Support = helpctr
Hyperterminal = hypertrm
Hotline Client = hotlineclient


Iexpress Wizard = iexpress
Indexing Service = ciadv.msc
Internet Connection Wizard = icwonn1
Internet Properties = inetcpl.cpl
Internet Setup Wizard = inetwiz
IP Configuration (Display Connection Configuration) = ipconfig /all
IP Configuration (Display DNS Cache Contents) = ipconfig /displaydns
IP Configuration (Delete DNS Cache Contents) = ipconfig /flushdns
IP Configuration (Release All Connections) = ipconfig /release
IP Configuration (Renew All Connections) = ipconfig /renew
IP Configuration (Refreshes DHCP & Re-Registers DNS) = ipconfig /registerdns
IP Configuration (Display DHCP Class ID) = ipconfig /showclassid
IP Configuration (Modifies DHCP Class ID) = ipconfig /setclassid


Java Control Panel ( if installed ) = jpicpl32.cpl
Java Control Panel ( if installed ) = javaws
Keyboard Properties = control keyboard


Local Security Settings = secpol.msc
Local Users and Groups = lusrmgr.msc
Logs You Out of Windows = logoff


Malicious Software Removal Tool = mrt
Microsoft Access ( if installed ) = access.cpl
Microsoft Chat = winchat
Microsoft Excel ( if installed ) = excel
Microsoft Diskpart = diskpart
Microsoft Frontpage ( if installed ) = frontpg
Microsoft Movie Maker = moviemk
Microsoft Management Console = mmc
Microsoft Narrator = narrator
Microsoft Paint = mspaint
Microsoft Powerpoint = powerpnt
Microsoft Word ( if installed ) = winword
Microsoft Syncronization Tool = mobsync
Minesweeper Game = winmine
Mouse Properties = control mouse
Mouse Properties = main.cpl
MS-Dos Editor = edit
MS-Dos FTP = ftp


Nero ( if installed ) = nero
Netmeeting = conf
Network Connections = control netconnections
Network Connections = ncpa.cpl
Network Setup Wizard = netsetup.cpl
Notepad = notepad
Nview Desktop Manager ( if installed ) = nvtuicpl.cpl


Object Packager = packager
ODBC Data Source Administrator = odbccp32
ODBC Data Source Administrator = odbccp32.cpl
On Screen Keyboard = osk
Opens AC3 Filter ( if installed ) = ac3filter.cpl
Outlook Express = msimn


Paint = pbrush
Password Properties = password.cpl
Performance Monitor = perfmon.msc
Performance Monitor = perfmon
Phone and Modem Options = telephon.cpl
Phone Dialer = dialer
Pinball Game = pinball
Power Configuration = powercfg.cpl
Printers and Faxes = control printers
Printers Folder = printers
Private Characters Editor = eudcedit


Quicktime ( if installed ) = quicktime.cpl
Quicktime Player ( if installed ) = quicktimeplayer


Real Player ( if installed ) = realplay
Regional Settings = intl.cpl
Registry Editor = regedit
Registry Editor = regedit32
Remote Access Phonebook = rasphone
Remote Desktop = mstsc
Removable Storage = ntmsmgr.msc
Removable Storage Operator Requests = ntmsoprq.msc
Resultant Set of Policy ( xp pro ) = rsop.msc


Scanners and Cameras = sticpl.cpl
Scheduled Tasks = control schedtasks
Security Center = wscui.cpl
Services = services.msc
Shared Folders = fsmgmt.msc
Sharing Session = rtcshare
Shuts Down Windows = shutdown
Sounds Recorder = sndrec32
Sounds and Audio = mmsys.cpl
Spider Solitare Card Game = spider
SQL Client Configuration = clicongf
System Configuration Editor = sysedit
System Configuration Utility = msconfig
System File Checker Utility ( Scan Immediately ) = sfc /scannow
System File Checker Utility ( Scan Once At Next Boot ) = sfc /scanonce
System File Checker Utility ( Scan On Every Boot ) = sfc /scanboot
System File Checker Utility ( Return to Default Settings) = sfc /revert
System File Checker Utility ( Purge File Cache ) = sfc /purgecache
System File Checker Utility ( Set Cache Size to Size x ) = sfc /cachesize=x
System Information = msinfo32
System Properties = sysdm.cpl


Task Manager = taskmgr
TCP Tester = tcptest
Telnet Client = telnet
Tweak UI ( if installed ) = tweakui
User Account Management = nusrmgr.cpl
Utility Manager = utilman


Volume Serial Number for C: = label
Volume Control = sndvol32
Windows Address Book = wab
Windows Address Book Import Utility = wabmig
Windows Backup Utility ( if installed ) = ntbackup
Windows Explorer = explorer
Windows Firewall = firewall.cpl
Windows Installer Details = msiexec
Windows Magnifier = magnify


Windows Management Infrastructure = wmimgmt.msc
Windows Media Player = wmplayer
Windows Messenger = msnsgs
Windows Picture Import Wizard (Need camera connected) = wiaacmgr
Windows System Security Tool = syskey
Windows Script host settings = wscript
Widnows Update Launches = wupdmgr
Windows Version ( shows your windows version ) = winver
Windows XP Tour Wizard = tourstart
Wordpad = write
Zoom Utility = igfxzoom


If you found this list of Run Commands useful, please Like and Share to Everyone.!

Monday, January 30

Refreshing Materialized view's



ALTER MATERIALIZED VIEW mview_name COMPILE;

exec DBMS_MVIEW.REFRESH('owner.mview_name','C');


SELECT * FROM DBA_MVIEW_REFRESH_TIMES WHERE 1=1 AND NAME ='HOLX_ACTIVE_INSTL_CONTRACTS_MV';
select * from DBA_SNAPSHOT_REFRESH_TIMES where NAME ='HOLX_ACTIVE_INSTL_CONTRACTS_MV';

SELECT * FROM DBA_MVIEWS WHERE MVIEW_NAME ='HOLX_ACTIVE_INSTL_CONTRACTS_MV';;
select * from All_MVIEWS where mview_NAME ='HOLX_ACTIVE_INSTL_CONTRACTS_MV';;



1)
DBMS_REFRESH.REFRESH ('holx.holx_oic_calculated_detail_mv');
ORA-23404: refresh group "HOLX"."HOLX_OIC_CALCULATED_DETAIL_MV" does not exist

DBMS_REFRESH.REFRESH is used for a refresh group containing a list of mviews.
Please refer "http://docs.oracle.com/cd/E11882_01/server.112/e10707/rarrefreshpac.htm#i94176" for details.

Now check from DBA_REFRESH if its a refresh group or a single mview.

a) select ROWNER,RNAME from DBA_REFRESH where ROWNER='HOLX' and RNAME='HOLX_OIC_CALCULATED_DETAIL_MV';

> If it returns a result, then check step (b).
b) select OWNER,NAME,REFGROUP from sys.rgroup$ where OWNER='HOLX' and NAME='HOLX_OIC_CALCULATED_DETAIL_MV';

If step (a) does Not return a result, then 
c) select * from dba_mviews where owner='HOLX' and MVIEW_NAME='HOLX_OIC_CALCULATED_DETAIL_MV';

( Please send me the step (c) output in a xl format for better reading. )

It should be a single mview.Then it is recommended to use DBMS_MVIEW.REFRESH instead of DBMS_REFRESH.REFRESH procedure while refreshing a single mview.

2) 
ORA-12004: REFRESH FAST cannot be used for materialized view "HOLX"."HOLX_OIC_CALCULATED_DETAIL_MV"

The result from previous step (c) would clarify this.
> Check for the "FAST_REFRESHABLE" column value for this mview.

> Also ensure you have a MLOG$ in place for this mview with below query.

A MLOG$ is necessary condition for performing FAST refresh on a mview.

COLUMN LOG_TABLE HEADING 'Mview|Log Table' FORMAT A20
COLUMN LOG_OWNER HEADING 'Mview|Log Owner' FORMAT A10
COLUMN MASTER HEADING 'Master' FORMAT A20
COLUMN MVIEW_ID HEADING 'Mview|ID' FORMAT 9999
COLUMN NAME HEADING 'Mview Name' FORMAT A20

SELECT L.LOG_TABLE, L.LOG_OWNER, B.MASTER, B.MVIEW_ID, R.NAME ,R.UPDATABLE,R.REFRESH_METHOD
FROM DBA_MVIEW_LOGS L,DBA_BASE_TABLE_MVIEWS B,DBA_REGISTERED_MVIEWS R
WHERE B.MVIEW_ID = R.MVIEW_ID
AND B.OWNER = L.LOG_OWNER
AND B.MASTER = L.MASTER
AND R.NAME='HOLX_OIC_CALCULATED_DETAIL_MV';

> Now if you do not have a MLOG$ table from above query, then please create one as below and retry the FAST refresh.

CREATE MATERIALIZED VIEW LOG ON owner_name.master_table_name; ---- Simpleset way to create one.

Refer: http://docs.oracle.com/cd/E11882_01/server.112/e10707/rarmanmv.htm#REPMA389

3)
DBMS_SNAPSHOT.REFRESH( 'HOLX.HOLX_OIC_CALCULATED_DETAIL_MV','C')

This command is similar to using DBMS_MVIEW.REFRESH.
However, the option 'C' here forces a manual complete refresh on the mview for which you do not need a MLOG$ table on the master.
Hence it is going through successfully.

see the below examples  ..

create table TEST_TABLE_JK 
( rnum1 number,
rname1 varchar2(100));

Insert into TEST_TABLE_JK values (1,'A');
Insert into TEST_TABLE_JK values (2,'B');

commit;

Create view TEST_TABLE_JK_V as select * from TEST_TABLE_JK;

Create Materialized view TEST_TABLE_JK_MV as select * from TEST_TABLE_JK_V;

select * from TEST_TABLE_JK; -- 2records
select * from TEST_TABLE_JK_V; -- 2 records
select * from TEST_TABLE_JK_MV;  -- 2 records

-- you will get 2 records in 3 select statements

Insert into TEST_TABLE_JK values (3,'C');

commit;

select * from TEST_TABLE_JK; -- 3records
select * from TEST_TABLE_JK_V; -- 3 records
select * from TEST_TABLE_JK_MV;  -- 2 records

--you will get only two record s from MView where as three from table and View
--Because Mview will act as a snapshot and it shows onlt the records  that exists when time of creation or Last Refresh.

begin 
DBMS_MVIEW.REFRESH   ('apps.TEST_TABLE_JK_MV'); 
end;
/

-- we can refersh the MView using above statements

select * from TEST_TABLE_JK; -- 3records
select * from TEST_TABLE_JK_V; -- 3 records
select * from TEST_TABLE_JK_MV;  -- 3 records

-- Now after Refershing we will 3 records in each

delete TEST_TABLE_JK where RNAME1='B';
commit;

select * from TEST_TABLE_JK; -- 2records
select * from TEST_TABLE_JK_V; -- 2 records
select * from TEST_TABLE_JK_MV;  -- 3 records

--you will get 3 records from MView where as only two from table and View
--Because Mview will act as a snapshot and it shows only the records  that exists when time of creation or Last Refresh.

begin 
DBMS_MVIEW.REFRESH   ('apps.TEST_TABLE_JK_MV'); 
end;
/

select * from TEST_TABLE_JK; -- 2records
select * from TEST_TABLE_JK_V; -- 2 records
select * from TEST_TABLE_JK_MV;  -- 2 records

select object_NAME, Object_type, status from all_objects where object_name like 'TEST_TABLE_JK%';

-- Now try to recreate the View

Create or replace view TEST_TABLE_JK_V as (select * from TEST_TABLE_JK where rname1 <> 'XXX');

select object_NAME, Object_type, status from all_objects where object_name like 'TEST_TABLE_JK%';

--if we modify the view the dependent Mview is will get Invalid

select * from TEST_TABLE_JK; -- 2records
select * from TEST_TABLE_JK_V; -- 2 records
select * from TEST_TABLE_JK_MV;  -- 2 records

--But the select query will return the data as there was an table create with same name of Mview and it will remain in valid status even if we modified the Mview


begin 
DBMS_MVIEW.REFRESH   ('apps.TEST_TABLE_JK_MV'); 
end;

/
-- The Mview will get come back to valid status after refershing it.
-- Try to Refrsh with other command

begin 
DBMS_SNAPSHOT.REFRESH  ('apps.TEST_TABLE_JK_MV', 'F'); 
end;
/
-- It will through the below error
--Error report:
--ORA-12004: REFRESH FAST cannot be used for materialized view "APPS"."TEST_TABLE_JK_MV"

-- Here the paramenter 'F' is for Fast refresh

select * from dba_mviews where owner='APPS' and MVIEW_NAME='TEST_TABLE_JK_MV';

-- The Fast refresh column in the above Query is Set to 'NO' and it is the reason for Refresh Failed to FAst Refresh.

-- In this Case we should do complete reeresh instead of Fast Refresh (see the below statements)

begin 
DBMS_SNAPSHOT.REFRESH  ('apps.TEST_TABLE_JK_MV', 'C'); 
end;

/
-- The Above Statement will Refersh the Complete MV and it is Similar to "DBMS_MVIEW.REFRESH   ('apps.TEST_TABLE_JK_MV'); "

--Now let us try to Refresh with other command

begin 
DBMS_REFRESH.REFRESH  ('apps.TEST_TABLE_JK_MV'); 
end;
/


--It will show the Below eRROR
--Error report:
--ORA-23404: refresh group "APPS"."TEST_TABLE_JK_MV" does not exist

--the command "DBMS_REFRESH.REFRESH  ('apps.TEST_TABLE_JK_MV'); " will refersh only refresh groups  but not Standalone Mviews or Single Mviews Which or not linked to a Refresh Group.

--Refresh group is a Group of Mviews. this group can contain 1 or More Mviews and all the Mvies in one refresh group can be refershed using 'DBMS_REFRESH.REFRESH  ('apps.TEST_TABLE_JK_MV'); '
-- we can Query the Referesh group details using below select statement it will return the record with the regersh group name 'TEST_TABLE_JK'

select ROWNER,RNAME from DBA_REFRESH where ROWNER='APPS' and RNAME='TEST_TABLE_JK_MV';

-- Now let us try to create a refersh group with Name TEST_TABLE_JK_MV ( Here we can create a refresh with same nname as Mview also we can create with our own name)

BEGIN
DBMS_REFRESH.MAKE (
name => 'TEST_TABLE_JK_MV',
list => 'TEST_TABLE_JK_MV',
next_date => NULL,
interval => NULL,
implicit_destroy => TRUE,
purge_option => 1,
parallelism => 0,
heap_size => 0);
END;

/
select ROWNER,RNAME from DBA_REFRESH where ROWNER='APPS' and RNAME='TEST_TABLE_JK_MV';

-- Also we can add MVIEW to an Existing Refersh group

-- Lets Create another Mview First 

Create Materialized view TEST_TABLE_JK_MV1 as select * from TEST_TABLE_JK_V;

BEGIN
   DBMS_REFRESH.ADD (
      name => 'TEST_TABLE_JK_MV',
      list => 'TEST_TABLE_JK_MV1',
      lax => TRUE);
END;
/

---now  This Statement will refersh the Group and Mviews in the group with out error

begin 
DBMS_REFRESH.REFRESH  ('apps.TEST_TABLE_JK_MV'); 
end;
/