Oracle logging table changes

In this post I’ll put a small example of how to create stored procedure for Oracle and log changes in database table

Read the rest of this entry »

Oracle – Dynamic Queries and Reporting

There are various tools to provide enterprise-level reports. But what to do if you need yo create reports for yourself? In this article I’m going to describe how to manage simple reports using Oracle 10g.

Read the rest of this entry »

Oracle stats (Part 3)

Finally let’s find out how to gather per table statistics.

Read the rest of this entry »

Oracle stats (Part 2)

This time we will monitor tablespace stats. I’d like to create one stats table per tablespace and use the same technique as in Part 1

Read the rest of this entry »

Oracle stats (Part 1)

Our customer runs Oracle database and my chief has given me a task to do for today.

I need to prepare prognosis of how much oracle database will grow in next 3 years. After short googling I’ve found interesting sripts [1]:

 select sum(bytes)/1024/1024 "Meg" from dba_data_files;
 select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;
 select sum(bytes)/1024/1024 "Meg" from sys.v_$log;
 select sum(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024) "MEG" from v$controlfile;

The main idea is to create oracle DBMS job which will fire each hour for let’s say 7 days (yeah, we’ve got only 7 days :() and outputs statistics about database size into some database table.

Read the rest of this entry »