1  GRANT DELETE,INSERT,SELECT,UPDATE ON charlie_production.* TO 'munger'@'localhost' IDENTIFIED BY 'xxx';

MySQL doesn’t have rownum, but user variables can be used as a replacement:

   1  select @rownum:=@rownum+1 'rank', p.* from products p, (SELECT @rownum:=0) r order by created_at desc limit 10;

Read more: MySQL 5.0 Reference Manual

Oracle variables are a great way of removing duplicate SQL statements from your Oracle SQL scripts. By using a variable, you can store the return value of a query and use it later in another, as this example illustrates:

   1  COLUMN your_column new_value your_variable;
   2  
   3  SELECT DISTINCT (your_column)
   4                                   FROM ...
   5                                  WHERE ...;
   6  
   7  SELECT &your_variable FROM dual;

The above example stores the value of the SELECT DISTINCT query in a variable named your_variable. You declare the variable with new_value, and set the value by executing an SQL statement that returns one row and column.

A PL /SQL example of a for loop that prints out the primary key for all rows returned by a query:

   1  DECLARE
   2    ID VARCHAR2(50);
   3  BEGIN
   4      DBMS_OUTPUT.ENABLE (1000000);
   5      FOR current_row IN (SELECT id FROM your_table)
   6  	LOOP
   7  		ID := current_row.id;    -- Assign value to variable
   8  		DBMS_OUTPUT.PUT_LINE( '' || ID);
   9  		DBMS_OUTPUT.PUT_LINE( '' || current_row.column_xxx);
  10  	END LOOP;
  11  END;
  12  /

There’s no equivalent to the MySQL slow query log in Oracle but these queries and the statspack reports are helpful when trying to find slow queries:

   1  -- Order queries by how many cpu seconds they consume
   2  SELECT   hash_value, executions, ROUND (elapsed_time / 1000000, 2) total_time,
   3           ROUND (cpu_time / 1000000, 2) cpu_seconds
   4      FROM (SELECT   *
   5                FROM v$sql
   6            ORDER BY elapsed_time DESC)
   7  ORDER BY cpu_seconds DESC;
   8  
   9  -- Find the total cpu seconds consumed
  10  SELECT SUM (cpu_seconds)
  11    FROM (SELECT   hash_value, executions,
  12                   ROUND (elapsed_time / 1000000, 2) total_time,
  13                   ROUND (cpu_time / 1000000, 2) cpu_seconds
  14              FROM (SELECT   *
  15                        FROM v$sql
  16                    ORDER BY elapsed_time DESC)
  17          ORDER BY cpu_seconds DESC);
  18  
  19  
  20  -- Find the total time queries have taken
  21  SELECT SUM (total_time)
  22    FROM (SELECT   hash_value, executions,
  23                   ROUND (elapsed_time / 1000000, 2) total_time,
  24                   ROUND (cpu_time / 1000000, 2) cpu_seconds
  25              FROM (SELECT   *
  26                        FROM v$sql
  27                    ORDER BY elapsed_time DESC)
  28          ORDER BY cpu_seconds DESC);
  29  
  30  -- Find execution plan for the damned query you should fix
  31  SELECT *
  32    FROM v$sql_plan
  33   WHERE hash_value = 2967942512;