{"id":75,"date":"2015-03-30T10:48:00","date_gmt":"2015-03-30T13:48:00","guid":{"rendered":"http:\/\/maxoracle.com\/2015\/03\/30\/tamanho-de-uma-tabela\/"},"modified":"2015-03-30T10:48:00","modified_gmt":"2015-03-30T13:48:00","slug":"tamanho-de-uma-tabela","status":"publish","type":"post","link":"https:\/\/www.soudba.com.br\/?p=75","title":{"rendered":"Tamanho de uma tabela"},"content":{"rendered":"

Ola pessoal,<\/p>\n

venho a mostrar a voces como temos o tamanho de uma tabela e de seus indices<\/p>\n

\u2013 Tamanho de Cada Tabela<\/span><\/span><\/div>\n

<\/span><\/span><\/div>\n
SELECT substr(owner,1,15) owner,segment_type, <\/span><\/span><\/div>\n
substr(tablespace_name,1,15) tablespace_name, <\/span><\/span><\/div>\n
round(sum(bytes\/1024\/1024),2) as Tamanho_MB <\/span><\/span><\/div>\n
FROM dba_segments<\/span><\/span><\/div>\n
WHERE owner = ‘SANKHYA’<\/span><\/span><\/div>\n
AND segment_type = ‘TABLE’<\/span><\/span><\/div>\n
AND segment_name like ‘TSILGT%’<\/span><\/span><\/div>\n
GROUP BY owner,segment_type, tablespace_name, segment_name<\/span><\/span><\/div>\n
union all<\/span><\/span><\/div>\n
SELECT substr(owner,1,15) owner,segment_type, <\/span><\/span><\/div>\n
substr(tablespace_name,1,15) tablespace_name, <\/span><\/span><\/div>\n
round(sum(bytes\/1024\/1024),2) as Tamanho_MB <\/span><\/span><\/div>\n
FROM dba_segments<\/span><\/span><\/div>\n
WHERE owner = ‘SANKHYA’<\/span><\/span><\/div>\n
AND segment_type = ‘INDEX’<\/span><\/span><\/div>\n
AND segment_name in (<\/span><\/span><\/div>\n
select index_name <\/span><\/span><\/div>\n
from dba_indexes <\/span><\/span><\/div>\n
where table_name = ‘TSILGT’<\/span><\/span><\/div>\n
and owner = ‘SANKHYA’)<\/span><\/span><\/div>\n
<\/div>\n
GROUP BY owner,segment_type, tablespace_name, segment_name;<\/span><\/span><\/div>\n
<\/div>\n
SELECT owner, tablespace_name, segment_name,<\/span><\/span><\/div>\n
round(sum(bytes\/1024\/1024),2) as Tamanho_MB \u2013, extents as Num_extents<\/span><\/span><\/div>\n
FROM dba_segments<\/span><\/span><\/div>\n
WHERE owner = ‘SCOTT’<\/span><\/span><\/div>\n
AND segment_type = ‘TABLE’<\/span><\/span><\/div>\n
\u2014 AND segment_name like ‘DEPT%’<\/span><\/span><\/div>\n
GROUP BY owner, tablespace_name, segment_name<\/span><\/span><\/div>\n

<\/span><\/span><\/div>\n
\u2013 Tamanho das Tabelas Por Usu\u00e1rio<\/span><\/span><\/div>\n

<\/span><\/span><\/div>\n
SELECT owner, round(sum(bytes\/1024\/1024),2) as Tamanho_MB \u2013, extents as Num_extents<\/span><\/span><\/div>\n
FROM dba_segments<\/span><\/span><\/div>\n
GROUP BY owner<\/span><\/span><\/div>\n

<\/span><\/span><\/div>\n
\u2013 Tamanho Total das Tabelas<\/span><\/span><\/div>\n

<\/span><\/span><\/div>\n
SELECT round(sum(bytes\/1024\/1024),2) as Tamanho_MB \u2013, extents as Num_extents<\/span><\/span><\/div>\n
FROM dba_segments<\/span><\/span><\/div>\n

<\/span><\/span><\/div>\n
\u2013 % de Uso das TableSpaces<\/span><\/span><\/div>\n

<\/span><\/span><\/div>\n
SELECT a.TABLESPACE_NAME “TableSpace Name”,<\/span><\/span><\/div>\n
round(a.BYTES\/1024\/1024) “MB Allocated”,<\/span><\/span><\/div>\n
round((a.BYTES-nvl(b.BYTES, 0)) \/ 1024 \/ 1024) “MB Used”,<\/span><\/span><\/div>\n
nvl(round(b.BYTES \/ 1024 \/ 1024), 0) “MB Free”,<\/span><\/span><\/div>\n
round(((a.BYTES-nvl(b.BYTES, 0))\/a.BYTES)*100,2) “Pct Used”,<\/span><\/span><\/div>\n
round((1-((a.BYTES-nvl(b.BYTES,0))\/a.BYTES))*100,2) “Pct Free”<\/span><\/span><\/div>\n
FROM (SELECT TABLESPACE_NAME,<\/span><\/span><\/div>\n
sum(BYTES) BYTES<\/span><\/span><\/div>\n
FROM dba_data_files<\/span><\/span><\/div>\n
GROUP BY TABLESPACE_NAME) a,<\/span><\/span><\/div>\n
(SELECT TABLESPACE_NAME,<\/span><\/span><\/div>\n
sum(BYTES) BYTES<\/span><\/span><\/div>\n
FROM sys.dba_free_space<\/span><\/span><\/div>\n
GROUP BY TABLESPACE_NAME) b<\/span><\/span><\/div>\n
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)<\/span><\/span><\/div>\n
ORDER BY ((a.BYTES-b.BYTES)\/a.BYTES);<\/span><\/span><\/div>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"

Ola pessoal, venho a mostrar a voces como temos o tamanho de uma tabela e de seus indices \u2013 Tamanho de Cada Tabela SELECT substr(owner,1,15) owner,segment_type,  substr(tablespace_name,1,15) tablespace_name,  round(sum(bytes\/1024\/1024),2) as Tamanho_MB  FROM dba_segments WHERE owner = ‘SANKHYA’ AND segment_type = … Continue lendo →<\/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":[1],"tags":[],"class_list":["post-75","post","type-post","status-publish","format-standard","hentry","category-sem-categoria"],"_links":{"self":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts\/75","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=75"}],"version-history":[{"count":0,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts\/75\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=75"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=75"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=75"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}