1) SQL> show parameter UNDO
| NAME | TYPE | VALUE |
|---|---|---|
| undo_management | string | MANUEL |
| undo_retention | integer | 14400 |
| undo_tablespace | string | UNDO |
SQL> — retention a 10 min
SQL> ALTER SYSTEM SET UNDO_RETENTION=600 SCOPE=MEMORY
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=MEMORYERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
C’est normal c’est un paramètre statique !
On va donc modifier le SPFILE et arrêter / redémarrer la base…
SQL> CONNECT SYS AS SYSDBA
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
On va donner le droit qui va bien à SCOTT pour faire du FLASHBACK :
SQL> Connect system (pas la peine d’être SYS…
SQL> grant flashback on scott.emp to scott
2) — on supprime la ligne (ca c’est pas compliqué…)
SQL> SELECT count(*) FROM emp;
14
SQL> DELETE FROM emp
WHERE ename=’KING’
1 row deleted
SQL> COMMIT
Commit complete
SQL> SELECT count(*) FROM emp;
13
3) — vérificatiuon de la table il y a 2 minutes (avant suppression de KING)
SQL> SELECT * FROM emp
AS OF timestamp(sysdate - 2/1440);
– réinsertion de KING
SQL> INSERT INTO emp
SELECT * FROM emp
AS OF timestamp(sysdate - 2/1440)
WHERE ENAME=’KING’;
SQL> COMMIT
CQF(D)D !
re tester le SELECT de depart...
SQL> explain plan for
2 SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city,
3 SUM(s.amount_sold) AS sum_amount_sold,
4 COUNT(s.amount_sold) AS count_amount_sold
5 FROM sales s, products p, times t, customers c
6 WHERE s.time_id=t.time_id
7 AND s.prod_id=p.prod_id
8 AND s.cust_id=c.cust_id
9 and t.calendar_year=1999
10 and country_id='US'
11 GROUP BY p.prod_subcategory, t.calendar_month_desc, c.cust_city;
Explained.
SQL> select * from table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3325364141
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25025 | 1857K| 30 (4)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| VM_VENTES2 | 25025 | 1857K| 30 (4)| 00:00:01 |
-------------------------------------------------------------------------------------------