Archive for the ‘script’ Category

oracle 9i: reclaim lob free space, tablespace map

June 22, 2011 1 comment

I’ve deleted a lot of LOB data (pdf, images, …) and I’ll never will reuse it, as I save now all the data on disk.

I want to reduce the  tablespace’s High Water Mark to avoid this error:

ORA-03297: file contains used data beyond requested RESIZE value

To achieve this, I create an auxiliary tablespace to move the LOB tables there:

SQL> create tablespace LOB_TEMP logging datafile '/app/oracle/oradata/db1/lob_temp_01.dbf' size 500M
extent management local uniform size 256K segment space management auto;

SQL> alter user <username> quota unlimited on LOB_TEMP;

Then, I move all the lobs to the new tablespace:

 select 'alter table ' || table_name || ' move lob ('|| column_name || ') store as (tablespace LOB_TEMP);' from user_lobs;

Now I have a lot of free segments in the former tablespace:

select file_id, block_id first_block, block_id+blocks-1 last_block, SEGMENT_NAME
where tablespace_name = 'FORMER_TS'
union all
select file_id, block_id, block_id+blocks-1, 'free'
where tablespace_name = 'FORMER_TS'

I perform a “move tablespace” for all the tables and indexes, just to reclaim some gaps:

select 'alter table ' || table_name || ' move;' from user_tables;
select 'alter index ' || index_name || ' rebuild;' from user_indexes where index_type <> 'LOB';

And then, move the lob segments to its original tablespace (although I prefer to have the LOB’s in its own tablespace):

select 'alter table ' || table_name || ' move lob ('|| column_name || ') tablespace (FORMER_TS);' from user_lobs;

When I select again for the free space, I have some gap of free space between used blocks, that I cannot reuse moving tables or rebuilding indexes. It’s difficult to understand how oracle manages the free space. Lets supose you have this scenario:

table (8 blocks)
table (8 blocks)
table (16 blocks)
index (8 blocks)
table (8 blocks)
free space (150 blocks)
table (8 blocks)
index (8 blocks)
index (8 blocks)
table (8 blocks)
free space (1000 blocks)

Even if I try to move the tables and indexes below the first free space, my data is not placed in that free blocks. To reduce even more this space:

  • move all the objects below the first free space (150 blocks) to another tablespace
  • once moved, all my free space is at the end of the datafile
  • move back the objects to its original tablespace
  • now the objects ocuppies the previous free space of 150 blocks, and I have at the end a free space of 1150 blocks, and now I can resize the datafile size

Collection of BDA queries and scripts

I’ve read something in his webpage some time ago. These days I’ve revisited it and it has a nice collection of DBA queries and scripts:

Very interesting!

Categories: linux, oracle, script Tags: , ,

isNumber function in oracle

August 10, 2010 Leave a comment

As we don’t have an isNumber function in oracle, if you want to know which values are not numbers, just type this:

select field from table where translate(field, '_0123456789', '_') is not null;
Categories: oracle, script Tags:

Batch convert files to UTF-8

March 10, 2010 Leave a comment

In my daily job as DBA I need to convert a lot of files from ISO-8859-1 to UTF-8. This files are commonly SQL scripts generated on Windows environments and I have to execute them in linux servers (Red Hat or Ubuntu) against UTF-8 databases.These scripts usually have characters in Spanish and Catalan (tildes, ñ, ç, etc.) and I need to convert to UTF-8 to avoid getting errors due to invalid characters.

I’ve tried with iconv, specifying as output file the same name as the input file:

$ iconv -f ISO-8859-1 -t utf-8 file.sql -o file.sql

but if the file is greater than 32KB I get this:

Bus error

To avoid this I’ve created a script (

if [ $# -lt 1 ]
  echo "Use: "$0" <file_name>"
  echo "Convert files from ISO-8859-1 to UTF8"

for i in $*
  if [ ! -f $i ]; then # Only convert text files
  # Generate temp file to avoid Bus error
  iconv -f ISO-8859-1 -t utf-8 $i -o $i.tmp
  mv $i.tmp $i

You just have to run it with the name of the file to convert, or using wildcards:

$ *.sql

Hope this help you!

Categories: linux, script