Oracle Flashback (drop, table, query, version, data archive, transaction, database)

----------------------

-- Flashback Drop   --
----------------------

Si basa sulla tecnologia della Recycle Bin. Se abilitata (parametro di inizializzazione "recyclebin", true/false) le tabelle droppate non vengono realmente cancellate ma settate come spazio disponibile. Queste tabelle droppate, fintantochè non vengono sovrascritte, possono essere lette. Quando un oggetto viene droppato si libera subito lo spazio nella DBA_FREE_SPACE, ma viene mantnuto sulla USER_TS_QUOTAS (almenochè non si fa la purge). Anche se un tablespace è autoextensible viene comnque usato (sovrascritto) lo spazio della recyclebin. Le DDL e le DML non sono permesse sugli oggetti nella recyclebin.

 

--> PARAMETRI INIZIALIZZAZIONE:

alter system set recyclebin=off scope=both;
alter session set recyclebin=off;
SELECT * FROM v$parameter where name='recyclebin';

--> RESTORE (BEFORE DROP):
flashback table "BIN$dui7OTJqRVCv3t3KJfHOnQ==$0" to before drop;
flashback table TAB_01 to before drop rename to TAB_00;

 

--> QUERY:
describe "BIN$VCfmqQB0FfPgQKjAYAJKzg==$0";
select last_name, first_name, email from "BIN$VCfmqQB0FfPgQKjAYAJKzg==$0" where rownum < 10;

 

--> PURGE:
drop table TAB_00 purge; --Non viene mantenuta nella recyclebin
purge table "BIN$U/9fvJKfXOzgQKjAYAIWhw==$0";
purge table TAB_01;
purge tablespace users;
purge tablespace web_orders user inet_us; --(system privilege DROP ANY TABLE)
purge DBA_RECYCLEBIN; --(connect as SYSDBA pivilegi)

-->DATA DICTIONARY:

select * from USER_RECYCLEBIN;
 

----------------------
-- Flashback Table  --

----------------------

Il range di tempo sul quale si può operare una Flashback Table dipende dalla retention dei segmenti di Undo (parametro di inizializzazione dell'istanza UNDO_RETENTION specificato in secondi & parametro del tablespace di UNDO retention guarantee). Per poter riportare una tabella allo stato precedente si dovrà abilitare la modifica dei rowid (ENABLE ROW MOVEMENT). Durante la fase di Flashback Table (transazione singola) oracle impone un lock exclusive ed i dati in tabella saranno consistenti alla data del lock stesso. Se la transaction fallisce l'intero comando viene rolbackato. Gli indici vengono aggiornati ma non le statistiche sull'oggetto. I trigger vengono disabilitati e poi riabilitati prima e dopo la transazione.

--> Grant:
grant FLASHBACK on table_name; -- o FLASHBACK ANY TABLE

--> Grant per opzione "Restor Point":
grant FLASHBACK ANY TABLE;     -- o SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE

--> Abilitazione cambio rowid:
ALTER TABLE table_name ENABLE ROW MOVEMENT;

--> Flashback Tabella
FLASHBACK TABLE nome_tabella TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);

----------------------
-- Flashback Query  --
----------------------

La Fleshback Query si basa sugli undo segments, quindi sottostano alle regole (retention, garantee) del tablespace di undo. Oltre agli undo data, oracle usa anche, se configurati, la Flashback Data Archive.
Settando UNDO_MANAGEMENT ad "AUTO" oracle automaticamente aggiusta la undo retention in base alla grandezza degli undo tablespace. Il parametro di UNDO_RETENTION viene ignorato se l'undo tablespace ha una grandezza fissa.
Se si vuole una retention maggiore degli undo per un subset di tabelle, si può usare  la Flashback Data Archive:

Settare la Retention Guarantee dei Tablespace Undo:

alter tablespace undotbs1 retention guarantee;
select tablespace_name,

       retention

from dba_tablespaces

where tablespace_name like 'UNDO%';

-->Grant
grant execute on dbms_flashback to public;
grant flashback any table to public;

--> Flashback Query (as of)
SQL> select * from t1;
        C1 C2                                                 C3
---------- -------------------------------------------------- -------------------
        10 skdhcbsk                                           01/04/2017 10:40:13
        10 skdhcbsk                                           01/04/2017 10:55:43
        10 skdhcbsk                                           01/04/2017 11:02:07
        10 punto pre punto ripristino                         01/04/2017 11:03:13

SQL> select sysdate from dual;
SYSDATE
-------------------
03/04/2017 15:52:40

SQL> insert into t1 select * from t1;
4 rows created.

SQL> commit;
Commit complete.

SQL> select count(*) from t1;
  COUNT(*)
----------
         8

SQL> select count(*) from t1 as of timestamp (to_date('03/04/2017 15:52:40'));
  COUNT(*)
----------
         4

--> Flashback su delete

SQL> DELETE SPEI_ADMIN.BONIFICO_BANCARIO  where CODICE_SCT='1101172930068931';

3 rows deleted.

SQL> commit;

Commit complete.

SQL> create table bck_delete as select * from SPEI_ADMIN.BONIFICO_BANCARIO as of timestamp (to_date('30/11/2017 09:00:00','dd/mm/yyyy hh24:mi:ss')) where CODICE_SCT='1101172930068931';

Table created.

SQL> select count(*) from bck_delete;

  COUNT(*)

----------

         3

--> Flashback su Truncate  

SQL> select sysdate from dual;
SYSDATE
-------------------
03/04/2017 16:02:46
SQL>  truncate table t11;
Table truncated.

SQL> select count(*) from t1 as of timestamp (to_date('03/04/2017 16:02:46'));
  COUNT(*)
----------
         8

 

Oppure

select * from t1 as of timestamp (systimestamp - interval '2' minute);

--> Flashback on Data Dictionary

 

select count(*) from user_tables as of timestamp (sysdate -200/24/60/60)
;ORA-01031: insufficient privileges

 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE      <-- Default
O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE       <-- Per usare la "AS OF"

 

 

--> Flashback on SCN:
I dati modificati e committati sono individuabili sia tramite il timestamp che l'SCN (system change number).

 

select dbms_flashback.get_system_change_number from dual;

 

select dbms_flashback.get_system_change_number from dual;

select * from employees as of scn   (27153780)

 

 

------------------------
-- Flashback Version  --
------------------------

Per visualizzare lo storico delle modifiche dei dati ad una tabella (relativamente al dato ancora contenuto negli undo segs).
Tramite la Flashback Version Query ritornerà la storia dei records di una tabella all'interno di uun range scn o timestamp.

 

VERSIONS_START{SCN|TIME} Inizio SCN/timestamp quando la modifica è stata apportata al record.
VERSION_END{SCN|TIME}    Fine SCN/timestamp. Se è NULL, l'operazione è ancora in corso o il record non esiste più.
VERSIONS_XID             Transaction ID

VERSIONS_OPERATION       (I=Insert, D=Delete, U=Update)

 

select versions_startscn start_scn,

       versions_endscn end_scn,

       versions_starttime start_time,

       versions_endtime end_time,
       versions_xid "TransactionID",

       versions_operation "InsDelUpd",

       c1, c2, c3
from tab04 versions between scn 1255059 and 1255314
where c1=10
;

------------------------------

-- Flashback Data Archive   --
------------------------------

Use Flashback Data Archive (FDA) to automatically track and archive historical versions of changes to tables enabled for flashback archive, ensuring SQL-level access to the versions of database objects without getting a snapshot-too-old error.  A Flashback Data Archive provides the ability to track and store transactional changes to a table over its lifetime. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.  Flashback Data Archive is configured with retention time.  After Flashback Data Archive is enabled for a table, you can disable it only if you either have the FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA. 

Nella versione 11g era aviabile solo con la licenza di “advanced compression option”, dalla 12c la FDA lavora anche senza compressione. 

Dalla 12c lo User-Context (quindi anche l’utenza che fa la DML sulla tabella source) può essere aggiunto alla storicizzazione del dato modificato; questo permette di usare l’FDA come audit security. 

I dati storici, i "change", vengono conservati su una tabella:

--> Creazione di uno spazio FDA da 10G sul tablespace fda_ts con retention di 1 anno:

   CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts QUOTA 10G RETENTION 1 YEAR;

--> Dare diritti di archive all'utenza test:

   GRANT  FLASHBACK ARCHIVE ADMINISTER TO test;

   GRANT  EXECUTE ON DBMS_FLASHBACK_ARCHIVE TO test;

--> Dare diritti di uso dell'FDA all'utenza test:

   GRANT  FLASHBACK ARCHIVE ON fda_1year TO test;

--> Creazione tabella in FDA:

   CREATE TABLE tab1 (

     id           NUMBER,

     description  VARCHAR2(50),

     CONSTRAINT tab_1_pk PRIMARY KEY (id)

   ) FLASHBACK ARCHIVE fda_1year

   ;

--> Cattura del SYS_CONTEXT:

EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('TYPICAL');

EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('ALL');

EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('NONE');

--> NOTE

Ricordarsi di attendere almeno 15 secondi tra la creazione della tabella associata alla FDA e la commit di eventuali DML (o le informazioni potrebbero essere perse).

Ricordarsi che se si vuole droppare la tabella bisogna prima rimuovere l'FDA.

--> QUERY (vedi Flashback Version):

COLUMN versions_startscn FORMAT 99999999999999999

COLUMN versions_starttime FORMAT A24

COLUMN versions_endscn FORMAT 99999999999999999

COLUMN versions_endtime FORMAT A24

COLUMN versions_xid FORMAT A16

COLUMN versions_operation FORMAT A1

COLUMN description FORMAT A11

COLUMN session_user FORMAT A20

COLUMN client_identifier FORMAT A20

COLUMN my_attribute FORMAT A20

SET LINESIZE 200

SELECT versions_startscn,

       --versions_starttime, 

       versions_endscn,

       --versions_endtime,

       versions_xid,

       versions_operation,

       description,

       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS session_user,

       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','CLIENT_IDENTIFIER') AS client_identifier,

       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'test_context','my_attribute') AS my_attribute

FROM   tab1 

       VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP-(1/24/60) AND SYSTIMESTAMP

WHERE  id = 1

ORDER BY versions_startscn;

--> Backup

BEGIN

  DBMS_FLASHBACK_ARCHIVE.create_temp_history_table(

    owner_name1 => 'TEST',

    table_name1 => 'TAB1');

END;

/

INSERT /*+ APPEND */ INTO temp_history SELECT * FROM SYS_FBA_HIST_77672;

--> Viste di dizionario:

SET LINESIZE 150

COLUMN owner_name FORMAT A20

COLUMN flashback_archive_name FORMAT A22

COLUMN create_time FORMAT A20

COLUMN last_purge_time FORMAT A20

SELECT owner_name,

       flashback_archive_name,

       flashback_archive#,

       retention_in_days,

       TO_CHAR(create_time, 'DD-MON-YYYY HH24:MI:SS') AS create_time,

       TO_CHAR(last_purge_time, 'DD-MON-YYYY HH24:MI:SS') AS last_purge_time,

       status

FROM   dba_flashback_archive

ORDER BY owner_name, flashback_archive_name;

SET LINESIZE 150

COLUMN flashback_archive_name FORMAT A22

COLUMN tablespace_name FORMAT A20

COLUMN quota_in_mb FORMAT A11

SELECT flashback_archive_name,

       flashback_archive#,

       tablespace_name,

       quota_in_mb

FROM   dba_flashback_archive_ts

ORDER BY flashback_archive_name;

SET LINESIZE 150

COLUMN owner_name FORMAT A20

COLUMN table_name FORMAT A20

COLUMN flashback_archive_name FORMAT A22

COLUMN archive_table_name FORMAT A20

SELECT owner_name,

       table_name,

       flashback_archive_name,

       archive_table_name,

       status

FROM   dba_flashback_archive_tables

ORDER BY owner_name, table_name;

----------------------------
-- Flashback Transaction  --
----------------------------

Per la visualizzazione dello storico di una transazione. Il database deve essere in ARCHIVELOG e devono essere abilitati log supplementari (primary key). Non  usa solo gli undo segments, ma anche i Redolog Online e Archive gli stessi che usa il Log Miner (tool, set of PL/SQL, che estrae DDL/DML dai redolog per generare un reverese dello statement SQL, vista V$LOGMNR_CONTENTS)

 

Abilitare il logging delle colonne e il valore delle PK:
alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;
(Comandi Da Vedere)
alter database    add  supplemental log data (primary key, unique) columns;
alter database    drop supplemental log data;
alter database    drop supplemental log data (primary key) columns;
alter table tab02 add  supplemental log data (primary key) columns
alter table tab02 add  supplemental log group (first unique index columns) always
alter table tab02 add  supplemental log data (all) columns;
select * from DBA_LOG_GROUPS;
select * from DBA_LOG_GROUP_COLUMNS;

 

-->Dare i privilege d'esecuzione DBMS_FLASHBACK sul package, SELECT ANY TRANSACTION privilege all'utente che userà la Flashback Transaction Query:
grant execute on dbms_flashback to sched;
grant select any transaction to sched;

 

-->Individuare la transazione:
select *
from (select  dbms_transaction.local_transaction_id id_univ from dual) id,
     V$TRANSACTION trans
where trans.XIDUSN||'.'||trans.XIDSLOT||'.'||trans.XIDSQN= id.id_univ
;

 

--> QUERY FLASHBACK TRANSACTION

select start_scn,
       commit_scn,
       logon_user,
       operation,
       table_name,
       undo_sql
from flashback_transaction_query
where xid = hextoraw('03001600E6030000')
;
select * from FLASHBACK_TRANSACTION_QUERY;
XID Transaction ID number
START_SCN SCN for the first DML in the transaction
START_TIMESTAMP Timestamp of the first DML in the transaction
COMMIT_SCN SCN when the transaction was committed
COMMIT_TIMESTAMP Timestamp when the transaction was committed
LOGON_USER User who owned the transaction
UNDO_CHANGE# Undo SCN
OPERATION DML operation performed: DELETE, INSERT, UPDATE, BEGIN, or UNKNOWN
TABLE_NAME Table changed by DML
TABLE_OWNER Owner of the table changed by DML
ROW_ID ROWID of the row modified by DML
UNDO_SQL SQL statement to undo the DML operation

-------------------------
-- Flashback Database  --
-------------------------

Il Flashback Database è simile al tradizionale point-in-time recovery. Ti consente di riportare un database al suo stato in un momento nel passato recente. Però il Flashback Database è molto più veloce del point-in-time recovery poiché non richiede il ripristino dei data filea dal backup e richiede l'applicazione di un numero inferiore di Redo Logs. Dopo aver abilitato il Flashback Database [e riaperto il database], a intervalli regolari, vengono copiati i blocchi modificati dei datafile nei Flashback Logs. Dopo aver attivato il Restore Point i blocchi presenti nei Flashback Logs vengono usati per ricostruire i datafiles, dopodichè vengono applicati una parte dei RedoLogs [e archive Redo Logs].

Configurare i seguenti settaggi prima di abilitare il Flashback Database:
 - Il database deve essere in archivelog mode
 - Abilitare la fast recovery area (per i flashback log) (in RAC la "fast recovery area" deve essere clustered o in ASM)

Abilitare il flashback logging:
 - Assicurarsi che il database è open o mounted (in questo caso deve essere a seguito di uno shudown cleanly).
 - Opzionalmente settare il parametro DB_FLASHBACK_RETENTION_TARGET in minuti [default 1 gg (1440 minuti)]
     ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days
 - Abilitare il Flashback Database
     ALTER DATABASE FLASHBACK ON;
 - Opzionalmente disabilitare il flashback logging per specifici tablespaces.  
    ALTER TABLESPACE tbs_3 FLASHBACK OFF;
    In questo caso ricordarsi di mettere i data files offline prima del FLASHBACK DATABASE.

--Settaggio della Recovery Area
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 300M;

System altered.

SQL> alter system set db_recovery_file_dest='/usw/app/oracle/admin/BMCMTEST/FRA' scope =memory;

System altered.
/usw/app/oracle/admin/BMCMTEST/FRA

db_recovery_file_dest                string      +BSSA01_FRA
db_recovery_file_dest_size           big integer 400G

SELECT name, log_mode, flashback_on
  FROM v$database;

instance started
alter database archivelog;
alter database noarchivelog;
alter database flashback on;

--Verificare che tutti i tablespace siano stati messi in flashback
select ts#,name, included_in_database_backup,flashback_on from v$tablespace;
alter tablespace TEMP flashback on;
alter tablespace TOOLS flashback on;
alter tablespace USERS flashback on;

alter database open;

-- Creazione punto di Ripristino
create restore point ripristino_db2 guarantee flashback database;

set line 175
column name format a35
column TIME format a30
SELECT scn,
       time,
       guarantee_flashback_database,
       name
  FROM v$restore_point
;

insert into an.t2 values ('riga 4 post punto ripristino');
ALTER SYSTEM SWITCH LOGFILE;

-- Restor Database. Use the V$RESTORE_POINT control file view  a list of all currently defined restore points (normal and guaranteed). Any restore point more recent than the value of CONTROL_FILE_RECORD_KEEP_TIME is retained, regardless of how many restore points are defined

shutdown immediate;
startup mount
flashback database to restore point ripristino_db2;

-- Apertura database
alter database open resetlogs;

--Cancellazione Restore Point
drop restore point ripristino_db;
drop restore point ripristino_db2;
alter database flashback off;
(ripulisce i file di log)

cancellazione archive con rman
sqlplus crosscheck archive

Oracle Cursor - Cursori

Un cursore è un puntamento ad una "private SQL area" dove risiede uno specific statement SQL. Si possono usare gli attribute del cursore per sapere il suo status attuale (quanti record ha elaborato, se è aperto o chiuso, se non ha trovato qualcosa..).

--Implicit/Explicit Cursor:
Il cursore della sessione costruita e gestita da PL/SQL è implicito.

  Il PL/SQL apre un cursore implicito ogni volta che esegue uno statement SQL.

  Per il cursore implicito non bisogna fare nessuna dichiarazione (come nel caso di uno statement SQL).

Il cursore della sessione che si "costruisce" e si "gestisce" è esplicito. Per aprire un cursore esplicito occorre una dichiarazione!

--> Gli attributi del Cursore Implicito sono:
 - SQL%ISOPEN          Attribute: Is the Cursor Open?
 - SQL%FOUND           Attribute: Were Any Rows Affected?
 - SQL%NOTFOUND        Attribute: Were No Rows Affected?
 - SQL%ROWCOUNT        Attribute: How Many Rows Were Affected?
 - SQL%BULK_ROWCOUNT   Getting Number of Rows Affected by FORALL Statement
 - SQL%BULK_EXCEPTIONS Handling FORALL Exceptions After FORALL Statement Completes

--> Gli attributi del Cursore Esplicito sono:
  - CURSOR_NAME%ISOPEN   Attribute: Is the Cursor Open?
  - CURSOR_NAME%FOUND    Attribute: Has a Row Been Fetched?
  - CURSOR_NAME%NOTFOUND Attribute: Has No Row Been Fetched?
  - CURSOR_NAME%ROWCOUNT Attribute: How Many Rows Were Fetched?

##########################################################  Cursore implicito
for cur_int in (   SELECT ename, sal, hiredate, deptno 
                     FROM emp
                )
loop
  ...
end loop;

##########################################################  Cursore Esplicito
DECLARE
  CURSOR c1 IS
  SELECT ename, sal, hiredate, deptno FROM emp;
  ...
BEGIN
  FOR emp_rec IN c1 LOOP
  ...
END LOOP;

##########################################################  Cursore Esplicito

create procedure CREA_SCRIPT_EXP_DWH   IS
cursor c_t is
select *
from bck.ELENCO_TABELLE_DWH
order by proprietario,tipo;
r c_t%rowtype;

BEGIN

--Apertura cursore
        open c_t; loop
        fetch c_t into r; exit when c_t%NOTFOUND;
--Chiusura cursore
        end loop; close c_t;
END;

/ 

##########################################################  ESEMPIO ATTRIBUTI CURSORE (con Dichiarazione RowType):

declare
   CURSOR c_esiti_adr IS
   SELECT *
     FROM d_ruoli_esiti_adr_stg
    WHERE tipo_flusso_esitato   = v_tipo_flusso;
   r_adr d_ruoli_esiti_adr_stg%ROWTYPE;
begin
         OPEN c_esiti_adr;
         LOOP
            FETCH c_esiti_adr INTO r_adr;
            EXIT WHEN c_esiti_adr%NOTFOUND; -- EXIT WHEN emp_cur%ROWCOUNT > 5; --IF emp_cur%FOUND THEN..else.endif.
                 dbms_output.put_line ('sax,ascjba,sdc');
         END LOOP;
         CLOSE c_esiti_adr;
end;
/

##########################################################ESEMPIO sql%rowcount (ATTRIBUTI del CURSORE-SQL):
DELETE d_ruoli_esiti_testa_coda_stg
 WHERE tipo_flusso_esitato   = v_tipo_flusso
;
dbms_output.put_line('--# Record Cancellati D_RUOLI_ESITI_TESTA_CODA_STG: '||sql%rowcount);

--Altri Attributi: SQL%Rowcount SQL%FOUND SQL%NOTFOUND

##########################################################ESEMPIO CURSORE Senza dichiarazione RowType:
declare
    CURSOR c1 IS
        SELECT *
          FROM d_riscosso_testa_coda_stg
         WHERE versione_flusso       = v_versione
           AND tipo_record           = v_tipo_record_file_testa
           AND flag_scarto           = '0';
begin
        FOR rec1 IN c1
        LOOP
                 dbms_output.put_line ('sax,ascjba,sdc');
        END LOOP;
end;
/

########################################################## REF CURSOR:
--> REF CURSOR

è un cursore esplicito che può essere costruito dinamicamente
To pass a cursor variable parameter between subprograms in different PL/SQL units, define the REF CURSOR type of the parameter in a package.

--> SYS REF CURSOR

è un evoluzione del REF_CURSOR che non necessita più della definizione.

Anonymus Procedure

DECLARE
-------------------------------
--Creazione tabella dinamica:--
-------------------------------
   creatabella            VARCHAR (3000)
      :=    'CREATE TABLE tmp_recesso_acq AS('
         || 'SELECT a.row_id FROM s_doc_quote a, s_quote_soln b, s_org_int c '
         || 'WHERE a.row_id = b.quote_id AND b.x_bu_id = c.row_id '
         || 'AND c.NAME = ''Telecom Italia France'' AND a.x_var_30_26 = ''Y'')';
-----------------------------------
--Cancellazione tabella dinamica:--
-----------------------------------
   cancellatabella        VARCHAR (300):= 'DROP TABLE tmp_recesso_acq';
---------------------------------
--Query sulla tabella dinamica:--
---------------------------------
   querycursore           VARCHAR (1000):= 'SELECT row_id FROM tmp_recesso_acq';

-----------------------------------------
--Dichiarazione di un cursore generico:--
-----------------------------------------
   TYPE doc_quote_cur_typ IS REF CURSOR;
   cur_all_quote          doc_quote_cur_typ;

---------------------------------------------------------------------
--Dichiarazione variabile per inserire i dati del cursore generico:--
---------------------------------------------------------------------
   rec_all_quote_row_id   VARCHAR2 (20);
BEGIN
-------------------------------
--Creazione tabella dinamica:--
-------------------------------
   EXECUTE IMMEDIATE (creatabella);

---------------------------------------------------------------------------------
--Apertura del un cursore generico sulla Query eseguita sulla tabella dinamica:--
---------------------------------------------------------------------------------
   OPEN cur_all_quote FOR querycursore;
   LOOP
      FETCH cur_all_quote INTO rec_all_quote_row_id;
      EXIT WHEN cur_all_quote%NOTFOUND;

......

   END LOOP;

-------------------------------------
--Chiusura del un cursore generico:--
-------------------------------------
   CLOSE cur_all_quote;

-----------------------------------
--Cancellazioen tabella dinamica:--
-----------------------------------
   EXECUTE IMMEDIATE (cancellatabella);

end;
/

########################################################## SYS REF CURSOR:

CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno    IN   emp.deptno%TYPE,
                      p_recordset OUT  SYS_REFCURSOR)

AS
BEGIN
  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END GetEmpRS;
/ 

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_cursor  SYS_REFCURSOR;
  l_ename   emp.ename%TYPE;
  l_empno   emp.empno%TYPE;
  l_deptno  emp.deptno%TYPE;
BEGIN
  get_emp_rs (p_deptno    => 30,
              p_recordset => l_cursor);           
  LOOP
    FETCH l_cursor
    INTO  l_ename, l_empno, l_deptno;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
  END LOOP;
  CLOSE l_cursor;
END;
/

Others..

##################################################  Ciclo for
--> Numerico

for i in 1..1000 loop
rlist(i) := rid_tab(i+SELF.howmany);
end loop;

--> Character string

SET SERVEROUTPUT ON SIZE 4000

DECLARE

   TYPE lista IS TABLE OF VARCHAR2 (50);

   my_list   lista := lista ('BMCMCU02', 'BMCMCU01', 'BMCMCA01');

BEGIN

   FOR i IN my_list.FIRST .. my_list.LAST

   LOOP

      DBMS_OUTPUT.put_line (my_list (i));

   END LOOP;

END;

##################################################  Ciclo While
WHILE condition
LOOP
     {.statements.}
END LOOP;
WHILE monthly_value <= 4000
LOOP
     monthly_value := daily_value * 31;
END LOOP;

Materialized View

---------------------------
-- Example Fast Refresh: --
---------------------------
conn /as sysdba@DBTEST
create public database link appo connect to usr_name identified by "abc123" using 'DBTEST';
drop user usr_name;
create user usr_name identified by abc123 default tablespace users quota unlimited on users;
grant connect, resource to usr_name;
grant create materialized view to usr_name;

conn usr_name/abc123@DBTEST4
create table appo (nome varchar2(100));
alter table appo add constraint appo_pk primary key (nome);
CREATE MATERIALIZED VIEW LOG ON appo;

conn usr_name/abc123@DBTEST
select count(*) from appo@appo;
CREATE MATERIALIZED VIEW appo_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND --FORCE Prova prima fare la Fast, se non ce la fà, fa una Complete
AS SELECT * FROM appo@DBTEST4;
execute DBMS_REFRESH.MAKE(name => 'mv_gr1',list => 'appo_mv', next_date => sysdate+1, interval =>'sysdate+1/48');
execute DBMS_REFRESH.REFRESH( name => 'mv_gr1');
select count(*) from appo_mv;
--0

conn usr_name/abc123@DBTEST4
insert into appo values ('sdvasvasdvas');
commit;

conn usr_name/abc123@DBTEST
execute DBMS_REFRESH.REFRESH( name => 'mv_gr1');
SQL> select * from appo_mv;
NOME
-----------------
sdvasvasdvas

------------------------
-- Materialized View  --
------------------------
La Materialized View è un segmento di tipo tabella che viene refreshata periodicamente in base ad una query prestabilita che interroga un'altro schema/database. Una delle differenze fra tabella normale ed vista materializzata è l'impossibilità per quest'ultima di aggiungere o modificare le colonne.
Quando la MV viene ceata, in automatico viene associato un indice (o pk o rowid in base alla tabella source e lo script DDL di create) chiamato: _PK (pk) I_SNAP$_ (rowid).
Se si crea una M.V. con la clausola "FOR UPDATE", diviene una vista modificabile e gli verrà associate ache l'oggetto tabella USLOG$_MvName.
Se la M.V. viene creata con la clausola "FAST REFRESH", nello schema source verrà creato anche l'oggetto MLOG$_MvName.  Quest'ultime due tabelle (MLOG$_, USLOG$_) raccoglieranno i delta da applicare rispettivamente alla tabella/MV source ed al Vista Materializzata target.

Le viste Materializzate possono essere gestite tramite gruppi per facilitarne il maintenance.

----------------------
-- Data Dictionary  --
----------------------
ALL_MVIEWS                DBA_MVIEWS                   USER_MVIEWS
all_snapshots(in più della all_mviews ha le colonne CURRENT_SNAPSHOTS and SNAPSHOT_ID)

ALL_BASE_TABLE_MVIEWS     DBA_BASE_TABLE_MVIEWS        USER_BASE_TABLE_MVIEWS
-                         DBA_MVIEW_LOG_FILTER_COLS    -
ALL_MVIEW_LOGS            DBA_MVIEW_LOGS               USER_MVIEW_LOGS
ALL_MVIEW_REFRESH_TIMES   DBA_MVIEW_REFRESH_TIMES      USER_MVIEW_REFRESH_TIMES
-                         DBA_RCHILD                   -
ALL_REFRESH               DBA_REFRESH                  USER_REFRESH
ALL_REFRESH_CHILDREN      DBA_REFRESH_CHILDREN         USER_REFRESH_CHILDREN
ALL_REGISTERED_MVIEWS     DBA_REGISTERED_MVIEWS        USER_REGISTERED_MVIEWS

ALL_REPGROUP
ALL_REPSITES

set line 175 pagesize 75
alter session set nls_date_format="dd/mm yyyy hh24:mi:ss";
column OWNER format a20
column MVIEW_NAME format a30
column MASTER_LINK format a20
select OWNER,
       MVIEW_NAME,
       UPDATABLE,
       MASTER_LINK,
       REFRESH_MODE,
       REFRESH_METHOD,
       FAST_REFRESHABLE,
       LAST_REFRESH_TYPE,
       LAST_REFRESH_DATE
 from  ALL_MVIEWS
;

SET LINESIZE 175 pagesize 75
COLUMN owner         FORMAT a25
COLUMN name          FORMAT a25
COLUMN MASTER_OWNER  FORMAT a25
COLUMN MASTER        FORMAT a25
  SELECT *
    FROM DBA_MVIEW_REFRESH_TIMES
--   WHERE NAME LIKE '%MVIEW_MASTER%'
ORDER BY LAST_REFRESH;

------------
-- GRANT  --
------------

GRANT CREATE MATERIALIZED VIEW TO schema_user;

GRANT CREATE DATABASE LINK     TO schema_user; --Se serve..

----------------
-- DDL Create --
----------------
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS SELECT ...;
IMMEDIATE:La vista materializzata viene popolata subito.
DEFERRED :La vista materializzata viene popolata al primo refresh.

COMPLETE :La tabella M.V. viene troncata e ripopolata con I dati estrapolati dalla query associate alla M.V.
FAST     :Necessita della M.V. Log nello schema.tabella source, essa viene usata per raccogliere i delta sulla tabella originale per poi essere applicati alla M.V.
FORCE    :Prova prima fare la Fast, se non ce la fà, fa una Complete

ON COMMIT:Il refresh avviene sul commit eseguito sulla tabella source. Non può essere usato se i database sono diversi.
ON DEMAND:Il refresh avviene su richiesta (manual o schedulata)

--------------------------------------
-- GET DDL della Materialized View  --
--------------------------------------

--> MetaScript

set line 150 pagesize 5000

select 'select DBMS_METADATA.GET_DDL(''MATERIALIZED_VIEW'','''||MVIEW_NAME||''','''||OWNER||''')||'';'' from dual;'
from dba_mviews
where OWNER not in ('APPQOSSYS', 'DIP', 'ORACLE_OCM', 'ANONYMOUS', 'EPG', 'CTXSYS', 'DBSNMP', 'EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW', 'OLAPSYS', 'OWBSYS', 'ORDPLUGINS',  'ORDSYS', 'OUTLN', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WK_TEST', 'WKSYS', 'WKPROXY', 'WMSYS', 'XDB', 'XS$NULL', 'XS\$NULL', 'BI', 'HR', 'OE', 'PM', 'IX', 'SH', 'PUBLIC')
  order by 1, OWNER, MVIEW_NAME
  ;

--> Script

set long 40000 longchunksize 40000 line 250 pagesize 0select DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_Name','Schema')||';' from dual;

--------------

-- Snapshot --
--------------
set line 175 pagesize 75
column mview format a35
column tabella format a35
column mmview format a35
column REFRESH_MET format a35
column MASTER_LINK format a15
column ERROR format 99999
select OWNER||'.'||NAME mview,
       TABLE_NAME tabella,
       --MASTER_VIEW||'.'||MASTER_OWNER mmview,
       MASTER,
       MASTER_LINK,
       REFRESH_METHOD,
       LAST_REFRESH,
       ERROR
--       ,REFRESH_GROUP
from all_snapshots
where REFRESH_GROUP=61
;

-----------------------------
-- Materialized View Index --
-----------------------------

-->Gli indici vengono create in automatic se si crea una M.V. con Fast Refresh

set line 150 pagesize 75
column OWNER format a20
column INDEX_NAME format a30
column INDEX_TYPE format a25
column TABLE_NAME format a30
column TABLE_OWNER format a20
select
       OWNER      ,
       INDEX_NAME ,
       INDEX_TYPE ,
       UNIQUENESS ,
       TABLE_OWNER,
       TABLE_NAME ,
       TABLE_TYPE
from all_indexes
where OWNER='XBSESDBO'
  and table_name='NOME_MV'                     --NomeVistaMaterializzata
  and INDEX_NAME like '%\_PK%'     escape '\'  -- Indice su Primary Key
   or index_name like '%I\_SNAP$%' escape '\'  -- Indice su ROWID
;

-------------------
-- ON COMMIT     --
-------------------
-->Con questa clausola la vista materializzata viene refreshiata sul commit effettuato data tabella/MV source.

NOTE: Non si può usare su database remoti
ATTENZIONE: Non si deve refreshare manualmente.. sennò è necessario il suo rebuild.

-------------------
-- FOR UPDATE    --
-------------------
La Vista Materializzata può essere creata con la clausola FOR UPDATE. In questo caso verrà creata una tabella di appoggio che riporterà i delta applicati alla vista con la seguente nomenclatura: USLOG$_nome_vistamaterializzata.

create materialized view nome_vistamaterializzata
for update
as select * from nome_tabellasource@SERVICE01
;

--> STEP  standard di un Refresh per una M.V. con la clausola "For Update":

           - Prima trasferisce i delta dalla Slave/Target/M.V.Site al Source/MasterSite.

           - Poi I dati della MasterSite/Source sono applicati sulla Slave/Target/M.V.Site.

-------------------
-- QUERY REWRITE --
-------------------
-La clausola QUERY REWRITE permette all'optimizer di usare la Materialized View per query che non la chiamano direttamente ma richiamano i dati della tabella sottostante.

CREATE MATERIALIZED VIEW cal_month_sales_mv
ENABLE QUERY REWRITE AS
SELECT  t.calendar_month_desc, SUM(s.amount_sold) AS dollars
FROM sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

-->EXPLAIN PLAN

EXPLAIN PLAN FOR
SELECT  t.calendar_month_desc, SUM(s.amount_sold)
FROM  sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

OPERATION                  OBJECT_NAME
--------------------       -----------
SELECT STATEMENT
MAT_VIEW REWRITE ACCESS    CALENDAR_MONTH_SALES_MV

-----------------------
-- ON PREBUILT TABLE --
-----------------------
--> Può essere usata dopo che si è già create una tabella identica all'originle con un "create table xxx as select * from xxx" (CTAS). Con questa clausola "ON PREBUILT TABLE" la Vista Materializzata non crea un nuovo oggetto (segmento tabella) ma costruisce solo la DDL su un oggetto/tabella preesistente. Al refresh della vista materializzata verrà refreshata la tabella pre-costruita. Ovviamente la tabella deve soddistare I requiiti (deve avere le stesse colonne) della query sottostante la creazione della M.V. Di solito si usa in associazione alla clausola  “create table as select”.

-->Esempio Clausola "PREBUILT TABLE"
create table mv1 as select * from TAB_TEST@localservicename;
CREATE MATERIALIZED VIEW mv1
ON PREBUILT TABLE
REFRESH FORCE ON DEMAND
AS select * from TAB_TEST@localservicename;

--------------------------------------

--  Materialized View FAST REFRESH  --
--------------------------------------

ATTENZIONE: 

- Una Vista Materializzata non può essere con refresh fast se è di tipo complesso:
           --> A CONNECT BY claue.
           --> INTERSECT, MINUS, or UNION ALL set operation.
           --> DISTINCT or UNIQUE keyword.
           --> In alcuni casi aggregate function.
           --> In alcuni casi joins other nelle subquery.
           --> In alcuni casi UNION operation.

PUNTI DI FORZA:

 La fast Refres supporta l'uso del SQL*Loader o le altre "bulk load" utility per l'inserimento dei dati nella tabella/MV Source del MasterSite.

 In questi casi Oracle crea un log del direct loader (da usare poi nel fast refresh) quando viene eseguito un direct-path INSERT sulla tabella Source/MasterSite.

--> Tabella dei LOG

Prima di creare la Materialized View Fast Refresh, bisogna prima creare una tabella sull'ambiente MasterSite che raccolga i delta dei cambiamenti avvenuta sulla tabella/MV Source. La "Materialized View Logs" (owner.MLOG$_nome_tabella) cattura il delta che viene apportato alla tabella source (owner.nome_tabella) per poi venire applicato (ON COMMIT, ON DEMAND) sulla vista materializzata collegata: al refresh la MV viene scritta in base ai dati della "Materialized View Table Logs" e successivamente quest'ultima viene resettata/azzerata.

CREATE MATERIALIZED VIEW LOG ON [schema.]table_name
[... storage options ...]
[... table_partitioning_clause ...]
[WITH {OBJECT | PRIMARY KEY | ROWID | SEQUENCE | column_list}]
[{INCLUDING | EXCLUDING} NEW VALUES];

--> Esempio:

CREATE MATERIALIZED VIEW LOG

ON schema_owner.TAB_TEST
TABLESPACE tools
WITH ROWID -- WITH PRIMARY KEY
INCLUDING NEW VALUES --EXCLUDING NEW VALUES

;
Opzione "INCLUDING/EXCLUDING NEW VALUES":

Specificando INCLUDING:             Salva i new e gli old. Si usa sulle single Master Table su cui viene costruita una Vista Materializzata Aggregata (su più tabelle) Fast.

Specificando EXCLUDING (new): Salva gli old. E' per default. si usa per evitare un overhead di informazioni. NON usare per M.V. FAST Aggregate.

-->Data Dictionary Elenco delle tabelle dei log.
set line 120
select LOG_OWNER,
       MASTER,
       LOG_TABLE,
       PRIMARY_KEY
from ALL_MVIEW_LOGS
;-->Tabella dei delta sul MasterSite
select * from MLOG$_nome_tabella;

-->Esempio SCRIPT CREAZIONE Materialized View FAST REFRESH (Schema DB Slave Site)
CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND  --FORCE Prova prima fare la Fast, se non ce la fà, fa una Complete
AS SELECT * FROM  TAB_TEST@localnetworkservice

;

-->  Purging Rows from a Materialized View Log (FAST REFRESH) --BEGIN
   DBMS_MVIEW.PURGE_LOG (
      master => 'owner_schema.nome_tabella',
      num    => 1,
      flag   => 'DELETE');
END;
/

num --> Number of least recently refreshed materialized views whose rows you want to remove from materialized view log.To delete all rows in the materialized view log, indicate a high number of materialized views to disregard, as in this example: DBMS_MVIEW.PURGE_LOG('master_table',9999);
flag--> Per default il suo valore lascia la precedenza al parametro precedente, se invece viene settato a 'DELETE' allora sovrascrive il parametro precedente e cancella tutti i log.

-------------
-- Esempio --
-------------
CREATE DATABASE LINK DB1.WORLD CONNECT TO schema_user IDENTIFIED BY xxxx USING 'DB1.WORLD';
CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND   --FORCE Prova prima fare la Fast, se non ce la fà, fa una Complete
AS SELECT * FROM emp@db1.world;

-->Ricordarsi di far girare le statistiche dopo la creazione della Materialized View.

BEGIN
  DBMS_STATS.gather_table_stats(
    ownname => 'schema_name',
    tabname => 'nome_mv_tab');
END;
/
-------------
-- Refresh --
-------------
- F :Fast Refresh,         -->Refresha solo I delta dell'oggetto tabella/MV source
- ? :Force Refresh,      -->Se possibile esegue FAST refresh ; altrimenti esegue il COMPLETE refresh
- C :Complete Refresh -->Ricrea tutti I record della source nella MV

- A :Always Refresh     -->Equivalente alla C.

--> DBMS_MVIEW.Refresh

Questo pacchetto (fra le tante cose che può fare) è capace di aggiornare le viste materializzate (che non fanno parte dello stesso refresh group).

DBMS_MVIEW.REFRESH (
   { list                 IN     VARCHAR2,
   | tab                  IN     DBMS_UTILITY.UNCL_ARRAY,}
   method                 IN     VARCHAR2       := NULL,
   rollback_seg           IN     VARCHAR2       := NULL,
   push_deferred_rpc      IN     BOOLEAN        := true,
   refresh_after_errors   IN     BOOLEAN        := false,
   purge_option           IN     BINARY_INTEGER := 1,
   parallelism            IN     BINARY_INTEGER := 0,
   heap_size              IN     BINARY_INTEGER := 0,
   atomic_refresh         IN     BOOLEAN        := true,
   nested                 IN     BOOLEAN        := false);

--> Refresh ALL M.V.

set pagesize 500
select 'exec DBMS_MVIEW.REFRESH('''||OWNER||'.'||MVIEW_NAME||''');'
from all_mviews
where OWNER in ('PBS01','PRB01')
order by OWNER, MVIEW_NAME
;

--> Refresh 1 M.V.

exec DBMS_MVIEW.REFRESH('OWNER.MVIEW_NAME','C');

Note: "C" sta per Complete

--> V$MVREFRESH per verificare quele MV si sta refreshando correntemente:

COLUMN CURRMVOWNER HEADING 'Owner' FORMAT A15
COLUMN CURRMVNAME HEADING 'Materialized|View' FORMAT A25
SELECT CURRMVOWNER, CURRMVNAME

FROM V$MVREFRESH

;

-->Schedulazione Refresh:

CREATE MATERIALIZED VIEW nome_mv
REFRESH FAST
START WITH SYSDATE NEXT SYSDATE+20/(24*60)
FOR UPDATE
AS SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;
 

Esempio
WHERE (date) > sysdate - 7/24;	7 ore
WHERE (date) > sysdate - 7;	7 giorni
WHERE (date) > sysdate - 7/1440;	7 minuti
7/24 13/24	7 ore 13 ore
1/24/60/60 7/24/60/60	1 sendo 7 secondi
1/24/60 5/24/60	1 minuto 5 minuti
1/24 5/24	1 ora 5 ore
TRUNC(SYSDATE+1/24,'HH')	ogni ora a aprtire dalla prossima ora

--> Schedulazione: Analisi del Job associate (vedere anche trace SID_jxx0_yyyyy.trc)
set line 175 pagesize 75
column job format 99999
column WHAT format a55
select    job,
          what,
          THIS_DATE,
          LAST_DATE,
          NEXT_DATE,
          TOTAL_TIME,
          broken,
          failures
from dba_jobs
where job=81
order by last_date
;

--> Execute Job Legato ad un Refresf Schedulato

execute dbms_job.run(81);

-----------------------------
-- Materialized View Group --
-----------------------------

Il pacchetto DBMS_REFRESH.MAKE serve per creare un gruppo di vise materializzate da refreshare tutte assieme:

--> Esempio 1:

execute DBMS_REFRESH.MAKE(name => 'mv_group_1',list => ' mview_1, mview_2', next_date => sysdate+1, interval =>'sysdate+1/48'); 

--> Esempio 2

BEGIN
   DBMS_REFRESH.make(
     name                 => 'SCOTT.MINUTE_REFRESH',
     list                 => '',
     next_date            => SYSDATE,
     interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
     implicit_destroy     => FALSE,
     lax                  => FALSE,
     job                  => 0,
     rollback_seg         => NULL,
     push_deferred_rpc    => TRUE,
     refresh_after_errors => TRUE,
     purge_option         => NULL,
     parallelism          => NULL,
     heap_size            => NULL);
END;
/

NOTE PER DBMS_REFRESH.MAKE:
push_deferred_rpc    -->Si usa solo per le MV updatabili. Bisogna usare il valore di default "true" se si vuole applicare I cambiamenti dalla MV alla Master (table/MV) prima del refresh della MV stessa. Diversamente, con "false", se si refreshia la vista i dati modificati della stessa andranno persi.
refresh_after_errors -->Si usa solo per le MV updatabili. E' legato al parametro recedente: in base al risultato del push_deferred_rpc (corretto o in errore), si stabilise se proseguire nel refresh della vista o meno.

--> Refresh MV Group <--
Durante il Refresh ogni MV del grupo è lockata per il tempo del refresh totale.

--> Pacchetto: DBMS_MVIEW.REFRESH.
exec DBMS_MVIEW.REFRESH (list => 'FAGGA_PR', method => 'C' );
exec DBMS_REFRESH.REFRESH('gropu_name');

Per vedere la data di Refresh si dovrà interrogare la vista DBA_MVIEW_REFRESH_TIMES:
SET LINESIZE         175
SET PAGESIZE         500
COLUMN owner         FORMAT a25
COLUMN name          FORMAT a25
COLUMN MASTER_OWNER  FORMAT a25
COLUMN MASTER        FORMAT a25
  SELECT *
    FROM DBA_MVIEW_REFRESH_TIMES
--   WHERE NAME LIKE '%MVIEW_MASTER%'
ORDER BY LAST_REFRESH;

--> ALTRI PACCHETTI DBMS_REFRESH  <--

CHANGE   (Cambia l'intervallo dii tempo del refresh)
DESTROY  (Rimuove le MV dal gruppo e cancella il gruppo stesso)
SUBTRACT (Rimuove le MV dal gruppo)
ADD      (Aggiunge MV al gruppo)

Si può usare il pacchetto DBMS_REFRESH.ADD per aggiungere una vista materializzata al gruppo creato precedentemente:

execute DBMS_REFRESH.ADD( name => 'my_group_1', list => 'mv_borrowing_rate');

Il pacchetto BMS_REFRESH.SUBTRACT elimina le viste materializzate da un gruppo creato precedentemente.
execute DBMS_REFRESH.SUBTRACT( name => 'my_group_1', list => 'mv_market_rate');

Il pacchetto DBMS_REFRESH.REFRESH è per comandare il refresh di tutto il gruppo
execute DBMS_REFRESH.REFRESH( name => 'my_group_1');

Per modificare l'intervallo di refresh usare il pacchetto DBMS_REFRESH.CHANGE
execute DBMS_REFRESH.CHANGE( name => 'my_group_1', next_date => NULL, interval => 'sysdate+1/96');

Per rimuovere il tutto usare DBMS_REFRESH.DESTROY (cancella materialized views ed il refresh group):
execute DBMS_REFRESH.DESTROY( name => 'my_group_1');

--> DATA DICTIONARY GROUP <-- 

La tabella ALL_REFRESH riporta tutti i Groups di MV:
column ROWNER format a20
column RNAME format a25
column INTERVAL format a35

set line 175 pagesize 75
select ROWNER,
       RNAME,
       REFGROUP,
       JOB,
       NEXT_DATE,
       INTERVAL,
       BROKEN,      -->Y significa che il job è ko e non girerà più
       PARALLELISM,
       PUSH_DEFERRED_RPC,   -->(Create MV FOR UPDATE) Indica se i cambiamenti sono applicati dalla vista
                            --  materializzata alla tabella/vista master prima del refrs (Y) o dopo (N)
       REFRESH_AFTER_ERRORS --> Indica se si procede con il refresh nonostante un errore sul pushing deferred RPCs (vedi campo precedente)
from all_refresh--where BROKEN='N'
;

ALL_REFRESH_CHILDREN  riporta la Lista delle MV nel Gruppo (anche Job Associato alla Schedulazione del Refresh)
set line 175 pagesize 75
column mv format a40
column TYPE format a25
column ROWNER format a12
column RNAME format a22
column refg  format 9999
column JOB   format 9999
column PURGE format 99999
column PARAL format 99999
column INTERVAL format a20
select OWNER||'.'||NAME mv,
--       TYPE                ,
       ROWNER              ,
       RNAME               ,
       REFGROUP            refg,
       IMPLICIT_DESTROY    ,
       PUSH_DEFERRED_RPC   ,
       REFRESH_AFTER_ERRORS,
       JOB                 ,
       NEXT_DATE           ,
       INTERVAL            ,
       BROKEN              ,
       PURGE_OPTION        purge,
       PARALLELISM         paral
from ALL_REFRESH_CHILDREN 
;

ROWNER                Name owner of Refresh Group
RNAME                 Name Refresh groupREFGROUP              Internal identifier Refresh Group
IMPLICIT_DESTROY      (Y| N) If Y, then destroy the refresh group when its last item is subtracted
PUSH_DEFERRED_RPC     (Y |N) If Y then push changes from snapshot to master before refreshREFRESH_AFTER_ERRORS  If Y, proceed with refresh despite error when pushing deferred RPCs
ROLLBACK_SEG          Name of the rollback segment to use while refreshingNEXT_DATE             Date that this job will next be refreshed automatically, if not broken
INTERVAL              A date function used to compute the next NEXT_DATEBROKEN                (Y |N) Y means the job is broken and will never be run
PURGE_OPTION          The method for purging the transaction queue after each push. 1 indicates quick purge option; 2 indicates precise purge optionPARALLELISM           The level of parallelism for transaction propagation
HEAP_SIZE             The size of the heap

--> DBA_RCHILD lista tutti i igli in ogni Group di MV.
set line 175 pagesize 75
column mv format a40
select OWNER||'.'||NAME    mv,
       REFGROUP,
       TYPE#
from DBA_RCHILD
;
REFGROUP  Internal identifier of refresh group
OWNER Owner of the object in the refresh group
NAME Name of the object in the refresh group
TYPE#  Type of the object in the refresh group

ALL_REPGROUP
ALL_REPSITES

--> Refrsh Automatico a seguito di un errore:
Quando Oracle fallisce un refresh Group (Job Oracle), riprova automaticamente a refreshare dopo 1 minuto, poi due minuti, 4 minuti e cosi via..fino a 16 volte, dopodichè mette il gruppo in Broken (colonna BROKEN in USER_REFRESH e USER_REFRESH_CHILDREN della data dictionary). L'errore è riportato nei file di trace nella "diagnostic dest" ed è legato al JOB associato. Dopo aver corretto il problema bisogna far ripartire il gruppo manualemnte ed oracle azzera il broken ripropoenendo il refresh automatico.

--> Job a seguito di Schedulazioni

Per un controllo più approfondito (nel caso di schedulazioni), andare anche a vedere la DBA_JOBS_RUNNING (e se ci sono problem il parametro di inizzializzazione JOB_QUEUE_PROCESSES).

set line 175 pagesize 75
column job format 99999
column WHAT format a55
select    job,
          what,
          THIS_DATE,
          LAST_DATE,
          NEXT_DATE,
          TOTAL_TIME,
          broken,
          failures
from dba_jobs
where job=81  --Vedere il valore nella all_refresh.job

order by last_date
;

--> Execute Job Legato ad un Refresf Schedulato

execute dbms_job.run(81);

-----------------------------------
-- Multitier Materialized Views  --
-----------------------------------
Multitier Materialized Views è una Materialized Views create su un altra Materialized Views. Può essere read-only o updatable. Materialized View costruita su tabella o altra MV è chiamata di "level 1", altra Materialized Views costruita su una tabella/MV di "level 1" è chiamata "level 2" (e così via).

Ogni Master Materialized View può avere molte Materialized View basate su di essa..


-----------------------------------------------
-- M.V Registration (lato Source/MasterSite) --
-----------------------------------------------
Tramite questa vista si possono anche vedere facilmente quali sono le tabelle che hanno delle viste materializzate associate.

--> Viste Registrate:
set line 175 pagesize 75
column mv format a35
column MVIEW_SITE format a35
select OWNER||'.'||NAME mv,
       MVIEW_SITE,
       CAN_USE_LOG,
       REFRESH_METHOD
from ALL_REGISTERED_MVIEWS
;

Oracle non garantisce la Registration/Unregistration delle MV sul loro master site durante la creazione/drop della MV. In alternative si può completare la registration/unregistration manualmente tramite il pacchetto DBMS_MVIEW.REGISTER_MVIEW.