Oracle DBA World

August 2015

DDL With the WAIT Option (DDL_LOCK_TIMEOUT) :

ORA-00054: resource busy" error

DDL commands require exclusive locks on internal structures. If these locks are not available the commands return with an "ORA-00054: resource busy" error message. This can be especially frustrating when trying to modify objects that are accessed frequently. To get round this Oracle 11g includes the DDL_LOCK_TIMEOUT parameter, which can be set at instance or session level using the ALTER SYSTEM and ALTER SESSION commands respectively.

The DDL_LOCK_TIMEOUT parameter indicates the number of seconds a DDL command should wait for the locks to become available before throwing the resource busy error message. The default value is zero.

ALTER SESSION SET ddl_lock_timeout=30;

Unusable indexes

Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is marked as 'unusable' with an ALTER INDEX command. A direct path

load against a table or partition will also leave its indexes unusable.
Queries and other operations agains a table with unuable indexes will generate errors:

ORA-01502: index ‘string.string’ or partition of such index is in unusable state

#################################################################################
unusable indexes
#################################################################################
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where STATUS='!VALID' group by OWNER,OBJECT_TYPE,status;
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where status='INVALID' group by OWNER,OBJECT_TYPE,status;
SELECT owner,index_name,TABLE_OWNER,TABLE_NAME,PARTITIONED,TABLESPACE_NAME,STATUS
FROM all_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM')
AND status != 'VALID'
AND
(
status != 'N/A'
OR index_name IN
(
SELECT index_name
FROM all_ind_partitions
WHERE status != 'USABLE'
AND
(
status != 'N/A'
OR index_name IN
(
SELECT index_name
FROM all_ind_subpartitions
WHERE status != 'USABLE'
)
)
)
);

Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||';' from USER_IND_PARTITIONS;
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from USER_IND_PARTITIONS;
Select 'ALTER INDEX '||INDEX_OWNER||'.'|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from DBA_IND_PARTITIONS
where INDEX_OWNER='ERXUATOWNER' and status='UNUSABLE';
Select 'ALTER INDEX '||INDEX_OWNER||'.'|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from DBA_IND_PARTITIONS
where INDEX_OWNER='ERXUATOWNER' and status='UNUSABLE';

#################################################################################
The following SQL comands can be used to detect unusable indexes:

Indexes:
SELECT owner, index_name, tablespace_name
FROM dba_indexes
WHERE status = 'UNUSABLE';

Index partitions:

SELECT index_owner, index_name, partition_name, tablespace_name
FROM dba_ind_PARTITIONS WHERE status = 'UNUSABLE';

The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:

Indexes:

SELECT 'alter index '||index_name||' rebuild tablespace '||tablespace_name ||';'
FROM dba_indexes WHERE status = 'UNUSABLE';

Index partitions:

SELECT 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';' FROM dba_ind_partitions WHERE status = 'UNUSABLE';

Generic:

1. Installation
2. Upgradation
3. DB creation
4. Apply the patch

ASM:

1. How to create the ASM database?
2. How to install software
3. How to upgrade ASM software
4. How to apply the patch

RAC

1.Steps to Install RAC software
2.Steps to create RAC database

Standby:

1. How to build the Physical standby?
2. How to build the Logical standby?
3. How to create the Snapshot standby
4. How to convert Physical standby to Active standby?
5. How to build the Physical stabdby using duplicate command?

Cloning:

1. How to clone the database with RMAN

2. EXPORT AND IMPORT

Steps to keep the standby database in read only mode

1, Cancel the recovery

alter database recover managed standby database cancel;

2. Start the database in read only mode

alter database open read only;

3. Keep the database is in recovery mode.

alter database recover managed standby database USING CURRENT LOGFILE disconnect from session;

4. Check for MRP is started

ps -ef|grep mrp

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS, DELAY_MINS from v$managed_standby;

5. Make sure the standby is sync with Primary

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN

(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Create Restore point:

% sqlplus “/as sysdba”
SQL> shutdown immediate;
SQL> startup mount;
SQL> CREATE RESTORE POINT 'abc_hg’ GUARANTEE FLASHBACK DATABASE;
SQL> alter database open;
SQL> select scn from v$restore_point where name = 'abc_hg';
SQL> select current_scn from V$database;

Fix the archive GAP issues in DATAGUARD

Gap is a range of archived redo logs that were created at a time when the standby database was not available to receive them.in many pratices it happen because the network problems.

We have 2 method to resolving this problem.

a. Manually resolving
b. Automatic resolving : Using log switched and FAL configuration

1. start with detecting gaps in the redo logs by querying the v$archive_gap.

SQL> select * from v$archive_gap;

thread# low_sequence# high_sequence#
-------- ---------------- ------------------
1 30 32
The output indicates our standby database is currently missing log files from sequence 30 to 34.

2. issue following statement on primary database to locate the archived redo log files. assuming
the local archive destination on primary is LOG_ARCHIVE_DEST_1

SQL> select name from v$archived_log where thread#=1 and dest_id=1 and sequence# between 30 and 32;
name
----------------------------------
/usr/oraarchive/hai/arch_t1_s30.dbf
/usr/oraarchive/hai/arch_t1_s31.dbf
/usr/oraarchive/hai/arch_t1_s32.dbf
/usr/oraarchive/hai/arch_t1_s33.dbf
/usr/oraarchive/hai/arch_t1_s34.dbf

3. stop the automatic recovery (MRP) of the data guard

SQL> alter database recover managed standby database cancel;

4. transfer manually the archived log files shown on the step 2 to standby database

5. register that archived log files on standby database

SQL> alter database register logfile '/usr/oraarchive/hai/arch_t1_s30.dbf';
SQL> alter database register logfile '/usr/oraarchive/hai/arch_t1_s31.dbf';
SQL> alter database register logfile '/usr/oraarchive/hai/arch_t1_s32.dbf';

6. put the standby database into automatic recovery managed mode

SQL> alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database using current logfile disconnect from session;

7. verify that the gap gets resolved on standby database

SQL> select sequence#, applied from v$archived_log order by sequence#;

SQL> select message from v$dataguard_status where severity like 'Warning';

8. Check the alrt log

#################################################################################
DATAGUARD SYNC
#################################################################################

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

select max(sequence#), thread#
from v$archived_log
group by thread#
order by 2;

select max(sequence#), thread#, applied
from v$archived_log
where applied='YES'
group by thread#, applied
order by 2,3;

set linesize 300
SELECT INST_ID,PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS, DELAY_MINS from gv$managed_standby;

select PROCESS, SEQUENCE#, THREAD#, BLOCK#, BLOCKS, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') TIME from V$MANAGED_STANDBY where PROCESS='MRP0';

#################################################################################
How to register the archive logs in standby
#################################################################################

select PROCESS, SEQUENCE#, THREAD#, BLOCK#, BLOCKS, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') TIME from V$MANAGED_STANDBY where PROCESS='MRP0';

Step 1:
------

copy the missing file from sourec.

Step 2:
------

alter database register logfile '/tmp/patches/sagardb_100313/thread_2_seq_118083.3191.827805009';

#################################################################################
How to delete archivelogs from standby:
#################################################################################

option-3)
Here I am pulling the max sequence numbers of the archivelog for each thread( since I have 3 -node RAC) and doing minus 10 so that I will have at least 10 archivelogs in my disk at any given time for any thread and then passing these sequence numbers to RMAN to delete the archivelogs.

Below is the script.

#!/bin/ksh
export ORACLE_HOME=/u02/u0001/oracle/sagaruct/10.2.0/db_1
export ORACLE_SID=STANDBYDBY1
export NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export TODAY=`date '+%Y%m%d'`
export TIME=`date '+%H%M%S'`
rm /fin02/u0001/scripts/logs/1.tmp
$ORACLE_HOME/bin/sqlplus -s / as sysdba<set heading off
set pages 0 echo off feedback off
spool /fin02/u0001/scripts/logs/1.tmp
select thread#,max(sequence#)-10 from v\$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ;
spool off;
exit;
EOF

seq1=`cat /fin02/u0001/scripts/logs/1.tmp | awk 'NR==1{print $2}'`
seq2=`cat /fin02/u0001/scripts/logs/1.tmp | awk 'NR==2{print $2}'`
seq3=`cat /fin02/u0001/scripts/logs/1.tmp | awk 'NR==3{print $2}'`
echo $seq1 $seq2 $seq3

$ORACLE_HOME/bin/rman msglog /fin02/u0001/scripts/logs/STANDBYDBY_ARC_DEL.${TODAY}_${TIME}.log << EOF

connect target /
run{
delete noprompt archivelog until sequence $seq1 thread 1;
delete noprompt archivelog until sequence $seq2 thread 2;
delete noprompt archivelog until sequence $seq3 thread 3;
}
EOF

select thread#,max(sequence#)-10 from gv$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ;

THREAD# MAX(SEQUENCE#)-10
---------- -----------------
1 19515
2 19316

archived log file name=+RECO/pgrptdg/archivelog/2014_08_15/thread_2_seq_15797.1527.855650967 RECID=30269 STAMP=855650972

select thread#,max(sequence#)-10 from gv$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ;

THREAD# MAX(SEQUENCE#)-10
---------- -----------------
1 19515
2 19316

delete noprompt archivelog until sequence 19515 thread 1;

delete noprompt archivelog until sequence 19316 thread 2;

delete noprompt archivelog until sequence 19316 thread 2;

SQL> select thread#,max(sequence#)-10 from gv$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ;

THREAD# MAX(SEQUENCE#)-10
---------- -----------------
1 19567
2 19368

delete noprompt archivelog until sequence 19316 thread 2;
delete noprompt archivelog until sequence 19567 thread 1;

THREAD# MAX(SEQUENCE#)-10
---------- -----------------
1 19618
2 19418

RMAN> delete noprompt archivelog until sequence 19515 thread 1;

Tablespace Growth History and Forecast
To get information about full database growth history and forecast, please see my article and script by clicking here.

Starting Oracle 10G, Oracle records tablespaces usage (allocated, used etc.) in AWR which can be retrieved by querying the data dictionary view

dba_hist_tbspc_space_usage. Following scripts can be used to view the history of tablespace(s) usage and predict the expected growth for the future.
Growth forecast is based on daily growth in the past.

This script is based on AWR. If your AWR retention period is 7 days, this script can only tell the growth history of last 7 days and predict based on last 7 days AND 30DAYS retention is always good.

Script for Single Tablespace

##############################################
set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
v_ts_name varchar2(200) := UPPER('&Tablespace_Name');
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_ts_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_ts_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;

SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id

= v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id =

v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id =

v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id =

v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round

(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('No data growth was found for this Tablespace');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||'

GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('If no data is displayed for this tablepace, it means AWR does not have any data for this tablespace');
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace usage information not found in AWR');

END;
/
##########################################################################################################################################

Sample Output

Enter value for tablespace_name: TEST

Tablespace Block Size: 8192
---------------------------

Summary
========
1) Allocated Space: 2048 MB (2 GB)
2) Used Space: 1558.44 MB (1.52 GB)
3) Used Space Percentage: 76.1 %

History
========
1) Allocated Space on 06-DEC-14: 2048 MB (2 GB)
2) Current Allocated Space on 10-JAN-15: 2048 MB (2 GB)
3) Used Space on 06-DEC-14: 1273 MB (1.24 GB)
4) Current Used Space on 10-JAN-15: 1558.44 MB (1.52 GB)
5) Total growth during last 35 days between 06-DEC-14 and 10-JAN-15: 285.44 MB (.28 GB)
6) Per day growth during last 35 days: 8.16 MB (.01 GB)

Expected Growth
===============
1) Expected growth for next 30 days: 244.66 MB (.24 GB)
2) Expected growth for next 60 days: 489.33 MB (.48 GB)
3) Expected growth for next 90 days: 733.99 MB (.72 GB)
If no data is displayed for this tablepace, it means AWR does not have any information for this tablespace
PL/SQL procedure successfully completed.

Script for All Tablespace (Except UNDO and TEMP)

set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
cursor v_cur is select tablespace_name from dba_tablespaces where contents='PERMANENT';

BEGIN
FOR v_rec in v_cur
LOOP
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_rec.tablespace_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_rec.tablespace_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;

SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id

= v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id =

v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id =

v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id =

v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round

(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('No data growth was found for this Tablespace');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||'

GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
END IF;
DBMS_OUTPUT.PUT_LINE('If no data is displayed for this tablepace, it means AWR does not have any data for this tablespace');
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\');

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace usage information not found in AWR');
NULL;
END;
END LOOP;
END;
/

ENABLE AND DISABLE THE AUTO TASK IN 11G

BEGIN
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
END;

BEGIN
dbms_auto_task_admin.enable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;

SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED

BEGIN
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
dbms_auto_task_admin.enable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;

ADDING DISKS TO DISKGROUP - add as sysasm USER

SQL> select * from v$asm_operation ;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
1 REBAL RUN 1 1 2378 195064 1665 115

1)select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk order by 6;

set linesize 200
col path for a45
select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk order by 6;

2) login as / as sysasm
select INSTANCE_NAME from v$instance;

3) Check for CANDIDATE disk

COL NAME FOR A20
COL PATH FOR A55
set linesize 300

select group_number,name, state, name, path, header_status,MODE_STATUS from v$asm_disk where header_status='CANDIDATE';
select group_number,name, state, name, path, header_status from v$asm_disk where header_status='CANDIDATE' and path like 'data01_asm%';
select group_number, state, name, path, header_status from v$asm_disk
select group_number,name, state, name, path, header_status from v$asm_disk where group_number=1;

4) !ls -ltr /dev/hai/ASM/hdd_disk_0015_L1A0

5) Add the disk

alter diskgroup DATA01 add disk '/dev/hai/ASM/hdd_disk_0015_L1A0' rebalance power 3;
alter diskgroup DATA01 add disk '/dev/hai/ASM/ods_asm_vol_66' rebalance power 3;

6) Check the space in ASM disk group

select GROUP_NUMBER,NAME,TOTAL_MB,FREE_MB,OFFLINE_DISKS from V$ASM_DISKGROUP;

GROUP_NUMBER NAME TOTAL_MB FREE_MB OFFLINE_DISKS
------------ ------------------------------ ---------- ---------- -------------
1 DATA01 3632389 101057 0

7)select * from v$asm_operation;

Change db_recovery_file_dest_size
=================================
Intiacl checks

1. Select * from v$flash_recovery_area_usage;

2. show parameter db_recovery

3. SELECT GROUP_NUMBER,NAME,TOTAL_MB,FREE_MB,USABLE_FILE_MB FROM V$ASM_DISKGROUP;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FLASHDG
db_recovery_file_dest_size big integer 13000M

alter system set db_recovery_file_dest_size=1650G scope=both;
alter system set db_recovery_file_dest = '+DATA01' SCOPE=BOTH;

Select Substr(Name,1,40) name,
Space_Limit/1024/1024/1024 Space_Limit,
Space_Used/1024/1024/1024 Spce_Used,
Space_Reclaimable/1024/1024/1024 Space_Reclaimable,
Number_Of_Files Number_files
from V$RECOVERY_FILE_DEST;

Final Checks:

4. Select * from v$flash_recovery_area_usage;

5. show parameter db_recovery

6. SELECT GROUP_NUMBER,NAME,TOTAL_MB,FREE_MB,USABLE_FILE_MB FROM V$ASM_DISKGROUP;

Configuring FRA

Following are the three initialization parameters that should be defined in order to set up the flash recovery area:

DB_RECOVERY_FILE_DEST_SIZE
DB_RECOVERY_FILE_DEST
DB_FLASHBACK_RETENTION_TARGET

DB_RECOVERY_FILE_DEST_SIZE specifies the total size of all files that can be stored in the Flash Recovery Area. The size of the flash recovery area should be large enough to hold a copy of all data files, all incremental backups, online redo logs, archived redo log not yet backed up on tape, control files, and control file auto backups.

ALTER SYSTEM SET db_recovery_file_dest_size = 10g SCOPE = OTH;

DB_RECOVERY_FILE_DEST parameter is to specify the physical location where all the flash recovery files are to be stored. Oracle recommends that this be a separate location from the datafiles, control files, and redo logs.

ALTER SYSTEM SET db_recovery_file_dest = '+DG1' SCOPE = BOTH;

If the database is using Automatic Storage Management (ASM) feature, then the shared disk area that ASM manages can be targeted for the Flashback Recovery Area.

ALTER SYSTEM SET db_recovery_file_dest = '+DG1' SCOPE = BOTH;

The DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST are defined to make the flash recovery area usable without shutting down and restarting the database instance i.e. these two parameters are dynamic.

ALTER SYSTEM SET db_flashback_retention_target = 1440 SCOPE = BOTH;

ONLINE_INDEX_CLEAN

If ORA-08104 error (ORA-08104: this index object < -- > is being online built or rebuilt) seen please run the below procedure

declare
isclean boolean;
begin
isclean := false;
while isclean = false
loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN
(dbms_repair.all_index_id, dbms_repair.lock_wait);
dbms_lock.sleep (10);
end loop;
end;
/

######################################################################################
0.DISK ERRORS :
######################################################################################
errpt
######################################################################################
1. Check the database AND LISTENER status
######################################################################################

ps -ef|grep pmon |grep -v

set linesize 200
col HOST_NAME for a20
select NAME,INSTANCE_NAME,LOG_MODE,OPEN_MODE,INSTANCE_NAME,HOST_NAME,STATUS,LOGINS,STARTUP_TIME from gv$instance, gv$database;

select NAME,LOG_MODE,OPEN_MODE,GUARD_STATUS,DATABASE_ROLE from gv$database;
select NAME,LOG_MODE,OPEN_MODE,GUARD_STATUS,DATABASE_ROLE from v$database;
select INSTANCE_NAME,HOST_NAME,status from gv$instance;
##############################################################################################
CHECK ANY ALERTS
##############################################################################################
select object_name, reason, metric_value, message_level,creation_time from sys.dba_outstanding_alerts;
###########################################################################################
2. Check listener status
###########################################################################################
lsnrctl status <LISTENERNAME>
###########################################################################################
3. Check alert log files
###########################################################################################
4.Check the database statistics
######################################################################################
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED,STATTYPE_LOCKED from dba_tab_statistics where TABLE_NAME='&Table_Name';
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from dba_tab_PARTITIONs where TABLE_NAME='&Table_Name';
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from dba_tab_PARTITIONs where TABLE_NAME='&Table_Name' order by 4
exec dbms_stats.gather_table_stats(ownname =>'ST_APP_1', tabname =>'TBF0_RX',estimate_percent =>20, method_opt => 'for all indexed columns size auto', cascade =>TRUE);
exec dbms_stats.gather_schema_stats(ownname => 'SYS', estimate_percent =>20, method_opt => 'for all indexed columns size auto', cascade =>TRUE);
select owner,table_name,last_analyzed from dba_tables where table_name='DEST';
select table_name,last_analyzed from dba_tables where owner='ABC' and last_analyzed like '18-NOV-14%';
######################################################################################
5. INVALID OBJECTS
######################################################################################
set linesize 200
col owner for a25
col OBJECT_NAME for a35
select count(*) from dba_objects where status='INVALID';
select owner,object_type,count(*) from dba_objects where status='INVALID' group by OWNER,object_type order by 1,2;
SELECT OWNER,OBJECT_NAME,STATUS FROM dba_objects WHERE STATUS='INVALID';
######################################################################################
5. Check the status and no of connections present in the database
######################################################################################
Log on Time:
============
select STATTYPE_LOCKED from dba_tab_statistics where STATTYPE_LOCKED='ALL';
select table_name,last_analyzed from dba_tables where owner='SYS' and last_analyzed like '27-NOV-14%';
select count(table_name) from dba_tables where owner='SYS' and last_analyzed like '27-NOV-14%'
select count(*) from dba_tables where owner='SYS';
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM,a.SQL_ID from gv$session a, gv$process b
where a.paddr=b.addr
and a.status='INACTIVE'
and a.logon_time < sysdate -8
order by 4;

SET LINESIZE 200
COL MACHINE FOR A20
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM,a.SQL_ID from gv$session a, gv$process b
where a.paddr=b.addr and a.status='INACTIVE' and a.username not in ('SYS','SYSTEM','DBSNMP');
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM,a.SQL_ID from gv$session a, gv$process b
where a.paddr=b.addr and a.username='HAI' order by 2;
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM from gv$session a, gv$process b
where a.paddr=b.addr and a.username='HAI' and sid=
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM from gv$session a, gv$process b
where a.paddr=b.addr and a.username='HAI' order by 2;
select a.username,to_char(a.logon_time,'dd:mm:yyyy') logon,count(*),a.status from gv$session a, gv$process b
where a.paddr=b.addr and a.username='HAI' group by a.username,to_char(a.logon_time,'dd:mm:yyyy'),a.status order by 2;
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM,a.SQL_ID from gv$session a, gv$process b
where a.sql_id='
select count(*) "Total Sessions" from v$session;
select username,status,count(*) from v$session group by username,status where status='INACTIVE';
select username,status from v$session group by username,status where status='INACTIVE';
select username,ACCOUNT_STATUS FROM dba_users
select username,ACCOUNT_STATUS FROM dba_users
select username,status,count(*) from v$session group by username,status;
select INST_ID,status,count(*) from gv$session group by INST_ID,status order by 1;
select INST_ID,status,username,count(*) from gv$session group by INST_ID,status,username order by 1;
select INST_ID,username,status,count(*) from gv$session where status='ACTIVE' and username not in ('SYS','SYSTEM','DBSNMP')
group by INST_ID,username,status ORDER BY 4;
select INST_ID,username,status,machine,count(*) from gv$session where status='ACTIVE' and username not in ('SYS','SYSTEM','DBSNMP') group by
INST_ID,username,status,machine ORDER BY 5;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session where status='ACTIVE' and username not in ('SYS','SYSTEM','DBSNMP') group by
INST_ID,username,status,machine,sql_id ORDER BY 6 DESC;
set linesize 300
col USERNAME for a12
col status for a15
select INST_ID,username,status,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP') group by INST_ID,username,status ORDER BY 4;
select INST_ID,username,status,machine,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP') group by INST_ID,username,status,machine ORDER BY 5;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP') group by INST_ID,username,status,machine,sql_id
ORDER BY 1,6 DESC;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP') and status='ACTIVE' group by
INST_ID,username,status,machine,sql_id ORDER BY 1,6 DESC;

select username,status,machine,count(*) from v$session group by username,status,machine ORDER BY 2;
select username,status,count(*) from v$session group by username,status ORDER BY 2;
select inst_id,count(*) from gv$session group by inst_id;
select username,status,machine,count(*) from v$session where status='ACTIVE' group by username,status,machine ORDER BY 3;
select username,status,machine,count(*) from v$session group by username,status,machine ORDER BY 3;
select username,status,count(*) from v$session where status='ACTIVE' group by username,status ORDER BY 3;
select username,status,machine,count(*) from v$session where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA') group by username,status,machine ORDER BY 4;
select INST_ID,username,status,count(*) from gv$session group by INST_ID,username,status ORDER BY 2;
select username,status,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA') and status='ACTIVE' group by username,status ORDER BY 3;
select INST_ID,username,status,machine,count(*) from gv$session group by INST_ID,username,status,machine ORDER BY 5;
select INST_ID,username,status,machine,count(*) from gv$session group by INST_ID,username,status,machine ORDER BY 5 DESC;
select INST_ID,username,status,machine,count(*) from gv$session where status='ACTIVE' group by INST_ID,username,status,machine ORDER BY 5 DESC;
select INST_ID,username,status,machine,LOGON_TIME from gv$session where username='ABALASUBRA';
select INST_ID,username,status,machine,LOGON_TIME from gv$session where status='INACTIVE';
select INST_ID,username,status,machine,LOGON_TIME from gv$session where status='INACTIVE' ORDER BY 5;
select INST_ID,username,status,machine,count(*) from gv$session group by INST_ID,username,status,machine order by 1;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,status,machine,sql_id ORDER BY 6 DESC;
select username,sql_id,status,machine,count(*) from gv$session group by username,status,machine,sql_id ORDER BY 2 DESC;
select username,status,machine,count(*) from gv$session
where username not in ('DBSNMP','TEMPDBA')
and status='ACTIVE'
group by username,status,machine ORDER BY 4 DESC;
select username,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by username,status,machine ORDER BY 4 DESC;
select INST_ID,username,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,status,machine ORDER BY 1,5 DESC;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,status,machine,sql_id ORDER BY 1,6 DESC;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='INACTIVE'
group by INST_ID,username,status,machine,sql_id ORDER BY 5 DESC;
select INST_ID,username,sql_id,status,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,sql_id,status ORDER BY 5 DESC;
select INST_ID,username,sql_id,status,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
group by INST_ID,username,sql_id,status ORDER BY 5 DESC;
select owner,table_name,last_analyzed from dba_tables where owner not in ('SYS','SYSTEM','DBSNMP',
'TEMPDBA','SYSMAN','OUTLN','APPQOSSYS','WMSYS','CTXSYS','EXFSYS','XDB','ORDDATA','MDSYS','ORDSYS','OLAPSYS','APEX_030200',');
and status='ACTIVE'
group by INST_ID,username,sql_id,status ORDER BY 5 DESC;
SELECT SID,SERIAL#,STATUS,MACHINE FROM V$SESSION WHERE SQL_ID='fwbqur34qynp3';
####################################################################################################################################
query to find the event
select SID,SERIAL# ,USERNAME,STATUS,machine,SQL_ID,LOGON_TIME,EVENT,PREV_SQL_ID from v$session where SID in ('5377','1138');
##############################################################################################################
Long running queries:
######################
select a.INST_ID,a.sid,a.serial#,a.username,a.process,a.LAST_CALL_ET,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b
where a.paddr=b.addr and a.username='RPTUSER' order by 7;
select a.INST_ID,a.sid,a.serial#,a.username,a.process,a.LAST_CALL_ET,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b
where a.paddr=b.addr and a.sql_id='8fkjugyagf3m7';
SELECT s.username,
sl.sid,
sl.last_update_time,
sl.sql_id,
target,
elapsed_seconds,
time_remaining,
sq.sql_fulltext
FROM v$session_longops sl
INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0;
##############################################################################################################
ACTIVE SESSIONS:
###################################################################################################################################################
set linesize 200
select INST_ID,username,status,machine,count(*) from gv$session group by INST_ID,username,status,machine ORDER BY 5 DESC;
select INST_ID,username,status,machine,count(*) from gv$session where status='ACTIVE' group by INST_ID,username,status,machine ORDER BY 5 DESC;
set long 50000
select SQL_FULLTEXT from gv$sqlarea where SQL_ID='&sql_id';
ORDER BY SQL_ID
===============
select sql_id,username,status,count(*) from v$session group by sql_id,username,status;
select sql_id,username,status,count(*) from v$session where status='ACTIVE' group by sql_id,username,status;
########################################################################################################
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid from v$session a, v$process b
where a.paddr=b.addr and a.process='20';
##############################################################################################################
To find SPID:
##############################################################################################################
PROMPT ENTER SID
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.username='IEMPHSYS' and a.status='ACTIVE';
where a.paddr=b.addr and a.sid='&sid';
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.paddr=b.addr and a.sid='&sid';
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.paddr=b.addr and b.spid='&spid';
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.username='IEMPHSYS' and a.status='INACTIVE';
#####################################################################################################################################################################
BLOCKING SESSION HISTORY:
##############################################################################################################
set pagesize 50
set linesize 120
col sql_id format a15
col inst_id format '9'
col sql_text format a50
col module format a10
col blocker_ses format '999999'
col blocker_ser format '999999'
SELECT distinct
a.sql_id ,
a.inst_id,
a.blocking_session blocker_ses,
a.blocking_session_serial# blocker_ser,
a.user_id,
s.sql_text,
a.module,
a.SAMPLE_TIME
FROM GV$ACTIVE_SESSION_HISTORY a,
gv$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0 -- exclude SYS user
and a.sample_time > sysdate - 1
/
SELECT distinct a.sql_id, a.blocking_session,a.blocking_session_serial#,
a.user_id,s.sql_text,a.module
FROM V$ACTIVE_SESSION_HISTORY a, v$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0
and a.sample_time between to_date('17/06/2011 00:00', 'dd/mm/yyyy hh24:mi')
and to_date('17/06/2011 23:50', 'dd/mm/yyyy hh24:mi');
####################################################################################################################################

TO FIND SID:
##############################################################################################################
PROMPT "ENTER SPID"
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.paddr=b.addr and b.spid='&SPID';
select sid,SQL_ID,SQL_HASH_VALUE from v$session where SID=291;
sql id coint:
=============
select sql_id,count(*) from v$session where status='ACTIVE' group by sql_id;
select sql_id,count(*) from v$session group by sql_id;
select sql_id,MACHINE,count(*) from v$session where status='ACTIVE' group by sql_id,machine;
select a.INST_ID,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b where a.paddr=b.addr
and a.username='HAIDB';
order by 3,2;
##############################################################################################################
kILL SESSION:
##############################################################################################################
select INST_ID,username,sql_id,status,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,sql_id,status ORDER BY 5 DESC;
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='ARCH_ONLINE' and status='ACTIVE' and SQL_ID='dvfsf0b47w6nb';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where SQL_ID='1fjaxytc89h8n';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='ABC' and status='ACTIVE';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='HAIDB' and status='ACTIVE';
1) select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='ACTIVE';
2) select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE';
Kill the sessions older than 2 days:
##############################################################################################################
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session a
where a.status='INACTIVE' and a.username='HAIDB' and a.last_call_et > 48*60*60;
##############################################################################################################
3) select 'alter system kill session '''||a.sid||','||a.serial#||''' immediate;' from v$session a, v$process b where a.username='ABC' and status='INACTIVE'
and a.paddr=b.addr AND b.spid in
(
'36503568',
'25886892',
'47775850'
);
##############################################################################################################
4) To find all the sessions that have been inactive for more than 2 hours, your query would look something like:
##############################################################################################################
SELECT * FROM v$session WHERE STATUS != 'ACTIVE' AND last_call_et > 2*60*60;
select a.INST_ID,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b where a.paddr=b.addr
and a.status='INACTIVE' and a.username='HAIDB_MDM_OWNER' order by 3;
select a.INST_ID,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b where a.paddr=b.addr
and a.status='INACTIVE' and a.username='HAIDB_MDM_OWNER' and a.last_call_et > 48*60*60;
select a.INST_ID,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b where a.paddr=b.addr
and a.status='INACTIVE' and a.username='HAIDB_MDM_OWNER' and a.last_call_et > 24*60*60 order by 3;

select count(*) from v$session where username='HAIDB';
########################################################################################################
6. Max session is allowed in the database
show parameter session
7. Check for Blocking sessions
8. Check for any long running jobs
9. CPU usage
top
topas
##############################################################################################################
10 .Check any mount point is reached 100%
df -k|grep 100
df -g|grep 100
df -h|grep 100
select * from v$session_event where sid= (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and event='virtual circuit wait';
select * from v$session_event where sid= (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and event='virtual circuit wait';

######################################################################################
11. Locked object details
######################################################################################
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON

######################################################################################
Session,locks and tables and sql information:
##############################################################################################################
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,SQ.SQL_FULLTEXT, S.LOGON_TIME FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE
L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;
######################################################################################
12. Recently modified objects
######################################################################################
13. Tablespaces reached 100%
######################################################################################
set pagesize 300
select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
group by tablespace_name) b
Where a.tbl=b.tblsp and round((1-(b.fsz/a.tsz))*100) > 0
order by 5;
select tablespace_name,used_percent from dba_tablespace_usage_metrics where used_percent > 90;
######################################################################################
14. Wait events
######################################################################################
Select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits) Sum_Waits From v$system_wait_class
Group by wait_class Order by 3 desc;
######################################################################################
15.INVALID OBJECTS
######################################################################################
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where status='INVALID' group by OWNER,OBJECT_TYPE,status;
######################################################################################
15. unusable indexes -- completed
######################################################################################
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where STATUS='!VALID' group by OWNER,OBJECT_TYPE,status;
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where status='INVALID' group by OWNER,OBJECT_TYPE,status;
SELECT owner,index_name,TABLE_OWNER,TABLE_NAME,PARTITIONED,TABLESPACE_NAME,STATUS
FROM all_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM')
AND status != 'VALID'
AND
(
status != 'N/A'
OR index_name IN
(
SELECT index_name
FROM all_ind_partitions
WHERE status != 'USABLE'
AND
(
status != 'N/A'
OR index_name IN
(
SELECT index_name
FROM all_ind_subpartitions
WHERE status != 'USABLE'
)
)
)
);
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||';' from USER_IND_PARTITIONS;
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from USER_IND_PARTITIONS;
Select 'ALTER INDEX '||INDEX_OWNER||'.'|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from DBA_IND_PARTITIONS
where INDEX_OWNER='ERXUATOWNER' and status='UNUSABLE';
Select 'ALTER INDEX '||INDEX_OWNER||'.'|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from DBA_IND_PARTITIONS
where INDEX_OWNER='ERXUATOWNER' and status='UNUSABLE';
##############################################################################################################
16. recently modified objects
######################################################################################
17. FRA - completed
set linesize 200
col FILE_TYPE for a25
select * from v$flash_recovery_area_usage;
######################################################################################
select * from v$resource_limit;
######################################################################################
col SID for 9999
col TILLNOW for 99999
col SQL_TEXT for a85
col TARGET for a21
set linesize 200 pages 200
select a.sid,target, sofar, totalwork, time_remaining still, elapsed_seconds tillnow from v$session a , v$sql b, v$session_longops c
where a.sid=c.sid
and a.sql_address = b.address
and a.sql_address = c.sql_address
and status = 'ACTIVE';
col SID for 9999
col TILLNOW for 99999
col SQL_TEXT for a85
col TARGET for a21
set linesize 200 pages 200
select a.sid, sql_text ,target, sofar, totalwork, time_remaining still, elapsed_seconds tillnow from v$session a , v$sql b, v$session_longops c
where a.sid=c.sid
and a.sql_address = b.address
and a.sql_address = c.sql_address
and status = 'ACTIVE';
col SID for 9999
col TILLNOW for 99999
col SQL_TEXT for a85
col TARGET for a21
set linesize 200 pages 200
select a.INST_ID,a.sid, sql_text ,target, sofar, totalwork, time_remaining still, elapsed_seconds tillnow from gv$session a , gv$sql b, gv$session_longops c
where a.sid=c.sid
and a.sql_address = b.address
and a.sql_address = c.sql_address
and a.INST_ID=c.INST_ID
and a.INST_ID=b.INST_ID
and status = 'ACTIVE';
select SQL_FULLTEXT from v$sqlarea where SQL_ID='2j802qu4vj37x';
18.
##############################################################################################################
select INDEX_NAME,STATUS,PARTITION_NAME,INDEX_OWNER from dba_ind_partitions where INDEX_NAME='UK_FACT_COMMUNICATION_01' order by 3;
##############################################################################################################
19.
SELECT * FROM
(SELECT
sql_fulltext,
sql_id,
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10;
##############################################################################################################
20.
set linesize 200
col REASON for a65
col OBJECT_TYPE for a20
col OBJECT_NAME for a20
col MESSAGE_LEVEL for 9999
col OWNER for a10
select OWNER,OBJECT_NAME,REASON ,OBJECT_TYPE,MESSAGE_TYPE,METRIC_VALUE,MESSAGE_LEVEL from dba_outstanding_alerts;
set linesize 200
select
to_char(creation_time, 'dd-mm-yyyy hh24:mi') crt,
instance_name,
object_type,
message_type,
message_level,
reason,
suggested_action
from
dba_outstanding_alerts
order by
creation_time;
select
object_type,
object_name,
reason,
suggested_action,
time_suggested,
resolution,
advisor_name,
metric_value,
message_type,
message_group,
message_level
from
dba_alert_history
where
-- creation_time <= sysdate-1 and
resolution = 'cleared' order by creation_time desc;
col REASON for a55
set linesize 200
col OBJECT_TYPE for a20
col OBJECT_NAME for a20
select
object_type,
object_name,
reason,
suggested_action
from
dba_alert_history
where
-- creation_time <= sysdate-1 and
resolution = 'cleared' order by creation_time desc;
######################################################################################
Identify database idle sessions
######################################################################################
set linesize 140
col username format a15
col idle format a15
col program format a30
PROMPT Enter the number of minutes for which the sessions should have been idle:
PROMPT
select
sid,username,status,
to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "IDLE",
program
from
v$session
where
type='USER'
and (LAST_CALL_ET / 60) > &minutes
order by last_call_et;

########################################################################################################################################
CHECK INVALIDS:
########################################################################################################################################
set linesize 300
set pause off
set pagesize 1000
column owner format a20
column object_name format a40
column index_name format a40
column object_type format a10
column status format a10
prompt OBJECTS INVALID:
select substr(owner,1,20) owner, substr(object_name,1,30) object_name,
substr(object_type,1,10) object_type, status
from dba_objects
where status<>'VALID'
and owner not in ('SYS','SYSTEM')
order by 1,2,3;
prompt
prompt
prompt INDEXES INVALID:
select substr(owner,1,20) owner, substr(index_name,1,40) index_name,
status from dba_indexes
where status not in ('VALID','N/A')
and owner not in ('SYS','SYSTEM')
order by 1,2,3;

########################################################################################################################
Hang Aaalyze :
http://www.dbi-services.com/index.php/blog/entry/oracle-is-hanging-dont-forget-hanganalyze-and-systemstate
########################################################################################################################

WAIT EVENTS:
============
1)WITH SQL ID
select
swh.seq#,
sess.sid,
sess.username username,
swh.event event,
swh.p1,
swh.p2
from
v$session sess,
v$session_wait_history swh
where
sess.sid = 74
and
sess.sid = swh.sid
order by
swh.seq#;

######################################################################################
Check for Fragmentation in table:
######################################################################################
http://select-star-from.blogspot.com/2013/09/how-to-check-table-fragmentation-in.html
Below query will show the total size of table with fragmentation, expected without fragmentation and how much % of size
we can reclaim after removing table fragmentation. Database Administrator has to provide table_name and schema_name as input to this query.

set pages 50000 lines 32767
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size",
round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables
where table_name ='&table_Name' AND OWNER LIKE '&schema_name'
/
######################################################################################
A. Top Recent Wait Events
######################################################################################
col EVENT format a60
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/
######################################################################################
B. List Of Users Currently Waiting
######################################################################################
col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120
select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;
col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120
select s.sid, s.username, s.program,s.module,se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.program like '%rman%'
and s.username is not null
order by se.wait_time;

######################################################################################
c. Find The Main Database Wait Events In A Particular Time Interval
######################################################################################
First determine the snapshot id values for the period in question.
In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum
######################################################################################
D. Top CPU Consuming SQL During A Certain Time Period
######################################################################################
Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM
select * from (
select
SQL_ID,
sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum < =5;
######################################################################################
E. Which Database Objects Experienced the Most Number of Waits in the Past One Hour
######################################################################################

set linesize 120
col event format a40
col object_name format a40
select * from
(
select dba_objects.owner,dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
dba_objects
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.owner,dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4 desc)
where rownum < 6;
######################################################################################
E. Top Segments ordered by Physical Reads
######################################################################################
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum
######################################################################################
E. Top 5 SQL statements in the past one hour
######################################################################################
select * from (
select active_session_history.sql_id,
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
order by 4 desc )
where rownum <=5;
######################################################################################
F. Find what the top SQL was at a particular reported time of day
######################################################################################
First determine the snapshot id values for the period in question.
In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='09-DEC-2014';
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
select * from
(
select
sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and
s.snap_id= &snapid
order by
c3 desc)
where rownum < 6
/
######################################################################################
G.Analyse a particular SQL ID and see the trends for the past day
######################################################################################
select
s.snap_id,
to_char(s.begin_interval_time,'HH24:MI') c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.cpu_time_delta c6,
sql.elapsed_time_delta c7
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and s.begin_interval_time > sysdate -1
and
sql.sql_id='&sqlid'
order by c7
/
####################################################################################################################################################################
H.Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance
#####################################################################################################################################################################
select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in ('&sqlid')
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;
######################################################################################
Script – What Wait Events Are Sessions Waiting On
######################################################################################
set linesize 120
col username format a10
col event format a30

select sid, serial#,username, event,
seconds_in_wait, wait_time
from v$session where state = 'WAITING'
and wait_class != 'Idle'
order by event
;
######################################################################################
Script – Sessions Waiting On A Particular Wait Event
######################################################################################
SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager',
'pmon timer','rdbms ipc message', 'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC;
######################################################################################
Top Recent Wait Events
######################################################################################
col EVENT format a60
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/
######################################################################################
Top Wait Events Since Instance Startup
######################################################################################
col event format a60
select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
where wait_class !='Idle'
group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;
######################################################################################
List Of Users Currently Waiting
######################################################################################
col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120
select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;
######################################################################################
Find The Main Database Wait Events In A Particular Time Interval
######################################################################################
First determine the snapshot id values for the period in question.
In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum;
######################################################################################
Top CPU Consuming SQL During A Certain Time Period:
######################################################################################
Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM
select * from (
select
SQL_ID,
sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum < 6;
######################################################################################
#Which Database Objects Experienced the Most Number of Waits in the Past One Hour#####
######################################################################################
set linesize 120
col event format a40
col object_name format a40
select * from
(
select dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
dba_objects
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4 desc)
where rownum < 6;
######################################################################################
Top Segments ordered by Physical Reads
######################################################################################
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum < 6;
######################################################################################
Top 5 SQL statements in the past one hour
######################################################################################
select * from (
select active_session_history.sql_id,
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
order by 4 desc )
where rownum;
######################################################################################
SQL with the highest I/O in the past one day
######################################################################################
select * from
(
SELECT /*+LEADING(x h) USE_NL(h)*/
h.sql_id
, SUM(10) ash_secs
FROM dba_hist_snapshot x
, dba_hist_active_sess_history h
WHERE x.begin_interval_time > sysdate -1
AND h.SNAP_id = X.SNAP_id
AND h.dbid = x.dbid
AND h.instance_number = x.instance_number
AND h.event in ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum;
######################################################################################
#Top CPU consuming queries since past one day
######################################################################################
select * from (
select
SQL_ID,
sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum < 6;
######################################################################################
Find what the top SQL was at a particular reported time of day
######################################################################################
First determine the snapshot id values for the period in question.
In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
select * from
(
select
sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and
s.snap_id= &snapid
order by
c3 desc)
where rownum < 6
/
######################################################################################
Analyse a particular SQL ID and see the trends for the past day
######################################################################################
analysis_sql_past_1day.sql
select
s.snap_id,
to_char(s.begin_interval_time,'HH24:MI') c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.cpu_time_delta c6,
sql.elapsed_time_delta c7
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and s.begin_interval_time > sysdate -1
and
sql.sql_id='&sqlid'
order by c7
/
#######################################################################################################################
Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance
#######################################################################################################################
select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in (
'&sqlid')
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;
#######################################################################################################################
Top 5 Queries for past week based on ADDM recommendations
#######################################################################################################################
/*
Top 10 SQL_ID's for the last 7 days as identified by ADDM
from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log
*/
col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit"
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7
and a.dbid = (select dbid from v$database)
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24')
and b.advisor_name = 'ADDM'
and b.task_id = l.task_id
and l.status = 'COMPLETED')
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;
#######################################################################################################################
-- Top 10 CPU consumers in last 5 minutes
#######################################################################################################################
SQL> select * from
(
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time > sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
#######################################################################################################################
Script – Segments with highest I/O activity
#######################################################################################################################
This script will list the top 10 segments in the database that have the most number of
physical reads against them.
Script can also be changed to query on 'physical writes' instead.
set pagesize 200
setlinesize 120
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum <=10;
#######################################################################################################################
Script – Monitor sessions with high Physical Reads
#######################################################################################################################
set linesize 120
col osuser format a10
col username format 10
select
OSUSER osuser,
username,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS;
#######################################################################################################################
Script – Latch Contention (top 5 latches)
#######################################################################################################################
This script will display the top 5 latches with the most sleeps.
Script can be changed to even sort the display on misses instead.
set linesize 120
col name format a30
select * from
(select name, gets,misses, sleeps
from v$latch
order by sleeps desc)
where rownum < 6;
#######################################################################################################################
BACKUP CHECKS:
#######################################################################################################################
1.Script – Check RMAN Backup Status
#######################################################################################################################
Scripts to check backup status and timings of database backups -
This script will be run in the database, not the catalog.
Login as sysdba -
This script will report on all backups – full, incremental and archivelog backups -
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
#######################################################################################################################
2.This script will report all on full and incremental backups, not archivelog backups -
#######################################################################################################################
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;
#######################################################################################################################
Script – Query the RMAN catalog to list backup completion status
#######################################################################################################################
Note – run this query connected as the owner of the RMAN catalog
set lines 80
set pages 250
ttitle "Daily Backup........"
select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile where AUTOBACKUP_DATE
is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),to_date(ARCBKP,'DD/MM/YYYY HH24:MI'))
/
#######################################################################################################################
Description : Displays a list of tablespaces that are nearly full.
#######################################################################################################################
SET PAGESIZE 100
PROMPT Tablespaces utilization is more than 90%
PROMPT ********************************************************
SELECT tablespace_name,
size_mb,
free_mb,
max_size_mb,
max_free_mb,
TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct
FROM (
SELECT a.tablespace_name,
b.size_mb,
a.free_mb,
b.max_size_mb,
a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
FROM (SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS free_mb
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS size_mb,
TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
)
WHERE ROUND((max_free_mb/max_size_mb) * 100,2) < 30;
#############################################################################################
Details for count of connections per service on each node
#############################################################################################
select ash.instance_number, network_name, count(*)
from dba_hist_active_sess_history ash, gv$active_services ase
where sample_time > to_date('25-AUG-2014 00:00:00','DD-MON-YYYY HH24:MI:SS')
and sample_time < to_date('25-AUG-2014 23:59:00','DD-MON-YYYY HH24:MI:SS')
and service_hash=name_hash
and ash.instance_number=ase.inst_id
group by ash.instance_number,network_name
order by ash.instance_number;

WAIT EVENTS:
############################################################################################################################
Script – Sessions Waiting On A Particular Wait Event
############################################################################################################################
set pagesize 200
set linesize 300
col event for a55
SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager',
'pmon timer','rdbms ipc message', 'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC;
Script – What Wait Events Are Sessions Waiting On
###############################################################
set linesize 120
col username format a10
col event format a30

select sid, serial#,username, event,
seconds_in_wait, wait_time
from v$session where state = 'WAITING'
and wait_class != 'Idle'
order by event
;
col "Description" format a50
select sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from v$session
where username = 'ARUP';
col "Description" format a50
select USERNAME,sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from v$session where status='ACTIVE';

############################################################################################################################
SQL> set linesize 200
SQL> SELECT * FROM
(SELECT
sql_fulltext,
sql_id,?
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10 ;

#######################################################################################
FIND THE QUERY
SET PAGES 5000 TERM OFF FEEDBACK OFF MARKUP HTML ON PREFORMAT OFF ENTMAP ON
set verify off
spool 20th_report_sql_detail.html
set lines 300
set long 9999999
select a.SESSION_ID, a.SAMPLE_TIME, a.USER_ID, (select username from dba_users where a.user_id=user_id) as username, b.SQL_ID, b.sql_text
from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b
where a.sql_id=b.sql_id and upper(b.sql_text) like '%FROM INVOICES%'
and a.snap_id>=53363 and a.snap_id<=53386;
spool off
set markup html off
EOF
SELECT SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT;
Check in OEM -> FIND SQL
#######################################################################################
Recipe #1 - Get details about long running operations
Run the following query and you'll get most information you need:
#######################################################################################
SELECT osuser,
sl.sql_id,
sl.sql_hash_value,
opname,
target,
elapsed_seconds,
time_remaining
FROM v$session_longops sl
inner join v$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL#
WHERE time_remaining > 0Recipe #2 - Show long running SQL Statements
#######################################################################################
If you also want to see the SQL query causing the long running operation, just join v$sql like show below:
#######################################################################################
SELECT s.username,
sl.sid,
sq.executions,
sl.last_update_time,
sl.sql_id,
sl.sql_hash_value,
opname,
target,
elapsed_seconds,
time_remaining,
sq.sql_fulltext
FROM v$session_longops sl
INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0;
######################################################################
LONG RUNNING QUERIES - FINAL
######################################################################
break on s.sql_id ON s.username DUPLICATES
select s.sql_id,s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s
join v$sqltext_with_newlines q
on s.sql_address = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by s.sql_id,sid,serial#,q.piece;

######################################################################################
CHANGE BASE LINE
######################################################################################
1)
SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
The PL/SQL function DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE tries to evolve new plans added by the optimizer to the
plan history of existing plan baselines. If the function can verify that
the new plan performs better than a plan chosen from the corresponding SQL plan baseline, the new plan is added as an accepted plan.
==============================================================================================================================================
2)
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_794f2b0bd2ff2fa6');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
3)
To view the plans stored in the SQL plan baseline for a given statement, use the DISPLAY_SQL_PLAN_BASELINE function of the DBMS_XPLAN package:
==============================================================================================================================================
select * from table(
dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_794f2b0bd2ff2fa6',format=>'basic'));
######################################################################################
The Number of ASM Disks and their Ownerships (grid, asmadmin) should be Correct.
The Major and Minor Numbers should be the same as Primary. Kfod command can be used to validate the disks.
If the disks are not listed in kfod, please check with Server team to rediscover the disks.
kfod asm_diskstring='/dev/oradisk/ASM/vot*' disks=all
######################################################################################
FAQ
http://select-star-from.blogspot.com/2013/09/how-to-check-table-fragmentation-in.html
http://select-star-from.blogspot.com/2013/07/oracle-dba-interview-questions-answers_24.html
######################################################################################

Pages

Oracle DBA World

Ads

Random Posts

Blog Archive

Oracle DBA World

Search This Blog

Blog Archive

My Blog List

Report Abuse

Followers

Lorem 1

Technology

Circle Gallery

Shooting

Racing

News

Lorem 4

QUERY FOR CPU USAGE

Blogger templates

Popular Posts

Advertisement

About

Advertisement

Advertisement With Us

Popular Posts

Sponsor Advertisement

Video of Day

Join Us