When the Temporary Tablespace is used? Most documents say that it is used when "SORTS" can't be done fully in memory...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
(order by, group by, union). Attending this, we tried to see what was happening with this query:
SELECT s.username, u."USER", u.tablespace,u.contents,u.extents, u.blocks,a.sql_text,u.sqladdr,u.sqlhash FROM v$session s,v$sort_usage u,v$sql a WHERE s.SADDR = u.SESSION_ADDR and a.hash_value = u.sqlhash and u.contents = 'TEMPORARY'
- Is the query used to determine what sql_texts use Temp TS wrong? Have you a better one?
- Why, when I run the Sql_Text in TOAD: "SELECT NOMBRE,DIA_PROCESO FROM ARINCD..." and then look V$SORT_USAGE, is it empty? No sort activity. Is there a difference between running a statement in SQL*Plus(TOAD) and a form ? Maybe this answer is related to question #1.
- Is the Temporary TS used for segments other than "SORTS" only? If so, do you have a query or script to see this?
The v$sort_usage query you run is fine. Just remember that v$sort_usage shows currently active sorts for the instance. Therefore, you will only see information returned via this query if a sort is currently active (i.e. you do not see the history of all sorts that have occurred).
The following query can identify some statistics regarding the users performing sorts. This helps identify the particular sessions that may be issuing resource-intensive sorts. You can also determine whether an in-memory or disk sort is being performed. The following query lists the users performing sorts a well as the type of sort:
SELECT substr(vs.username,1,20) "db user", substr(vs.osuser,1,20) "os user", substr(vsn.name,1,20) "Type of Sort", vss.value FROM v$session vs, v$sesstat vss, v$statname vsn WHERE (vss.statistic#=vsn.statistic#) AND (vs.sid = vss.sid) AND (vsn.name like '%sort%') ORDER BY 2,3;
If you want to see which objects (tables, indexes, etc) are stored in which tablespaces, you'll need to query dba_segments. It will show you a list of objects by name and type and by tablespace.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database backup and recovery
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.