Do you delete larege amounts of data from these tables, if not then you will hardly reclaim any space.
As for reducing the datafile size you need to find which extents are at the end of the datafile you can then rebuild them so it will move the extents
Use the script below:
SET VERIFY OFF
PROMPT This database contains the following datafiles:
SELECT file_name FROM dba_data_files;
ACCEPT fname PROMPT 'Specify a datafile whose contents are to be determined: '
TTITLE CENTER 'Database Segments in File &fname' SKIP 2
COLUMN stype FORMAT A15 JUSTIFY C HEADING 'Segment Type
COLUMN sown FORMAT A15 JUSTIFY C HEADING 'Segment Owner'
COLUMN sname FORMAT A40 JUSTIFY C HEADING 'Segment Name'
BREAK ON stype SKIP 1 ON sown SKIP 1
SELECT e.segment_type stype,
e.owner sown,
e.segment_name sname
FROM dba_extents e,
dba_data_files f
WHERE f.file_name = '&fname'
AND e.file_id = f.file_id
ORDER BY e.segment_type, e.owner, e.segment_name;
hi,
Yesterday on our testing database we deleted the data from table about 10 GB. After that free space in data tablespace got increased . Then i rebuilded the indexes to remove the deleted entries from indx tablespace. but i want free space in indx tbs also.
We've taken backup of tht data so that i future if we require tht we can restore it.
Whether is it a good way to drop and recreate tht indexes so tht we get free space in indx tablespace?
Does it cause any loss?
Please guide me.
This is an extract from another forum "Ask Tom", so thanks Tom.
-------------------------------------------------------------
So, what do you think was happening here? Well, the fact is that indexes, like
people, have a certain "weight" they like to be at. Some of us are chubby --
some skinny -- some tall -- some short. Sure, we can go on a diet -- but we
tend to gravitate BACK to the weight we were. The same is true for indexes --
what happened to this person is their index wanted to be wide and fat and EVERY
MONTH they rebuilt it (put it on a diet). It would spend the first half of the
month then getting fat again and generating gobs of redo due to the block splits
it was undergoing to get there.
In this case, rebuilding the index on their system had these effects:
o the system would generate 4.5 times the redo
o the system would run slower
o the system would consume more resources (CPU, IO, latching, etc)
o the system would not be able to handle the same user load
until the system got back to where the system actually wanted to be. And then
-- AND THEN -- they (the dba's) would do it all over again!!!! They would
destroy the equilibrium that the system worked so hard to get to. Bravo!!
(can you tell what my opinion is on regularly scheduled index rebuilds??? I
despise them).
If you are suffering from fragmentation -- your problem is not the index
rebuilds. It would be because you are using DICTIONARY managed tablespaces.
Switch over to locally managed tablespaces and you'll never have fragmentation
again. So, my suggestion -- one last index rebuild:
o create a locally managed tablespace
o alter index rebuild tablespace LMT_FROM_ABOVE storage ( initial 1k );
and then forget about them. You will rebuild an index in response to IDENTIFIED
and DEGRADED performance -- period. You will not rebulid indexes based on a
schedule. Period.
It is the RARE index that needs to be rebuilt.
It is not a rule that indexes need to be rebuilt.
o create a locally managed tablespace
o alter index rebuild tablespace LMT_FROM_ABOVE storage ( initial 1k );
--------------------------------------------
consider this as first option.
is this a composite/bitmap?
Yesterday i fired following qury to rebuild index:
alter index index_name rebuild ONLINE
Then i got these errors:
ERROR at line 1:
ORA—01652: unable to extend tenp segnent by 256 in tablespace TEMP
ORA—27059: skqfrsz: could not reduce file size
OSD—O4005: SetrilePointer() failure, unable to read fron file
0/S—Error: (05 112) There is not enough space on the disk.
My temporay tablespace is 2 GB and free space in index tablespace is 40 GB and the index is of 10 GB.
But free space on the drive where temp01.dbf is reside is 760 kb . but before tht i've removed 4 GB of datafiles to another drive. Still its showing 760 KB. Why it is so? I will add tempfile to tht temp tablespace to anothr drive and will try again.
Now if i give command
alter index index_name rebuild unrecoverable tablespace indx, then whether it will give me same error?thanks in advance.
Last edited by minal_yawale; 08-31-2006 at 02:07 AM.
Reply With Quote
Originally Posted by robertbalmer
Bookmarks