{"id":133,"date":"2008-12-17T22:21:00","date_gmt":"2008-12-17T20:21:00","guid":{"rendered":"http:\/\/antynet.pl\/index.php\/2008\/12\/17\/oracle-space-management\/"},"modified":"2010-08-25T23:27:24","modified_gmt":"2010-08-25T21:27:24","slug":"oracle-space-management","status":"publish","type":"post","link":"https:\/\/www.antynet.pl\/index.php\/2008\/12\/17\/oracle-space-management\/","title":{"rendered":"ORACLE SPACE MANAGEMENT"},"content":{"rendered":"

sprawdzanie wielko\u015bci poszczeg\u00f3lnych segment\u00f3w:<\/span><\/p>\n

SELECT segment_name,tablespace_name,bytes\/(1024*1024) as totsize_bytes FROM DBA_SEGMENTS WHERE owner='[user]' order by totsize_bytes;<\/pre>\n
w przypadku obiekt\u00f3w LOB, zapytania szczeg\u00f3\u0142owe nale\u017cy odnie\u015b\u0107 do DBA_LOBS
sprawdzanie wielko\u015bci segment\u00f3w wraz z lobami:<\/span><\/p>\n
SELECT a.tablespace_name,a.segment_name,b.table_name,b.column_name,a.bytes\/(1024*1024) as MB,c.comments FROM dba_segments a,dba_lobs b,all_tab_comments c WHERE a.owner=upper('[user]') and a.segment_name=b.segment_name(+) and b.table_name=c.table_name(+) and b.owner=c.owner(+) order by MB desc;<\/pre>\n
sprawdzanie liczby segmentow w przestrzeniach tabel dla okre\u015blonego usera:<\/span><\/p>\n
SELECT tablespace_name,count(*) FROM DBA_SEGMENTS WHERE owner=[user] GROUP BY tablespace_name;<\/pre>\n
shrink tabeli (pami\u0119ta\u0107 aby nie by\u0142o tam sesji aktywnych, nie shrinkuje lobow):<\/span><\/p>\n
ALTER TABLE [table] ENABLE ROW MOVEMENT;<\/pre>\n
tabele z indexami:<\/span><\/p>\n
ALTER TABLE [table] SHRINK SPACE CASCADE;<\/pre>\n
nie zmienia hwm musi nast\u0105pi\u0107 po niej normalny shrink:<\/span><\/p>\n
ALTER TABLE [table] SHRINK SPACE COMPACT;<\/pre>\n
ALTER TABLE [table] SHRINK PACE;<\/pre>\n
ALTER TABLE [table] DISABLE ROW MOVEMENT;<\/pre>\n
shrink segmentu lobow (pamietac aby nie by\u0142o tam sesji aktywnych):<\/span><\/p>\n
ALTER TABLE [table] MODIFY LOB([column]) (SHRINK SPACE)update pustego loba:UPDATE [table] SET [column] = EMPTY_BLOB()|EMPTY_CLOB();<\/pre>\n
szacowanie plik\u00f3w do zmniejszenia:<\/span><\/p>\n
SELECT 'alter database datafile ''' || file_name || ''' resize ' || ceil( (nvl(hwm,1)*8192)\/1024\/1024 ) || 'm;' cmd FROM dba_data_files a,( SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b WHERE a.file_id = b.file_id(+) AND ceil(blocks*8192\/1024\/1024)-ceil((nvl(hwm,1)*8192)\/1024\/1024 ) > 0;<\/pre>\n
sprawdzenie rozk\u0142adu blok\u00f3w w segmencie:<\/span><\/p>\n
variable unf_blocks number;
variable unf_bytes number;
variable full_blocks number;
variable full_bytes number;
variable fs1_blocks number;
variable fs1_bytes number;
variable fs2_blocks number;
variable fs2_bytes number;
variable fs3_blocks number;
variable fs3_bytes number;
variable fs4_blocks number;
variable fs4_bytes number;

begin
  dbms_space.space_usage('[user]','[segment]',
  '[TABLE|INDEX|CLUSTER|LOB]',
  :unf_blocks, :unf_bytes,
  :fs1_blocks, :fs1_bytes,
  :fs2_blocks, :fs2_bytes,
  :fs3_blocks, :fs3_bytes,
  :fs4_blocks, :fs4_bytes,
  :full_blocks, :full_bytes);
end;
\/
print unf_blocks; --liczba niesformatowanych blokow
print unf_bytes;
print fs4_blocks; --liczba blokow ktore maja od 75-100% wolnej przestrzeni
print fs4_bytes;
print fs3_blocks; --liczba blokow ktore maja od 50-75% wolnej przestrzeni
print fs3_bytes;
print fs2_blocks; --liczba blokow ktore maja od 25-50% wolnej przestrzeni
print fs2_bytes;
print fs1_blocks; --liczba blokow ktore maja od 0-25% wolnej przestrzeni
print fs1_bytes;
print full_blocks; --liczba pelnych blokow
print full_bytes;
<\/pre>\n
procedura do sprawdzania:<\/b><\/p>\n
CREATE OR REPLACE PROCEDURE SYS.SEGMENT_USAGE(
val_owner varchar2,
val_segment varchar2,
val_type varchar2) is
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;

begin
  dbms_space.space_usage(
  segment_owner => val_owner,
  segment_name => val_segment,
  segment_type => val_type,
  fs1_bytes => l_fs1_bytes,
  fs1_blocks => l_fs1_blocks,
  fs2_bytes => l_fs2_bytes,
  fs2_blocks => l_fs2_blocks,
  fs3_bytes => l_fs3_bytes,
  fs3_blocks => l_fs3_blocks,
  fs4_bytes => l_fs4_bytes,
  fs4_blocks => l_fs4_blocks,
  full_bytes => l_full_bytes,
  full_blocks => l_full_blocks,
  unformatted_blocks => l_unformatted_blocks,
  unformatted_bytes => l_unformatted_bytes
  );

  dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
  dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
  dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
  dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
  dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = '||l_full_bytes);

end;
\/
<\/pre>\n
wywolanie powy\u017cszej procedury:<\/span> <\/p>\n
BEGIN
  FOR rec in (SELECT segment_name FROM dba_segments WHERE owner='' AND segment_type='[type]'   ORDER BY segment_name)
  LOOP
    BEGIN
      DBMS_OUTPUT.PUT_LINE(rec.segment_name);
      SEGMENT_USAGE('[user]',rec.segment_name,'[user]');
    END;
  END LOOP;
END;
<\/pre>\n
optymalizacja wszystkich tabel:<\/p>\n
DECLARE
  dupa varchar2(200);BEGIN for rec in (SELECT segment_name FROM dba_segments WHERE owner='[user]' and segment_type='[TABLE|INDEX]' ) LOOP
BEGIN
  DBMS_UTILITY.EXEC_DDL_STATEMENT(' ALTER TABLE TEST.'||rec.segment_name||' MOVE');
  dupa := rec.segment_name;
  DBMS_OUTPUT.PUT_LINE ('ZOPTYMALIZOWALEM SEGMENT: '||dupa||'');
  EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE (dupa);
END; 
END LOOP;
END;
<\/pre>\n
sprawdzenie kt\u00f3re segmenty znajduj\u0105 si\u0119 na ko\u0144cu przestrzeni tabel:<\/span><\/p>\n
SELECT * FROM (SELECT owner, segment_name, segment_type, block_id FROM dba_extents WHERE file_id = [file_id] ORDER BY block_id DESC ) WHERE rownum = 5;<\/pre>\n
sprawdzanie HWM w pliku:<\/span><\/p>\n
SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents where file_id = [file_id] group by file_id;<\/pre>\n
sprawdzenie segmentow kt\u00f3rych block_id sa ostatnie w pliku:<\/span><\/p>\n
SELECT * FROM (SELECT segment_name, max(block_id) FROM dba_extents WHERE file_id=[file_id] GROUP BY segment_name ORDER BY max(block_id) desc ) WHERE rownum 10;<\/pre>\n
sprawdzanie block_id dla okre\u015blonego segmentu:<\/span><\/p>\n
SELECT segment_name, max(block_id) FROM dba_extents WHERE file_id=21 AND segment_name='[segment]' GROUP BY segment_name<\/pre>\n
sprawdzanie nr blok\u00f3w dla lob\u00f3w:<\/span><\/p>\n
SELECT tablespace_name,(SELECT max(block_id+blocks-1) FROM dba_extents where segment_name = b.segment_name group by segment_name) last_block, c.header_block,c.blocks,b.segment_name,a.index_name,a.index_type,a.table_name,b.column_name FROM dba_indexes a,dba_lobs b,dba_segments c
where a.index_name=b.index_name and b.segment_name=c.segment_name and a.owner=upper('[owner]') and a.index_type=upper('lob');<\/pre>\n","protected":false},"excerpt":{"rendered":"
sprawdzanie wielko\u015bci poszczeg\u00f3lnych segment\u00f3w: SELECT segment_name,tablespace_name,bytes\/(1024*1024) as totsize_bytes FROM DBA_SEGMENTS WHERE owner='[user]’ order by totsize_bytes; w przypadku obiekt\u00f3w LOB, zapytania szczeg\u00f3\u0142owe nale\u017cy odnie\u015b\u0107 do DBA_LOBSsprawdzanie wielko\u015bci segment\u00f3w wraz z lobami: SELECT a.tablespace_name,a.segment_name,b.table_name,b.column_name,a.bytes\/(1024*1024) as MB,c.comments FROM dba_segments a,dba_lobs b,all_tab_comments c WHERE a.owner=upper('[user]’) and a.segment_name=b.segment_name(+) and b.table_name=c.table_name(+) and b.owner=c.owner(+) order by MB desc; sprawdzanie liczby segmentow w […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[80],"tags":[51],"class_list":["post-133","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle-space-management"],"_links":{"self":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/133","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/comments?post=133"}],"version-history":[{"count":2,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/133\/revisions"}],"predecessor-version":[{"id":228,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/133\/revisions\/228"}],"wp:attachment":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/media?parent=133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/categories?post=133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/tags?post=133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}