{"id":13,"date":"2012-03-22T12:57:22","date_gmt":"2012-03-22T11:57:22","guid":{"rendered":"http:\/\/clemente.pamplona.name\/dba\/?p=13"},"modified":"2012-09-19T17:43:36","modified_gmt":"2012-09-19T15:43:36","slug":"consultas-basicas-sobre-tablespaces","status":"publish","type":"post","link":"http:\/\/clemente.pamplona.name\/dba\/consultas-basicas-sobre-tablespaces\/","title":{"rendered":"Sqlplus para dummies I ( Consultas sobre tablespaces )"},"content":{"rendered":"

Sin duda alguna el pegarse con los tablespaces es la tarea m\u00e1s b\u00e1sica y la mas habitual de un DBA.<\/p>\n

Para los que siguen teniendo los tablespaces en modo \u00abmanual\u00bb aqui hay varias consultas \u00fatiles
\nTama\u00f1o y porcentaje de ocupaci\u00f3n<\/strong><\/p>\n

\r\nselect\r\n t.tablespace_name,\r\n round(t.kbtotal\/1024,2) \"Total MB\",\r\n round(l.kblibre\/1024,2) \"MB Libres\",\r\n 100-(round(l.kblibre\/t.kbtotal,2)*100)||'%' Ocupado\r\n\u00a0 from\r\n\u00a0 (select tablespace_name, sum(bytes)\/1024 kbtotal\r\n\u00a0 from dba_data_files group by tablespace_name) t ,\r\n\u00a0 (select tablespace_name, sum(bytes)\/1024 kblibre\r\n\u00a0 from dba_free_space group by tablespace_name) l\r\n\u00a0 where\u00a0 t.tablespace_name=l.tablespace_name\r\n\u00a0order by \"OCUPADO\" desc;<\/pre>\n
Uso de tablespaces temporal<\/strong><\/p>\n
select\r\n t2.\"TempTotal\" \"TempTotal (Mb)\",\r\n t1.\"TempUsed\" \"TempUsed (Mb)\",\r\n t2.\"TempTotal\" - t1.\"TempUsed\" \"TempFree (Mb)\"\r\n from (select nvl(\r\n         round(sum(tu.blocks * tf.block_size)\r\n                  \/ 1024 \/ 1024, 2), 0) \"TempUsed\"\r\n \u00a0\u00a0\u00a0\u00a0 from v$tempseg_usage tu, dba_tablespaces tf\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where tu.TABLESPACE = tf.tablespace_name) t1,\r\n \u00a0\u00a0\u00a0\u00a0\u00a0    (select round(sum(bytes)\r\n                     \/ 1024 \/ 1024, 2) \"TempTotal\"\r\n \u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 from dba_temp_files) t2\r\n\u00a0<\/pre>\n
Tablespaces a mas del 80%\u00a0 o con menos de 2 Gb libres no extensibles ni UNDOS<\/strong><\/p>\n
select\u00a0 *\u00a0 \r\n  from\r\n (select 100-(round(l.kblibre\/t.kbtotal,2)*100) Ocupado ,\r\n         to_char(round(l.kblibre\/1024,0)) Mb ,\r\n           l.tablespace_name\r\n      from \r\n   (select tablespace_name, sum(bytes)\/1024 kbtotal \r\n             from dba_data_files\r\n               group by tablespace_name) t ,\r\n  \u00a0(select tablespace_name, sum(bytes)\/1024 kblibre\r\n          from dba_free_space \r\n          group by tablespace_name) l where \r\n  \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (t.tablespace_name=l.tablespace_name\r\n   \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and l.tablespace_name not in \r\n               (select distinct(tablespace_NAME)\r\n                   from dba_data_files\r\n                     where autoextensible='YES')\r\n\u00a0\u00a0 and l.tablespace_name not like 'UNDOT%'\r\n\u00a0\u00a0 and to_char(round(l.kblibre\/1024,0))< 2000 )\r\n\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0 where\u00a0 ocupado > 80\r\n\u00a0\u00a0\u00a0 order by ocupado desc\r\n<\/pre>\n
Datafiles de los que consta cada Tablespace\u00a0 (he incluido los gestionados localmente)<\/strong><\/p>\n
select \r\ntablespace_name,\r\nfile_name,\r\nround(bytes\/1024\/1024,2) \"Total MB\",\r\n status\r\n  from dba_data_files\r\nunion\r\n      select \r\n           p.name tablespace,\r\n           t.name fichero,\r\n           t.bytes\/1024\/1024 Mb,\r\n           t.status\r\n          from v$tempfile t, v$tablespace p \r\n                where t.ts#=p.ts#\r\norder by tablespace_name;<\/pre>\n
Datafiles autoextensibles<\/strong><\/p>\n
\r\nselect\r\n   file_name,\r\n   tablespace_name,\r\n   bytes\/1024\/1024\u00a0 Mb,\r\n   FILE_ID\u00a0 \r\nfrom dba_data_files \r\nwhere AUTOEXTENSIBLE='YES'<\/pre>\n
Datafiles temporales  autoextensibles<\/strong><\/p>\n
select \r\n   file_name,\r\n   tablespace_name,\r\n   bytes\/1024\/1024\u00a0 Mb\u00a0 \r\nfrom dba_temp_files \r\n  where AUTOEXTENSIBLE='YES'<\/pre>\n","protected":false},"excerpt":{"rendered":"
Sin duda alguna el pegarse con los tablespaces es la tarea m\u00e1s b\u00e1sica y la mas habitual de un DBA. Para los que siguen teniendo los tablespaces en modo \u00abmanual\u00bb aqui hay varias consultas \u00fatiles Tama\u00f1o y porcentaje de ocupaci\u00f3n … Sigue leyendo →<\/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,4],"tags":[247],"class_list":["post-13","post","type-post","status-publish","format-standard","hentry","category-dummie","category-sqlplus","category-tablespaces","tag-sqlplus"],"_links":{"self":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/13","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=13"}],"version-history":[{"count":12,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/13\/revisions"}],"predecessor-version":[{"id":16,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/13\/revisions\/16"}],"wp:attachment":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/media?parent=13"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/categories?post=13"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/tags?post=13"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}