Inflate tables and devour tablespaces


Picture to give the right mood

Good day!


On the Internet in general and on Habré in particular, there are a lot of publications about the repair of broken blocks. For example, here is about pulling out intact data , and here is the story of a victory over a broken LOB segment .

I will not bring legions of links to the Internet with such articles. But there is a common feature in most of these articles. When the data is saved (or destroyed, as it turns out), it is proposed to defeat the damaged free block by capturing all the free space in the tablespace. And for some reason this proposal is descriptive.

So add specifics!

We’ll write a script that eats (almost like fish in the picture), and then free up all the free space on Oracle DB.

The preparatory part.

We looked at our broken blocks:
select * from v$database_block_corruption;

Make sure they are.

Did all our “free” blocks look exactly (suddenly another broken segment was lost?):
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
     , greatest(e.block_id, c.block#) corr_start_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) 
       - greatest(e.block_id, c.block#) + 1 blocks_corrupted
     , null description
  FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
   AND e.block_id <= c.block# + c.blocks - 1
   AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
     , header_block corr_start_block#
     , header_block corr_end_block#
     , 1 blocks_corrupted
     , 'Segment Header' description
  FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
   AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
     , greatest(f.block_id, c.block#) corr_start_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) 
       - greatest(f.block_id, c.block#) + 1 blocks_corrupted
     , 'Free Block' description
  FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
   AND f.block_id <= c.block# + c.blocks - 1
   AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;


Do not forget to disable autoextend for all files of our TS with broken free blocks. The consequences of forgetfulness may not be very pleasant.
Yes, you can have time to “compress” files back. But why do we need extra problems?
select 
    'alter database datafile '||
    file_name||
    ' '||
    ' autoextend off;'
from 
    dba_data_files where TABLESPACE_NAME='PSAPSR3';

Got a list of teams. Launched them. Then, in the same way, we will create a list of commands that will return autoextend.
But here you have to be careful. Maybe the data files are located on different disks? And they have different extension settings? It’s worth being careful here.

(Yes, my specialty is “SAP BASIS Specialist.” This explains the choice of name for tablespace. After all, I have to fix the errors there.)

And only now, when the preparatory steps are completed.

Main part

For work with Oracle sql I use SQL Developer . It has everything that is needed, and what is not needed is not observed.

What do we want?
Reformat all free tablespace blocks. To do this, we will create tables, and add extents to them until the place runs out.

Here is a PL \ SQL script that normally does what we need.
SET SERVEROUTPUT ON
DECLARE
type ARR_TABLE is table of varchar2(13);
TBLS ARR_TABLE:=ARR_TABLE();
I number;
SPACE_AVAILABLE float;
--Константа с целевым tablespace
TABLESPACE_FOR_FULL CONSTANT varchar2(20) := 'PSAPSR3';
--Константа со схемой. Почему бы нет?
USER_SCHEMA CONSTANT varchar2(20) := 'SAPSR3';
--Вычисляем свободное место в TS
function TABLESPACE_FREESIZE(TN varchar2) return number
as si number; 
begin
  SELECT round(sum(bytes)/1048576,2) into si from DBA_FREE_SPACE where TABLESPACE_NAME = TN;
  return SI;
end TABLESPACE_FREESIZE;
--Создание и раздувание очередной таблицы пока не выскочит ora-1653
procedure create_new_tables as
  N number;
  I number;
  UNABLE_TO_EXTEND EXCEPTION;
  PRAGMA EXCEPTION_INIT(UNABLE_TO_EXTEND,-1653);
BEGIN
  N:=TBLS.COUNT;
  N:=N+1;
  TBLS.extend;
  TBLS(N):='TESTTABLE'||N;
  execute immediate 'create table '||USER_SCHEMA||'.'||TBLS(n)||\' (id number(10), USER_NAME varchar2(10), CREATE_DATE date) tablespace '||TABLESPACE_FOR_FULL;
   WHILE true LOOP
    begin
      execute immediate 'alter table '||USER_SCHEMA||'.'||TBLS(n)||' allocate extent';
      EXCEPTION
      when UNABLE_TO_EXTEND then
      EXIT;
    end;
  END LOOP;
 end create_new_tables;
BEGIN
--Подготовка
  DBMS_OUTPUT.PUT_LINE( 'Time start: '||TO_CHAR(sysdate,  'DD-MM-YYYY HH24:MI:SS'));
  SPACE_AVAILABLE:=TABLESPACE_FREESIZE(TABLESPACE_FOR_FULL);
  DBMS_OUTPUT.PUT_LINE('Space available='||SPACE_AVAILABLE);
--Запуск 
  WHILE SPACE_AVAILABLE>0.001 LOOP
    CREATE_NEW_TABLES();
    SPACE_AVAILABLE:=TABLESPACE_FREESIZE(TABLESPACE_FOR_FULL);
--Когда функция TABLESPACE_FREESIZE начнет выдавать NULL вместо числа - цикл остановится.
  end LOOP;
--Очистка созданных таблиц
  for I in 1..TBLS.COUNT LOOP
    execute immediate 'drop table '||USER_SCHEMA||\'.'||TBLS(I);
  end LOOP;
  DBMS_OUTPUT.PUT_LINE( 'Time end: '||TO_CHAR(sysdate,  'DD-MM-YYYY HH24:MI:SS'));
end;

(I ask you to pay attention to two extra slashes "\". In the lines with "create table" and "drop table". They are to ensure that the highlighting does not go. They must be demolished before combat use. And I do not want to change the quotation marks to others The subsequent search for “infidels” is very annoying.)

Again, the given constants TABLESPACE_FOR_FULL and USER_SCHEMA seem to hint at the specifics of my databases.
It seems unnecessary to comment on the operation algorithm.

The speed of such methods suits me.

anonymous block completed
Time start: 22-10-2013 13:10:10
Space available=827,88
Time end: 22-10-2013 13:10:11
anonymous block completed
Time start: 22-10-2013 13:10:27
Space available=10668,75
Time end: 22-10-2013 13:10:46
anonymous block completed
Time start: 22-10-2013 13:11:26
Space available=99266,81
Time end: 22-10-2013 13:14:37


Just under 100Gb in 3 minutes. It is clear that the equipment plays a very large role, but where the equipment is weak and there will be no special sizes. Moreover, Tb free space is a rather strange situation.

Final steps

Now returning autoextend, you can do a database check.

rman target /

AND
BACKUP VALIDATE database;


Then the v $ database_block_corruption view should be clean as ... well, you get the idea.

Epilogue

Is there a shorter and faster way? It should be. Mandatory. I started writing in PL \ SQL three weeks ago. And first of all, I am an SAP BASIS specialist, not a DBA.
This is enough for me. But it would be interesting to look at the solution to this problem from the pros.

Also popular now: