When Developer switch gears from MySQL to Oracle

I was using MySQL far more than any other DBMSs. But I had to move to Oracle from last week. 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. So I had to spend some time to get the some of the equivalent queries for those easy-to-use MySQL commands.

I have given some MySQL specific commands/Syntaxes & equivalent Oracle techniques below. Here I have assumed that, we have a schema called "menu_db" and current user has access to that schema. In addition to that, there is a table called pizza_tb with the column names pizza_id & pizza_name.


1) To get the list of databases
MySQL : show databases
Oracle : SELECT username FROM all_users ORDER BY username;

2) To get the current schema
MySQL : select DATABASE();
Oracle : SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

3) To get the list of tables within the current database
MySQL : use menu_db;
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.

4) 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';

5) To limit the selection
MySQL : select * from user limit 10;

Oracle : select * from pizza_tb where ROWNUM <= 10;

6) To select rows which is somewhere middle

MySQL : select username from user limit 10, 15;

Oracle : select pizza_name from (select pizza_name, ROWNUM as row_number from pizza_tb) as t1 where t1.row_number > 10 and t1.row_number <= 15;


Note : Here i had to use SubQuery rather than call it directly as "select pizza_name from pizza_tb) 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.

7) Describe table has a same syntax in both MySQL & Oracle. So same syntax will apply in both.
desc pizza_tb;

8) To view errors/warnings

MySQL : show warings / show errors

Oracle : select * from user_errors;/ show errors

9) MySQL has auto_increment Columns

MySQL :
create table pizza_tb (pizza_id int AUTO_INCREMENT primary, pizza_name varchar(20));

Oracle :
i) Create table without the auto_increment keywords (because it does not exist in Oracle)
create table pizza_tb (pizza_id int primary, pizza_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 pizza_tb
FOR EACH ROW
BEGIN
SELECT auto_incrementer.NEXTVAL into :new.pizza_id FROM dual;
END;


10) To get the table create script back

MySQL : show create table pizza_tb;

Oracle :
need some effort to get it for the first time :) 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', 'PIZZA_TB', 'menu_db') from dual;

11) 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;

12) Explain the execution plan of a sql statement

MySQL : explain select * from pizza_tb;

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 pizza_tb;

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;