How to find Last Updated Table in MYSQL Database

MYSQL database is used by many applications and in most of the cases users struggle to get the list of tables which are modified and updated recently using application GUI.

Most of the time users try to find out:

• How to get the last modified date of a table?
• When a mysql table was last updated?
• How to find out table updated during last one hour?
• How to get exact date and time of a table when it was last updated?

Below are the list of queries using which you can find out recently modified tables in MYSQL.

List of tables updated in last 5 Minutes:

SELECT TABLE_SCHEMA,TABLE_NAME,UPDATE_TIME
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE
DATE_SUB(NOW(), INTERVAL 5 MINUTE) < `UPDATE_TIME`;

List of tables updated in last one Hour:

SELECT *
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE
DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`;

Find updated time based on table name:

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'DBNAME'
AND TABLE_NAME = 'TABLE_NAME';

Based on column name :timestamp 

SELECT MAX(updated_at) FROM table_name;

MYSQL RESOURCES:
https://dev.mysql.com/type
https://www.oracle.com/in/mysql/index.html
https://en.wikipedia.org/wiki/MySQL

Read more ...

Resolved ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [210], [39496], [39684]

 ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[210], [39496], [39684], [], [], [], [], [], [], [] error in oracle?

You might have faced this issue while trying to start the database. Most of the time this ora error occur during database restart after abnormal shutdown or power failure. To resolve it you need to recover the database using backup control file as shown below.

[Resolution]

C:\Users\san>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 3 14:53:57 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 3307048960 bytes
Fixed Size                  2180264 bytes
Variable Size            2046823256 bytes
Database Buffers         1241513984 bytes
Redo Buffers               16531456 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[210], [39496], [39684], [], [], [], [], [], [], []

STEP 1: Shut down the database and startup in mount state

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3307048960 bytes
Fixed Size                  2180264 bytes
Variable Size            2046823256 bytes
Database Buffers         1241513984 bytes
Redo Buffers               16531456 bytes
Database mounted.


STEP 2: Take backup of control file manually at os level using cp or copy command.

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      C:\APP\SAN\ORADATA\ORCL\
                                                 CONTROL01.CTL, C:\APP\SAN
                                                 \FLASH_RECOVERY_AREA\ORCL\CON
                                                 TROL02.CTL


STEP 3: Check the Current Logfile 

SQL> select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT'
  2  ;

MEMBER
--------------------------------------------------------------------------------
    GROUP# STATUS
---------- ----------------
C:\APP\SAN\ORADATA\ORCL\REDO03.LOG
         3 CURRENT



STEP 4: Shutdown the DB and startup in Mount state

SQL> Shutdown abort ;
ORACLE instance shut down.


SQL> Startup mount ;
ORACLE instance started.

Total System Global Area 3307048960 bytes
Fixed Size                  2180264 bytes
Variable Size            2046823256 bytes
Database Buffers         1241513984 bytes
Redo Buffers               16531456 bytes
Database mounted.



STEP 5: Recover the database using backup controlfile until cancel.

SQL> recover database using backup controlfile until cancel ;
ORA-00279: change 2737134 generated at 12/27/2017 12:21:37 needed for thread 1
ORA-00289: suggestion :
C:\APP\SAN\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2018_01_03\O1_MF_1_210_%U_.
ARC
ORA-00280: change 2737134 for thread 1 is in sequence #210


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\APP\SAN\ORADATA\ORCL\REDO03.LOG
Log applied.
Media recovery complete.
SQL>



STEP 6: Reset the logs and open the database

SQL> Alter database open resetlogs ;

Database altered.

SQL>
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\san>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 3 15:00:37 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 3307048960 bytes
Fixed Size                  2180264 bytes
Variable Size            2046823256 bytes
Database Buffers         1241513984 bytes
Redo Buffers               16531456 bytes
Database mounted.
Database opened.
SQL>
SQL>

Read more ...