Usage:
SELECT tablespace_name FROM dba_tablespaces
Why:
A tablespace is Oracle's logical storage unit that groups related database objects together. Each tablespace maps to one or more physical datafiles on disk. Oracle organizes all storage through tablespaces - you can't create tables without specifying which tablespace they belong to. This allows DBAs to control exactly where different types of data are stored, separate hot/cold data across different disks, and manage backup/recovery at the tablespace level.
MySQL:
SHOW DATABASES - MySQL uses databases as the top-level organizational unit, much simpler but less granular control
Usage:
SELECT file_name, tablespace_name, bytes FROM dba_data_files
Why:
These are the actual physical files (.dbf extension) on the operating system that store your database data. Each tablespace consists of one or more datafiles. When a tablespace runs out of space, you either add more datafiles or extend existing ones. Critical for monitoring disk usage and planning storage growth.
MySQL:
No equivalent - MySQL manages files automatically per table (.ibd files for InnoDB)
Usage:
SELECT autoextensible FROM dba_data_files
Why:
Controls whether Oracle can automatically grow a datafile when the tablespace runs out of space. If set to NO and tablespace fills up, all operations fail with "ORA-01653: unable to extend table". If YES, Oracle will grow the file up to MAXBYTES limit. Essential for preventing application crashes due to space issues.
MySQL:
Not applicable - MySQL files grow automatically as needed
Usage:
SELECT maxbytes FROM dba_data_files
Why:
The maximum size a datafile can grow to when autoextensible is enabled. Prevents runaway processes from filling up entire disk. Set to UNLIMITED means it can grow until disk is full. Used for capacity planning and preventing disk space emergencies.
MySQL:
Not applicable
Usage:
SELECT sid FROM v$session
Why:
Session Identifier - a unique number Oracle assigns to each database connection. Ranges from 1 to SESSIONS parameter. When you connect to Oracle, you get assigned a SID. Used in all monitoring queries to identify which user session is doing what. Required for killing problem sessions.
MySQL:
SELECT CONNECTION_ID() - MySQL's equivalent but called connection ID
Usage:
SELECT sid, serial# FROM v$session
Why:
Oracle reuses SID numbers when sessions disconnect. Serial# distinguishes between different sessions that used the same SID. When killing a session, you need both SID and SERIAL# to ensure you kill the right one, not a new session that reused the old SID.
MySQL:
Not needed - MySQL connection IDs are never reused
Usage:
SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL
Why:
Shows which session (SID) is holding a lock that's blocking another session. Essential for resolving deadlocks and performance issues. If session 123 is blocked, blocking_session tells you which session is holding the lock that session 123 is waiting for.
MySQL:
SELECT blocking_pid FROM sys.innodb_lock_waits - similar concept
Usage:
SELECT last_call_et FROM v$session
Why:
"Last Call Elapsed Time" - seconds since the session last sent a request to the database. Used to identify idle sessions (high values) vs active sessions (low values). Sessions with high LAST_CALL_ET values are candidates for killing to free up resources.
MySQL:
TIME column in SHOW PROCESSLIST - seconds the current command has been running
Usage:
SELECT machine FROM v$session
Why:
The hostname or IP address of the client machine connecting to Oracle. Useful for identifying which application servers or user workstations are connected. In LIMS environments, helps track which lab instruments or workstations are causing issues.
MySQL:
HOST column in SHOW PROCESSLIST - shows client hostname:port
Usage:
SELECT program FROM v$session WHERE program LIKE '%java%'
Why:
The name of the client program connecting to Oracle (like "sqlplus.exe", "java.exe", "w3wp.exe" for IIS). Critical for LIMS troubleshooting to identify whether issues are coming from the web application, background jobs, or interactive users.
MySQL:
No equivalent - MySQL doesn't track client program names
Usage:
SELECT sql_id, sql_text FROM v$sql WHERE sql_id = 'abc123xyz'
Why:
A unique 13-character hash identifier Oracle generates for each distinct SQL statement. Same SQL text always gets same SQL_ID. Allows you to track performance of specific queries across multiple executions and different sessions. Essential for Oracle performance tuning.
MySQL:
DIGEST in performance_schema.events_statements_summary_by_digest - 64-character hash
Usage:
SELECT sql_text FROM v$sql WHERE sql_id = 'abc123xyz'
Why:
The actual SQL statement text. Oracle stores recently executed SQL in memory for performance analysis. Combined with SQL_ID, lets you see exactly what queries are running and how they're performing.
MySQL:
DIGEST_TEXT in performance_schema - normalized SQL text
Usage:
SELECT elapsed_time/1000000 as seconds FROM v$sql
Why:
Total elapsed time for all executions of this SQL statement, stored in microseconds. Divide by 1,000,000 to get seconds. Key metric for identifying slow queries - high elapsed_time indicates performance problems.
MySQL:
SUM_TIMER_WAIT in performance_schema (picoseconds, divide by 1,000,000,000,000)
Usage:
SELECT executions FROM v$sql WHERE executions > 1000
Why:
How many times this SQL statement has been executed since it was loaded into memory. High execution counts with high elapsed times indicate frequently-run slow queries that should be optimized first.
MySQL:
COUNT_STAR in performance_schema events tables
Usage:
SELECT buffer_gets FROM v$sql
Why:
Number of logical I/O operations (memory reads) performed by this SQL. Includes both data found in memory and data that had to be read from disk into memory. High buffer_gets often correlates with poor performance - indicates inefficient data access.
MySQL:
No direct equivalent - MySQL doesn't expose logical read statistics
Usage:
SELECT disk_reads FROM v$sql WHERE disk_reads > 1000
Why:
Number of physical I/O operations (actual disk reads) performed by this SQL. Physical I/O is much slower than logical I/O. High disk_reads indicates queries that aren't benefiting from Oracle's buffer cache.
MySQL:
No direct equivalent - use iostat or similar OS tools
Usage:
SELECT wait_class FROM v$session_wait WHERE wait_class != 'Idle'
Why:
Oracle categorizes all wait events into classes like "User I/O" (waiting for disk), "Concurrency" (waiting for locks), "System I/O", etc. Helps quickly identify the type of bottleneck your database is experiencing.
MySQL:
performance_schema uses similar categorization in EVENT_NAME
Usage:
SELECT event FROM v$session_wait WHERE event = 'db file sequential read'
Why:
Specific wait event name that describes exactly what Oracle is waiting for. Examples: "db file sequential read" (index lookup), "db file scattered read" (full table scan), "enq: TX - row lock contention" (waiting for row lock). Essential for diagnosing performance issues.
MySQL:
EVENT_NAME in performance_schema (similar but different event names)
Usage:
SELECT object_name FROM dba_objects WHERE object_type = 'TABLE'
Why:
The name of any database object (table, index, view, procedure, etc.). Oracle tracks all objects in its data dictionary. Used to find specific objects, check their status, or see what objects exist in the database.
MySQL:
Spread across different information_schema tables (TABLES, VIEWS, ROUTINES, etc.)
Usage:
SELECT DISTINCT object_type FROM dba_objects ORDER BY object_type
Why:
Oracle supports many object types: TABLE, INDEX, VIEW, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, SEQUENCE, SYNONYM, etc. Packages group related procedures/functions together - a feature MySQL doesn't have.
MySQL:
Limited to TABLE, VIEW, PROCEDURE, FUNCTION, TRIGGER - no packages or sequences
Usage:
SELECT owner, object_name FROM dba_objects WHERE owner = 'LIMS_USER'
Why:
Oracle schema name that owns the object. Oracle uses schemas to organize objects - each user has a schema. Multiple users can have tables with the same name in different schemas (SCOTT.EMPLOYEES vs HR.EMPLOYEES).
MySQL:
TABLE_SCHEMA in information_schema - similar concept but simpler implementation
Usage:
SELECT object_name FROM dba_objects WHERE status = 'INVALID'
Why:
Objects can be VALID or INVALID. Objects become invalid when their dependencies change (like altering a table that a view depends on). Invalid objects cause runtime errors in applications until recompiled.
MySQL:
No equivalent - MySQL doesn't track object validity status
Usage:
SELECT object_name, last_ddl_time FROM dba_objects ORDER BY last_ddl_time DESC
Why:
Timestamp when the object was last modified with DDL (CREATE, ALTER, DROP). Useful for change tracking and finding recently modified objects that might be causing issues.
MySQL:
No equivalent - MySQL doesn't track DDL timestamps
Usage:
SELECT session_id, object_id FROM v$locked_object
Why:
Shows which database objects currently have locks on them and which sessions hold those locks. Essential for diagnosing blocking situations where users can't access data because another session is holding locks.
MySQL:
performance_schema.data_locks - similar information with different structure
Usage:
SELECT ses_addr, start_time, used_ublk FROM v$transaction
Why:
Shows all currently active transactions. Long-running transactions can cause performance issues by holding locks and consuming undo space. Used to identify problem transactions that should be committed or rolled back.
MySQL:
information_schema.innodb_trx - shows InnoDB transactions
Usage:
SELECT used_ublk * 8192 / 1024 / 1024 as undo_mb FROM v$transaction
Why:
Number of undo blocks consumed by the transaction. Undo blocks store the "before image" of data for rollback purposes. Large values indicate big transactions that are modifying lots of data.
MySQL:
No equivalent - InnoDB handles undo differently
Usage:
SELECT start_time FROM v$transaction ORDER BY start_time
Why:
When the transaction began. Long-running transactions (old start_time) can cause issues by preventing undo space from being reused and holding locks for extended periods.
MySQL:
trx_started in information_schema.innodb_trx
Usage:
SELECT metric_name, value FROM v$sysmetric WHERE metric_name LIKE '%CPU%'
Why:
Real-time database performance metrics collected by Oracle every 15 seconds and 60 seconds. Includes CPU usage, response times, throughput, etc. Essential for monitoring database health and performance trends.
MySQL:
SHOW GLOBAL STATUS - similar metrics but different collection method
Usage:
SELECT timestamp, message_text FROM v$diag_alert_ext WHERE message_level <= 16
Why:
Oracle's alert log entries accessible via SQL. Contains error messages, startup/shutdown info, parameter changes, etc. Critical for diagnosing database problems and monitoring database health.
MySQL:
Error log is a text file, not accessible via SQL
Usage:
SELECT session_addr, blocks * 8192 / 1024 / 1024 as temp_mb FROM v$tempseg_usage
Why:
Shows which sessions are using temporary space for sorting, hash joins, etc. Large temp usage can indicate inefficient queries that need tuning or insufficient memory allocation.
MySQL:
No equivalent - temp table usage not easily monitored
Usage:
SELECT event, total_waits, time_waited FROM v$system_event ORDER BY time_waited DESC
Why:
Cumulative wait statistics since database startup. Shows which events the database has spent the most time waiting for. Key for identifying systemic performance bottlenecks.
MySQL:
performance_schema.events_waits_summary_global_by_event_name - similar concept
Usage:
SELECT SYSDATE FROM dual
Why:
Special one-row, one-column table provided by Oracle. Oracle's SQL parser requires a FROM clause in all SELECT statements (strict SQL standard compliance), so DUAL serves as a dummy table when selecting constants, functions, or expressions without querying real data.
MySQL:
SELECT NOW() - MySQL allows SELECT without FROM clause for constants and functions
Usage:
SELECT * FROM employees WHERE ROWNUM <= 10
Why:
Pseudo-column that assigns sequential numbers (1, 2, 3...) to rows as they're returned by a query. Applied before ORDER BY, so can give unexpected results. Used for limiting result sets before Oracle 12c introduced FETCH FIRST syntax.
MySQL:
LIMIT 10 - much simpler and more intuitive syntax
Usage:
SELECT SYSDATE FROM dual
Why:
Oracle function that returns current date and time from the database server. Returns Oracle DATE datatype which includes both date and time components. Always uses database server's timezone.
MySQL:
NOW() - returns current timestamp
Usage:
SELECT my_sequence.NEXTVAL FROM dual
Why:
Oracle sequences are independent database objects that generate unique numbers. NEXTVAL gets the next value from the sequence. Sequences can be shared across multiple tables and are cached for performance.
MySQL:
AUTO_INCREMENT - per-table only, no standalone sequences
Usage:
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY
Why:
SQL standard syntax for limiting rows (Oracle 12c+). Applied after ORDER BY, unlike ROWNUM. Modern replacement for ROWNUM-based row limiting.
MySQL:
LIMIT 10 - MySQL's syntax is simpler and predates the SQL standard
Usage:
SELECT level, employee_id, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id START WITH manager_id IS NULL
Why:
Oracle's hierarchical query syntax for traversing tree structures (like org charts). Allows recursive queries to navigate parent-child relationships in a single SQL statement.
MySQL:
WITH RECURSIVE (MySQL 8.0+) - standard SQL recursive CTE syntax
Usage:
SELECT * FROM dba_tables WHERE owner = 'SCOTT'
Why:
Database Administrator views that show information about ALL objects in the database, regardless of ownership. Requires DBA privileges to access. Most comprehensive view of database metadata.
MySQL:
information_schema shows all accessible objects - no privilege-based view distinction
Usage:
SELECT table_name FROM user_tables
Why:
Shows only objects owned by the current user. Automatically filters to your schema - no need to specify WHERE owner = USER. Convenient for developers working in their own schema.
MySQL:
Use information_schema with WHERE clause filtering
Usage:
SELECT table_name FROM all_tables WHERE owner = 'HR'
Why:
Shows objects you have privileges to access, whether you own them or not. Broader than USER_ but narrower than DBA_. Most commonly used in applications.
MySQL:
information_schema automatically shows only accessible objects
Usage:
SELECT * FROM v$session WHERE username = 'SCOTT'
Why:
Dynamic performance views that show real-time database activity and statistics. Data comes from memory structures, not stored tables. Essential for monitoring and troubleshooting. V$ views are synonyms for underlying X$ tables.
MySQL:
performance_schema serves similar purpose but with different structure
Usage:
SELECT NVL(commission_pct, 0) FROM employees
Why:
"Null Value" function that replaces NULL with a specified value. Oracle treats NULL differently than empty strings, so NVL is essential for handling missing data in calculations and displays.
MySQL:
IFNULL(commission_pct, 0) or COALESCE(commission_pct, 0)
Usage:
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM employees
Why:
Oracle's original conditional logic function, predates CASE statements. Acts like a lookup table or switch statement. Still widely used in legacy code though CASE is now preferred.
MySQL:
CASE WHEN status = 'A' THEN 'Active' WHEN status = 'I' THEN 'Inactive' ELSE 'Unknown' END
Usage:
SELECT SUBSTR(last_name, 1, 3) FROM employees
Why:
Extract substring from a string. Oracle uses 1-based indexing (first character is position 1). Negative positions count from the end of the string.
MySQL:
SUBSTRING(last_name, 1, 3) - same functionality, different name
Usage:
SELECT TRUNC(SYSDATE) FROM dual
Why:
For dates, removes the time portion leaving just the date. For numbers, removes decimal places. Essential for date comparisons when you want to ignore time.
MySQL:
DATE(NOW()) for dates, TRUNCATE(number, 0) for numbers
Usage:
SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') FROM employees
Why:
Converts dates, numbers, or other datatypes to character strings with specified formatting. Oracle's universal formatting function with extensive format mask options.
MySQL:
DATE_FORMAT(hire_date, '%Y-%m-%d') for dates, FORMAT() for numbers
Usage:
SELECT TO_DATE('2024-01-01', 'YYYY-MM-DD') FROM dual
Why:
Converts character strings to DATE datatype using specified format mask. Essential for date manipulation since Oracle is strict about date formats.
MySQL:
STR_TO_DATE('2024-01-01', '%Y-%m-%d') - similar concept with different format specifiers