Day to day MYSQL DBA operations (Compared with Oracle DBA)

Here is a mapping of commonly used Oracle dynamic performance views (V$ views) and data dictionary views (DBA_*) to their closest equivalents in MySQL. These are especially useful for Oracle DBAs learning how to inspect system-level information in MySQL.

---

🔄 Oracle V$ and DBA_ Views vs MySQL INFORMATION_SCHEMA / Performance Schema

Oracle View	MySQL Equivalent	Purpose / Notes
V$SESSION	information_schema.PROCESSLISTperformance_schema.threads	Shows active sessions / threads. Also use SHOW FULL PROCESSLIST.
V$DATABASE	SELECT @@hostname, @@version, @@datadirSELECT schema_name FROM information_schema.schemata	No direct equivalent; use system variables + schemata info.
V$INSTANCE	SELECT @@hostname, @@port, @@version, @@basedir, @@socket	Shows server instance details.
V$LOCK	performance_schema.metadata_locksinformation_schema.innodb_locks (if enabled)	For active locks. Note: innodb_locks is deprecated in newer versions.
DBA_TABLES	information_schema.tables	Shows table metadata (rows, engine, size etc).
DBA_VIEWS	information_schema.views	Lists view definitions and properties.
DBA_OBJECTS	No single equivalent. Use combination of:• information_schema.tables• information_schema.views• mysql.proc (for routines)	Lists all objects (tables, views, procedures, triggers, etc). MySQL separates this.
DBA_USERS	mysql.user (use: SELECT User, Host FROM mysql.user)	Lists all users. Not available in information_schema.
V$PARAMETER	SHOW VARIABLESinformation_schema.global_variables	For system parameter values.
V$SYSSTAT	SHOW GLOBAL STATUSperformance_schema.global_status	For system statistics.
V$DATAFILE	MySQL manages files internally.Use: SHOW TABLE STATUS or check InnoDB tablespace info	MySQL hides datafile info; not user-controllable like Oracle.
DBA_TAB_COLUMNS	information_schema.columns	Shows column-level metadata.
DBA_INDEXES	information_schema.statistics	Shows indexes, columns, uniqueness, etc.
DBA_CONSTRAINTS	information_schema.table_constraints	Includes PRIMARY KEY, UNIQUE, FOREIGN KEY.
DBA_TRIGGERS	information_schema.triggers	Lists triggers defined in the database.
DBA_PROCEDURES	mysql.proc (before MySQL 8)information_schema.routines (MySQL 8+)	Lists stored procedures and functions.
DBA_ROLES	mysql.role_edges (MySQL 8+)	Role information in MySQL 8+. Earlier versions lack role support.
DBA_TAB_PRIVS	information_schema.schema_privilegesinformation_schema.table_privileges	Lists privileges granted on schema/table level.
DBA_JOBS	No direct match. Use mysql.event	Event Scheduler can act as job manager. Enable with SET GLOBAL event_scheduler = ON.

---

✅ Examples of Useful MySQL Queries

-- Active sessions (like V$SESSION)
SELECT * FROM information_schema.PROCESSLIST;

-- All databases (like DBA_USERS schemas)
SELECT schema_name FROM information_schema.schemata;

-- All tables and row counts
SELECT table_schema, table_name, engine, table_rows 
FROM information_schema.tables 
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema');

-- Table columns
SELECT table_name, column_name, data_type, is_nullable, column_default 
FROM information_schema.columns 
WHERE table_schema = 'your_db';

-- Views
SELECT * FROM information_schema.views 
WHERE table_schema = 'your_db';

-- Stored procedures and functions
SELECT * FROM information_schema.routines 
WHERE routine_schema = 'your_db';

-- Indexes (like DBA_INDEXES)
SELECT * FROM information_schema.statistics 
WHERE table_schema = 'your_db';

-- Locks
SELECT * FROM performance_schema.metadata_locks;

-- Events (Scheduled jobs)
SELECT * FROM information_schema.events;

-- System variables (like V$PARAMETER)
SHOW VARIABLES;

-- System status (like V$SYSSTAT)
SHOW GLOBAL STATUS;

---