Oracle Logbook

November 16, 2009

Setting two defaults tablespaces to an schema

Filed under: Uncategorized — heliodias @ 5:49 pm

Today i saw this question on a forum and i think that it´s quite simple.

All you have to do is creating an event trigger that before a create DDL, it´ll add the tablespace information.

later when i have time, i´ll post the solution here

Advertisements

November 6, 2009

ORA-1652: unable to extend temp segment by 128 in tablespace

Filed under: Uncategorized — heliodias @ 8:55 pm

Sometimes you have a huge space on temporary tablespace and even so some bad query use it all.
First step is investigate the normal usage of sort area, and if you see an abnormal behavior, then it´s time to find out , what´s this query.

SQL> SELECT MAX(SORTS_TOTAL/EXECUTIONS_TOTAL) FROM DBA_HIST_SQLSTAT WHERE EXECUTIONS_TOTAL>0; 

MAX(SORTS_TOTAL/EXECUTIONS_TOTAL)
———————————
                           102173

The above query will show the top temporary resource consumer. Then all you have to do is

SQL> SELECT SQL_ID FROM DBA_HIST_SQLSTAT WHERE EXECUTIONS_TOTAL>0 AND SORTS_TOTAL/EXECUTIONS_TOTAL>=102173;

SQL_ID
————-
4w84ym20xy7da

And finally

SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID=’4w84ym20xy7da’;

Blog at WordPress.com.