Archive

Posts Tagged ‘datafile’

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: , , ,

Drop a datafile in oracle 10g RAC with ASM

August 11, 2010 Leave a comment

By mistake, i added a datafile in an ASM+ at a wrong location.

ALTER TABLESPACE "TEST" ADD DATAFILE '+DG_MIKE_DATA1/mike_03.dbf' SIZE 1G

The correct ASM path would be ‘+DG_MIKE_DATA1/mike/datafile/mike_03.dbf’

Trying to solve the mistake, i took the datafile offline (not offline normal) using the Enterprise Manager console:

ALTER DATABASE DATAFILE '+DG_MIKE_DATA1/mike_03.dbf' OFFLINE;

When I tried to drop the datafile via sqlplus i get this:

SQL> ALTER TABLESPACE MIKE DROP DATAFILE '+DG_MIKE_DATA1/mike_03.dbf';
ERROR at line 1:
ORA-01113: file 76 needs media recovery
ORA-01110: data file 76:
'+DG_MIKE_DATA1/mike_03.dbf'

Thus, I’ve found this blog http://esemrick.blogspot.com/2006/03/recovery-of-offline-data-files.html and applied his knowledge to my mistake:

SQL> select status from v$datafile where file#=76;
STATUS
----------
RECOVER
SQL> recover datafile 76;

with all the suggested logs to take back offline the datafile:

SQL> select status from v$datafile where file#=76;
STATUS
----------
OFFLINE
SQL> alter database datafile 76 online;
SQL> alter tablespace TEST DROP DATAFILE 76;

And now I can re-create the datafile in it’s correct location:

ALTER TABLESPACE "TEST" ADD DATAFILE '+DG_MIKE_DATA1/mike/datafile/mike_03.dbf' SIZE 1G

[UPDATED 2010-08-17]

Be careful if you drop a datafile on your primary database (in my case a RAC database) and  you have a standby database (dataguard). Now I have the bug 5623467, “Corrupt redo from ALTER TABLESPACE DROP DATAFILE”, and I think the only workaround is to restore&recover the standby database from the primary database 😦

Categories: oracle Tags: , , , , , , ,