SQL query to obtain tablespace usage

Below are some queries to obtain the amount of space available as well as total size:

-- All size
select a.tablespace_name,
ROUND(a.bytes,2) "SIZE(MB)",
ROUND(a.maxbytes,2) "MAX SIZE(MB)",
ROUND(nvl(b.bytes,0),2) "FREE SPACE(MB)",
ROUND((a.bytes - nvl(b.bytes,0)),2) "USED(MB)",
ROUND((a.bytes - nvl(b.bytes,0))/a.bytes*100,2) "PCT USED"
-- ,ROUND((a.bytes - nvl(b.bytes,0))/a.maxbytes*100,2) "PCT MAX USED"
from (
select TABLESPACE_NAME,
sum(BYTES/(1024*1024)) bytes,
sum(decode(MAXBYTES,0,bytes,maxbytes)/(1024*1024)) maxbytes
from dba_data_files
group by TABLESPACE_NAME) a,
(
select TABLESPACE_NAME,
sum(BYTES/(1024*1024)) bytes
from dba_free_space
group by TABLESPACE_NAME) b
where a.tablespace_name=b.tablespace_name(+)
order by 1;

-- tablespace size
select tablespace_name, sum(bytes)/1024/1024 mbytes from user_segments
group by tablespace_name
order by 2 desc;

Determining SQL statements currently being executed

In addition to determine session information, the Oracle data dictionary tables can also be used to determine the current SQL statements being executed. James Koopmann has authored several articles on Database Journal regarding monitoring SQL statements. I strongly recommend reading his article. Below are some examples of extracting the SQL statements currently executed on an instance:

SELECT hash_value,
sql_text
FROM v$sqlarea
WHERE users_executing > 0;

SELECT sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
FROM v$sqlarea sqlarea,
v$session sesion
WHERE sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null;

SELECT u.sid,
substr(u.username,1,12) user_name,
s.sql_text
FROM v$sql s,
v$session u
WHERE s.hash_value = u.sql_hash_value
and sql_text not like '%from v$sql s, v$session u%'
ORDER BY u.sid;

Using Oracle’s explain plan

Use the EXPLAIN PLAN statement to determine the execution plan Oracle follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table. You can also issue the EXPLAIN PLAN statement as part of the SQL trace facility.

If you are using cost-based optimization, then this statement also determines the cost of executing the statement. If any domain indexes are defined on the table, then user-defined CPU and I/O costs will also be inserted.

The definition of a sample output table PLAN_TABLE is available in a SQL script on your distribution media. Your output table must have the same column names and datatypes as this table. The common name of this script is UTLXPLAN.SQL. The exact name and location depend on your operating system.

The DBMS_XPLAN package provides an easy way to format the output of the EXPLAIN PLAN command. Below is an sample SQL which will generate an explain plan and store in the plan table:

EXPLAIN PLAN FOR

SELECT *

FROM emp e, dept d

WHERE e.deptno = d.deptno

  AND e.ename='su';

Below is example of using the DBMS_XPLAN procedure and the output produced:

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

--------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |     1 |    50 |     3 |

|*  1 | HASH JOIN            |             |     1 |    50 |     3 |

|*  2 | TABLE ACCESS FULL    | EMP         |     1 |    32 |     1 |

|   3 | TABLE ACCESS FULL    | DEPT        |     4 |    72 |     1 |

--------------------------------------------------------------------

Predicate Information (identified by operation id)

1 - access("E1"."DEPTNO"="D1"."DEPTNO")

2 - filter("E1"."ENAME"='su')

Oracle SQL query to determine session information

The v$session is one of the most useful Oracle data dictionary tables. This table provides information such as

• The user associated with each session
• The status of a session (active or inactive)
• The program associated with the session
• The machine where the session originated from

This information can be valuable when determining which users or applications are utilizing Oracle. Below is an example of a query that will obtain the number of session associated with each user:

select substr(username,1,10) as username, substr(program,1,30) as program,
status, substr(machine,1,20) as machine, count(*)
from v$session
group by username,status,program, machine
order by status,program,username;

This query will return a result set like the following:

USERNAME	PROGRAM	STATUS	MACHINE	COUNT(*)
-------------	-----------------	--------	----------	--------
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
USER1	sqlplusw.exe	ACTIVE	doug-pc	1
USER1		ACTIVE	appsvr	7
USER1	sqlplus.exe	INACTIVE	doug-pc	3
USER2	sqlplus.exe	INACTIVE	doug-pc	1
USER3	sqlplus.exe	INACTIVE	tony-pc	2
USER4	sqlplus.exe	INACTIVE	jeff-pc	3
USER5	sqlplus.exe	INACTIVE	ann-pc	2
USER6	sqlplus.exe	INACTIVE	ann-pc	6
USER1		INACTIVE	appsvr	2

The sessions with the name "oracle" in the program name are created by Oracle and not from users. In this example there are 6 users with sessions. USER1 has a total of 13 sessions (4 from doug-pc and 9 from appsvr). Of the 13 sessions, 8 sessions are active. Note the empty value for the program name for some of the USER1 sessions. These appear to be related to sessions using JBDC.

Oracle's online documentation is an excellent source for learning more about v$session and other data dictionary tables.