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;
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);
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 |
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);
Post a Comment