How to Get Apps Password From Backend STEP 1. First login to Database through Command Prompt sqlplus / as sysdba STEP 2 : Create Function From Below Script …
Read More »How to Find Request Group associated for a Program
How to Find Request Group associated for a Program SELECT rg.application_id “Request Group Application ID”, rg.request_group_id “Request Group – Group ID”, rg.request_group_name, rg.description, rgu.unit_application_id, rgu.request_group_id “Request Group Unit – Group ID”, rgu.request_unit_id, cp.concurrent_program_id, cp.concurrent_program_name, cpt.user_concurrent_program_name, Decode(rgu.request_unit_type, ‘P’, ‘Program’, ‘S’, ‘Set’, rgu.request_unit_type) “Unit Type” FROM fnd_request_groups rg, fnd_request_group_units rgu, fnd_concurrent_programs cp, fnd_concurrent_programs_tl cpt WHERE rg.request_group_id = rgu.request_group_id …
Read More »How to Find Scheduled Concurrent Program
How to Find Scheduled Concurrent Program Below Query will Fetch all the details for a scheduled concurrent program. SELECT a.requested_by, a.status_code, a.phase_code, a.request_id, b.user_concurrent_program_name, c.concurrent_program_name, a.requested_start_date, c.execution_method_code, d.execution_file_name, d.execution_file_path FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_concurrent_programs c, …
Read More »How to Find Database Object and it’s Corresponding Datafiles
How to Find Database Object and it’s Corresponding Datafiles SELECT DISTINCT t.ts#, t.name AS Tablespace_name, d.name AS DATAFILE, s.owner, s.segment_name, s.segment_type, s.bytes / 1024 / 1024 AS “MB” FROM v$tablespace t, v$datafile d, dba_segments s WHERE t.ts# = d.ts# AND t.name = s.tablespace_name AND s.owner = ‘APPS’;
Read More »How to Find CPU Usage for a Session
How to Find CPU Usage for a Session SET linesize 145 SET pagesize 9999 COLUMN sid format 9999 heading ‘SID’ COLUMN serial_id format 999999 heading ‘Serial#’ COLUMN session_status format a9 heading ‘Status’ justify right
Read More »Query To find the details of the Accounting Flexfield structure
SELECT gls.name, idfs.id_flex_num chart_of_accounts_id, idfs.segment_num, idfs.flex_value_set_id, fvs.flex_value_set_name, idfs.application_id, idfs.id_flex_code, idfs.application_column_name, idfs.segment_name, fvs.security_enabled_flag, ( CASE WHEN fvs.validation_type = ‘F’ THEN ‘Table’ WHEN fvs.validation_type = ‘I’ THEN ‘Independent’ WHEN fvs.validation_type = ‘D’ THEN ‘Dependent’ WHEN fvs.validation_type = ‘N’ THEN ‘None’ WHEN fvs.validation_type = ‘P’ THEN ‘Pair’ WHEN fvs.validation_type = ‘U’ THEN ‘Special’ ELSE ‘Unknown Type’ END ) validation_type, ( CASE WHEN fvs.validation_type = ‘F’ THEN fvt.application_table_name ELSE ‘Not Applicable’ END ) validation_table_name FROM gl_ledgers gls, fnd_id_flex_segments idfs, fnd_flex_value_sets fvs, fnd_flex_validation_tables fvt WHERE gls.chart_of_accounts_id (+) = idfs.id_flex_num AND fvs.flex_value_set_id = idfs.flex_value_set_id AND gls.ledger_id = &ledgerid …
Read More »How To Move Table to Different TableSpace
How To Move Table to Different TableSpace SYNTAX ALTER TABLE table_name move TABLESPACE tablespace_name (INITIAL=64k MINEXTENTS=1 MAXEXTENTS=UNLIMITED); EXAMPLE ALTER TABLE hr.employee_details move TABLESPACE hr; OR ALTER TABLE hr.employee_details move TABLESPACE hr (INITIAL=64k MINEXTENTS=1 MAXEXTENTS=UNLIMITED);
Read More »Undo Related Queries Part 2
List of All active Transactions
Read More »PL/SQL Code For Dropping All Objects in Schema
PL/SQL Code For Dropping All Objects in Schema DECLARE v_itemcount INTEGER; BEGIN SELECT Count(*) INTO v_itemcount FROM all_objects AO WHERE AO.owner = USER AND AO.object_type NOT IN ( ‘INDEX’ ) AND AO.object_name NOT LIKE ‘BIN$%’; WHILE ( v_itemcount > 0 ) LOOP FOR v_cmd IN (SELECT ‘drop ‘ || AO.object_type || ‘ ‘ || AO.object_name || Decode(AO.object_type, ‘TABLE’, ‘ CASCADE CONSTRAINTS’, ”) AS DROPCMD FROM all_objects AO WHERE AO.owner = USER AND AO.object_type NOT IN ( ‘INDEX’ ) AND AO.object_name NOT LIKE ‘BIN$%’) LOOP …
Read More »Undo Related Queries Part 1
To check Retention Guarantee for Undo Tablespace
Read More »