Category Archives: Database

Creating database links for another schema

      No Comments on Creating database links for another schema

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 »

Password History – Reusing a password

      No Comments on Password History – Reusing a password

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 »

Expired passwords in 11g database using 10g client

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 »

Dump interesting information about Oracle files

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 »

Privileges for AWR reporting

      No Comments on Privileges for AWR reporting

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 »

Calculating Compression Ratio

      No Comments on Calculating Compression Ratio

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 »