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. :)