Database Scripts

September 1, 2017 PL/SQL Code For Dropping All Objects in Schema

Database ScriptsScripts

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

September 1, 2017 PL/SQL Code for Counting Rows In Schema Tables

Database ScriptsScripts

PL/SQL Code for Counting Rows In Schema Tables DECLARE i INTEGER; BEGIN dbms_output.Put_line(‘Table Name,Row Count,’ || To_char(SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’)); FOR v_table IN (SELECT ut.table_name FROM user_tables ut) LOOP EXECUTE IMMEDIATE ‘select count(*) from ‘|| v_table.table_name INTO i; dbms_output.Put_line(v_table.table_name || ‘,’ || To_char(i));…

September 1, 2017 SQL Queries to Check ACTIVE / INACTIVE Sessions

AppsDBA ScriptsDatabase ScriptsScripts

SQL Queries to Check ACTIVE / INACTIVE Sessions

September 1, 2017 Concurrent Request Queries

AppsDBA ScriptsDatabase ScriptsScripts

Concurrent Request Queries HISTORY OF CONCURRENT REQUEST (PROGRAM WISE) SELECT DISTINCT t.user_concurrent_program_name, r.request_id, To_char(r.actual_start_date, ‘dd-mm-yy hh24:mi:ss’) “Started at”, To_char(r.actual_completion_date, ‘dd-mm-yy hh24:mi:ss’) “Completed at”, Decode(r.phase_code, ‘C’, ‘Completed’, ‘I’, ‘Inactive’, ‘P ‘, ‘Pending’, ‘R’, ‘Running’, ‘NA’) phasecode,…