Monday, March 23, 2009

Queries to verify OATM Migration

select migration_status,error_text from FND_TS_MIG_CMDS where migration_status ='ERROR'

select * from all_objects where object_name like 'FND_TS%' and object_type='TABLE'

select * from FND_TS_MIG_STATUS

select * from FND_TS_SIZING

select IS_NEW_TS_MODE from fnd_product_groups (o/p should be “Y”

Tuesday, March 17, 2009

Query to find type of objects available in a tablespace

select ds.segment_name,do.object_type,do.status,ds.tablespace_name from dba_segments ds,dba_objects do where ds.tablespace_name='TOOLS'

Query to count no. of segments in each t.s.

select tablespace_name,count(*) from dba_segments group by tablespace_name;

Monday, March 16, 2009

Query to get workflow using WFLOAD

WFLOAD apps/W1tty 0 Y DOWNLOAD <<(d/w file)I2PATCARD_prd.wft>> <<(original file) PATCARD>>

tkprof Execution

tkprof orcl_ora_4930.trc report.out SYS=NO EXPLAIN=SCOTT/TIGER

Monday, March 9, 2009

Queries to find Database Size

The database mainly comprises of datafiles, temp files and redo log files.
The biggest portion of a database’s size comes from the datafiles.

To find out how many megabytes are allocated to all datafiles:

SELECT sum(bytes)/1024/1024 data_size FROM dba_data_files;

To get the size of all TEMP files:

SELECT nvl(sum(bytes),0)/1024/1024 temp_size FROM dba_temp_files;

To get the size of the on-line redo-logs:

SELECT sum(bytes)/1024/1024 redo_size FROM sys.v_$log;

Finally, summing up the three above, total database size can be found:

SELECT (dsize.data_size + tsize.temp_size + rsize.redo_size)/1024/1024 "total_size"
FROM (SELECT sum(bytes) data_size
FROM dba_data_files ) dsize,
(SELECT nvl(sum(bytes),0) temp_size
FROM dba_temp_files ) tsize,
(SELECT sum(bytes) redo_size
FROM sys.v_$log ) rsize;

Query to find space used by a database user

Query to find space used by a database user. Following query can be used to know the space used by the logged in user in MBs:

SELECT sum(bytes)/1024/1024 user_size FROM user_segments;

Query to find the space occupied by all the users in a database. This requires access to dba_segments table:

SELECT owner, sum(bytes)/1024/1024 total_size FROM dba_segments
GROUP BY owner ORDER BY total_size DESC;

Total space occupied by all users:

SELECT sum(bytes)/1024/1024 total_size FROM dba_segments;

Wednesday, March 4, 2009

Login to Oracle Applications directly thru f60cgi

Accessing f60cgi is disabled in 11.5.10

With increased security in Oracle EBusiness Suite 11.5.10, the ability to
connect directly to forms via f60cgi has been disabled. By default, a user will
see the following error after entering their username and password:

APP-FND-01542: This Applications Server is not authorized to access this database.

This is expected functionality.

Enabling f60cgi direct login


It is possible to login however this method should only be used when
debugging problems.

1. Backup and open $APPL_TOP/admin/_.xml context file

2. Update the context variable:

By default in 11.5.10, this is set to SECURE.
In previous 11i versions, this was set to OFF.
For debug purposes, you can use ON or OFF.

- ON : Partial
- SECURE : activates full server security (SECURE mode)
- OFF : deactivates server security

3. Run Autoconfig to instantiate the change.

You should now be able to access forms directly again using the f60cgi call.

4. After you have finished your Forms debugging, please reset
s_appserverid_authentication to SECURE and re-run Autoconfig.

Alternative option

Running Autoconfig is the preferred method of updating

If you are unable to run Autoconfig during troubleshooting, you can run the
the following commands instead from $FND_TOP/secure directory:


java apps/apps \
AUTHENTICATION OFF DBC=host_visdemo1.dbc


To activate basic server security, from the command line, enter:

jre apps/apps \

To activate full server security (SECURE mode), from the command
line, enter:

jre apps/apps \

Check the status:

java apps/apps \
STATUS DBC=host_visdemo1.dbc

552301.1 How To Access Forms Directly In Oracle Applications R12

Tuesday, March 3, 2009

Query to find the responsibilty for which form was assigned

SELECT fu.user_name,fu.description,furg.start_date,
FROM fnd_user fu,fnd_user_resp_groups furg,fnd_responsibility_vl frvl,
fnd_compiled_menu_functions fcmf,fnd_form_functions_vl fff,fnd_form_vl ff
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = frvl.responsibility_id
AND frvl.menu_id = fcmf.menu_id
AND fff.function_id = fcmf.function_id
AND fff.form_id = ff.form_id
AND (fu.end_date IS NULL OR fu.end_date >= SYSDATE)
AND (furg.end_date IS NULL OR furg.end_date >= SYSDATE)
AND (frvl.end_date IS NULL OR frvl.end_date >= SYSDATE)
AND form_name IN ('FORMNAME')