Oracle System Statistiken

System Statistiken sammeln

Die aktuellen System Statistiken werden gespeichert in der folgenden Tabelle:

select * from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

Bevor man neue Statistiken sammelt, sollte man die aktuellen Werte aus der sys.aux_stats$ Tabelle löschen. Ansonsten versucht die Datenbank die aktuell in der Tabelle vorhandenen Werte und die neuen gesammelten Werte zu mergen.

Löschen der aktuellen Werte:

exec dbms_stats.delete_system_stats();

Dabei werden die aktuellen Werte aus der sys.aux_stats$ Tabelle entfernt und in eine andere Tabelle gespeichert, damit die Werte für einen eventuellen Restore in der Zukunft trotzdem noch verfügbar sind.

Neue Werte können dann wie folgt gesammelt werden:

exec dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL',interval=> 120);

D.h., dass die Datenbank 120 Minuten lang die Statistiken ermittelt und am Ende die besten Werte übernimmt.

Um einen Überblick über die Historie aller Sammlungen/Löschungen zu erhalten, kann man folgende Query benutzen:

select target,operation,status,start_time,end_time from dba_optstat_operations where operation= 'gather_system_stats' or operation = 'delete_system_stats' order by start_time asc;

Wiederherstellen der Statistiken

Um rauszufinden, wie „viel“ maximal man in der Vergangenheit zurückgehen kann, kann man folgende Query ausführen:

select dbms_stats.get_stats_history_availability from dual;

Die Query wird das maximal in der Vergangenheit verfügbare Datum anzeigen, zu dem man die System-Statistiken zurücksetzen kann.

Ein Beispiel:

Die Query liefert zurück:

10.10.15 06:37:49

D.h. bis zu dem angegeben Zeitpunkt, kann ich zum Wiederherstellen irgendein Zeitpunkt zwischen dem jetzigen, aktuellen Zeitpunkt und den maximalen Punkt in der Vergangenheit wählen. Die Datenbank weis für jede Millisekunde von jetzt bis zu dem maximalen Zeitpunkt in der Vergangenheit, welche System-Statistiken Werte zu der Millisekunde aktiv waren!

Nun kommen wir zum eigentlichen Restore. Zuerst sollte man wieder erstmal die aktuellen Werte aus der sys.aux_stats$ Tabellelöschen:

exec dbms_stats.delete_system_stats();

Nun kann der Restore erfolgen:

exec dbms_stats.restore_system_stats(to_date('09.11.2015 12:00:16','dd.mm.yyyy hh24:mi:ss'));

Man kann als Parameter also genau den Zeitpunkt angeben, zu dem man „springen möchte“. Wenn man z.B. also Statistiken gesammelt hat am 15.10.2015 16:00:00 und dann wieder neue Statistiken gesammelt hat am 20.10.2015 18:00:00 und nach dem ermitteln der Werte des zweiten Sammelns, möchte man zurück zu den Werten, die man beim ersten Mal gesammelt hatte, dann muss man für die restore_system_stats Funktion nicht unbedingt genau den Zeitpunkt „15.10.2015 16:00:00“ angeben, sondern man kann irgendeinen Zeitpunkt angeben, an dem die gewünschten Werte aktuell/aktiv waren, das heißt bis zum 20.10.2015 18:00:00. Das wäre z.b. 16.10.2015 17:55:02, oder 19.10.2015 02:20:23, oder gar 20.10.2015 17:59:59.

How to install Oracle Example Schemas

Hello,

I have been looking everywhere for a simple way to install the HR, OE, etc... sample schemas. If you do not have the files already somewhere on your system, this is a pretty good way to get these schemas:

1. Download the Oracle Examples from:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Choose your version and operating system and click on "See All". On the next page select and download the "Examples".

2. Copy the archive to your system

3. Extract the archive, you will have an "examples" folder now.

4.  Go to examples/stage/Components/oracle.rdbms.companion/11.2.0.1.0/1/DataFiles

There you find a lot of filegroupX.jar files.

5. For the specific schema you want, extract the filegroupX.jar file:

9 - BI
10 - HR
11 - IX
12 - mksample.sql
13 - OE
14 - PM
15 - SH

For example, for the HR schema, do: unzip filegroup10.jar

This will create a "demo" folder.

6. Copy the whole "human_resources" folder to ORACLE_HOME/demo/schemas/

7. Go into the $ORACLE_HOME/demo/schema/human_resources folder

7. In your SQL-Tool (SQLPLus, SQL Developer, etc..) run the hr_main.sql as SYS-User!!! out of the human_resources folder. This will install the schema for you.

Oracle import / export Grant source database grantee does not exist in target database

Hi there,

today I was confronted with the following problem:

I had to export a schema from one database and import to another database. The schema contained grants granted to some user/grantee, who only existed in the source database. These grants should be replaces by another grantee for the target database. A normal export and import was failing with the following errors:

GRANT ON ... ON "SCHEMA"."TABLE" TO "GRANTEE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'GRANTEE' does not exist

A solution for this scenario is:

Do the normal import and ignore the errors. Then do the import again with the sqlfile parameter:

impdp system/peBa4ci9 schemas=... dumpfile=xxx.dmp sqlfile=getddl.sql

When you run this import command, it will not execute an import. Rather it will only create the sql file which will contain all the DDL statements that the import consists of.

Once you have the file, open it and search for the grants DDL's that affect the user/grantee you want to replace. All DDL's for this will be at the same place such as:

GRANT DEBUG ON "SCHEMA"."TABLE1" TO "GRANTEE";
GRANT QUERY REWRITE ON "SCHEMA"."TABLE2" TO "GRANTEE";
GRANT ON COMMIT REFRESH ON "SCHEMA"."TABLE3" TO "GRANTEE";
GRANT REFERENCES ON "SCHEMA"."TABLE4" TO "GRANTEE";
GRANT UPDATE ON "SCHEMA"."TABLE5" TO "GRANTEE";
GRANT SELECT ON "SCHEMA"."TABLE6" TO "GRANTEE";

Replace the GRANTEE with the one you want to have in the target database and run the GRANT commands again on the target database so that the GRANTS are set correctly. FINISH :)

ORA-30372: fine grain access policy conflicts with materialized view

Hello, I was getting the following error when creating and refreshing a materialized view:

ORA-30372 -  "fine grain access policy conflicts with materialized view"
*Cause:    A fine grain access control procedure has applied a non-null policy
           to the query for the materialized view.

This error occurs, when you are creating/refreshing a materialized view on the base of a remote table which is located on another database where Virtual Private Database (VPD) is enabled.

The simplest workaround is to use the "USING TRUSTED CONSTRAINTS" keyword in the syntax:

ORA-01619: thread 1 is mounted by another instance

The following problem may appear when patching the Oracle Grid Infrastructure:

If so, the solution for the problem is the following. You need to find out which node of the RAC is failing to startup. Once you have found out, you need to run the following command on that node (probably in nomount/mount state, locally direct on the specific instance):

alter system set thread=4 scope=spfile sid=’orcl4';

The thread parameter should be one of the available threads in the cluster. You can find that information from the following query:

SQL> select thread#, sequence#, status from v$log;

Then you should  shutdown the instance and retry:

[oracle@dbnodt1 ~]$ srvctl start database -d dbtiso

Opatch version check passed for oracle home but Opatch version check failed Grid Infrastructure

Hi,

if you ever run into the problem, that during patching the Grid Infrastructure the OPatch version check fails although you have updated the OPatch in the GI-HOME correctly and the console says:

The opatch minimum version  check for patch <Patch> failed  <DB_HOME>
The opatch minimum version  check for patch ... failed  for ...
Opatch version check failed for oracle home ...
Opatch version  check failed
ERROR: update the opatch version for the failed homes and retry

And the logs say the following:

Opatch version check passed for oracle home  <$GI_HOME>
Opatch version  check failed

The problem probably that you have only updated the OPatch of the GI-HOME before the running the GI-Patch. You also have to update the OPatch of the DB-Home to the latest version before patching the GI-Home.

Grant user access to several tables easy way

Hi, if you want to grant a database user access (insert, select, delete, update) to several tables of a view, you can use the following PL/SQL snippet: