How to viewing Oracle bind variable values?

  View V$SQL_BIND_CAPTURE has been introduced to report information on bind variables used by SQL cursors.

  Following query uses the sql_id address that should be specified for each unique SQL statement:

set linesize 256
set pagesize 54
set scan on
set heading on termout on verify off echo off

col KILL_SERIAL_SID format a52
col sql_text format a50 wrap
col name format a15 wrap
col datatype_string format a30
col value_string format a30 wrap

SELECT
   ss.serial#,
   ss.sid,
   --ALTER SYSTEM KILL SESSION '573,31517' IMMEDIATE
   'ALTER SYSTEM KILL SESSION ''' || ss.sid || ',' || ss.serial# || ''' IMMEDIATE;' kill_serial_sid,   
   b.name,
   b.position,
   b.datatype_string,
   b.value_string,
   a.sql_text
FROM
  v$sql_bind_capture b,
  v$sqlarea          a,
  v$sql s,
  v$session ss
WHERE 1 = 1
AND s.sql_id = a.sql_id   
AND b.sql_id = a.sql_id
-- Join v$session to v$sqlarea 
--AND ss.sql_address = a.address 
--AND ss.sql_hash_value = a.hash_value
--
-- Join v$session to v$sql 
--AND ss.SQL_ADDRESS = s.ADDRESS
-- joining on SQL_ID is much more efficient
AND ss.sql_id = s.sql_id
-- To capture only active session with specified SQL_ID
--AND ss.STATUS = 'ACTIVE'
AND b.sql_id = '&sql_id' --'4453zzgmqssxr'
;

You can get the SQL ID from the v$sql view:

set linesize 256
set pagesize 54
set scan on
set heading on termout on verify off echo off

col sql_text format a130 wrap
col bind_name format a35 wrap
col bind_string format a50 wrap

select
  sql_id,
  t.sql_text SQL_TEXT,
  b.name BIND_NAME,
  b.value_string BIND_STRING
from
  v$sql t
  join v$sql_bind_capture b
  using (sql_id)
where
  b.value_string is not null
  and sql_id='&sql_id' --'f8pavn1bvsj7t'
;

How to viewing in SQL Monitor of Enterprise Manager 11g
   Offline Analysis of Long Running SQL using SQL Monitoring Active Reports

Using 10046 trace for tracing bind variable values
  You can run a 10046 level 4 trace to debug code with bind variables and see the bind variable values as it steps through the code:

EXECUTE sys.dbms_system.set_ev ('||SID||','||SERIAL#||',10046,4,'''')

Bind Capture History

set linesize 256
set pagesize 54
set scan on
set heading on termout on verify off echo off

col name format a50 wrap
col value_string format a50 wrap

VARIABLE sql_id VARCHAR2(50)
EXEC :sql_id := '4453zzgmqssxr'

select snap_id, name, position, value_string, last_captured
  from dba_hist_sqlbind
 where sql_id = :sql_id
 order by snap_id desc;

Reading alert log via SQL in Oracle 11g

X$DBGALERTEXT показывает последний log.xml

X$DIAG_ALERT_EXT показывает объединенное содержимое для файлов log_XX.xml, включая log.xml.

connect sys as sysdba

select * from X$DBGALERTEXT where rownum <= 20;


set linesize 254
col ORIGINATING_TIMESTAMP format a50
col MESSAGE_TEXT format a170

SELECT ORIGINATING_TIMESTAMP, message_text FROM X$DIAG_ALERT_EXT
where 1 = 1
  and originating_timestamp >= sysdate - 30
  and message_text like 'ALTER SYSTEM%'
  and message_text not like 'ALTER SYSTEM ARCHIVE LOG%'
;

Select ORA-XXXXX and TNS-XXXXX errors last 10 minutes:

set linesize 254
col ORIGINATING_TIMESTAMP format a50
col MESSAGE_TEXT format a170

SELECT inst_id, originating_timestamp, MESSAGE_TEXT
    FROM TABLE (
            gv$ (
               CURSOR (
                  SELECT inst_id, originating_timestamp, MESSAGE_TEXT
                    FROM v$diag_alert_ext
                   WHERE     originating_timestamp > (SYSDATE - 10/1440)
                         AND (MESSAGE_TEXT LIKE '%ORA-%' or MESSAGE_TEXT LIKE '%TNS-%')
                         AND MESSAGE_TEXT NOT LIKE '%TNS-12502%'
                      )
                )
               )
ORDER BY inst_id, originating_timestamp;

Shrinking temporary tablespace in Oracle 10g and 11g

Useful links

• Reclaiming Unused Space in Datafiles
• Temporary Tablespace Enhancements in Oracle Database 11g Release 1

Shrinking temporary tablespace in Oracle 10g

CREATE TEMPORARY TABLESPACE TEMP02 TEMPFILE 
  '/oracle/oradata/db/TEMP_02.dbf' SIZE 50M AUTOEXTEND ON;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;

Switch all existing users from old TEMP to new TEMP02 tablespace:

set serveroutput on
-- Switch all existing users to new temp tablespace.
BEGIN
  FOR cur_user IN (SELECT username FROM dba_users WHERE temporary_tablespace = 'TEMP') LOOP
    dbms_output.put_line('ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE TEMP02');    
    EXECUTE IMMEDIATE 'ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE TEMP02';
  END LOOP;
END;
/

Waiting for release old TEMP tablespace usage by applications/sessions:

lsof | grep temp01.dbf | wc -l
124

select count(*) from V$TEMPSEG_USAGE
where tablespace = 'TEMP';

  COUNT(*)
----------
        91

select tablespace, username, count(*)
  from V$TEMPSEG_USAGE
 where 1 = 1
   --and tablespace in ('TEMP', 'TEMP02')
   and tablespace = 'TEMP'
 group by tablespace, username;

when count become zero then drop unused old TEMP tablespace and files:

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Recreate old TEMP tablespace and if you want switch back from TEMP02

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 
  '/oracle/oradata/db/TEMP_01.dbf' SIZE 50M AUTOEXTEND ON;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Switch all existing users back to TEMP tablespace:

set serveroutput on
-- Switch all existing users to new temp tablespace.
BEGIN
  FOR cur_user IN (SELECT username FROM dba_users WHERE temporary_tablespace = 'TEMP02') LOOP
    dbms_output.put_line('ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE TEMP');    
    EXECUTE IMMEDIATE 'ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE TEMP';
  END LOOP;
END;
/

Waiting for release TEMP02 tablespace usage by applications/sessions:

lsof | grep temp02.dbf | wc -l
124

select count(*) from V$TEMPSEG_USAGE
where tablespace = 'TEMP02';

  COUNT(*)
----------
        91

select tablespace, username, count(*)
  from V$TEMPSEG_USAGE
 where 1 = 1
   --and tablespace in ('TEMP', 'TEMP02')
   and tablespace = 'TEMP02'
 group by tablespace, username;

when count become zero then drop unused TEMP02 tablespace and files:

DROP TABLESPACE TEMP02 INCLUDING CONTENTS AND DATAFILES;

Temporary Tablespace Enhancements in Oracle Database 11g

alter tablespace temp shrink space [keep 100m];

The shrink can also be directed to a specific tempfile using the TEMPFILE clause.

ALTER TABLESPACE temp SHRINK TEMPFILE '/oracle/oradata/db/temp01.dbf' KEEP 30M;

View V$TEMPSEG_USAGE

lsof | grep temp01.dbf | wc -l
124

select * from V$TEMPSEG_USAGE

select count(*)
  from V$TEMPSEG_USAGE
 where tablespace = 'TEMP';

select tablespace, username, count(*)
  from V$TEMPSEG_USAGE
 where 1 = 1
   --and tablespace in ('TEMP', 'TEMP02')
   --and tablespace = 'TEMP'
 group by tablespace, username;

Issue: Shrinking of Temp Tablespace does not take effect

Run this query to find if there are any sessions using your temp tablespace a long time:

SELECT se.username username,
       se.SID sid, se.serial# serial#,
       se.status status,
       'alter system kill session ''' || se.sid || ',' || se.serial# || ''' immediate;' kill_session_cmd,
       se.sql_hash_value, se.sql_id,
       se.prev_hash_value,se.machine machine,
       su.TABLESPACE tablespace,su.segtype,
       su.CONTENTS CONTENTS
  FROM v$session se,
       v$tempseg_usage su /* v$sort_usage */
 WHERE se.saddr=su.session_addr
   AND status = 'ACTIVE'
;

Check if you can drop those sessions and then try shrink. Or you can create a new tablespace move every user to that temp tablespace and than drop this old one.

Move Flash Recovery Area to a new location

For to accomplish moving flash recovery area:

1. Change the value of the DB_RECOVERY_FILE_DEST initialization parameter to a new value.
2. Shut down the instance.
3. Start up the instance and mount the database.
4. Execute the ALTER DATABASE FLASHBACK OFF command.
5. Execute the ALTER DATABASE FLASHBACK ON command.
6. Open the database.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST= '/u01/oradata/flash';

SQL>  SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE FLASHBACK OFF;

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;

Use Data Recovery Advisor to Perform Recovery

rman target /
LIST FAILURE;
LIST FAILURE 182 DETAIL;
ADVISE FAILURE;
REPAIR FAILURE;

• LIST FAILURE - to show information about the failures.
• ADVISE FAILURE - to obtain a report of your repair options.
• Finally, REPAIR FAILURE will fix the problem.