{"id":1901,"date":"2018-03-11T21:44:26","date_gmt":"2018-03-11T20:44:26","guid":{"rendered":"http:\/\/clemente.pamplona.name\/dba\/?p=1901"},"modified":"2018-03-09T08:40:56","modified_gmt":"2018-03-09T07:40:56","slug":"consultas-basicas-para-lob-segments","status":"publish","type":"post","link":"http:\/\/clemente.pamplona.name\/dba\/consultas-basicas-para-lob-segments\/","title":{"rendered":"Consultas basicas para lob segments"},"content":{"rendered":"

Hoy vamos a otra de estas entradas para dummies que recopilan SQL utles, en este caso para tratar con los lobs.
\nLas variables de formateo del sqlplus para estas consultas serian<\/p>\n

\r\nset linesize 180 pagesize 900\r\ncolumn SEGMENT_NAME format a40;\r\ncolumn TABLE_NAME format a60;\r\ncolumn TABLESPACE_NAME format a30;\r\ncolumn owner format a20;\r\n<\/pre>\n
 Lista de los lobs mas grandes y lo que ocupan para el esquema <\/h2>\n
\r\nselect  e.owner,l.tablespace_name,\r\nl.table_name,\r\nl.segment_name,sum(e.bytes\/(1024*1024*1024)) Gb\r\nfrom dba_extents e,dba_lobs  l\r\nwhere\r\n\te.owner = l.owner\r\n\tand \te.segment_name = l.segment_name\r\n\tand \te.OWNER='ESQUEMA'\r\n\tand \te.segment_type = 'LOBSEGMENT'\r\n     group by  \r\n     e.owner,l.tablespace_name,\r\n    l.table_name,\r\n     l.segment_name \r\n     order by Gb desc ;\r\n<\/pre>\n
 Obtener los datos  (esquema,tabla y columna) de un LOB determinado  <\/h2>\n
\r\nselect OWNER,TABLE_NAME,COLUMN_NAME from dba_lobs\r\n where SEGMENT_NAME='SYS_LOBXXXXXX$$';\r\n<\/pre>\n
 Bytes ocupados por un LOB <\/h2>\n
\r\nselect sum(dbms_lob.getlength (COLUMNA))\/1024\/1024 Mb ESQUEMA.TABLA;\r\n
\r\no bien \r\n<\/pre>\n
select bytes\/1024\/1024 Mb
\nfrom dba_segments where segment_name ='SYS_LOBXXXXXX$$' ;<\/p>\n
o esta mas completa <\/p>\n
\r\nset serveroutput on\r\ndeclare\r\n     l_segment_size_blocks NUMBER;\r\n     l_segment_size_bytes NUMBER;\r\n     l_used_blocks NUMBER;\r\n     l_used_bytes NUMBER;\r\n     l_expired_blocks NUMBER;\r\n     l_expired_bytes NUMBER;\r\n     l_unexpired_blocks NUMBER;\r\n     l_unexpired_bytes NUMBER;\r\n     l_unused_blocks NUMBER;\r\n     l_unused_bytes NUMBER;\r\n     l_non_data_blocks NUMBER;\r\n     l_non_data_bytes NUMBER;\r\n BEGIN\r\n\tDBMS_SPACE.SPACE_USAGE(\r\n   \t  segment_owner =>'ESQUEMA',\r\n\t  segment_name => 'SYS_LOB0000227238C00034$$',\r\n\t  segment_type => 'LOB',\r\n\t  segment_size_blocks => l_segment_size_blocks,\r\n\t  segment_size_bytes => l_segment_size_bytes,\r\n\t  used_blocks => l_used_blocks,\r\n\t  used_bytes => l_used_bytes,\r\n\t  expired_blocks => l_expired_blocks,\r\n\t  expired_bytes => l_expired_bytes,\r\n\t  unexpired_blocks => l_unexpired_blocks,\r\n\t  unexpired_bytes => l_unexpired_bytes\r\n           );\r\n      l_unused_blocks := l_segment_size_blocks - (l_used_blocks + l_expired_blocks + l_unexpired_blocks);\r\n      l_unused_bytes := l_segment_size_bytes - (l_used_bytes + l_expired_bytes + l_unexpired_bytes);\r\n\t  l_non_data_blocks := l_unused_blocks + l_expired_blocks + l_unexpired_blocks;\r\n\t  l_non_data_bytes :=  l_unused_bytes + l_expired_bytes + l_unexpired_bytes;\r\n\t  DBMS_OUTPUT.ENABLE;\r\n\t   DBMS_OUTPUT.PUT_LINE(' Segment Blocks\/Bytes   = '||l_segment_size_blocks||' \/ '||l_segment_size_bytes);\r\n\t  DBMS_OUTPUT.PUT_LINE(' Unused Blocks\/Bytes    = '||l_unused_blocks||' \/ '||l_unused_bytes);\r\n\t  DBMS_OUTPUT.PUT_LINE(' Used Blocks\/Bytes      = '||l_used_blocks||' \/ '||l_used_bytes);\r\n\t  DBMS_OUTPUT.PUT_LINE(' Expired Blocks\/Bytes   = '||l_expired_blocks||' \/ '||l_expired_bytes);\r\n\t  DBMS_OUTPUT.PUT_LINE(' Unexpired Blocks\/Bytes = '||l_unexpired_blocks||' \/ '||l_unexpired_bytes);\r\n\t  DBMS_OUTPUT.PUT_LINE('===========================================================================');\r\n\t  DBMS_OUTPUT.PUT_LINE(' NON Data Blocks\/Bytes  = '||l_non_data_blocks||' \/ '||l_non_data_bytes);\r\n END;\r\n \/\r\n<\/pre>\n
 Mover una tabla con  Lobs  ( para hacer shrink) <\/h2>\n
Este metodo implica bloqueo durante el traslado <\/p>\n
\r\nALTER TABLE ESQUEMA.TABLA MOVETABLESPACE NUEVOTABLESPACE;\r\n
\r\nY para cada uno de los lobs de la tabla \r\n<\/pre>\n
ALTER TABLE ESQUEMA.TABLA MOVE LOB(COLUMNA) STORE AS SECUREFILE (TABLESPACE NUEVOTABLESPACE);<\/p>\n","protected":false},"excerpt":{"rendered":"
Hoy vamos a otra de estas entradas para dummies que recopilan SQL utles, en este caso para tratar con los lobs. Las variables de formateo del sqlplus para estas consultas serian set linesize 180 pagesize 900 column SEGMENT_NAME format a40; … Sigue leyendo →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11,60,9,7],"tags":[897,895,893],"class_list":["post-1901","post","type-post","status-publish","format-standard","hentry","category-11g","category-12c","category-dummie","category-sqlplus","tag-10-83-60-41","tag-dba_lob","tag-lob"],"_links":{"self":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/1901","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=1901"}],"version-history":[{"count":11,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/1901\/revisions"}],"predecessor-version":[{"id":1912,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/1901\/revisions\/1912"}],"wp:attachment":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/media?parent=1901"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/categories?post=1901"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/tags?post=1901"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}