ORA-1722 and Regular Expressions

Yesterday I was importing some data I downloaded. When creating a new table I tried to convert two columns with latitude and longitude, stored as VARCHAR2, to numbers (in order to use them in the SDO_GEOMETRY constructor). The create table as select (CTAS) statement failed with ORA-01722. Problem is it does not tell you which line or what value is the offender. You may turn on some tracing with event 1722, but using regular expression is much easier:

select longitude 
from data_import
where regexp_instr(longitude,'\d+.\d{7}') = 0;

This will show you rows that does match the format used in the to_number function. Which in this case was '99.9999999'. Turned out that some of the rows had "(blank)" in them... they were updated to NULL.

Oracle database has support for regular expressions in theses functions:

• REGEXP_COUNT
• REGEXP_INSTR
• REGEXP_REPLACE
• REGEXP_SUBSTR

24 Basic Tips on Using SQL Developer

I have been using SQL Developer since it required some goodwill to work. Some of the motivation came from an annoyance with Toad sessions in the database. I remember that I back then googled "how to block Toad users from the database".  

Anyway, these days most developers around me are using SQL Developer. But quite a few are learning only the least to get their job done. The following list is what I use often, and some of it results in "How did you do that?" when they are looking over my shoulder. The shortcuts here refer to the Windows version since that is what I'm currently using at work. You can easily remap them from Preferences.

1. Use templates from preferences to create shortcuts to frequently used SQL statements, or fraction of it. You'll find it under Database - SQL Editor Code Templates
2. Speaking of preferences, there are lots of stuff to tweak, if you are looking for something special use the search field in the upper left corner
   
3. Templates are useful, so are snippets which can be anything from functions and hints to small scripts. Check out View -> Snippets, and when you see something cool, just drag it into the editor window.
4. Drag the table from the list of tables in the Connections window to the worksheet, and it will offer to generate SQL for you (INSERT, SELECT, etc).
5. Copy rows from Query Result with Shift-Ctrl-C to get the column headers. Paste it directly into Excel or in Outlook Email. In the latter you can use the "Convert to text" function to get a nice looking table in a few steps.
6. Attach shortcuts to worksheets with Alt-Shift-number (1-9). Switch between them with Alt-number
7. If you have used one of those tools that requires you to mark the statement you want to execute, you don't have to do this with SQL Dev; with the cursor somewhere on the statement just hit Ctrl-Enter.
8. To convert text to and from upper, lower, or initcap, mark the text and hit Ctrl-' (this one was not set on my Mac version, if that is your case, go to Preferences, search for shortcut, and in that window search for 'other'). Repeating it will cycle through upper, lower, and initcap.
9. How to insert a list you receive into a table: Say someone sends you a list of 10 rows in an email, go to the Data tab for the table and add 10 rows. Copy the rows from the email and put the cursor on the first row in the data tab, and paste them in with Ctrl-V. SQL Dev will paste them in as you expect in Excel. This even works with two or more columns if the columns are separated with tabs. 
10. Using Subversion (or Git) from SQL Dev saves you a mental context switch compared to using an external client. By opening SQL scripts in SQL Developer, edit, save, and commit them (to the source control system) your work flows better.
11. Reformat code with Ctrl-F7. Makes it easier to read when you have copied and pasted generated code from somewhere.
12. Get instant info about a table. Put the cursor on the table name in the worksheet and hit Shift-F4. A window pops up with lots of vital information, and it is quicker than to search for it in the hierarchy. 
13. Hit Ctrl-G to go to a specific line in your editor window. 
14. After you have become more productive and live your life in SQL Dev, it may need more memory.  See this post for details: http://www.thatjeffsmith.com/archive/2014/06/configuring-memory-usage-in-oracle-sql-developer/ In short, uncomment the line in product.conf that starts with AddVMOption, and change the value from 800m (default) to say 1024m. 
15. Save query output for future reference by using the red pin in the Query result window. You can go back and see what SQL produced it by clicking the blue "SQL".
16. When you look at the indexes for a table in the Pop-up info box (Shift-F4), you can open the declaration for the index by right-clicking on a row for an index, and choose Go To...
17. Right-click on an editor tab and choose Float. It will move the worksheet tab to a separate window. Then you can click on other tabs and drag them to that window and group connections that belong together.
18. Change the DATE-format in the Preferences, the default format yyyy.mm.dd is usually not enough for me, so I add HH24:MI:SS 
19. DBAs and others often work on many tasks at the same time, some scripts or SQL commands take long time to execute. Instead of checking back once in a while for its completion open the Task Progress from the View menu. It will show tasks that are running, completed or failed. Click the icon on the right side in order to remove finished tasks.
20. SQL History (F8) is useful, even as backup of previous complex SQL that you have been working on, but didn't save before the crash...
21. Use Connection Color when setting up a connection towards a production database. I use red and it will add a discrete line around the edge to remind me not to do something stupid. This is much better than having the entire background bleeding when doing serious business.
22. Hit F10 to get the execution plan for the statement where your cursor is.
23. If you need to get to your database through a jump server, see my blogpost about it.
24. Code completion is very useful, but sometimes it doesn't work. There is a limit to how much processing and parsing SQL Developer can do before your patience runs out. It helps to at least finish previous statement with a semi-colon or something that clearly marks the start of next statement, and write correct SQL.

There is a lot smartness built into the tool, sometimes a bit hidden; to not get in your way. If you think SQL Developer can't do something, just try it out. So far I have never received any obscene error messages.

Function to let user B see all tables of user A

In case you do not want to grant a user access to data dictionary tables like DBA_TABLES, but will let user B see the list of all tables belonging to user A, you can work around it with a pipelined function in schema A:

create type str_set as table of varchar2(30);
/

create or replace function a_tables return str_set pipelined is
l_str varchar2(30);
begin
for l_str in (select table_name from user_tables)
loop
pipe row(l_str.table_name);
end loop;
return;
end;
/
grant execute on a_tables to B;

Then user B can see the list of A's table with:

select * from table(a.a_tables);

Trying to solve this with a view in schema A that selects on USER_TABLES does not work, but prove me wrong, please.

Corrupt database blocks III

If you don't know what procrastination is you could start your own blog. Start a post with something like "I worked with ... and decided to write three posts about it". Procrastination is what you experience before the last post is ready; seemingly simple things take forever to get out the door.

This post is important for my own record and shows how to salvage data from a large table with several corrupt blocks. I also had a learning experience I wanted to remember, explaining the redundancy of information here. Previous post was about how to map out tables and the corrupt blocks in a temporary table (that is, not a table created as a global temporary table, but a table you'll drop when you're finished.)

Note 61685.1 on My Oracle Support (MOS) shows how to extract data from a corrupt table. This post builds on some of that. Before you begin; data in corrupt blocks are gone. Unless you have special tools and start reading the data files with a tool like DUDE you won't recover those data. This is about how to recover the data that reside in blocks that have not been marked as corrupt.

One essential function gives you the ROWID from the block number and its file number: DBMS_ROWID.ROWID_CREATE:

select DBMS_ROWID.ROWID_CREATE(1,253071,89,660864,0) from DUAL;

In the example above 1 means extended rowid; no need to change this, 253071 is the DATA_OBJECT_ID from DBA_OBJECTS, 89 is FILE# (file number) from V$DATABASE_BLOCK_CORRUPTION , and 660864 is BLOCK# (block number) from the same view. The last parameter is for the row number in that block, in this case we want the whole block and therefore starts with row 0.

This is an example of the content of V$DATABASE_BLOCK_CORRUPTION:

FILE#     BLOCK#    BLOCKS  CORRUPTION_CHANGE#  CORRUPTION_TYPE
89        661112    8       0                   CORRUPT
89        661048    16      0                   CORRUPT
89        660984    16      0                   CORRUPT
89        660920    16      0                   CORRUPT
89        660864    8       0                   CORRUPT

In previous post I created a table SEGMENTS_WITH_CORRUPTION. The following query gives a report of corrupt segments with an interval by rowid using the function above:

select O.OWNER,O.OBJECT_NAME,O.OBJECT_TYPE,
DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT,0) ROWID_OF_FIRST_CORR,
  DBMS_ROWID.ROWID_CREATE(1,o.DATA_OBJECT_ID,c.file_id,c.start_corrupt+c.corrupt_blocks,0) ROWID_OF_NEXT_CLEAN
from segments_with_corruption c,DBA_OBJECTS O
where C.OWNER=O.OWNER
and c.segment_name=o.object_name;

An example of output (slightly redacted so I don't reveal the source)

OWNER  OBJECT_NAME  OBJECT_TYPE    ROWID_OF_FIRST_CORR   ROWID_OF_NEXT_CLEAN
LUSER  FUBAR        TABLE          AAA9yPABZAAChZ4AAA    AAA9yPABZAAChaAAAA
LUSER  FUBAR        TABLE          AAA9yPABZAAChY4AAA    AAA9yPABZAAChZIAAA
LUSER  FUBAR        TABLE          AAA9yPABZAAChX4AAA    AAA9yPABZAAChYIAAA
LUSER  FUBAR        TABLE          AAA9yPABZAAChW4AAA    AAA9yPABZAAChXIAAA
LUSER  FUBAR        TABLE          AAA9yPABZAAChWAAAA    AAA9yPABZAAChWIAAA

Now, create a new table to hold the good data. Using LUSER.FUBAR as the owner.table_name for the original table:

Create table luser.fubar_recover as
Select * from luser.fubar
Where 1=0;

This means we create an empty table with the same structure, we will fill it with data in a few steps. If you had only a few intervals of corrupt blocks, you can create insert statements like the following in order to retrieve the good data:

Insert into luser.fubar_recover
  select *
  from luser.fubar
  where rowid < ‘AAA9yPABZAAChZ4AAA’;

Insert into luser.fubar_recover
  select *
  from luser.fubar
  where rowid >= ‘AAA9yPABZAAChaAAAA’  
  and rowid < ‘AAA9yPABZAAChY4AAA’;

In the first example you get all the rows in blocks up to the corrupt one. In the second you get all the rows between two corrupt blocks. In my case I had a large table with several stretches of corrupt and good blocks. I did not want to handcraft all these insert statements, I wanted one statement that could retrieve all the good data from the table.

To achieve that I started with a query that generates interval of corrupt blocks in the order as they are laid out in the table. At this point I was only working on one table and one data file as the query below shows.

select FIRST_CORRUPT,FIRST_CLEAN,LAG(FIRST_CORRUPT) over (order by start_corrupt) PREVIOUS_CORRUPT,
  LEAD(FIRST_CORRUPT) over (order by start_corrupt) NEXT_CORRUPT, rownum rn
  from(
    select 
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT,0) FIRST_CORRUPT,
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT+C.CORRUPT_BLOCKS,0) FIRST_CLEAN,
    C.START_CORRUPT
    from SEGMENTS_WITH_CORRUPTION C, DBA_OBJECTS O
    where C.OWNER=O.OWNER
    and C.SEGMENT_NAME=O.OBJECT_NAME
    and FILE_ID=89 and C.OWNER=’LUSER’ and C.SEGMENT_NAME='FUBAR’
    order by c.start_corrupt
  )
;

The output looked like this:

FIRST_CORRUPT	FIRST_CLEAN	PREVIOUS_CORRUPT	NEXT_CORRUPT	RN
AAA9yPABZAAChWAAAA	AAA9yPABZAAChWIAAA		AAA9yPABZAAChW4AAA	1
AAA9yPABZAAChW4AAA	AAA9yPABZAAChXIAAA	AAA9yPABZAAChWAAAA	AAA9yPABZAAChX4AAA	2
AAA9yPABZAAChX4AAA	AAA9yPABZAAChYIAAA	AAA9yPABZAAChW4AAA	AAA9yPABZAAChY4AAA	3
AAA9yPABZAAChY4AAA	AAA9yPABZAAChZIAAA	AAA9yPABZAAChX4AAA	AAA9yPABZAAChZ4AAA	4
AAA9yPABZAAChZ4AAA	AAA9yPABZAAChaAAAA	AAA9yPABZAAChY4AAA		5

Oh, well. Layout of the last table could have been better, I experimented with export html-code from SQL Developer that can be pasted into the HTML-tab.  Anyway, it shows stretches of blocks that are corrupt for this file. Now I want to generate WHERE clauses that are used in the select statement that retrieves the good data:

with INTERVALS as (
select FIRST_CORRUPT,FIRST_CLEAN,LAG(FIRST_CORRUPT) over (order by start_corrupt) PREVIOUS_CORRUPT,
  LEAD(FIRST_CORRUPT) over (order by start_corrupt) NEXT_CORRUPT, rownum rn
  from(
    select 
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT,0) FIRST_CORRUPT,
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT+C.CORRUPT_BLOCKS,0) FIRST_CLEAN,
    C.START_CORRUPT
    from SEGMENTS_WITH_CORRUPTION C, DBA_OBJECTS O
    where C.OWNER=O.OWNER
    and C.SEGMENT_NAME=O.OBJECT_NAME
    and FILE_ID=89 and C.OWNER='LUSER' and C.SEGMENT_NAME='FUBAR'
    order by c.start_corrupt
  )
)  select 'where rowid < ''' || FIRST_CORRUPT || '''' LINE
   from INTERVALS
   where RN=1
   union
   select 'or (rowid >= ''' || FIRST_CLEAN || ''' and rowid < ''' || NEXT_CORRUPT || ''')' LINE
   from INTERVALS
   where RN>1 and NEXT_CORRUPT is not null
   union
   select 'or (rowid >= ''' || FIRST_CLEAN || ''')' LINE
   from INTERVALS
   where NEXT_CORRUPT is null
   order by rn
  ;

This gives the following output:

LINE
where rowid < 'AAA9yPABZAAChWAAAA'
or (rowid >= 'AAA9yPABZAAChXIAAA' and rowid < 'AAA9yPABZAAChX4AAA')
or (rowid >= 'AAA9yPABZAAChYIAAA' and rowid < 'AAA9yPABZAAChY4AAA')
or (rowid >= 'AAA9yPABZAAChZIAAA' and rowid < 'AAA9yPABZAAChZ4AAA')
or (rowid >= 'AAA9yPABZAAChaAAAA)

I can now amend the insert statement by using these where clauses at the end of the select statement:

insert   into LUSER.FUBAR_RECOVER
select /*+ ROWID(T) */ *
from LUSER.FUBAR T
where rowid < 'AAA9yPABZAAChWAAAA'
or (rowid >= 'AAA9yPABZAAChXIAAA' and rowid < 'AAA9yPABZAAChX4AAA')
or (rowid >= 'AAA9yPABZAAChYIAAA' and rowid < 'AAA9yPABZAAChY4AAA')
or (rowid >= 'AAA9yPABZAAChZIAAA' and rowid < 'AAA9yPABZAAChZ4AAA')
or (rowid >= 'AAA9yPABZAAChaAAAA');

Also note the ROWID hint that tells Oracle how to scan the table. The biggest problem with this query is that it does not work(!) Yes, I'm going to drag you through the learning experience I had when making things automated and slightly more advanced. When executing the SQL above Oracle performs a normal table scan and aborts when it reads the first corrupt block. This can be seen if you look at the execution plan:

explain plan for
select /*+ ROWID(T) */ *
from LUSER.FUBAR T
where rowid < 'AAA9yPABZAAChWAAAA'
or (rowid >= 'AAA9yPABZAAChXIAAA' and rowid < 'AAA9yPABZAAChX4AAA')
or (rowid >= 'AAA9yPABZAAChYIAAA' and rowid < 'AAA9yPABZAAChY4AAA')
or (rowid >= 'AAA9yPABZAAChZIAAA' and rowid < 'AAA9yPABZAAChZ4AAA')
or (rowid >= 'AAA9yPABZAAChaAAAA');
select * from table(dbms_xplan.display);

Plan hash value: 3975471795
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |    11M|  2111M|   838K  (1)| 02:47:37 |
|*  1 |  TABLE ACCESS FULL| FUBAR        |    11M|  2111M|   838K  (1)| 02:47:37 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWID<'AAA9yPABZAAChWAAAA' OR ROWID>='AAA9yPABZAAChaAAAA'
              OR ROWID>='AAA9yPABZAAChXIAAA' AND ROWID<'AAA9yPABZAAChX4AAA' OR
              ROWID>='AAA9yPABZAAChYIAAA' AND ROWID<'AAA9yPABZAAChY4AAA' OR
              ROWID>='AAA9yPABZAAChZIAAA' AND ROWID<'AAA9yPABZAAChZ4AAA')

Compare this to the plan for the SQL that was used in the beginning:

explain plan for
select /*+ ROWID(T) */ *
from LUSER.FUBAR T
where rowid >= 'AAA9yPABZAAChaAAAA';
select * from table(dbms_xplan.display);

Plan hash value: 3328636578
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  5445K|  1012M|   836K  (1)| 02:47:18 |
|*  1 |  TABLE ACCESS BY ROWID RANGE| FUBAR        |  5445K|  1012M|   836K  (1)| 02:47:18 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access(ROWID>='AAA9yPABZAAChaAAAA')

This one uses TABLE ACCESS BY ROWID, but the one that fails uses normal TABLE SCAN. I therefore changed slightly the SQL to generate where clauses:

with INTERVALS as (
select FIRST_CORRUPT,FIRST_CLEAN,LAG(FIRST_CORRUPT) over (order by start_corrupt) PREVIOUS_CORRUPT,
  LEAD(FIRST_CORRUPT) over (order by start_corrupt) NEXT_CORRUPT, rownum rn
  from(
    select 
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT,0) FIRST_CORRUPT,
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT+C.CORRUPT_BLOCKS,0) FIRST_CLEAN,
    C.START_CORRUPT
    from SEGMENTS_WITH_CORRUPTION C, DBA_OBJECTS O
    where C.OWNER=O.OWNER
    and C.SEGMENT_NAME=O.OBJECT_NAME
    and FILE_ID=89 and C.OWNER='LUSER' and C.SEGMENT_NAME='FUBAR'
    order by c.start_corrupt
  )
)  select 'select /*+ ROWID(T) */ *
from LUSER.FUBAR T where rowid < ''' || FIRST_CORRUPT || '''' LINE 
   from INTERVALS
   where RN=1
   union
   select 'union select /*+ ROWID(T) */ *
from LUSER.FUBAR T  where rowid >= ''' || FIRST_CLEAN || ''' and rowid < ''' || NEXT_CORRUPT || '''' LINE
   from INTERVALS
   where RN>1 and NEXT_CORRUPT is not null
   union
   select 'union select /*+ ROWID(T) */ *
from LUSER.FUBAR T where rowid >= ''' || FIRST_CLEAN || ''';' LINE
   from INTERVALS
   where NEXT_CORRUPT is null
   order by RN
  ;

The resulting SQL used to retrieve good data looks like this:

select /*+ ROWID(T) */ *
from LUSER.FUBAR T where rowid < 'AAA9yPABZAAChWAAAA'
union select /*+ ROWID(T) */ *
from LUSER.FUBAR T  where rowid >= 'AAA9yPABZAAChXIAAA' and rowid < 'AAA9yPABZAAChX4AAA'
union select /*+ ROWID(T) */ *
from LUSER.FUBAR T  where rowid >= 'AAA9yPABZAAChYIAAA' and rowid < 'AAA9yPABZAAChY4AAA'
union select /*+ ROWID(T) */ *
from LUSER.FUBAR T  where rowid >= 'AAA9yPABZAAChZIAAA' and rowid < 'AAA9yPABZAAChZ4AAA'
union select /*+ ROWID(T) */ *
from LUSER.FUBAR T where rowid >= 'AAA9yPABZAAChaAAAA';

This one actually runs, but it runs for a long time and it eventually failed later because TEMP ran out of space. The reason for this can also be seen in the execution plan:

Plan hash value: 3469639691
------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    11M|  2177M|       |  4680K (78)| 15:36:06 |
|   1 |  SORT UNIQUE                  |              |    11M|  2177M|  2858M|  4680K (78)| 15:36:06 |
|   2 |   UNION-ALL                   |              |       |       |       |            |          |
|*  3 |    TABLE ACCESS BY ROWID RANGE| FUBAR        |  5445K|  1012M|       |   836K  (1)| 02:47:18 |
|*  4 |    TABLE ACCESS BY ROWID RANGE| FUBAR        |   272K|    50M|       |   836K  (1)| 02:47:16 |
|*  5 |    TABLE ACCESS BY ROWID RANGE| FUBAR        |   272K|    50M|       |   836K  (1)| 02:47:16 |
|*  6 |    TABLE ACCESS BY ROWID RANGE| FUBAR        |   272K|    50M|       |   836K  (1)| 02:47:16 |
|*  7 |    TABLE ACCESS BY ROWID RANGE| FUBAR        |  5445K|  1012M|       |   836K  (1)| 02:47:18 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(ROWID<'AAA9yPABZAAChWAAAA')
   4 - access(ROWID>='AAA9yPABZAAChXIAAA' AND ROWID<'AAA9yPABZAAChX4AAA')
   5 - access(ROWID>='AAA9yPABZAAChYIAAA' AND ROWID<'AAA9yPABZAAChY4AAA')
   6 - access(ROWID>='AAA9yPABZAAChZIAAA' AND ROWID<'AAA9yPABZAAChZ4AAA')
   7 - access(ROWID>='AAA9yPABZAAChaAAAA')
 

Look at line 1 and 2 in the plan. I forgot to use UNION ALL instead of UNION and Oracle then goes on to sort this stuff in order to return distinct rows.  The table had over 100M rows and sorting this filled the TEMP tablespace completely. Since I know I want all rows, I changed it to UNION ALL (this is finally a good one you can use):

select FIRST_CORRUPT,FIRST_CLEAN,LAG(FIRST_CORRUPT) over (order by start_corrupt) PREVIOUS_CORRUPT,
  LEAD(FIRST_CORRUPT) over (order by start_corrupt) NEXT_CORRUPT, rownum rn
  from(
    select 
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT,0) FIRST_CORRUPT,
    DBMS_ROWID.ROWID_CREATE(1,O.DATA_OBJECT_ID,C.FILE_ID,C.START_CORRUPT+C.CORRUPT_BLOCKS,0) FIRST_CLEAN,
    C.START_CORRUPT
    from SEGMENTS_WITH_CORRUPTION C, DBA_OBJECTS O
    where C.OWNER=O.OWNER
    and C.SEGMENT_NAME=O.OBJECT_NAME
    and FILE_ID=89 and C.OWNER='LUSER' and C.SEGMENT_NAME='FUBAR'
    order by c.start_corrupt
  )
)  select 'select /*+ ROWID(T) */ *
from LUSER.FUBAR T where rowid < ''' || FIRST_CORRUPT || '''' LINE 
   from INTERVALS
   where RN=1
   union 
   select 'union all select /*+ ROWID(T) */ *
from LUSER.FUBAR T  where rowid >= ''' || FIRST_CLEAN || ''' and rowid < ''' || NEXT_CORRUPT || '''' LINE
   from INTERVALS
   where RN>1 and NEXT_CORRUPT is not null
   union all
   select 'union all select /*+ ROWID(T) */ *
from LUSER.FUBAR T where rowid >= ''' || FIRST_CLEAN || ''';' LINE
   from INTERVALS
   where NEXT_CORRUPT is null
   order by RN
  ;

The new execution plan looks like this:

Plan hash value: 439408122
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    11M|  2177M|  4181K (81)| 13:56:23 |
|   1 |  UNION-ALL                   |              |       |       |            |          |
|*  2 |   TABLE ACCESS BY ROWID RANGE| FUBAR        |  5445K|  1012M|   836K  (1)| 02:47:18 |
|*  3 |   TABLE ACCESS BY ROWID RANGE| FUBAR        |   272K|    50M|   836K  (1)| 02:47:16 |
|*  4 |   TABLE ACCESS BY ROWID RANGE| FUBAR        |   272K|    50M|   836K  (1)| 02:47:16 |
|*  5 |   TABLE ACCESS BY ROWID RANGE| FUBAR        |   272K|    50M|   836K  (1)| 02:47:16 |
|*  6 |   TABLE ACCESS BY ROWID RANGE| FUBAR        |  5445K|  1012M|   836K  (1)| 02:47:18 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(ROWID<'AAA9yPABZAAChWAAAA')
   3 - access(ROWID>='AAA9yPABZAAChXIAAA' AND ROWID<'AAA9yPABZAAChX4AAA')
   4 - access(ROWID>='AAA9yPABZAAChYIAAA' AND ROWID<'AAA9yPABZAAChY4AAA')
   5 - access(ROWID>='AAA9yPABZAAChZIAAA' AND ROWID<'AAA9yPABZAAChZ4AAA')
   6 - access(ROWID>='AAA9yPABZAAChaAAAA')
 

To wrap it up:

If you have a large table with several corrupt blocks and many good ones, you can save the good data by creating a table that maps the bad blocks and then create a select statements that retrieve the rows from all the good blocks.

Best is of course to get your backup and recover routines in order, combine it with a decent fault management so you'll discover the corrupt blocks at an early stage, thereby making it possible to recover the blocks before they have been aged out of your backup system.

If corrupt blocks happens again and again you should see if it happens only with very few tables or many. If the corruption is restricted to few out of many tables it may indicate that it has something to do with how these tables are created and used (thinking about NOLOGGING operations here). In case of frequent and seemingly random corruption I would start thinking about the underlying storage.

Wordfeud and regular expression in SQL

Three family members invited me to play Wordfeud with them this Christmas. At the end of such a game losing badly to my sister-in-law and with only a few letters left I was thinking about how I could find candidate words using regular expression in SQL.

First I found a list of Norwegian words on the net. I should have taken the effort to get hold of the same word list as Wordfeud is using. The list was loaded into a table using SQL Developer.

Say you want to see if there is any word that start with N and uses only the letters C, F, S, and A. The following SQL may find it:

select word
from norwegians
where regexp_substr(word,'^n[cfsa]+$') is not null ;

For those not familiar with regular expressions, well, just Google or check out any good book on Perl. In short, the ^ and $ means the beginning and end of line (word in this case) respectively, [] is used to create a group of letters; [cfsa] means any letter of c, f, s, and a; + means one or more occurrences.

Now I only need to make a mobile interface to this, and create a function that calculate the score for each word based on Wordfeud's rules. Next Christmas, maybe. I do know there are apps ready to suggest this, but that is not half as fun :-)