Here we will see how to copy schema statistics from one schema
to another. Basically there are two methods for doing this:
using
DBMS_STATS or
using
Data Pump
This is especially useful when doing large
data transfers between either schemas or databases and statistics are needed
fast. Remember that data density, row chaining or index cluster factor changed
due to the data import and index rebuilds.
Method 1: Using DBMS_STATS
Extract the statistics
First we need to login as schema owner which
from which we want to extract...
Saturday, 7 June 2014
Sunday, 25 May 2014
Restoring Optimizer Statistics
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...
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...
Sunday, 30 March 2014
Rapid Growth of SYSAUX Tablespace
Oracle 10g onwards, The SYSAUX an auxiliary tablespace to the SYSTEM
tablespace created in your database.
Some database components that formerly created and used separate tablespaces
now occupy the SYSAUX tablespace.
Below table shows the components that uses the SYSAUX tablespace as their default tablespace and
the tablespace in which they were stored in earlier releases.
Component
Using SYSAUX
Tablespace in
Earlier Releases
Analytical Workspace
Object Table
SYSTEM
Enterprise Manager
Repository
...
Saturday, 22 February 2014
Impdp does not created user/schema
Until now my understanding for EXPDP/IMPDP was that
if the user performing import has 'IMPORT FULL DATABASE' privilege, it'll
create the users/schemas in the target database. I needed to export (expdp)
schema from one machine and import (impdp) it to another machine. I just wanted impdp to create schema on target database.
While doing import i
encountered error
$ impdp system/oracle123
directory=TEST_DIR dumpfile=test.dmp logfile=impdptest.log
.......
Starting
"SYSTEM"."SYS_IMPORT_FULL_01": system/********...