In some cases we
may find that gathering fresh statistics has led to the optimizer executing
sub-optimal execution plans and we would like to restore the statistics as of a
date when we had good performance.
Here we can restore table statistics to a previous point in the past by using the DBMS_STATS.RESTORE_TABLE_STATS package.
We can check how
far back in time we can go to restore statistics.
SQL> select
DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
13-MAR-14
10.51.37.266819000...
Sunday, 25 May 2014
Refresh of Mview is Throwing ORA-00942
Refresh of Mview is throwing ORA-942 after
successful creation.
Create materialized view
schema_a.client_mview
tablespace users build immediate
refresh force start with trunc(sysdate)+1455/1440
next trunc(sysdate)+1455/1440
as
select client_id, username, createdate, swver
from client_req@TEST_LINK
where swver is not null;
Materialized View Created.
select db_link, username from dba_db_links where db_link like 'TEST_LINK';
DB_LINK USERNAME
--------------------...
Saturday, 10 May 2014
LOG MINING IN ORACLE DATABASE
Suppose data in a table has been deleted.
Audit trail was not enabled, therefore we do not have a clue of the user who has
performed deletion and there are around 30 users that have DELETE rights on
that table. Management want to know the name of the user, here we can use Oracle LOG MINING feature to filter out
the transactions performed during that period.
Note: Make sure the Archive log must be
enabled previously, if you want to use LOG Mining technique.
CONSIDERATIONS
Database : ORCL
Schema : SCOTT Table : STUDENTS
Date of Deletion...