{"id":622,"date":"2013-07-16T15:03:03","date_gmt":"2013-07-16T13:03:03","guid":{"rendered":"http:\/\/clemente.pamplona.name\/dba\/?p=622"},"modified":"2013-07-24T15:24:39","modified_gmt":"2013-07-24T13:24:39","slug":"consultas-para-tablespaces-temporales","status":"publish","type":"post","link":"http:\/\/clemente.pamplona.name\/dba\/consultas-para-tablespaces-temporales\/","title":{"rendered":"Consultas para tablespaces temporales"},"content":{"rendered":"<p>Hoy vamos a volver con las entradas para dummies. Vamos algunas consultas pr\u00e1cticas sobre tablespaces temporales.<\/p>\n<p>Lo primero vamos a ir a las consultas mas b\u00e1sicas, ver los tablespaces temporales, los tempfiles, crear un o o modificar su tama\u00f1o<\/p>\n<pre>\r\n-- Ficheros temporales\r\nselect * from dba_temp_files;\r\n\r\n\r\n-- Creaci\u00f3n de Tablespaces temporales gestionados localmente.\r\ncreate temporary tablespace tempaux \r\ntempfile '\/oradata\/orcl\/temp_aux01.dbf' SIZE 300M REUSE\r\nEXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;\r\n\r\n--A\u00f1adimos un fichero a un tablespace\r\nalter tablespace TEMPAUX \r\n     add  tempfile  '\/oradata\/orcl\/temp:aux02.dbf' size 200M ;\r\n\r\n--Cambio de tama\u00f1o\r\nalter database tempfile\r\n '\/oradata\/orcl\/temp_aux02.dbf' resize 5000M;\r\n\r\n<\/pre>\n<p>Y luego veamos algunas consultas algo mas complejas que nos pueden servir para comprobar el uso de los temporales <\/p>\n<pre>\r\n--Uso de tablespaces temporal\r\nselect t2.\"TempTotal\" \"TempTotal (Mb)\",\r\n       t1.\"TempUsed\" \"TempUsed (Mb)\",\r\n       t2.\"TempTotal\" - t1.\"TempUsed\" \"TempFree (Mb)\"\r\n  from (select nvl(round(sum(tu.blocks * tf.block_size) \/ 1024 \/ 1024, 2), 0) \"TempUsed\"\r\n          from v$tempseg_usage tu, dba_tablespaces tf\r\n         where tu.TABLESPACE = tf.tablespace_name) t1,\r\n       (select round(sum(bytes) \/ 1024 \/ 1024, 2) \"TempTotal\"\r\n          from dba_temp_files) t2;  \r\n\r\n--Uso de temporal por sesion \r\nSELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,\r\nP.program, SUM (T.blocks) * TBS.block_size \/ 1024 \/ 1024 mb_used, T.tablespace,\r\nCOUNT(*) statements\r\nFROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P\r\nWHERE T.session_addr = S.saddr\r\nAND S.paddr = P.addr \r\nAND T.tablespace = TBS.tablespace_name\r\nGROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,\r\nP.program, TBS.block_size, T.tablespace\r\nORDER BY sid_serial;\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Hoy vamos a volver con las entradas para dummies. Vamos algunas consultas pr\u00e1cticas sobre tablespaces temporales. Lo primero vamos a ir a las consultas mas b\u00e1sicas, ver los tablespaces temporales, los tempfiles, crear un o o modificar su tama\u00f1o &#8212; &hellip; <a href=\"http:\/\/clemente.pamplona.name\/dba\/consultas-para-tablespaces-temporales\/\">Sigue leyendo <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,7],"tags":[248,247,22,109],"class_list":["post-622","post","type-post","status-publish","format-standard","hentry","category-dummie","category-sqlplus","tag-dummie","tag-sqlplus","tag-tablespace","tag-temporary"],"_links":{"self":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/622","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/comments?post=622"}],"version-history":[{"count":4,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/622\/revisions"}],"predecessor-version":[{"id":679,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/622\/revisions\/679"}],"wp:attachment":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/media?parent=622"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/categories?post=622"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/tags?post=622"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}