Scripts

QUERY TO CHECK LIST OF RUNNING REQUEST

QUERY TO GET all user assigned to GIVEN RESPONSIBILITY

QUERY TO CHECK SIZE OF ALL TABLESPACES

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 …

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  …

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,  …

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’;  

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

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  …

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);