MySQL and Oracle command equivalents

MySQL has specific commands, which provides the easy access to the information_schema database, to get the schema level details. But oracle does not provide such easy access to some of the schema level meta data.

Here are  some MySQL specific commands/Syntaxes & equivalent Oracle techniques:

To get the list of databases

MySQL :

show databases

Oracle :

SELECT username FROM all_users ORDER BY username;

To get the current schema

MySQL :

select DATABASE();

Oracle :

SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

To get the list of tables within the current database

MySQL :

use database_name;
show tables;

Oracle :

select * from user_tables;

Here schema is based on the connected username, so it is selected during the creation of the connection.
USER_TABLES will have a row for every table in your schema. If you are looking for the tables in your schema, this would be the correct query. If you are looking for the tables in some other schema, this is not the right table to use.

ALL_TABLES will have a row for every table you have access to regardless of schema. You would, presumably, want to qualify the query by specifying the name of the schema you are interested in, i.e.

SELECT table_name
  FROM all_tables
 WHERE owner = <<name of schema>>

Of course, that assumes that you have at least SELECT access on every table in that schema. If that is not the case, then you would need to use DBA_TABLES (which would require that the DBA grant you access to that table), i.e.

SELECT table_name
  FROM dba_tables
 WHERE owner = <<name of schema>>

To get the connected connection info

MySQL :

show processlist

Oracle :

SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER';

To limit the selection

MySQL :

select * from user limit 10;

Oracle :

select * from table_name where ROWNUM <= 10;

To select rows which is somewhere middle

MySQL :

select username from user limit 10, 15;

Oracle :

select element_name from (select element_name, ROWNUM as row_number from table_name) as t1 where t1.row_number > 10 and t1.row_number <= 15; 

Note: Here we have to use SubQuery rather than call it directly as "select element_name from table_name as t1 where ROWNUM > 10 and ROWNUM <= 15;". This cannot be done as these ROWNUMs are assigned once they are satisfied the given conditions, which follows the WHERE. Since condition "ROWNUM > 10 and ROWNUM <= 15" will never be satisfied from the start ROWNUMs will never be incremented. So we need to use the Subqueries to let the ROWNUMs assigned within the Subquery and later filter the required results from the outside query.

Describe table has a same syntax in both MySQL & Oracle.

desc table_name;

To view errors/warnings

MySQL :

show warings / show errors

Oracle :

select * from user_errors;/ show errors

MySQL has auto_increment Columns

MySQL :

create table table_name (element_id int AUTO_INCREMENT primary, element_name varchar(20));

Oracle :
i) Create table without the auto_increment keywords (because it does not exist in Oracle)

create table table_name (element_id int primary, element_name varchar(20));

ii) Create a sequence, which provides the incremented values

create sequence auto_incrementor;

iii) Create a trigger, which gets the next value from the sequence and updates it to the column to be auto_incremented

CREATE TRIGGER trig_incrementor BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
SELECT auto_incrementer.NEXTVAL into :new.element_id FROM dual;
END;

To get the table create script back

MySQL :

show create table table_name;

Oracle :
Make sure that the select_catalog_role is already available for the given user if not assign the role, as shown below.
grant select_catalog_role to [username];Increase the page size and maximum width for displaying the results so that complete table definition can be displayed in the sqlplus console.

set pagesize 999
set long 9000
select dbms_metadata.get_ddl('TABLE', 'TABLE_NAME', 'database_name') from dual;

To get the session variables

MySQL :

show variables; or show variables like 'inno%';

Oracle :

SELECT name, value FROM gv$parameter; or SELECT sys_context('USERENV', ) FROM dual;

Explain the execution plan of a sql statement

MySQL :

explain select * from table_name;

Oracle :

i) First execute the explain plan so that it will fill the plan_table (this table need to be created according to the standard plan_table format, if it does not exist already)

explain plan select * from table_name;

ii) Now the results of the explain plan will be populated in the plan_table, so we need to use a row connecting query to get a readable summary of the results.

select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", object_name "Object" from plan_table start with id = 0 connect by prior id=parent_id;