Some sharings on Oracle database and MySQL database

Recently, I am getting involved a lot on database related tasks. My previous jobs mostly deal with gdbm and MySQL. This "new" job makes me deal a lot with Oracle database and cdb. :)

I actually never bother to get to know the special tables in the database. I need to do a tiny query on production server to get to know the number of records of some of the tables today. Imagine a database with thousands of tables. I can't be doing


select count(*) from <table name>

for all of the table names. In addition, I don't know the list of the tables of interest but only the pattern of the table names.

Luckily, found this post : http://stackoverflow.com/questions/6703444/counting-no-of-records-from-multiple-tables-oracle-db

There is a special table host the tables information, namely ALL_TABLES. To get the list of tables in the database and number of rows for each table, run this sql statement in sqlplus.


select TABLE_NAME, NUM_ROWS FROM ALL_TABLES;

For MySQL :


select table_name, table_rows from information_schema.tables;

If you want to filter by table name, please note that Oracle database is case-sensitive.

---

To get the history of last queries, Oracle database seems a lot easier.

For Oracle, to select sql run for the past hour :


select cast(sql_fulltext as varchar(<number of characters>)) from v$sqlarea where last_active_time > (sysdate-1/24);

The field sql_fulltext is of clob type, thus a cast would help to display the "real" fulltext. :)

For MySQL, you'll need to turn on the general_log options by start the mySql service with this option :


--general_log=1

By default, this option is turned off. If you do not know where is the location of the log file, you may actually find it from the database.


select general_log_file from information_schema.global_variables;

Please note that, you are not able to change the file path or name by updating this field. Similarly, you can specify it when you start the mySql service with this option :


--general_log_file=<file path and name>

That's all for now. I am going to continue to study on MySQL equivalent to the flashback in Oracle before the next sharing on this topic. :)

Accessing data in cdb file using Perl

See this post on cdb file related.

Never knew this can be so easy, thanks to the contributors, see here.

To install the library using cpan. Note, must use root to perform this as this will need the root access to create new file/directory.


sudo cpan
install CDB_File

Say you have a cdb file created call abc.cdb with a key summer. Here's how you can get the value of the key summer.


#!/usr/bin/perl
use strict;

use CDB_File;

my $cdb = "abc.cdb";

my %hash;

tie %hash,'CDB_File',$cdb or
die("unable to tie to cdb file");
my $value = $hash{'summer'};

print "$value\n";




Please note, if a key is not found, it will return a null value.


Without the CDB_File library, the tie will not work, and will have this error message :


Can't locate object method "TIEHASH" via package "CDB_File"


So, have fun! :)