Oracle Database

Indexes Not Used

by

SELECT * FROM all_views WHERE view_name LIKE ‘%USAGE%’ select a.index_name, (s.bytes/1024/1024/1024) from all_object_usage a, dba_segments s where a.index_name=s.segment_name and a.used=’NO’ group by a.index_name; SELECT SUM (ROUND (bytes / 1024 / 1024 / 1024)) AS GB FROM dba_segments WHERE segment_name IN (SELECT index_name FROM all_object_usage WHERE used = ‘NO’); SELECT * FROM gisdba.dba_object_usage; SELECT (bytes / … Read more

Average Active Sessions

by

How to find the average active sessions between 2 dates. SELECT rollup_timestamp, average, minimum, maximum FROM mgmt$metric_daily WHERE target_name = ‘TRCOPDOV’ AND metric_label = ‘Throughput’ AND column_label = ‘Average Active Sessions’ AND rollup_timestamp BETWEEN TO_DATE (’01/11/2015′, ‘dd/mm/yyyy’) AND TO_DATE (’01/12/2015′, ‘dd/mm/yyyy’) ORDER BY 1;

ASM Disk Usage

by

SELECT target_name, diskgroup, MAX (DECODE (seq, 7, VALUE)) REDUNDANCY, MAX (DECODE (seq, 4, VALUE)) PERCENT_USED, MAX (DECODE (seq, 6, ceil(VALUE/1024))) TOTAL_GB, MAX (DECODE (seq, 9, ceil(VALUE/1024))) USABLE_TOTAL_GB, MAX (DECODE (seq, 3, ceil(VALUE/1024))) FREE_GB, MAX (DECODE (seq, 8, ceil(VALUE/1024))) USABLE_FREE_GB, MAX (DECODE (seq, 2, VALUE)) NO_OF_DISK, ceil(( MAX (DECODE (seq, 6, ceil(VALUE/1024)))) /(MAX (DECODE (seq, 2, … Read more

Oracle – How to Empty the Listener.log File

by

Solution Step 1. Set the environment of the effected listener.log file If there are multiple listeners on the box, you can use the following syntax lsnrctl SET CURRENT_LISTENER [listener_name] $ lsnrctl LSNRCTL for Solaris: Version 8.1.7.4.0 – Production on 31-AUG-2012 08:21:39 (c) Copyright 1998 Oracle Corporation. All rights reserved. Welcome to LSNRCTL, type “help” for … Read more

How To Save Database Statistics Before They Are Updated

by

Step-by-step guide Exporting statistics is a three step process. The result is a single table containing schema statistics (table, index and column) and system statistics (workload and non-workload). The first step creates a physical version of a StatTable. The StatTable is a consolidated table to hold all types of statistics, so the format is very … Read more