Home » RDBMS Server » Server Administration » fragmentation (oracle10g EE on linux)
fragmentation [message #561681] Tue, 24 July 2012 13:13 Go to next message
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
Re: fragmentation [message #561683 is a reply to message #561681] Tue, 24 July 2012 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 57645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ export/drop/import
2/ create a new tablespace/move objects in it/drop old tablespace

Regards
Michel
Re: fragmentation [message #561684 is a reply to message #561683] Tue, 24 July 2012 13:24 Go to previous messageGo to next message
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
Re: fragmentation [message #561685 is a reply to message #561684] Tue, 24 July 2012 13:29 Go to previous messageGo to next message
BlackSwan
Messages: 21968
Registered: January 2009
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?
Re: fragmentation [message #561686 is a reply to message #561685] Tue, 24 July 2012 13:46 Go to previous messageGo to next message
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
Re: fragmentation [message #561689 is a reply to message #561686] Tue, 24 July 2012 14:13 Go to previous messageGo to next message
Michel Cadot
Messages: 57645
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: fragmentation [message #561700 is a reply to message #561689] Tue, 24 July 2012 17:18 Go to previous messageGo to next message
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
Re: fragmentation [message #561701 is a reply to message #561700] Tue, 24 July 2012 17:24 Go to previous messageGo to next message
BlackSwan
Messages: 21968
Registered: January 2009
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.
Re: fragmentation [message #561702 is a reply to message #561701] Tue, 24 July 2012 17:29 Go to previous messageGo to next message
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.
Re: fragmentation [message #561705 is a reply to message #561702] Tue, 24 July 2012 18:52 Go to previous messageGo to next message
BlackSwan
Messages: 21968
Registered: January 2009
Senior Member
http://docs.oracle.com/cd/E11882_01/server.112/e25789/logical.htm#CNCPT89022
Re: fragmentation [message #561846 is a reply to message #561705] Wed, 25 July 2012 12:41 Go to previous messageGo to next message
gauravsrivastava
Messages: 22
Registered: November 2011
Location: india
Junior Member
Thanks to all of you for your valuable Advise
Re: fragmentation [message #568421 is a reply to message #561681] Thu, 11 October 2012 07:04 Go to previous messageGo to next message
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

Re: fragmentation [message #568422 is a reply to message #568421] Thu, 11 October 2012 07:19 Go to previous message
Michel Cadot
Messages: 57645
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Advantages of using DBMS_schedular ovet dbms_job
Next Topic: How to access Active directory with Oracle database.
Goto Forum:
  


Current Time: Thu Apr 24 09:53:19 CDT 2014

Total time taken to generate the page: 0.14302 seconds