Scripts

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

List of All active Transactions

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 …

To check Retention Guarantee for Undo Tablespace