October 2, 2018

Sreekanth B

IBM Frequently Asked Oracle SQL Queries In Written Test Interview Questions

CPU usage of the USER

Displays CPU usage for each User. Useful to understand database load by user.

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
    FROM v$session ss, v$sesstat se, v$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

Long Query progress in database

Show the progress of long running queries.

SELECT a.sid,
         a.serial#,
         b.username,
         opname OPERATION,
         target OBJECT,
         TRUNC (elapsed_seconds, 5) "ET (s)",
         TO_CHAR (start_time, 'HH24:MI:SS') start_time,
         ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"
    FROM v$session_longops a, v$session b
   WHERE     a.sid = b.sid
         AND b.username NOT IN ('SYS', 'SYSTEM')
         AND totalwork > 0
ORDER BY elapsed_seconds;
IBM Frequently Asked Oracle SQL Queries In Written Test Interview Questions
IBM Frequently Asked Oracle SQL Queries In Written Test Interview Questions

Get current session id, process id, client process id?

This is for those who wants to do some voodoo magic using process ids and session ids.

SELECT b.sid,
       b.serial#,
       a.spid processid,
       b.process clientpid
  FROM v$process a, v$session b
 WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');
V$SESSION.SID AND V$SESSION.SERIAL# is database process id
V$PROCESS.SPID is shadow process id on this database server
V$SESSION.PROCESS is client PROCESS ID, ON windows it IS : separated THE FIRST # IS THE PROCESS ID ON THE client AND 2nd one IS THE THREAD id.

Last SQL Fired from particular Schema or Table:

SELECT CREATED, TIMESTAMP, last_ddl_time
  FROM all_objects
 WHERE     OWNER = 'MYSCHEMA'
       AND OBJECT_TYPE = 'TABLE'
       AND OBJECT_NAME = 'EMPLOYEE_TABLE';

Find Top 10 SQL by reads per execution

SELECT *
  FROM (  SELECT ROWNUM,
                 SUBSTR (a.sql_text, 1, 200) sql_text,
                 TRUNC (
                    a.disk_reads / DECODE (a.executions, 0, 1, a.executions))
                    reads_per_execution,
                 a.buffer_gets,
                 a.disk_reads,
                 a.executions,
                 a.sorts,
                 a.address
            FROM v$sqlarea a
        ORDER BY 3 DESC)
 WHERE ROWNUM < 10;

Oracle SQL query over the view that shows actual Oracle connections.

SELECT osuser,
         username,
         machine,
         program
    FROM v$session
ORDER BY osuser;

Oracle SQL query that show the opened connections group by the program that opens the connection.

SELECT program application, COUNT (program) Numero_Sesiones
    FROM v$session
GROUP BY program
ORDER BY Numero_Sesiones DESC;

Oracle SQL query that shows Oracle users connected and the sessions number for user

SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones
    FROM v$session
GROUP BY username
ORDER BY Numero_Sesiones DESC;

Get number of objects per owner

SELECT owner, COUNT (owner) number_of_objects
    FROM dba_objects
GROUP BY owner
ORDER BY number_of_objects DESC;

Convert number to words

More info: Converting number into words in Oracle

SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL;
Output:

one thousand five hundred twenty-six

Find string in package source code

Below query will search for string ‘FOO_SOMETHING’ in all package source. This query comes handy when you want to find a particular procedure or function call from all the source code.

--search a string foo_something in package source code
SELECT *
  FROM dba_source
 WHERE UPPER (text) LIKE '%FOO_SOMETHING%'
AND owner = 'USER_NAME';

Convert Comma Separated Values into Table

The query can come quite handy when you have comma separated data string that you need to convert into table so that you can use other SQL queries like IN or NOT IN. Here we are converting ‘AA,BB,CC,DD,EE,FF’ string to table containing AA, BB, CC etc. as each row. Once you have this table you can join it with other table to quickly do some useful stuffs.

WITH csv
     AS (SELECT 'AA,BB,CC,DD,EE,FF'
                   AS csvdata
           FROM DUAL)
    SELECT REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) pivot_char
      FROM DUAL, csv
CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+', 1, LEVEL) IS NOT NULL;

Find the last record from a table

This ones straight forward. Use this when your table does not have primary key or you cannot be sure if record having max primary key is the latest one.

SELECT *
  FROM employees
 WHERE ROWID IN (SELECT MAX (ROWID) FROM employees);

(OR)

SELECT * FROM employees
MINUS
SELECT *
  FROM employees
 WHERE ROWNUM < (SELECT COUNT (*) FROM employees);

Row Data Multiplication in Oracle

This query use some tricky math functions to multiply values from each row. Read below article for more details.
More info: Row Data Multiplication In Oracle

WITH tbl
     AS (SELECT -2 num FROM DUAL
         UNION
         SELECT -3 num FROM DUAL
         UNION
         SELECT -4 num FROM DUAL),
     sign_val
     AS (SELECT CASE MOD (COUNT (*), 2) WHEN 0 THEN 1 ELSE -1 END val
           FROM tbl
          WHERE num < 0)
  SELECT EXP (SUM (LN (ABS (num)))) * val
    FROM tbl, sign_val
GROUP BY val;

Generating Random Data In Oracle

You might want to generate some random data to quickly insert in table for testing. Below query help you do that. Read this article for more details.
More info: Random Data in Oracle

SELECT LEVEL empl_id,
           MOD (ROWNUM, 50000) dept_id,
           TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary,
           DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)),  1, 'M',  2, 'F') gender,
           TO_DATE (
                 ROUND (DBMS_RANDOM.VALUE (1, 28))
              || '-'
              || ROUND (DBMS_RANDOM.VALUE (1, 12))
              || '-'
              || ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
              'DD-MM-YYYY')
              dob,
           DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address
      FROM DUAL
CONNECT BY LEVEL < 10000;

Random number generator in Oracle

Plain old random number generator in Oracle. This ones generate a random number between 0 and 100. Change the multiplier to number that you want to set limit for.

--generate random number between 0 and 100
SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL;

Check if table contains any data

This one can be written in multiple ways. You can create count(*) on a table to know number of rows. But this query is more efficient given the fact that we are only interested in knowing if table has any data.

SELECT 1
  FROM TABLE_NAME
 WHERE ROWNUM = 1;

Oracle Select Query

Oracle select query is used to fetch records from database. For example:

SELECT * from customers;

Oracle Insert Query

Oracle insert query is used to insert records into table. For example:

insert into customers values(101,'rahul','delhi');

Oracle Update Query

Oracle update query is used to update records of a table. For example:

update customers set name='bob', city='london' where id=101;

Oracle Delete Query

Oracle update query is used to delete records of a table from database. For example:

delete from customers where id=101;

Get position of 'o' in name 'John' from employee table

Select instr(FIRST_NAME,'o') from employee where first_name = 'John'

Oracle Truncate Query

Oracle update query is used to truncate or remove records of a table. It doesn't remove structure. For example:

truncate table customers;

Get First_Name from employee table after replacing 'o' with '$'

select REPLACE(FIRST_NAME,'o','$') from employee

Oracle Drop Query

Oracle drop query is used to drop a table or view. It doesn't have structure and data. For example:

drop table customers;

Get Joining Date,Time including milliseconds from employee table

select to_char(JOINING_DATE,'dd/mm/yyyy HH:mi:ss.ff') from
EMPLOYEE . Column Data Type should be “TimeStamp”

Oracle Create Query

Oracle create query is used to create a table, view, sequence, procedure and function. For example:

CREATE TABLE customers
( id number(10) NOT NULL,
  name varchar2(50) NOT NULL,
  city varchar2(50),
CONSTRAINT customers_pk PRIMARY KEY (id) 
);

Oracle Alter Query

Oracle alter query is used to add, modify, delete or drop colums of a table. Let's see a query to add column in customers table:

ALTER TABLE customers
ADD age varchar2(50);

Subscribe to get more Posts :