Database Scripts

QUERY TO CHECK SIZE OF ALL TABLESPACES

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

Generating AWR Reports You can generate AWR reports by running SQL scripts, as described below :- # Generating an AWR Report @$ORACLE_HOME/rdbms/admin/awrrpt.sql   # To generate an AWR report on …

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

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 …

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

SQL Queries to Check ACTIVE / INACTIVE Sessions

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, Decode(r.status_code, ‘A’, ‘Waiting’, ‘B’, ‘Resuming’, ‘C’, ‘Normal’, ‘D’, ‘Cancelled’, ‘E’, ‘Error’, ‘F’, ‘Scheduled’, ‘G’, ‘Warning’, ‘H’, ‘On Hold’, ‘I’, ‘Normal’, ‘M’, ‘No Manager’, …

Oracle Forms Session Queries