Here are some useful statements for generating trace files in your udump directory about various important Oracle files: Get a dump of your controlfiles: ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME CONTROLF LEVEL 10’; Dump the headers for all your datafiles: ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME FILE_HDRS LEVEL 10’; Dump the headers of your redologs: ALTER SESSION SET… Read more »
When you want to permanently remove an agent from you OEM GC environment, you will have the GUI method and the manual method. The reason for writing this post is that I was able to remove all targets from within the GUI except the agent itself, at least at screen I was expecting to offer this functionality. The GUI method… Read more »
Sometimes during a project there are people that would like to generate AWR reports. Here are the privilges on certain database objecs that a user needs to be able to generate such reports using SQL*Plus (in this case granted to a role). CREATE ROLE awr_user; GRANT SELECT ON SYS.V_$DATABASE TO awr_user; GRANT SELECT ON SYS.V_$INSTANCE TO awr_user; GRANT EXECUTE ON… Read more »
Sometimes it can be useful to simulate a problem in your database by simulating an ORA-error. After we had a nasty bug with Grid Control (GC suddenly wasn’t able to monitor the alertlog of 10.2.0.5 databases anymore, see my post about this problem) where we had to discover this bug by having a problem in a database which was not… Read more »
Before implementing one or more of the Oracle compression options that are available within an Oracle database, there are a couple of things you would like to examine before implementing one or more of these compression options. One thing you would like to know in front, without actually compressing an table, table partition or index, how much storage you would… Read more »
Until last week I din’t know about a listener process having one or more childrens (a child process has exactly the same name as its parent but have their parent-pid set to the parent listener pid). Trying to find information about the existence of listener child processes (first of all I wanted to know if this is “normal”) always pointed… Read more »
The DBMS_METADATA is a powerful package that offers the retrieval of metadata from the data dictionary as XML or DDL and to submit XML to re-create an object. In this post I will show you how you can quickly generate DDL statements for lots of objects in the database using some examples. There are quite some functions in this package,… Read more »
When adding or enabling a constraint on a table, it is possible that an error occurs because of an constraint violation. If for example you are trying to add a unique constraint to a column (or combination of columns) of a table that has duplicates (two ore more rows with the constraint columns having the same values) you will receive… Read more »
Within Oracle databases passwords get hashed. When, for example, you create a new account or change the password of an account, the specified clear-text password gets hashed. This hash value is stored in the database with the account. Once hashed it is not possible to get the original clear-text password from the hash value (one-way hash algorithms) again. The case-insentive… Read more »
There are circumstances where you whish to have a look at the source of an wrapped PL/SQL package, procedure or function. The circumstances I want to have peek at the source is when some PL/SQL code becomes invalidated because an object the code depends on disapears or grants on such objects gets lost. It would/should of course be nice if… Read more »