Archive

Posts Tagged ‘tablespace’

Oracle: Alter tablespace read only causes enqueue TX contention

I’m trying to get a tablespace in read only mode, but it causes an Enqueue TX contention wait event.

This is because there are still active transactions in the database (uncommited or not rolled back) in other tablespaces. I didn’t know that I have to wait for other transactions to commit or rollback on other tablespaces.

So, I’ll try it again during peak-off time.

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
FROM DBA_EXTENTS
where tablespace_name = 'FORMER_TS'
union all
select file_id, block_id, block_id+blocks-1, 'free'
FROM DBA_FREE_SPACE
where tablespace_name = 'FORMER_TS'
ORDER BY FILE_ID, FIRST_BLOCK;

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

Rename a tablespace/datafile in Oracle 9i/10g

October 25, 2010 2 comments

To rename a tablespace in Oracle 10g is very easy:

SQL> alter tablespace test1 rename to test2;

The rename only works in Oracle 10, and updates the dictionary entries for user quotas (user_ts_quotas). There is no simple way to rename a tablespace in Oracle 9, you have to use exp/imp, so, you cannot issue this online.

If you want to delete a tablespace, it’s better to first unassign all quotas on that tablespace, because if you later create a tablespace with the same name, the old quotas are still active:

SQL> alter user <username> quota 0 on <tablespace_name>;
SQL> drop tablespace <tablespace_name> including contents and datafiles;

If you want to rename also its datafile(s) you have to do it via SQL*Plus and linux console:

SQL> ALTER TABLESPACE test2 OFFLINE;
linux> mv 'oldfile.dbf' 'newfile.dbf'
SQL>  ALTER TABLESPACE test2 RENAME datafile '/path_to_old_datafile/oldfile.dbf' TO '/path_to_new_datafile/newfile.dbf'

SQL>  ALTER TABLESPACE test2 ONLINE;
Categories: oracle, SQL*Plus Tags: , , ,