There are situations (like after cloning databases) where you, as a DBA, want to create database links in other schema’s, for which you don’t have the password. Unfortunately the security of Oracle doesn’t allow (even SYS) you to drop/create database links for other schema’s. One solution for this is to get the password hash value, temporarily change the password for… Read more »
By setting either one or both the profile limits PASSWORD_REUSE_MAX or PASSWORD_REUSE_TIME are set to anything other than UNLIMITED, Oracle somewhere keeps a history of passwords used by a user. This password history is stored in a table with the name user_history$ which is part of the SYS schema. Whenever the users password is changed, either by the user of… Read more »
Some time ago we had a problem using the adrci (ADR Command Interpreter) to look into some diagnostic information for a listener. After staring up the tool it came back with the warning “No ADR base is set”, even while the ORACLE_BASE environment variable was set. While it is possible to use the SET BASE <ADR base directory> to set… Read more »
When I create a new user/schema in an Oracle database I specify a password and immediately use the PASSWORD EXPIRE clause to expire this one-time-use only password. This way I make sure the user/application who is going to use this new account will be forced to specify his own password. This works great unless you have a pre 10.2.0.5 Oracle… Read more »
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 »
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 »