Change Oracle password temporarily

Change Oracle password temporarily

SQL> SELECT a.username, b.password FROM dba_users a, sys.user$ b WHERE a.username = 'AHMED' AND a.user_id = b.user#


USERNAME PASSWORD
-------- ----------------
AHMED 3KD55KGFST529KT5


SQL> alter user HRPAY identified by newpassword

After completing your work , now you can change the password back by using an undocumented feature called "by values"

SQL>alter user AHMED identified by values '3KD55KGFST529KT5';

Attach Concurrent Program / Request Set to Request Group

Attach Concurrent Program / Request Set to Request Group


1. Find the Request Group of the Responsibility
Responsibility: System Administrator
Navigation: Security > Responsibility > Define



Search for the Responsibility with which the Concurrent Program needs to be run. Copy the request group name.



2. Attach the Concurrent Program/Request Set to the Request Group
Responsibility: System Administrator
Navigation: Security > Responsibility > Request

Search the request Group name for attaching the concurrent Program



Attach the Concurrent Program or Request Set to this Request Group.



Once attached, the Concurrent Program/Request Set can be run from given responsibility from the SRS.

Query

The main query joins Concurrent Program table or Request Set table and Request Group table with the help of Request Group Units as the intermediate table. The Request Group table is finally joined with the Responsibility table. Below are the main tables used:

FND_REQUEST_GROUPS
FND_REQUEST_GROUP_UNITS
FND_CONCURRENT_PROGRAMS (For Concurrent Program)
FND_CONCURRENT_PROGRAMS_TL (For Concurrent Program)
FND_REQUEST_SETS_TL (For Request Set)
FND_REQUEST_SETS (For Request Set)
FND_RESPONSIBILITY_TL
FND_RESPONSIBILITY

The main queries take the final shape as below:

Concurrent Program
select fcpt.user_concurrent_program_name ,fcp.concurrent_program_name program_short_name ,frt.responsibility_name ,frg.request_group_name from fnd_request_groups frg ,fnd_request_group_units frgu ,fnd_concurrent_programs fcp ,fnd_concurrent_programs_tl fcpt ,fnd_responsibility_tl frt ,fnd_responsibility frs where frgu.unit_application_id = fcp.application_id and frgu.request_unit_id = fcp.concurrent_program_id and frg.request_group_id = frgu.request_group_id and frg.application_id = frgu.application_id and fcpt.language='US' and fcp.application_id = fcpt.application_id and fcp.concurrent_program_id = fcpt.concurrent_program_id and frs.application_id = frt.application_id and frs.responsibility_id = frt.responsibility_id and frt.language='US' and frs.request_group_id = frg.request_group_id and frs.application_id = frg.application_id and fcp.concurrent_program_name = nvl(:concurrent_program_name,fcp.concurrent_program_name) and fcpt.user_concurrent_program_name =nvl(:user_concurrent_program_name ,fcpt.user_concurrent_program_name) order by fcpt.user_concurrent_program_name;


Request Set
select frst.user_request_set_name ,frt.responsibility_name ,frg.request_group_name from apps.fnd_request_sets_tl frst ,apps.fnd_request_sets frs ,fnd_request_group_units frgu ,fnd_request_groups frg ,fnd_responsibility_tl frt ,fnd_responsibility fr where 1=1 and fr.application_id = frt.application_id and fr.responsibility_id = frt.responsibility_id and frt.language = 'US' and fr.request_group_id = frg.request_group_id and fr.application_id = frg.application_id and frg.request_group_id = frgu.request_group_id and frg.application_id = frgu.application_id and frgu.unit_application_id = frs.application_id and frgu.request_unit_id=frs.request_set_id and frs.request_set_id=frst.request_set_id and frst.language='US' and frst.user_request_set_name= :user_request_set_name order by frst.user_request_set_name;

How to create Oracle AWR report for a single instance Oracle database

How to create Oracle AWR report for a single instance Oracle database

To create the AWR report for a single instance Oracle database (use awrrpt.sql) as:

1. Find the script
2. Login as sysdba
3. Execute the script. While executing supply the following:

a. format of the report (html/text)

b. for number of days (don’t enter anything if you want to generate for specific snapshots). press enter

c. enter starting snapshot id from the displayed list

d. enter ending snapshot id from the displayed list

e. give a name for the report including file extension (txt/html/htm)

4. Once report is generated you exit sql*plus and view the report in the server or
5. transfer the report to your local machine and view it.

Here are the steps:

[oracle@DEVSERV ~]$ ls -l /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrr*
-rw-r–r– 1 oracle oinstall 7575 Apr 18  2005 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpti.sql
-rw-r–r– 1 oracle oinstall 1999 Oct 24  2003 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpt.sql
[oracle@DEVSERV ~]$
[[oracle@DEVSERV ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 – Production on Wed Apr 20 15:45:45 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pages 999 line 300
SQL>
SQL>
SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id    DB Name      Inst Num Instance
———– ———— ——– ————
2330100236 DEVDB               1 DEVDB

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: text

Type Specified:  text

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id     Inst Num DB Name      Instance     Host
———— ——– ———— ———— ————
* 2330100236        1 DEVDB        DEVDB        DEVSERV

Using 2330100236 for database Id
Using          1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days:

Listing all Completed Snapshots

Snap
Instance     DB Name        Snap Id    Snap Started    Level
———— ———— ——— —————— —–
DEVDB        DEVDB            24129 07 Apr 2011 01:00      1
24130 07 Apr 2011 02:00      1
24131 07 Apr 2011 03:00      1
24132 07 Apr 2011 04:00      1
24133 07 Apr 2011 05:00      1
24134 07 Apr 2011 06:00      1
24135 07 Apr 2011 07:00      1
24136 07 Apr 2011 08:00      1
24137 07 Apr 2011 09:00      1
24138 07 Apr 2011 10:00      1
24139 07 Apr 2011 11:00      1
24140 07 Apr 2011 12:00      1
24141 07 Apr 2011 13:00      1
24142 07 Apr 2011 14:00      1
24143 07 Apr 2011 15:00      1
24144 07 Apr 2011 16:00      1
24145 08 Apr 2011 18:00      1
24146 08 Nov 2011 18:00      1
24147 08 Feb 2012 13:56      1
24148 12 Oct 2013 18:00      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 24146
Begin Snapshot Id specified: 24146

Enter value for end_snap: 24148
End   Snapshot Id specified: 24148

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_24146_24148.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: new_awr_report_24146_24148.txt

Using the report name new_awr_report_24146_24148.txt

Report will be generated now and scroll through the screen. Wait until it completes.

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
———— ———– ———— ——– ————— ———– —
DEVDB         2330100236 DEVDB               1 11-Mar-11 12:42 11.1.0.7.0  NO

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
—————- ——————————– —- —– ——- ———-
DEVSERV          Linux x86 64-bit                    8     8       4      31.29

Snap Id      Snap Time      Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap:     24146 08-Nov-11 18:00:59       125       1.0
End Snap:     24148 12-Oct-13 18:00:50       123        .9
Elapsed:        1,013,759.86 (mins)
DB Time:        7,337,318.40 (mins)

——————–

——————–

——————–

——————–

——————–

End of Report
Report written to new_awr_report_24146_24148.txt
SQL>

How to purge e-mail notifications from the Workflow queue

How to purge e-mail notifications from the Workflow queue

Some times due to large number of e-mail notifications to accumulated in the queue Workflow mailer will not start,To fix this issue we need purge the notifications from the Workflow queue.


The below outlines the steps, Please take proper backup before performing the below.

1) You need to update the notifications you do not want sent, in the WF_NOTIFICATIONS table.

2) Check the WF_NOTIFICATIONS table as below. Records where status = 'OPEN' and mail_status = 'MAIL' are notifications that will have an e-mail notification sent.

SQL> select notification_id,status,mail_status,begin_date from WF_NOTIFICATIONS where status = 'OPEN' and mail_status = 'MAIL';

3) This should show you which notifications are waiting to be e-mailed.

4) To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = 'SENT'. The mailer will think the e-mail has already been sent and it will not send it again.

SQL> update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

-> This will update all notifications waiting to be sent by the mailer.

5) Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer.

SQL>sqlplus apps/apps_pwd @$FND_TOP/patch/115/sql/wfntfqup APPS APPS_PWD APPLSYS

6) Now you can start your WF Containers and then Mailer

Reference : How To Purge E-Mail Notifications From The Workflow Queue So The E-Mail Is Not Sent (Doc ID 372933.1)

Copy data from one database / table to another database / table in oracle using TOAD

Copy data from one database / table to another database / table in oracle using TOAD


A. Create a copy of the table in destination DB
1.In source DB in a schema browser window click on the table you want to copy, select "script" tab in the right part of the window: you will find the script to re-create your table; copy this script
2.Paste the script in a new SQL editor window in destination DB and run it. This should create the new table

B. Copy data in new table
1. In a schema browser window right click on table name in source DB
2. Select "Export Data" from context menu
3. Write "where" statement of your export query (leave it blank if you want to copy the entire table)
4. Select destination: clipboard
5. Click "ok" (now insert statements are stored in your clipboard)
6. Paste insert statements in a new SQL editor window in destination DB
7. Run statements as script (shortcut F5)
Through database link ( dblink )

INSERT INTO tool_service_mapping SELECT * FROM tool_service_mapping@<<db link to source database>>