To further extend the self-service capabilities of Oracle10g and Oracle 11g, the DBA can grant
system and object privileges to users to allow them to fix their own problems, usually without any
DBA intervention. In the following example, we??™re enabling the user SCOTT to perform Flashback
operations on specific tables and to access transaction metadata across the database:
SQL> grant insert, update, delete, select on hr.employees to scott;
Grant succeeded.
SQL> grant insert, update, delete, select on hr.departments to scott;
Grant succeeded.
SQL> grant flashback on hr.employees to scott;
Grant succeeded.
SQL> grant flashback on hr.departments to scott;
Grant succeeded.
SQL> grant select any transaction to scott;
Grant succeeded.
Flashback Query
Starting with Oracle9i Release 2, the as of clause is available in a select query to retrieve the state
of a table as of a given timestamp or SCN. You might use this to find out which rows in a table were
deleted since midnight, or you might want to just do a comparison of the rows in a table today
versus what was in the table yesterday.
In the following example, HR is cleaning up the EMPLOYEES table and deletes two
employees who no longer work for the company:
SQL> delete from employees
2 where employee_id in (195,196);
2 rows deleted.
SQL> commit;
Commit complete.
SQL>
224 Oracle Database 11g DBA Handbook
Normally, HR will copy these rows to the EMPLOYEES_ARCHIVE table first, but she forgot
to do that this time; HR doesn??™t need to put those rows back into the EMPLOYEES table, but she
needs to get the two deleted rows and put them into the archive table.
Pages:
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384