Check tablespace size usage

Rewritten version from Top DBA Shell Scripts for Monitoring the Database based on MAXBYTES of each file within tablespace:

SELECT F.TABLESPACE_NAME,
       TO_CHAR ((T.ALLOCATED_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
       TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
       TO_CHAR (T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE), '999,999') "FREE (MB)",
       TO_CHAR ((ROUND (((T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE))/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM   (
       SELECT       TABLESPACE_NAME,
                    ROUND (SUM (BLOCKS*(SELECT VALUE/1024
                                        FROM V$PARAMETER
                                        WHERE NAME = 'db_block_size')/1024)
                           ) FREE_SPACE
       FROM DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME
       ) F,
       (
       SELECT TABLESPACE_NAME,
       ROUND(SUM (MAXBYTES/1048576)) TOTAL_SPACE, 
       ROUND (SUM (BYTES/1048576)) ALLOCATED_SPACE
       FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME
       ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
--AND ROUND (((T.TOTAL_SPACE - (T.ALLOCATED_SPACE - F.FREE_SPACE))/T.TOTAL_SPACE)*100) < 10;

Restore archivelog from backup by logseq/scn

See details in RMAN effective use

set archivelog destination to '/disk1/oracle/temp_restore';
#restore archivelog from scn=460779 until scn =506141
restore archivelog from logseq 1 until logseq 10;

ADRCI usage examples

Usage example 1:

[oracle@host ~]$ adrci
adrci> show homes
set home diag/rdbms/orcl/orcl
# Output with enter to editor
show alert -p "ORIGINATING_TIMESTAMP >= '2014-02-24 00:00:00'"
# Output to termnal without enter to editor
show alert -p "ORIGINATING_TIMESTAMP >= '2014-02-24 00:00:00'" -term
# Show all 'ORA-%' and 'TNS-%' messages
show alert -p "ORIGINATING_TIMESTAMP >= '2014-02-24 00:00:00' AND (MESSAGE_TEXT LIKE '%ORA-%' or MESSAGE_TEXT LIKE '%TNS-%')" -term
# Show all 'ORA-%' and 'TNS-%' messages with exclude from output specified
show alert -p "ORIGINATING_TIMESTAMP > '2013-08-16 00:00:00' AND (MESSAGE_TEXT LIKE '%ORA-%' or MESSAGE_TEXT LIKE '%TNS-%') AND (MESSAGE_TEXT NOT LIKE '%TNS-12502%' AND MESSAGE_TEXT NOT LIKE '%TNS-12537%' AND MESSAGE_TEXT NOT LIKE '%ORA-609%')" -term
show alert -tail -f
exit

Usage example 2:

[oracle@host ~]$ adrci
adrci> show homes
set home diag/rdbms/orcl/orcl
show alert -tail -f
exit

Incident & Problem

adrci>
show problem
show incident
show incident -mode detail -p "incident_id=6201"
show trace /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc

Creation of Packages & ZIP files to send to Oracle Support

adrci>
ips create package problem 1 correlate all
ips generate package 2 in "/home/oracle"

Managing, especially purging of tracefiles

adrci>
show tracefile -rt
show control
set control (SHORTP_POLICY = 360)
set control (LONGP_POLICY = 2190)
show control

# Purge tracefiles manually
purge -age 2880 -type trace
show tracefile -rt

# Purge log directories for listener ADRCI Home
for i in `adrci exec="show homes"|grep listener`;do
# Purge ADRCI Homes log directories older 60 days ago
#for i in `adrci exec="show homes"|sed '1d'`;do
  du -hs $ORACLE_BASE/$i | sort -rh
  du -hs $ORACLE_BASE/$i/* | sort -rh
  du -hs $ORACLE_BASE/$i/trace | sort -rh

  # Purge listener log directory older 60 days ago
  # (60 days * 24h * 60 mins = 86400 mins)
  # -age  - The data older than  ago will be purged
  echo "adrci exec=\"set home $i;purge -age 86400\""
  adrci exec="set home $i;purge -age 86400";
  adrci exec="set home $i;show control;set control \(SHORTP_POLICY = 180\);set control \(LONGP_POLICY = 720\);show control";

  du -hs $ORACLE_BASE/$i | sort -rh
  du -hs $ORACLE_BASE/$i/* | sort -rh
  du -hs $ORACLE_BASE/$i/trace | sort -rh
done

# Check ADRCI Homes policy
for i in `adrci exec="show homes"|sed '1d'`;do
  adrci exec="set home $i;show control;";
done

# Set ADRCI Homes policy
for i in `adrci exec="show homes"|sed '1d'`;do
  adrci exec="set home $i;show control;set control \(SHORTP_POLICY = 180\);set control \(LONGP_POLICY = 720\);show control";
done

Read data from clob

Read data from clob:

declare
  v_body clob;
  offset number := 1;
  v_str varchar(32767);
  amount number := 32767;
begin
  v_body := rpad('Big clob data ', 32767 * 4, 'Big clob data ');
  while(offset <= dbms_lob.getlength(v_body)) loop
    dbms_lob.read(v_body, amount, offset, v_str);

    -- Operate with v_str:
    --dbms_output.put_line(v_str);

    offset := offset + amount;
  end loop;
end;

Capture any failed SQL queries using database trigger in Oracle

Создаем таблицу куда будем записывать запросы с ошибками:

CONNECT SYSTEM;

DROP TABLE servererror_log;

CREATE TABLE servererror_log (
  error_datetime  TIMESTAMP,
  error_user      VARCHAR2(30),
  db_name         VARCHAR2(30),
  error_stack     VARCHAR2(2000),
  call_stack      VARCHAR2(2000),
  SID             NUMBER,
  SQL_ID          VARCHAR2 (13),
  sql_statement   VARCHAR2(2000),
  sql_statement_all   CLOB,
  CLIENT_INFO  VARCHAR2(256 BYTE),
  CURR_SCHEMA  VARCHAR2(256 BYTE),
  CURR_USER    VARCHAR2(256 BYTE),
  CURR_DB_NAME VARCHAR2(256 BYTE),
  HOST         VARCHAR2(256 BYTE),
  IP           VARCHAR2(256 BYTE),
  OSUSER       VARCHAR2(256 BYTE),
  SESSID       VARCHAR2(256 BYTE),
  SESS_USER    VARCHAR2(256 BYTE),
  TERMINAL     VARCHAR2(256 BYTE)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON SYSTEM.SERVERERROR_LOG TO SCOTT;

Выдаем явный грант на V_$SESSION пользователю SYSTEM для того чтобы триггер log_server_errors указанный ниже скомпилировался.

CONNECT SYS AS SYSDBA;
GRANT SELECT ON V_$SESSION TO SYSTEM;

Триггер базы данных на SERVERERROR(в условии WHEN можем исключить ошибки которые не нужно логгировать, в данном примере - это ORA-25254):

CONNECT SYSTEM;

DROP TRIGGER log_server_errors;

CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE WHEN (
     ORA_SERVER_ERROR(1)<>25254 /*ORA-25254: time-out in LISTEN while waiting for a message*/
   )
DECLARE
  v_sql_statement VARCHAR2(32767);
  sql_text ora_name_list_t;
  n        pls_integer;
  cursor c_user is
    select vs.sid                                     sid,
           vs.sql_id                                  sql_id,
           sys_context('USERENV','CLIENT_INFO')       client_info,
           sys_context('USERENV','CURRENT_SCHEMA')    curr_schema,
           sys_context('USERENV','CURRENT_USER')      curr_user,
           sys_context('USERENV','DB_NAME')           db_name,
           sys_context('USERENV','HOST')              host,
           sys_context('USERENV','IP_ADDRESS')        ip,
           sys_context('USERENV','OS_USER')           osuser,
           sys_context('USERENV','SESSIONID')         sessid,
           sys_context('USERENV','SESSION_USER')      sess_user,
           sys_context('USERENV','TERMINAL')          terminal
      from dual
    cross join v$session vs
    where 1 = 1     
      and sys_context('USERENV','SESSIONID') = audsid
  ;
  user_rec c_user%rowtype;
BEGIN
  open c_user;
  fetch c_user into user_rec;
  close c_user;

  n := ora_sql_txt(sql_text);
      
  FOR i IN 1..n LOOP
    v_sql_statement := v_sql_statement || sql_text(i);        
  END LOOP;
  INSERT INTO servererror_log(
    error_datetime, error_user, db_name,
    error_stack, call_stack,
    SID, SQL_ID,
    sql_statement, sql_statement_all,
    CLIENT_INFO, CURR_SCHEMA, CURR_USER, CURR_DB_NAME,
    HOST, IP, OSUSER, SESSID,
    SESS_USER, TERMINAL
  ) VALUES(
    systimestamp, sys.login_user, sys.database_name,
    ora_server_error_msg(1)/*dbms_utility.format_error_stack*/,
    dbms_utility.format_call_stack,
    user_rec.sid, user_rec.sql_id,
    substrb(v_sql_statement, 1, 2000),
    v_sql_statement,
    user_rec.client_info, user_rec.curr_schema, user_rec.curr_user, 
    user_rec.db_name,     user_rec.host,        user_rec.ip, 
    user_rec.osuser,      user_rec.sessid,      user_rec.sess_user, 
    user_rec.terminal
  );
  --commit;
END;
/

Выбираем запросы с ошибками(см. поля SID, SQL_STATEMENT, SQL_STATEMENT_ALL):

CONNECT SCOTT;

select * from system.servererror_log
 --where ERROR_USER = 'SCOTT'
 order by error_datetime desc
;

Дополнительные команды которые возможно пригодятся для трассировки сессии:

CONNECT SYSTEM;

ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;
...
  select * from system.servererror_log;
...

ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;

--After Logon Trigger for Tracing on Schema:
CONNECT SCOTT;

CREATE OR REPLACE TRIGGER SCOTT_AFTER_LOGON_TRG_00
  AFTER LOGON ON SCOTT.SCHEMA 
DECLARE
 sqlstr VARCHAR2(200) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''';
BEGIN
  IF (USER = 'SCOTT') THEN
    execute immediate sqlstr;
  END IF;
END;
/