ORACLE SPACE MANAGEMENT

sprawdzanie wielkości poszczególnych segmentów:

SELECT segment_name,tablespace_name,bytes/(1024*1024) as totsize_bytes FROM DBA_SEGMENTS WHERE owner='[user]' order by totsize_bytes;

w przypadku obiektów LOB, zapytania szczegółowe należy odnieść do DBA_LOBS
sprawdzanie wielkości segmentów 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 przestrzeniach tabel dla określonego usera:

SELECT tablespace_name,count(*) FROM DBA_SEGMENTS WHERE owner=[user] GROUP BY tablespace_name;

shrink tabeli (pamiętać aby nie było tam sesji aktywnych, nie shrinkuje lobow):

ALTER TABLE [table] ENABLE ROW MOVEMENT;

tabele z indexami:

ALTER TABLE [table] SHRINK SPACE CASCADE;

nie zmienia hwm musi nastąpić po niej normalny shrink:

ALTER TABLE [table] SHRINK SPACE COMPACT;

ALTER TABLE [table] SHRINK PACE;

ALTER TABLE [table] DISABLE ROW MOVEMENT;

shrink segmentu lobow (pamietac aby nie było tam sesji aktywnych):

ALTER TABLE [table] MODIFY LOB([column]) (SHRINK SPACE)update pustego loba:UPDATE [table] SET [column] = EMPTY_BLOB()|EMPTY_CLOB();

szacowanie plików do zmniejszenia:

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;

sprawdzenie rozkładu bloków w segmencie:

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;

procedura do sprawdzania:

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;
/

wywolanie powyższej procedury:

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;

optymalizacja wszystkich tabel:

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;

sprawdzenie które segmenty znajdują się na końcu przestrzeni tabel:

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;

sprawdzanie HWM w pliku:

SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents where file_id = [file_id] group by file_id;

sprawdzenie segmentow których block_id sa ostatnie w pliku:

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;

sprawdzanie block_id dla określonego segmentu:

SELECT segment_name, max(block_id) FROM dba_extents WHERE file_id=21 AND segment_name='[segment]' GROUP BY segment_name

sprawdzanie nr bloków dla lobów:

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');