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 many publications about repairing broken blocks. For example, here about pulling out intact data , and then the story of the victory over the broken LOB segment .

Lead the legions of links to the Internet with similar articles will not. But there is a common feature in most of these articles. When the data is saved (or destroyed, as it will), it is proposed to defeat the damaged free block by capturing all the free space in the tablespace. And for some reason this sentence is descriptive.
')
So add specifics!

Let's write a script that eats (almost like the fish in the picture), and then it will free up all the space on the 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 (what if another broken segment got 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 our TS files with beaten free blocks. The consequences of forgetfulness may not be very pleasant.
Yes, "compress" files back can be in time. 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 commands. Launched them. Then in the same way we will create a list of commands that will be returned by autoextend.
But here we must be careful. Maybe the data files are located on different disks? And they have different extension settings? It is worth being careful here.

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

And only now that the preparatory steps have been completed.

Main part

I use SQL Developer to work with Oracle sql. It has everything you need, and that which is not necessary 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 is finished.

This PL / SQL script 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; 

(Please pay attention to the two extra slashes "\". In the lines with the "create table" and "drop table". They are to ensure that the color does not move. Before combat use, they must be demolished. And I don’t want to change the quotes to others Very annoying subsequent search for "infidels".)

Again, the given constants TABLESPACE_FOR_FULL and USER_SCHEMA seem to hint at the specifics of my databases.
Comment on the algorithm of work seems to me superfluous.

The speed of this method 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 

Slightly less than 100Gb in 3 minutes. It is clear that the equipment plays a very large role, but where the equipment is weak and the dimensions will not be special. Moreover, Tb free space is a rather strange situation.

Final steps

Now returning autoextend, you can do a base check.

 rman target / 

AND

 BACKUP VALIDATE database; 

After that, the representation of v $ database_block_corruption should be clean as ... well, you understand.

Epilogue

Is there a shorter and faster way? It should be. Required. I started writing PL \ SQL three weeks ago. First and foremost, I’m a SAP BASIS specialist, not a DBA.
This is enough for me. But it would be interesting to look at the solution of this problem from the pros.