Scripts

Oracle log_switch_activity

select to_char(first_time,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'99') "00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'99') "01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'99') "02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'99') "03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'99') "04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'99') "05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'99') "06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'99') "07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'99') "08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'99') "09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'99') "10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'99') "11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'99') "12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'99') "13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'99') "14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'99') "15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'99') "16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'99') "17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'99') "18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'99') "19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'99') "20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'99') "21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'99') "22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'99') "23"
from v$log_history
group by to_char(first_time,'YYYY-MM-DD');

Clean_schem:

begin

for v_x in (select 'DROP MATERIALIZED VIEW ' || object_name  as ddl_command from user_objects where object_type='MATERIALIZED VIEW'
            union all
            select 'DROP TRIGGER '  || object_name  as ddl_command from user_objects where object_type='TRIGGER'
            union all
            select 'DROP FUNCTION ' || object_name  as ddl_command from user_objects where object_type='FUNCTION'
            union all
            select 'DROP PROCEDURE ' || object_name  as ddl_command from user_objects where object_type='PROCEDURE'
            union all
            select 'DROP VIEW '     || object_name  as ddl_command from user_objects where object_type='VIEW'
           union all
            select 'DROP PACKAGE BODY '  || object_name  as ddl_command from user_objects where object_type='PACKAGE BODY'
            union all
            select 'DROP PACKAGE '  || object_name  as ddl_command from user_objects where object_type='PACKAGE'
            union all
            select 'DROP SYNONYM "' || object_name || '"'  as ddl_command from user_objects where object_type='SYNONYM'
           union all
            select 'DROP TABLE '    || object_name || ' CASCADE CONSTRAINTS PURGE' as ddl_command from user_objects where object_type='TABLE'
            union all
            select 'DROP DATABASE LINK ' || object_name  as ddl_command from user_objects where object_type='DATABASE LINK'
            union all
            select 'DROP LIBRARY ' || object_name  as ddl_command from user_objects where object_type='LIBRARY'
            union all
            select 'DROP TYPE ' || object_name  as ddl_command from user_objects where object_type='TYPE'
            union all
            select 'DROP TYPE ' || object_name  as ddl_command from user_objects where object_type='TYPE'
            union all
            select 'DROP SEQUENCE ' || object_name  as ddl_command from user_objects where object_type='SEQUENCE'
            union all
            select 'DROP JAVA CLASS "'||OBJECT_NAME||'"' from user_objects where OBJECT_TYPE='JAVA CLASS')
loop
begin
 execute immediate v_x.ddl_command;
exception
when others then null;
end;
end loop;

end;
/

Restore controlfile from backupset:

DECLARE
v_dev            varchar2(50);             
v_done           boolean;                 
v_file           varchar2(255);

BEGIN
 v_file := '/mnt/sde/oracle/backup_db_1.bac';
 v_dev := sys.dbms_backup_restore.deviceAllocate(type=>null, ident=>'d1');
 sys.dbms_backup_restore.restoreSetDatafile;
 sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/oracle/database/db_1/control_01.ctl');
 sys.dbms_backup_restore.restorespfileto('/oracle/product/10.2.0/db_1/dbs/spfile_.ora');
 sys.dbms_backup_restore.restoreBackupPiece(done=>v_done, handle=>v_file, params=>null);
 sys.dbms_backup_restore.deviceDeallocate;
END;
/

Как узнать Absolute Fuzzy SCN: 

БД можно открыть когда у всех датафайлов не только одинаковый CHECKPOINT_CHANGE#, но и когда FUZZY бит равен нулю.
Нужно вычислить на каком scn fuzzy bit сбрасывается.


RMAN> RESTORE DATABASE PREVIEW ;
Recovery must be done beyond SCN 47530561 to clear datafile fuzziness

или так:

spool scandatafile.sql
set serveroutput on
declare
  scn number(12) := 0;
  scnmax number(12) := 0;
begin
  for f in (select * from v$datafile) loop
    scn := dbms_backup_restore.scandatafile(f.file#);
    dbms_output.put_line('File ' || f.file# ||' absolute fuzzy scn = ' || scn);
    if scn > scnmax then scnmax := scn; end if;
  end loop;

  dbms_output.put_line('Minimum PITR SCN = ' || scnmax);
end;

или с помощью трассировки:

SQL>alter session set events 'immediate trace name file_hdrs level 3';


смотрим трассировочный файл, там пишет
...
DATA FILE #4:
name #4: D:\ORA11G\ORCL\USERS01.DBF
...
Absolute fuzzy scn: 0x0000.02d54241
...

Т.е. гнать нужно как минимум до scn = 47530561

RMAN> recover database until scn 47530560;
Тут база не откроется.

RMAN> recover database until scn 47530561;
media recovery complete, elapsed time: 00:00:01

RMAN> alter database open resetlogs;

Мониторинг RMAN BACKUPS:

In RC_BACKUP_SET_DETAILS the Backup_type has the following values set :-

BACKUP_TYPE: The type of the backup:

D (full backup or level 0 incremental),
I (incremental level 1),
L (archived redo log).
This works as expected in 11gr2 and Below version.

However in 12c database  When we take a Level 0 backup the backup type is Shown as I and not D.

BUG 25055352 - RC_BACKUP_SET_DETAILS.BACKUP_TYPE INCORRECTLY SHOWS LEVEL 0 AS I  ---> Closed as Duplicate of BUG 23248000 - RC_BACKUP_SET_DETAILS.BACKUP_TYPE INCORRECTLY SHOWS


v$rman_configuration
v$rman_status
v$rman_output
v$rman_backup_job_details



RMAN> configure controlfile autobackup on;

select *
  from v$rman_configuration;



column sid format 9999
column MBYTES_PROCESSED heading 'Size' format 999,999,999
column operation format a15 word wrap

select sid,
       operation,
       status,
       MBYTES_PROCESSED,
       start_time,
       end_time,i
       nput_bytes,
       output_bytes
 from v$rman_status
order by start_time
/


select  sid,
        row_type,
        operation,
        status,
        to_char(start_time,'dd-mm-yyyy hh24:mi:ss') start_time,
        to_char(end_time,'dd-mm-yyyy hh24:mi:ss') end_time
  from v$rman_status;




select  sid,
        output
 from v$rman_output;



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;



column INPUT_BYTES_PER_SEC_DISPLAY heading 'Input IO|per sec' format a10
column OUTPUT_BYTES_PER_SEC_DISPLAY heading 'Output IO|per sec' format a10
column TIME_TAKEN_DISPLAY heading 'Time' format a12

select start_time,
       elapsed_seconds,
       status,
       INPUT_BYTES_PER_SEC_DISPLAY,
       OUTPUT_BYTES_PER_SEC_DISPLAY ,
       TIME_TAKEN_DISPLAY
 from V$RMAN_BACKUP_JOB_DETAILS
order by start_time;


select session_key,
       input_type,
       status,
       input_bytes,
       output_bytes,
       to_char(start_time, 'dd.mm.yyyy hh24:mi:ss') start_time,
       to_char(end_time, 'dd.mm.yyyy hh24:mi:ss') end_time,
       output_device_type,
       elapsed_seconds
 from v$rman_backup_job_details;





set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;



with rman_status as (
select distinct substr(backup_type,1,instr(backup_type||' datafile','
datafile')) backup_type
    , round(mbytes_processed/1024,1) total_gb, output_device_type device_type
    , round((mbytes_processed/1024)/((end_time-start_time))/24,1) gb_hour
    , start_time, end_time, status, session_stamp, recid
    , to_char(mod(floor((end_time-start_time)*24),24),'00')||'h'
   || to_char(mod(floor((end_time-start_time)*24*60),60),'00')||'m'
   || to_char(mod(floor((end_time-start_time)*24*60*60),60),'00')||'s'
elapsed_time
    , case when status='FAILED' then
        case when (select count(1) from v$rman_output
                   where session_stamp = x.session_stamp
                     and rman_status_recid = x.recid) = 0
             then 'RMAN Output Not Found'
             else (select
nvl(rtrim(xmlagg(xmlelement(e,substr(output,1,instr(output,':')-1)||',')).extract('//text()'),','),'No Errors ~ Session Hung')
                   from v$rman_output
                   where session_stamp = x.session_stamp
                     and rman_status_recid = x.recid
                     and output like 'ORA-%')
             end
      else null
      end errors
from (select nvl(substr(y.output,instr(y.output,'starting ')+9),object_type)
backup_type
           , mbytes_processed, object_type, output_device_type
           , start_time, end_time, status, x.session_stamp, x.recid
      from v$rman_status x, v$rman_output y
      where x.session_stamp = y.session_stamp(+)
        and x.recid = y.rman_status_recid(+)
        and object_type in ('DB INCR','DB FULL')
        and status != 'RUNNING'
        and operation = 'BACKUP'
        and ((lower(y.output) like '%starting%datafile%backup%'
          and lower(y.output) not like '%validation%')
           or y.output is null)) x
order by session_stamp desc, recid desc)
select session_stamp, recid||'' recid
    , substr(replace(initcap(backup_type),'Db ','DB '),1,25) backup_type
    , substr(device_type,1,10) device_type
    , substr(status,1,11) status
    , to_char(start_time,'dd-Mon-yyyy  hh24:mi')||' ~
'||to_char(end_time,'hh24:mi')||decode(trunc(end_time-start_time),0,null,'(+1Day)') time_frame
    , elapsed_time, total_gb, gb_hour, substr(errors,1,30) errors
from rman_status;

NetBackup link:

cd $ORACLE_HOME/lib
ls -l libobk.so
mv libobk.so libobk.so.orig
ln -s /usr/openv/netbackup/bin/libobk.so64 libobk.so


ln /opt/Tivoli/tsm/client/oracle/bin/libobk.so $ORACLE_HOME/lib/libobk.so

Netbackup on Solaris:
allocate channel t1 type 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so.1';

Netbackup on Windows:
allocate channel t1 type 'SBT_TAPE' send "NB_ORA_CLIENT=client_machine_name";

Omniback DataProtector on HP-UX:
allocate channel t1 type 'SBT_TAPE' PARMS='SBT_LIBRARY= /opt/omni/lib/libob2oracle8_64bit.sl';

Omniback DataProtector on HP-UX:
allocate channel 'dev_1' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=orcl,OB2BARLIST=machinename_orcl_archlogs)';

Create Database link:

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF

COL OWNER FOR A20
COL DB_LINK FOR A20
COL USERNAME FOR A20
COL HOST FOR A20

select * from dba_db_links
order by 1;

OWNER    DB_LINK     USERNAME   HOST     CREATED
---------------------------------------------------
ANGOR    DBLINK_1    USER_1     HOST_1   08-AUG-17
ANGOR    DBLINK_2    USER_2     HOST_2   08-AUG-17
ANGOR    DBLINK_3    USER_3     HOST_3   08-AUG-17
ANGOR    DBLINK_4    USER_4     HOST_4   08-AUG-17
ANGOR    DBLINK_5    USER_5     HOST_5   08-AUG-17
ANGOR    DBLINK_6    USER_6     HOST_6   08-AUG-17
ANGOR    DBLINK_7    USER_7     HOST_7   08-AUG-17
ANGOR    DBLINK_8    USER_8     HOST_8   08-AUG-17


CREATE DATABASE LINK DBLINK_3 CONNECT TO USER_3 IDENTIFIED BY "PassWord" USING 'HOST_3';

$ tnsping HOST_3;

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (TRANSPORT_CONNECT_TIMEOUT=10)
(ADDRESS_LIST= (FAILOVER=on) (LOAD_BALANCE=off) (ADDRESS=(PROTOCOL=TCP)(HOST=omega01)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=omega02)(PORT=1521)))
(CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=app)))
OK (0 msec)


Линк создаётся в схеме ANGOR.
Необходимо знать пароль для подключения пользователя USER_3 к базе app на сервере
omega01.


Проверка dblink:

select * from dba_db_links

select * from dual@dblink
alter session close database link dblink

select * from dual@MY_LINK.SUN.COM;
select * from all_tables@MY_LINK.SUN.COM;
select * from  t1@MY_LINK.SUN.COM;


или так:

begin
DBMS_SESSION.CLOSE_DATABASE_LINK ('dblink');
end;
/



Удалить public линк:

SELECT   'DROP PUBLIC DATABASE LINK ' || object_name ||';'
FROM     SYS.dba_objects
where    object_type = 'DATABASE LINK'
and      owner = 'PUBLIC';


Просмотр информации о линках:

select * from dba_db_links;

select * from dba_objects  where object_type like '%DATABASE LINK%'

select * from sys.v_$dblink;


Поиск сессий, поступающих из удаленной базы данных или отправляющихся в нее:

x$k2gte.k2gtdses соответствует v$session.saddr,
x$k2gte.k2gtdxcb соответствует v$transaction.addr.


select /*+ ordered */ substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) origin,
substr(g.k2gtitid_ora,1,35) gtxid,
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) lsession,
s.ksuudlna username,
substr(decode(bitand(ksuseidl,11), 1,'ACTIVE', 0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'), 2,'SNIPED', 3,'SNIPED', 'KILLED'),1,1) status,
e.kslednam waiting
from x$k2gte g, x$ktcxb t, x$ksuse s, x$ksled e
where g.k2gtdxcb=t.ktcxbxba
and g.k2gtdses=t.ktcxbses
and s.addr=g.k2gtdses
and e.indx=s.ksuseopc;

(This SQL was posted to Metalink, now officially in Doc Note:104420.1.)

Создание пользователей:

create user user_1 identified by "PassWord" default tablespace USERS temporary tablespace TEMP profile default password expire;
grant create session to user_1;


grant SELECT ANY DICTIONARY to user_1;


Какие гранты даны пользователям user_1, user_2, user_3 к объектам типа TABLE и VIEW в схеме ANGOR:


select 'grant select '|| 'on ' ||owner||'.' ||object_name || ' to ' ||'user_1, user_2, user_3'||';'
from all_objects
where owner in ('ANGOR')
and (object_type like '%VIEW%' or object_type like '%TABLE%');

Мониторинг параллельного режима:

col username for a12
col "QC SID" for A6
col "SID" for A6
col "QC/Slave" for A8
col "Req. DOP" for 9999
col "Actual DOP" for 9999
col "Slaveset" for A8
col "Slave INST" for A9
col "QC INST" for A6
set pages 300 lines 300
col wait_event format a30
select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(s.sid) "SID",
to_char(px.inst_id) "Slave INST",
decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE,    
case  sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST",
px.req_degree "Req. DOP",
px.degree "Actual DOP"
from gv$px_session px,
gv$session s ,
gv$px_process pp,
gv$session_wait sw
where px.sid=s.sid (+)
and px.serial#=s.serial#(+)
and px.inst_id = s.inst_id(+)
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
and sw.sid = s.sid 
and sw.inst_id = s.inst_id  
order by
  decode(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID),
  px.QCSID,
  decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
  px.SERVER_SET,
  px.INST_ID
/



set pages 300 lines 300
col wait_event format a30
select
  sw.SID as RCVSID,
  decode(pp.server_name,
         NULL, 'A QC',
         pp.server_name) as RCVR,
  sw.inst_id as RCVRINST,
case  sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
  decode(bitand(p1, 65535),
         65535, 'QC',
         'P'||to_char(bitand(p1, 65535),'fm000')) as SNDR,
  bitand(p1, 16711680) - 65535 as SNDRINST,
  decode(bitand(p1, 65535),
         65535, ps.qcsid,
         (select
            sid
          from
            gv$px_process
          where
            server_name = 'P'||to_char(bitand(sw.p1, 65535),'fm000') and
            inst_id = bitand(sw.p1, 16711680) - 65535)
        ) as SNDRSID,
   decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE    
from
  gv$session_wait sw,
  gv$px_process pp,
  gv$px_session ps
where
  sw.sid = pp.sid (+) and
  sw.inst_id = pp.inst_id (+) and
  sw.sid = ps.sid (+) and
  sw.inst_id = ps.inst_id (+) and
  p1text  = 'sleeptime/senderid' and
  bitand(p1, 268435456) = 268435456
order by
  decode(ps.QCINST_ID,  NULL, ps.INST_ID,  ps.QCINST_ID),
  ps.QCSID,
  decode(ps.SERVER_GROUP, NULL, 0, ps.SERVER_GROUP),
  ps.SERVER_SET,
  ps.INST_ID
/




set pages 300 lines 300
col "Username" for a12
col "QC/Slave" for A8
col "Slaveset" for A8
col "Slave INST" for A9
col "QC SID" for A6
col "QC INST" for A6
col "operation_name" for A30
col "target" for A30

select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(px.inst_id) "Slave INST",
substr(opname,1,30)  operation_name,
substr(target,1,30) target,
sofar,
totalwork,
units,
start_time,
timestamp,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST"
from gv$px_session px,
gv$px_process pp,
gv$session_longops s
where px.sid=s.sid
and px.serial#=s.serial#
and px.inst_id = s.inst_id
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
order by
  decode(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID),
  px.QCSID,
  decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
  px.SERVER_SET,
  px.INST_ID
/

EMCLI export/import templates

SQL> SELECT TEMPLATE_NAME,TARGET_TYPE FROM MGMT_TEMPLATES;

#Export
./emcli export_template -name="HOST_PROD" -target_type="host" -output_file="HOST_PROD.xml"

#Import
emcli import_template -files="HOST_PROD.xml"

#oder mehrere auf einmal
emcli import_template -files="HOST_PROD.xml;DB_PROD.xml"

13c AGENT DEPLOYMENT PROCESS CRASHES 

ON REDHAT/OEL 7.X TARGET HOSTS

SOLUTION

Add the following lines on a target machine either into .bash_profile or .bashrc file and retry the agent deployment

export TMP=/tmp
export _JAVA_OPTIONS="-Xms256m -Xmx512m"
export EM_COMMON_JAVA_OPTIONS="-Xms256m -Xmx512m"

NOTE: After an agent is deployed successfully the lines can be removed.


REFERENCES

EM 12c Agent Deployment Fails With Error "Error occurred during initialization of VM" (Doc ID 1488163.1)

Количество процессоров и размер памяти:

SELECT
  STAT_NAME,
  DECODE(STAT_NAME,'PHYSICAL_MEMORY_BYTES',(ROUND(VALUE/1024/1024/1024,2))
  || ' GB','FREE_MEMORY_BYTES',(ROUND(VALUE            /1024/1024/1024,2))
  || ' GB',VALUE ) VALUE
FROM
  v$osstat
WHERE
  stat_name IN ( 'FREE_MEMORY_BYTES', 'LOAD', 'NUM_CPUS', 'NUM_CPU_CORES',
  'NUM_CPU_SOCKETS', 'PHYSICAL_MEMORY_BYTES' );

Мониторинг alert.log

SELECT to_char(originating_timestamp, 'dd.mm.yyyy hh24:mi:ss') time, message_text
FROM X$DBGALERTEXT
WHERE
message_text like '%ORA-%'
-- and originating_timestamp  between to_date('19.10.2019 00:00:00','dd.mm.yyyy hh24:mi:ss') and to_date('20.10.2019 23:59:59','dd.mm.yyyy hh24:mi:ss')
order by originating_timestamp desc;


для standby проверка наката логов:

set lines 400
col TIME format a20
col MESSAGE_TEXT format a200
set pagesize 50000

SELECT to_char (originating_timestamp, 'dd.mm.yyyy hh24:mi:ss') time, message_text
FROM X$DBGALERTEXT
WHERE message_text like '%RECO%'
and originating_timestamp  between to_date('10.11.2019 00:00:00','dd.mm.yyyy hh24:mi:ss') and to_date('11.11.2019 23:59:59','dd.mm.yyyy hh24:mi:ss')
order by originating_timestamp;




SELECT
    record_id,
    to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS'),
    message_text
 from x$dbgalertext
where originating_timestamp between sysdate - interval
--'1' second and sysdate;
--'1' minute and sysdate;
'5' hour   and sysdate;
--'1' day    and sysdate;
--'1' month  and sysdate;
--'1' year   and sysdate;


select distinct
       originating_timestamp,
       message_text
from x$dbgalertext
where originating_timestamp > sysdate-2
      and (message_text = 'ORA-00600' or message_text like '%Fatal%');



select rownum   "line",
       message_text "error"
  from sys.v_x$dbgalertext
 where originating_timestamp > (sysdate - 5/1440)
   and message_text like '%ORA-%'
order by originating_timestamp;


или так:


select ORIGINATING_TIMESTAMP,
       MESSAGE_TEXT
  from V$DIAG_ALERT_EXT
 WHERE ORIGINATING_TIMESTAMP > systimestamp - INTERVAL '15' minute
   and trim(COMPONENT_ID)='rdbms'
   and inst_id=1
order by originating_timestamp;



select to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS'),
       MESSAGE_TEXT
 from V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP > sysdate - 5/24
and trim(COMPONENT_ID)='rdbms';



set pagesize 120
set linesize 300
column adr_home format a40
column message_text format a80

select call_monitor,
       adr_home,
       inst_id,
       to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS'),
       message_text
 from (select adr_home,
              inst_id,
              ORIGINATING_TIMESTAMP,
              message_text,
              dense_rank() over (PARTITION BY adr_home order by ORIGINATING_TIMESTAMP DESC NULLS LAST) as call_monitor
         from v$diag_alert_ext)
where ORIGINATING_TIMESTAMP > systimestamp - INTERVAL '24' hour
order by ORIGINATING_TIMESTAMP;



 Selecting from the Listener log :

select to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS'),
       MESSAGE_TEXT
  from V$DIAG_ALERT_EXT
 WHERE ORIGINATING_TIMESTAMP between sysdate - interval '10' hour and sysdate
   and trim(COMPONENT_ID)='tnslsnr';


XML markup V$DIAG_ALERT_EXT

set pagesize 120
set linesize 200
set long 99999
column ADR_HOME format a40
column "mylog.xml" format a180

select
   xmlelement(noentityescaping "msg",
           xmlattributes( alt.originating_timestamp as "time",
                          alt.organization_id       as "org_id",
                          alt.component_id          as "comp_id",
                          alt.message_id            as "msg_id",
                          alt.message_type          as "type",
                          alt.message_group         as "group",
                          alt.message_level         as "level",
                          alt.host_id               as "host_id",
                          alt.host_address          as "host_addr",
                          alt.process_id            as "pid_id",
                          alt.version               as "version"
                        ),
                xmlelement("txt", message_text)                       
            ) as "mylog.xml"
from 
   x$dbgalertext alt;
where
   rownum < = 30;



select
   xmlserialize
     (content
      xmlelement
        (noentityescaping "msg",
            xmlattributes
           ( alt.originating_timestamp as "time",
             alt.organization_id       as "org_id",
             alt.component_id          as "comp_id",
             alt.message_id            as "msg_id",
             alt.message_type          as "type",
             alt.message_group         as "group",
             alt.message_level         as "level",
             alt.host_id               as "host_id",
             alt.host_address          as "host_addr",
             alt.process_id            as "pid_id",
             alt.version               as "version"
           ),
             xmlelement("txt", message_text)                       
        )
     indent size=0 hide defaults
     ) as "mylog.xml"
from 
   x$dbgalertext alt
where
   rownum < = 30;

Получить расписание для календарной строки:

Процедура принимает четыре параметра:

calendar_string  Обрабатываемая календарная строка.

start_date  Дата (в виде значения типа TIMESTAMP), с которой следует начать.

return_date_after  Если необходимо указывать даты и моменты времени, стоящие после какой-то определенной даты, то используйте этот параметр для начала последовательности планируемых после данной даты дат.

next_run_date  Это выходной параметр. Процедура записывает в него дату и время, когда планировщик будет выполнять данную календарную строку.



DECLARE

l_start_date TIMESTAMP;
l_next_date TIMESTAMP;
l_return_date TIMESTAMP;

BEGIN
l_start_date := TRUNC (SYSTIMESTAMP);
l_return_date := l_start_date;

FOR ctr IN 1 .. 10
 LOOP
 dbms_scheduler.evaluate_calendar_string (
    'FREQ=YEARLY; BYMONTH=JAN; BYDAY=SUN',
     l_start_date,
     l_return_date,
     l_next_date
);
 DBMS_OUTPUT.put_line ( 'Next Run on: '
 || TO_CHAR (l_next_date, 'dd/mm/yyyy hh24:mi:ss')
 );
 l_return_date := l_next_date;
 END LOOP;
END;


Next Run on: 06/01/2019 00:00:00
Next Run on: 13/01/2019 00:00:00
Next Run on: 20/01/2019 00:00:00
Next Run on: 27/01/2019 00:00:00
Next Run on: 05/01/2020 00:00:00
Next Run on: 12/01/2020 00:00:00
Next Run on: 19/01/2020 00:00:00
Next Run on: 26/01/2020 00:00:00
Next Run on: 03/01/2021 00:00:00
Next Run on: 10/01/2021 00:00:00

Удаление baseline:

col origin format a15
col sql_handle format a21

select s.sql_id, s.plan_hash_value, b.sql_handle, b.plan_name,
       b.parsing_schema_name, b.optimizer_cost, b.origin,
       b.enabled, b.accepted, b.fixed, b.autopurge
from v$sql s, dba_sql_plan_baselines b
where s.exact_matching_signature = b.signature
--  and s.sql_id= '&ORIGINAL_SQL_ID';



Проверить использование плана на базе SPM Baseline для SQL_ID:

col sql_profile format a30
col sql_patch format a28
col sql_plan_baseline format a30
col last_load_time format a20
col last_active_time format a20

select INST_ID,
       SQL_ID,
       CHILD_NUMBER,
       PLAN_HASH_VALUE,
       SQL_PROFILE,
       SQL_PATCH,
       SQL_PLAN_BASELINE,
       LAST_LOAD_TIME,
       LAST_ACTIVE_TIME
from GV$SQL
-- where SQL_ID in ('&ORIGINAL_SQL_ID');

Удаляем:

declare
  l_sql_plan_baseline varchar2(30) := 'SQL_PLAN_4vb6hsprgpdtfe12z832h';
  l_sql_handle varchar2(30);
  l_res number;
begin
  select sql_handle
    into l_sql_handle
    from dba_sql_plan_baselines b
   where plan_name = l_sql_plan_baseline;
  l_res := dbms_spm.drop_sql_plan_baseline(sql_handle => l_sql_handle);
  dbms_output.put_line(l_res);
end;

Удаление SQL - профиля:

select distinct
       p.name sql_profile_name,
       s.sql_id,
       p.category,
       p.status
  from DBA_SQL_PROFILES p,
       DBA_HIST_SQLSTAT s
 where p.name = s.sql_profile
--  and s.sql_id in ('&SQL_ID');

Проверить использование  SQL Profile для SQL_ID:

col sql_profile format a30
col sql_patch format a28
col sql_plan_baseline format a30
col last_load_time format a20
col last_active_time format a20

select INST_ID,
       SQL_ID,
       CHILD_NUMBER,
       PLAN_HASH_VALUE,
       SQL_PROFILE,
       SQL_PATCH,
       SQL_PLAN_BASELINE,
       LAST_LOAD_TIME,
       LAST_ACTIVE_TIME
from GV$SQL
-- where SQL_ID in ('&SQL_ID');

Удаляем:

begin
  dbms_sqltune.drop_sql_profile(name => 'PROF_f7g5rv1nmcrq9', ignore => true);
end;

 

Удалениe SQL патча:

select name, status from dba_sql_patches
--where name LIKE '%%';

 

Проверить использование SQL Patch для SQL_ID:
col sql_profile format a30
col sql_patch format a28
col sql_plan_baseline format a30
col last_load_time format a20
col last_active_time format a20

select INST_ID,
       SQL_ID,
       CHILD_NUMBER,
       PLAN_HASH_VALUE,
       SQL_PROFILE,
       SQL_PATCH,
       SQL_PLAN_BASELINE,
       LAST_LOAD_TIME,
       LAST_ACTIVE_TIME
from GV$SQL
-- where SQL_ID in ('&ORIGINAL_SQL_ID');

Удаляем:

begin
  dbms_sqldiag.drop_sql_patch(name=>'patch_8hko4kdf3rdzt');
end;

adrci purge

$more  /export/home/oradb/bin/_lib/adrci_purge.sh

#!/bin/sh

for ADRHOME in `adrci exec="show home"`
do
    if [ $ADRHOME = "ADR" -o $ADRHOME = "Homes:" ]
    then
        continue;
    fi
    echo $ADRHOME
    adrci << EOF
         set home $ADRHOME
         echo $ADRHOME
         set control (SHORTP_POLICY = 168)
         set control (LONGP_POLICY = 720)

         # purge all tracefiles older than 2 days (2880 minutes):
         purge -age   2880 -type trace

         # purging ALERT older than 30 days
         purge -age 43200 -type ALERT

         # purging INCIDENT older than 14 days
         purge -age  20160 -type INCIDENT

         # purging TRACE older than 14 days
         purge -age  20160 -type TRACE

         # purging CDUMP older than 14 days
         purge -age  20160 -type CDUMP

         # purging HM older than 14 days
         purge -age  20160 -type HM

         show tracefile -rt
         purge
    exit
EOF
done

clean system audit

#
# Define local variables:
#

# Variables:


host_name=${HOST_NAME}
oracle_sid=${ORACLE_SID}
mail_recipients="$MAIL_DBA"

sys_audit_dir=${ORACLE_HOME}/rdbms/audit/

echo "`date +%T` Check directory for system audit: (${sys_audit_dir})"

if [ -r ${sys_audit_dir} ]; then
  echo "system audit RDBMS dir done."
  ${FIND_UTL} /u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/audit/ -type f -name "*_*\_*\_*.aud" -mtime +${retention_period} -print  -exec ls -l {} \;
  ${FIND_UTL} /u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/audit/ -type f -name "*_*\_*\_*.aud" -mtime +${retention_period} -print  -exec rm {} \;
else
  loc_mess_theme="${host_name}(${oracle_sid})-${pkg_name}: Error! Not Found RDBMS Directory for Clean system audit"
  loc_mess_body="Not exist directory: ${sys_audit_dir}"
  echo "${loc_mess_body}" | ${BIN_DIR}/_lib/utl_send.sh "${loc_mess_theme}" "${mail_recipients}"
  echo " - Error!"
  exit
fi

Удаление строк дубликатов:

SET LINESIZE 32767
SET PAGESIZE 50000
alter session set nls_date_format='DD-MON-YYYY';
alter session set nls_language=AMERICAN;

DROP TABLE EMP;

CREATE TABLE EMP(
    EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    HIREDATE DATE
);

alter session set nls_date_format='DD-MON-YYYY';
alter session set nls_language=AMERICAN;


INSERT INTO EMP VALUES(7369, 'SMITH',  'CLERK',     TO_DATE('17-DEC-1980', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7499, 'ALLEN',  'SALESMAN',  TO_DATE('20-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7521, 'WARD',   'SALESMAN',  TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7566, 'JONES',  'MANAGER',   TO_DATE('2-APR-1981',  'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN',  TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7698, 'BLAKE',  'MANAGER',   TO_DATE('1-MAY-1981',  'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7782, 'CLARK',  'MANAGER',   TO_DATE('9-JUN-1981',  'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7788, 'SCOTT',  'ANALYST',   TO_DATE('09-DEC-1982', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7839, 'KING',   'PRESIDENT', TO_DATE('17-NOV-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7699, 'BLAKE',  'MANAGER',   TO_DATE('1-MAY-1981',  'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN',  TO_DATE('8-SEP-1981',  'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7876, 'ADAMS',  'CLERK',     TO_DATE('12-JAN-1983', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7900, 'JAMES',  'CLERK',     TO_DATE('3-DEC-1981',  'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7902, 'FORD',   'ANALYST',   TO_DATE('3-DEC-1981',  'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK',     TO_DATE('23-JAN-1982', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7598, 'BLAKE',  'MANAGER',   TO_DATE('1-MAY-1981',  'DD-MON-YYYY'));
INSERT INTO EMP VALUES(7482, 'CLARK',  'MANAGER',   TO_DATE('9-JUN-1981',  'DD-MON-YYYY'));


SET LINESIZE 32767
SET PAGESIZE 50000

SELECT  ENAME, JOB, HIREDATE FROM EMP;


ENAME      JOB       HIREDATE 
---------- --------- -----------
SMITH      CLERK     17-DEC-1980
ALLEN      SALESMAN  20-FEB-1981
WARD       SALESMAN  22-FEB-1981
JONES      MANAGER   02-APR-1981
MARTIN     SALESMAN  28-SEP-1981
BLAKE      MANAGER   01-MAY-1981
CLARK      MANAGER   09-JUN-1981
SCOTT      ANALYST   09-DEC-1982
KING       PRESIDENT 17-NOV-1981
BLAKE      MANAGER   01-MAY-1981
TURNER     SALESMAN  08-SEP-1981
ADAMS      CLERK     12-JAN-1983
JAMES      CLERK     03-DEC-1981
FORD       ANALYST   03-DEC-1981
MILLER     CLERK     23-JAN-1982
BLAKE      MANAGER   01-MAY-1981
CLARK      MANAGER   09-JUN-1981

17 rows selected.


Повторяющиеся строки есть:
SELECT  COUNT(*), ENAME, JOB, HIREDATE
FROM EMP
GROUP BY ENAME, JOB, HIREDATE;


 COUNT(*) ENAME      JOB       HIREDATE 
---------- ---------- --------- -----------
         1 SMITH      CLERK     17-DEC-1980
         1 WARD       SALESMAN  22-FEB-1981
         1 FORD       ANALYST   03-DEC-1981
         3 BLAKE      MANAGER   01-MAY-1981
         1 JONES      MANAGER   02-APR-1981
         1 MILLER     CLERK     23-JAN-1982
         1 ALLEN      SALESMAN  20-FEB-1981
         1 TURNER     SALESMAN  08-SEP-1981
         2 CLARK      MANAGER   09-JUN-1981
         1 JAMES      CLERK     03-DEC-1981
         1 MARTIN     SALESMAN  28-SEP-1981
         1 SCOTT      ANALYST   09-DEC-1982
         1 ADAMS      CLERK     12-JAN-1983
         1 KING       PRESIDENT 17-NOV-1981

14 rows selected.


Отбор только уникальных строк:

SELECT MIN(rowid), ENAME, JOB, HIREDATE
FROM EMP
GROUP BY ENAME, JOB, HIREDATE;


MIN(ROWID)         ENAME      JOB       HIREDATE 
------------------ ---------- --------- -----------
AAAPQHAAEAAAACzAAA SMITH      CLERK     17-DEC-1980
AAAPQHAAEAAAACzAAC WARD       SALESMAN  22-FEB-1981
AAAPQHAAEAAAACzAAN FORD       ANALYST   03-DEC-1981
AAAPQHAAEAAAACzAAF BLAKE      MANAGER   01-MAY-1981
AAAPQHAAEAAAACzAAD JONES      MANAGER   02-APR-1981
AAAPQHAAEAAAACzAAO MILLER     CLERK     23-JAN-1982
AAAPQHAAEAAAACzAAB ALLEN      SALESMAN  20-FEB-1981
AAAPQHAAEAAAACzAAK TURNER     SALESMAN  08-SEP-1981
AAAPQHAAEAAAACzAAG CLARK      MANAGER   09-JUN-1981
AAAPQHAAEAAAACzAAM JAMES      CLERK     03-DEC-1981
AAAPQHAAEAAAACzAAE MARTIN     SALESMAN  28-SEP-1981
AAAPQHAAEAAAACzAAH SCOTT      ANALYST   09-DEC-1982
AAAPQHAAEAAAACzAAL ADAMS      CLERK     12-JAN-1983
AAAPQHAAEAAAACzAAI KING       PRESIDENT 17-NOV-1981

14 rows selected.



Это нам и нужно оставить в результате.


SELECT MIN(rowid)
FROM EMP
GROUP BY ENAME, JOB, HIREDATE;


MIN(ROWID)      
------------------
AAAPQHAAEAAAACzAAA
AAAPQHAAEAAAACzAAC
AAAPQHAAEAAAACzAAN
AAAPQHAAEAAAACzAAF
AAAPQHAAEAAAACzAAD
AAAPQHAAEAAAACzAAO
AAAPQHAAEAAAACzAAB
AAAPQHAAEAAAACzAAK
AAAPQHAAEAAAACzAAG
AAAPQHAAEAAAACzAAM
AAAPQHAAEAAAACzAAE
AAAPQHAAEAAAACzAAH
AAAPQHAAEAAAACzAAL
AAAPQHAAEAAAACzAAI

14 rows selected.



Удалаем все строки, кроме уникальных:
DELETE FROM EMP WHERE rowid NOT IN
(SELECT MIN(rowid)
FROM EMP
GROUP BY ENAME, JOB, HIREDATE);

3 rows deleted.



Проверяем:

SELECT rowid, ENAME, JOB, HIREDATE
FROM EMP;


ROWID              ENAME      JOB       HIREDATE 
------------------ ---------- --------- -----------
AAAPQHAAEAAAACzAAA SMITH      CLERK     17-DEC-1980
AAAPQHAAEAAAACzAAB ALLEN      SALESMAN  20-FEB-1981
AAAPQHAAEAAAACzAAC WARD       SALESMAN  22-FEB-1981
AAAPQHAAEAAAACzAAD JONES      MANAGER   02-APR-1981
AAAPQHAAEAAAACzAAE MARTIN     SALESMAN  28-SEP-1981
AAAPQHAAEAAAACzAAF BLAKE      MANAGER   01-MAY-1981
AAAPQHAAEAAAACzAAG CLARK      MANAGER   09-JUN-1981
AAAPQHAAEAAAACzAAH SCOTT      ANALYST   09-DEC-1982
AAAPQHAAEAAAACzAAI KING       PRESIDENT 17-NOV-1981
AAAPQHAAEAAAACzAAK TURNER     SALESMAN  08-SEP-1981
AAAPQHAAEAAAACzAAL ADAMS      CLERK     12-JAN-1983
AAAPQHAAEAAAACzAAM JAMES      CLERK     03-DEC-1981
AAAPQHAAEAAAACzAAN FORD       ANALYST   03-DEC-1981
AAAPQHAAEAAAACzAAO MILLER     CLERK     23-JAN-1982

14 rows selected.

Ещё один вариант поиска и удаления строк дубликатов:

Разобъём всё результирующее множество на группы
В каждую группу входят поля  ENAME, JOB, HIREDATE и сортируются по ENAME
Совокупность данных полей и будем проверять на уникальность.

Можно получить всех строки пронумерованные внутри групп.

SELECT  ENAME, ROW_NUMBER() OVER (PARTITION BY ENAME, JOB, HIREDATE ORDER BY ENAME) RN
FROM EMP;


ENAME              RN
---------- ----------
ADAMS               1
ALLEN               1
BLAKE               1
BLAKE               2
BLAKE               3
CLARK               1
CLARK               2
FORD                1
JAMES               1
JONES               1
KING                1
MARTIN              1
MILLER              1
SCOTT               1
SMITH               1
TURNER              1
WARD                1

17 rows selected.


Можно получить rowid всех строк пронумерованных внутри группы.
SELECT  rowid, ROW_NUMBER() OVER (PARTITION BY ENAME, JOB, HIREDATE ORDER BY rowid) RN
FROM EMP;

ROWID                      RN
------------------ ----------
AAAPQIAAEAAAAC7AAL          1
AAAPQIAAEAAAAC7AAB          1
AAAPQIAAEAAAAC7AAF          1
AAAPQIAAEAAAAC7AAJ          2
AAAPQIAAEAAAAC7AAP          3
AAAPQIAAEAAAAC7AAG          1
AAAPQIAAEAAAAC7AAQ          2
AAAPQIAAEAAAAC7AAN          1
AAAPQIAAEAAAAC7AAM          1
AAAPQIAAEAAAAC7AAD          1
AAAPQIAAEAAAAC7AAI          1
AAAPQIAAEAAAAC7AAE          1
AAAPQIAAEAAAAC7AAO          1
AAAPQIAAEAAAAC7AAH          1
AAAPQIAAEAAAAC7AAA          1
AAAPQIAAEAAAAC7AAK          1
AAAPQIAAEAAAAC7AAC          1

17 rows selected.


Оставим в каждой группе только по одной строке с порядковым номером 1,
для этого найдём строки в каждой группе с порядковым номером большим чем 1.
Эти строки необходимо удалить.

SELECT * FROM
  (SELECT  rowid, ROW_NUMBER() OVER (PARTITION BY ENAME, JOB, HIREDATE ORDER BY rowid) RN
   FROM EMP)
WHERE RN > 1;


ROWID                      RN
------------------ ----------
AAAPQIAAEAAAAC7AAJ          2
AAAPQIAAEAAAAC7AAP          3
AAAPQIAAEAAAAC7AAQ          2


Вывод только rowid этих строк.

SELECT rowid FROM
  (SELECT  rowid, ROW_NUMBER() OVER (PARTITION BY ENAME, JOB, HIREDATE ORDER BY rowid) RN
   FROM EMP)
WHERE RN > 1;


ROWID           
------------------
AAAPQIAAEAAAAC7AAJ
AAAPQIAAEAAAAC7AAP
AAAPQIAAEAAAAC7AAQ


Удаляем лишние строки по их rowid

DELETE FROM EMP WHERE rowid IN (
SELECT rowid FROM
  (SELECT  rowid, ROW_NUMBER() OVER (PARTITION BY ENAME, JOB, HIREDATE ORDER BY rowid) RN
   FROM EMP)
WHERE RN > 1);


3 rows deleted.


Проверяем:

SELECT rowid, ENAME, JOB, HIREDATE
FROM EMP;


ROWID              ENAME      JOB       HIREDATE 
------------------ ---------- --------- -----------
AAAPQIAAEAAAAC7AAA SMITH      CLERK     17-DEC-1980
AAAPQIAAEAAAAC7AAB ALLEN      SALESMAN  20-FEB-1981
AAAPQIAAEAAAAC7AAC WARD       SALESMAN  22-FEB-1981
AAAPQIAAEAAAAC7AAD JONES      MANAGER   02-APR-1981
AAAPQIAAEAAAAC7AAE MARTIN     SALESMAN  28-SEP-1981
AAAPQIAAEAAAAC7AAF BLAKE      MANAGER   01-MAY-1981
AAAPQIAAEAAAAC7AAG CLARK      MANAGER   09-JUN-1981
AAAPQIAAEAAAAC7AAH SCOTT      ANALYST   09-DEC-1982
AAAPQIAAEAAAAC7AAI KING       PRESIDENT 17-NOV-1981
AAAPQIAAEAAAAC7AAK TURNER     SALESMAN  08-SEP-1981
AAAPQIAAEAAAAC7AAL ADAMS      CLERK     12-JAN-1983
AAAPQIAAEAAAAC7AAM JAMES      CLERK     03-DEC-1981
AAAPQIAAEAAAAC7AAN FORD       ANALYST   03-DEC-1981
AAAPQIAAEAAAAC7AAO MILLER     CLERK     23-JAN-1982

14 rows selected.

PL/SQL subprogram

set pages 500 linesize 160
col calling_code for a30
col username for a20
col sqltext for a40

select s.sid,
       s.username,
       s.sql_id,
       p1.object_name,
       p1.procedure_name,
       p2.object_name,
       p2.procedure_name,
       substr(st.sql_text,1,40) sqltext,
       count(*)
  from v$session  s,
       all_procedures p1,
       all_procedures p2,
       v$sql st
 where s.plsql_entry_object_id  = p1.object_id (+)
   and s.plsql_entry_subprogram_id = p1.subprogram_id (+)
   and s.plsql_object_id   = p2.object_id (+)
   and s.plsql_subprogram_id  = p2.subprogram_id (+)
   and s.sql_id = st.sql_id(+)
--   and s.sql_id = 'dr2v8xdg4w2ag'
--   and p1.object_name = '...'
--   and p1.procedure_name = '...'
--   and p2.object_name = '...'
--   and p2.procedure_name = '...'
--   and s.module = '...'
group by  s.sid,
       s.username,
       s.sql_id,
       p1.object_name,
       p1.procedure_name,
       p2.object_name,
       p2.procedure_name,
       substr(st.sql_text,1,40)
order by count(*) desc;


  

select ash.top_level_sql_id,
       ash.sql_id,
       P1.OBJECT_NAME,
       P1.PROCEDURE_NAME,
       P2.OBJECT_NAME,
       P2.PROCEDURE_NAME,
       substr(st.sql_text,1,40) sqltext,
       count(*)
  from v$active_session_history ash,
       dba_procedures p1,
       dba_procedures p2,
       v$sql st
 where ASH.PLSQL_ENTRY_OBJECT_ID = P1.OBJECT_ID (+)
   and ASH.PLSQL_ENTRY_SUBPROGRAM_ID = P1.SUBPROGRAM_ID (+)
   and ASH.PLSQL_OBJECT_ID = P2.OBJECT_ID (+)
   and ASH.PLSQL_SUBPROGRAM_ID = P2.SUBPROGRAM_ID (+)
   and ash.sql_id = st.sql_id(+)
--   and ash.sql_id = 'dr2v8xdg4w2ag'
--   and p1.object_name = '...'
--   and p1.procedure_name = '...'
--   and p2.object_name = '...'
--   and p2.procedure_name = '...'
--   and ash.sample_time > ...
--   and ash.module = '...'
group by ash.top_level_sql_id,
       ash.sql_id,
       P1.OBJECT_NAME,
       P1.PROCEDURE_NAME,
       P2.OBJECT_NAME,
       P2.PROCEDURE_NAME,
       substr(st.sql_text,1,40)
order by count(*) desc;

Oracle PL/SQL exceptions

Пример 1:

declare
    x number;

begin
    select 1 into x from dual
    where 1<>1;

    dbms_output.put_line(x || 'return output');

exception
    when NO_DATA_FOUND then
    dbms_output.put_line('Query return no result');

end;
/



Пример 2:

declare
    code number;

begin
    select qval into code from emp;
    dbms_output.put_line('Qval is: ' || code);

exception
    when TOO_MANY_ROWS then
    dbms_output.put_line('More that one rows');

end;
/



Пример 3:

declare
    n number;

begin
    select 1/0 into n from dual;

exception
    when ZERO_DIVIDE then
    dbms_output.put_line('Divide a number by zero');

end;
/



Пример 4:

declare
    n number;

begin
    select to_number('123abc') into n from dual;

exception
    when INVALID_NUMBER then
    dbms_output.put_line('Non numeric value');

end;
/


Пример 5:

declare

    cursor rec is select * from emp;
    x emp%rowtype;

begin
    open rec;
    loop
        fetch rec into x;
        exit when SQL%NOTFOUND
    end loop;
    close rec;

exception
    when CURSOR_ALREADY_OPEN then
    dbms_output.put_line('Cursor already open');

end;
/



Пример 6:
declare

    cursor rec is select * from emp;
    x dept%rowtype;

begin
    open rec;
    loop
        fetch rec into x;
        exit when SQL%NOTFOUND
    end loop;
    close rec;

exception
    when ROW_TYPE_MISMATH then
    dbms_output.put_line('Different row type');

end;
/



Пример 7:

declare

    cursor rec is select * from emp;
    x emp%rowtype;

begin
    -- open rec;
    loop
        fetch rec into x;
        exit when SQL%NOTFOUND
    end loop;
    close rec;

exception
    when INVALID_CURSOR then
    dbms_output.put_line('Cursor is not opened');

end;
/

Oracle Отправка почты

Отправка почты из Oracle:

CREATE OR REPLACE PROCEDURE send_mail (
pSender VARCHAR2,
pRecipient VARCHAR2,
pSubject VARCHAR2,
pMessage VARCHAR2) IS

mailhost CONSTANT VARCHAR2(30) := 'smtp01.us.oracle.com';
crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
mesg VARCHAR2(1000);
mail_conn utl_smtp.connection;

BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);

mesg := 'Date: ' ||
TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
'From: <'|| pSender ||'>' || crlf ||
'Subject: '|| pSubject || crlf ||
'To: '||pRecipient || crlf || '' || crlf || pMessage;

utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, pSender);
utl_smtp.rcpt(mail_conn, pRecipient);
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN INVALID_OPERATION THEN
NULL;
WHEN TRANSIENT_ERROR THEN
NULL;
WHEN PERMANENT_ERROR THEN
NULL;
WHEN OTHERS THEN
NULL;
END send_mail;
/


DECLARE
c utl_smtp.connection;
PROCEDURE send_header(name VARCHAR2, header VARCHAR2) AS
BEGIN
utl_smtp.write_data(c,name ||':'|| header || UTL_TCP.CRLF);
END;

BEGIN
c := utl_smtp.open_connection('smtp-server.psoug.org');
utl_smtp.helo(c, 'demo.org');
utl_smtp.mail(c, 'mailsys@demo.com');
utl_smtp.rcpt(c, 'recipient@oracle.com');
utl_smtp.open_data(c);
send_header('From', '"Sender" ');
send_header('To', '"Recipient" ');
send_header('Subject', 'Hello');
utl_smtp.write_data(c, UTL_TCP.CRLF || 'Hello, world!');
utl_smtp.close_data(c);
utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error
OR utl_smtp.permanent_error THEN
NULL;
END;
raise_application_error(-20000, SQLERRM);
END;
/






begin
test_mail.send (
recipients => 'aaa@mail.ru',
cc => 'bbb@mail.ru',
bcc => 'ccc@mail.ru',
subject => 'Тест темы сообщения',
message => 'Текст сообщения',
priority => 3,
mime_type => 'text/plain; charset=windows-1251'
);
end;
/

в initSID.ora должен быть определен параметр:
smtp_out_server='сервер smtp'