OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » fragmentation (oracle10g EE on linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

fragmentation [message #561681]

Tue, 24 July 2012 13:13

gauravsrivastava
Messages: 22
Registered: November 2011
Location: india

Junior Member

Hi All,

My current PROD database size is 1000GB and in every week we are purging some transactional tables using delete operations, at least 15 tables is a part of this purge and having lob segment on one column.one tablespace(450Gb) webmdata having 17 datafiles hold all the data of these tables.Currently the tablespace is now 75% of usages and it shows approx 90GB free.We decided to shrink all the tables involve in purging one by one in every week maintenance window and in first attempts we release 35GB of space from one tables and now our tablespace used % is down from 75% to 66%. But our problem is we are not able to shrink our datafiles under this tablespace even its show 10gb Free space in all 17 datafiles.

Please provide your Advise to shrink my datafiles , because we need to release space from disk .

Thanks in Advanced.

Gaurav

Report message to a moderator

Re: fragmentation [message #561683 is a reply to message #561681]

Tue, 24 July 2012 13:16

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ export/drop/import
2/ create a new tablespace/move objects in it/drop old tablespace

Regards
Michel

Report message to a moderator

Re: fragmentation [message #561684 is a reply to message #561683]

Tue, 24 July 2012 13:24

gauravsrivastava
Messages: 22
Registered: November 2011
Location: india

Junior Member

Hi Michel,

Thanks for the quick response.

I know these two options but our problem is we do not take a long maintenance window.

I am not able to understand if the space released from tablespace level the why we are not able to minimize our datafiles at lower level. like my datafile size is 31000MB and used is only 20000MB , and we are not able to shrink datafile at 25000MB.

Thanks Gaurav

Report message to a moderator

Re: fragmentation [message #561685 is a reply to message #561684]

Tue, 24 July 2012 13:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

HIGH WATER MARK
Existing Object1
F
R
E
E

S
P
A
C
E
Existing object2

The High Water Mark (HWM) can not be lowered any farther than the highest block for any existing object.
If you move Existing Object1 into different tablespace, then HWM can be lowered to Existing Object2
Understand now?

Report message to a moderator

Re: fragmentation [message #561686 is a reply to message #561685]

Tue, 24 July 2012 13:46

gauravsrivastava
Messages: 22
Registered: November 2011
Location: india

Junior Member

Thanks

I understand your point.

now i need to plan Move table , but i think it going to be very difficult to manage in short maintenance window.

Can you just provide me an approx time to move 41GB object from one tablespace to another and back again or provide me the fastest way to do this , currently i have 8 CPU machine with 2 node RAC.

Thanks Gaurav

Report message to a moderator

Re: fragmentation [message #561689 is a reply to message #561686]

Tue, 24 July 2012 14:13

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Nobody can provide figures for your hardware.
The fastest way (given you cannot do it in one shot) is to move the last object(s) in the file into another tablespace, shrink the file and go on until you reach the last object and can delete the original tablespace.

Regards
Michel

Report message to a moderator

Re: fragmentation [message #561700 is a reply to message #561689]

Tue, 24 July 2012 17:18

alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California

Senior Member

I usually try and move all the objects in a tablespace to another tablespace. This way I can reclaim all the free space. In the following example we see that the "alter table move" command does not usually move the table to the lowest available block making it hard to re-claim space unless you move the objects to another tablespace.

ECSCDAD3 > @dt user

INSTANCE_N INC_MEG AUT TABLESPACE_NAME      ID  STATUS CREATION_ MAX_MEG MEG       STATUS NAME
---------- ------- --- -------------------- --- ------ --------- ------- --------- ------ ------
CSCDAD        1000 YES USERS                4   ONLINE 17-SEP-11   32768 207       ONLINE /u02/app/oracle/oradata/CSCDAD/users01.dbf

ECSCDAD3 > list
  1  select instance_name,ddf.increment_by*8192/1024/1024 inc_meg,ddf.autoextensible,
  2  ddf.tablespace_name,to_char(vd.file#) id,vd.status,vd.creation_time,
  3  ddf.maxbytes/1024/1024 max_meg,to_char(vd.bytes/1024/1024) meg,vd.status,vd.name
  4  from dba_data_files ddf, v$datafile vd,v$instance
  5  where tablespace_name like upper('%&1%') and ddf.file_name=vd.name
  6* order by autoextensible,CREATION_TIME
ECSCDAD3 > @mapperi 4

OWNER         OBJECT          FILE_ID   BLOCK_ID     BLOCKS
------------- --------------- ------- ---------- ----------
free space                          4        128      13312
ECSCDAD3 TABL ALAN10.               4      13440          8
ECSCDAD3 TABL ALAN100.              4      13448          8
ECSCDAD3 TABL ALAN_LOCAL.           4      13456          8
free space                          4      13464        112
ECSCDAD3 TABL ALAN_REMOTE2.         4      13576          8
free space                          4      13584      12912

ECSCDAD3 > list
  1  select     /*+ Rule */     'free space' owner      /*"owner" of free space*/
  2  ,          '         ' object              /*blank object name*/
  3  ,          file_id                         /*file id for the extent header*/
  4  ,          block_id                        /*block id for the extent header*/
  5  ,          blocks                          /*length of the extent, in blocks*/
  6  from               dba_free_space
  7  where              file_id=&1
  8  union
  9  select     /*+ Rule */ substr(owner,1,20)||' '||substr(segment_type,1,9) /*owner name (first 20 chars)*/
 10  ,          substr(segment_name,1,32)||'.'||partition_name  /*segment name*/
 11  ,          file_id                         /*file id for the extent header*/
 12  ,          block_id                        /*block id for the extent header*/
 13  ,          blocks                          /*length of the extent, in blocks*/
 14  from               dba_extents
 15  where              file_id=&1
 16* order by   3,4

ECSCDAD3 > select 13584*8192/1024/1024 from dual;

13584*8192/1024/1024
--------------------
             106.125

ECSCDAD3 > alter database datafile '/u02/app/oracle/oradata/CSCDAD/users01.dbf' resize 107m;

Database altered.

ECSCDAD3 > @mapperi 4

OWNER         OBJECT          FILE_ID   BLOCK_ID     BLOCKS
------------- --------------- ------- ---------- ----------
free space                          4        128      13312
ECSCDAD3 TABL ALAN10.               4      13440          8
ECSCDAD3 TABL ALAN100.              4      13448          8
ECSCDAD3 TABL ALAN_LOCAL.           4      13456          8
free space                          4      13464        112
ECSCDAD3 TABL ALAN_REMOTE2.         4      13576          8
free space                          4      13584        112

ECSCDAD3 > drop table ALAN_REMOTE2 purge;

Table dropped.

ECSCDAD3 > drop table ALAN_LOCAL purge;

Table dropped.

ECSCDAD3 > drop table ALAN100 purge;

Table dropped.

ECSCDAD3 > @mapperi 4

OWNER         OBJECT          FILE_ID   BLOCK_ID     BLOCKS
------------- --------------- ------- ---------- ----------
free space                          4        128      13312
ECSCDAD3 TABL ALAN10.               4      13440          8
free space                          4      13448        248

ECSCDAD3 > select 13448*8192/1024/1024 from dual;

13448*8192/1024/1024
--------------------
            105.0625

ECSCDAD3 > alter database datafile '/u02/app/oracle/oradata/CSCDAD/users01.dbf' resize 106m;

Database altered.

ECSCDAD3 > @mapperi 4

OWNER         OBJECT          FILE_ID   BLOCK_ID     BLOCKS
------------- --------------- ------- ---------- ----------
free space                          4        128      13312
ECSCDAD3 TABL ALAN10.               4      13440          8
free space                          4      13448        120

ECSCDAD3 > alter table alan10 move;

Table altered.

ECSCDAD3 > @mapperi 4;

OWNER         OBJECT          FILE_ID   BLOCK_ID     BLOCKS
------------- --------------- ------- ---------- ----------
free space                          4        128      13320
ECSCDAD3 TABL ALAN10.               4      13448          8
free space                          4      13456        112

ECSCDAD3 > alter table alan10 move;

Table altered.

ECSCDAD3 > @mapperi 4;

OWNER         OBJECT          FILE_ID   BLOCK_ID     BLOCKS
------------- --------------- ------- ---------- ----------
free space                          4        128      13312
ECSCDAD3 TABL ALAN10.               4      13440          8
free space                          4      13448        120

ECSCDAD3 > alter table alan10 move tablespace clubs;

Table altered.

ECSCDAD3 > @mapperi 4

OWNER         OBJECT          FILE_ID   BLOCK_ID     BLOCKS
------------- --------------- ------- ---------- ----------
free space                          4        128      13440

ECSCDAD3 > alter database datafile '/u02/app/oracle/oradata/CSCDAD/users01.dbf' resize 1m;
alter database datafile '/u02/app/oracle/oradata/CSCDAD/users01.dbf' resize 1m
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required

ECSCDAD3 > alter database datafile '/u02/app/oracle/oradata/CSCDAD/users01.dbf' resize 2m;

Database altered.

ECSCDAD3 > @mapperi 4

OWNER         OBJECT          FILE_ID   BLOCK_ID     BLOCKS
------------- --------------- ------- ---------- ----------
free space                          4        128        128

ECSCDAD3 > alter table alan10 move tablespace users;

Table altered.

ECSCDAD3 > @mapperi 4

OWNER         OBJECT          FILE_ID   BLOCK_ID     BLOCKS
------------- --------------- ------- ---------- ----------
ECSCDAD3 TABL ALAN10.               4        128          8
free space                          4        136        120

ECSCDAD3 > @dt users

INSTANCE_N INC_MEG AUT TABLESPACE_NAME      ID  STATUS CREATION_ MAX_MEG MEG       STATUS NAME
---------- ------- --- -------------------- --- ------ --------- ------- --------- ------ ------------------------------------------
CSCDAD        1000 YES USERS                4   ONLINE 17-SEP-11   32768 2         ONLINE /u02/app/oracle/oradata/CSCDAD/users01.dbf

Report message to a moderator

Re: fragmentation [message #561701 is a reply to message #561700]

Tue, 24 July 2012 17:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Please provide your Advise to shrink my datafiles ,
Don't bother doing anything. Oracle will reuse the space as new rows come into the table.

>because we need to release space from disk .
If you do then Oracle will expend resources to grow the table as new data gets inserted.

Oracle DB should not be treated like an accordion that needs to shrink & expand on a regular basis.

Report message to a moderator

Re: fragmentation [message #561702 is a reply to message #561701]

Tue, 24 July 2012 17:29

alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California

Senior Member

Good point BlackSwan,
So Gaurav, if you make fewer tablespaces, the free space will be shared amungst schemas and there will be no need to re-claim space.

Report message to a moderator

Re: fragmentation [message #561705 is a reply to message #561702]

Tue, 24 July 2012 18:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://docs.oracle.com/cd/E11882_01/server.112/e25789/logical.htm#CNCPT89022

Report message to a moderator

Re: fragmentation [message #561846 is a reply to message #561705]

Wed, 25 July 2012 12:41

gauravsrivastava
Messages: 22
Registered: November 2011
Location: india

Junior Member

Thanks to all of you for your valuable Advise

Report message to a moderator

Re: fragmentation [message #568421 is a reply to message #561681]

Thu, 11 October 2012 07:04

gauravsrivastava
Messages: 22
Registered: November 2011
Location: india

Junior Member

Dear All,

Thank you to all on supporting me on my above mention problem, i have one more doubts . In my database one of my transaction table A having 120GB (20GB data and 100GB is LOB), we can not reclaim space from this table using EXP/IMP because of some time limitation, that is why i am trying to use the below approach .

Let us assume the table name is A and one of a column under this table B is a LOB and we retain at least 45 days of data in this table.

First we rename this to to A_old
then create a new table A as same structure like A_OLD
Then up our Application and webmethod
Wait for next 45 days.

after 45 days when we drop our A_OLD table completely , then can we resize(Decrease) our datafiles up to 100 GB?

Current tablespace size is :490GB
Used:387 GB

Thanks
Gaurav

Report message to a moderator

Re: fragmentation [message #568422 is a reply to message #568421]

Thu, 11 October 2012 07:19

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Create 2 tablespaces and flip-flop between them. You can then resize to almost 0 the tablespaces that contained the dropped table(s).

Regards
Michel

Report message to a moderator

Previous Topic:	Advantages of using DBMS_schedular ovet dbms_job
Next Topic:	How to access Active directory with Oracle database.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 28 23:33:53 CST 2024