Sooner or later it will come to a situation in which we have to transfer data between different database. Given the fact we have to do a normal database upgrade Oracle Export/Import will help us to do as long we will go with a full database export and all users, priviledges, permissions and quota will get transfered.

Now given the case that we based on schedules or migration plans just have to overtake single schemas world does look different. Oracle Import will expect theuser schema already exisiting and even try to create all database objects in relation to their old location within the former database.

Now I’m not going to explain you how to change tablespace or storage information within this posting … perhaps in another one … but will try to help you with pre-setting up the schema and additional security information before importing the data.

As for we work with some database system all data can for sure get queried and even stored within directly executable sql statement. Since years I’m working with an easy to use set of scripts collecting these information for me.

Feel free to download it from here and using it for your own pleasure.  When unpacked you can call the script 0_capture_security_information.sql within SQLPLUS and you will get generated another result set of scripts containing all users, roles and granted system and role priviledges stored with the database you are connected too.

Connecting now to the new target database and executing the scripts full or in parts will allow you to set up security there again.

Sooner or later with a permanent growing database we will end up in a situation that our night isn’t just long enough anymore to backup our database completely.

Now you might wonder about what size of database I’m talking about right now. But seriously, database at a size of several terrabyte are nothing uncommon today anymore. Especially when it comes to SAP or similar applications working on them databases can grow exceptional sizes..

The Oracle Database Version 10g now comes along with some new feature helping us to deal with that situation. It is a feature called Block Change Tracking and marks down all modified database blocks changed by all transactions within an additional external file.

When a commit is issued against a data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer and during the next checkpoint, the CTWR process writes down the information from the CTWR RAM buffer to the former defined change-tracking file.

Now doing an incremental backup with Oracle 10g, RMAN has a mechanism to identify and bypass those data blocks which have not changed by just easily following the list of changed blocks within this file.

The syntax for Oracle block level change tracking is simple:

    ALTER DATABASE
    ENABLE BLOCK CHANGE TRACKING
    USING FILE os_file_name;

By default, Oracle does not record block change information!

To enable this feature, we need to issue the following command:

SQL> alter database enable block change tracking;

To disable this feature, we issue this command:

SQL> alter database disable block change tracking;

So it’s an absolutely easy to configure mechanism, being able to speed up our nightly incremental backups dramatically. Leaving the full backups for the weekends, customers then hopefully don’t mind.

The only thing we have to be aware about is the space this file later on will need to get written. Based on our databases transaction load this change-tracking file can reach some serious size for sure.

Working as a database administrator sooner or later we will get confronted with the customer or developer request about executing a SQL Statement for them within the production database.

Sure, as the DBA we are and with all the rights we have, it isn’t really a big deal for us to manipulate objects within the context of another schema owner. We all know that it just needs fully qualified the object like ‘schema_name.object_name’ and the database will know which database object we reference to.

Just how often we will get such full specified scripts to execute from our customers? Guess never! So it needs us to overwork all the scripts and at the end even to overtaking responsibility for the scripts we changed.

How much more convenient would it be for us to directly work within the user context in question? Impossible?! We need the user password first! The password the user has forgotten to share with us first!

And unfortunately we are also not able to change it, because it is hard coded within the application. And changing it would leave the application inoperable later on?!

Now working with Oracle this situation can get solved for sure! You wonder how?

Within Oracle versions older than 11g, we have a DES hash password which can queried from database with the following SQL Command:

• DBA_USERS : SELECT username, password FROM DBA_USERS;

• SYS.USER$ : SELECT name,password FROM SYS.USER$ WHERE password is not null;

Starting with Oracle 11g we can query the new SHA-1 hash by using this SQL Command:

• SYS.USER$ : SELECT name,spare4 FROM SYS.USER$ WHERE password is not null;

   
Now it is possible to change a password temporarily. And using this little trick here, we will be able to act as a different user.

SQL> select username,password from dba_users where username=’SCOTT’;

USERNAME PASSWORD
——– —————-
SCOTT F894844C34402B67

SQL> alter user scott identified by mypassword;

Now we can login with the following credentials: scott/mypassword

And after our work is done we can change the password back by using an undocumented feature called “by values”

SQL> alter user scott identified by values ‘F894844C34402B67’;

And now we just revert our password manipulation by using exactly the password hash we have queried earlier from the database and all will be fine again.