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;
