Tnks for your solution, caffeine-addict!

> Are you deallocating Unused Space or wanting to Shrink the data back to
> beginning of the tablespace?

Yes, since the size of the datafile as well as the dump file is huge, the
storage for the backups is not efficiently used, caffeine-addict.
What I wanted is to have my backups as small as they should. Let me explain
a little bit more details about my problem, guy.

I have not so many experiences working as an oracle dba and I designed the
system to have several jobs pooling the data from a database outside through
a database link. The changes daily of the datasource is big and I have to
monitor these changes and do updates/inserts as appropriate to the database
on my side. Such the operations make the data files size become
irreasonable. Job that pool data is done by a PL/SQL procedure and I have
policy to frequently run it, say 30 mins interval. Database version is
9.0.2.

It became troublesome several months ago when the data was available again
(the 'data provider' system was reconstructed so the database link was dead
during that time) and the job was re-activated. I guessed that the problem
lied in the amount of data that need to be updated/inserted makes the whole
thing sucked. Could you guys please guide me through that ocean since I've
lost my way...

Best regards,

MinhTD


----- Original Message -----
From: "caffeine-addict"
<oracledba-ezmlmshield-x14993516.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Thursday, September 06, 2007 10:42 AM
Subject: Re: Shrink huge datafiles size


> Are you deallocating Unused Space or wanting to Shrink the data back to
> beginning of the tablespace?
>
> Segment shrink is an online, in-place operation. DML operations and
> queries can be issued during the data movement phase of segment shrink.
> Concurrent DML operation are blocked for a short time at the end of the
> shrink operation, when the space is deallocated. Indexes are maintained
> during the shrink operation and remain usable after the operation is
> complete. Segment shrink does not require extra disk space to be
> allocated.
>
> Segment shrink requires that rows be moved to new locations. Therefore,
> you must first enable row movement in the object you want to shrink and
> disable any rowid-based triggers defined on the object. You enable row
> movement in a table with the ALTER TABLE ... ENABLE ROW MOVEMENT command.
>
> Some examples:
> Shrink a table and all of its dependent segments (including LOB
> segments):ALTER TABLE employees SHRINK SPACE CASCADE;
> Shrink a LOB segment only:ALTER TABLE employees MODIFY LOB (perf_review)
> (SHRINK SPACE);
>
> When you deallocate unused space, the database frees the unused space at
> the unused (high water mark) end of the database segment and makes the
> space available for other segments in the tablespace.
>
> Some examples:
> ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
> ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
> ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;
>
>
> LS
>
> "640KB ought to be enough for anybody." - Bill Gates, 1981
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>



Oracle LazyDBA home page