Fragmentation in datafiles
I have database on Linux having size of 23 GB. In Linux you can not create file more than 2GB, I have 12 data files and all of them having some data but not fully utilized now I want to coalecse the frgmented space how do I proceed?
There is one option is that I can export the whole database and import it back but will it solve my problem I am not sure about it please help me.
One more thing I want to ask that the percentage use of system file is almost near to 100 now what do you recomend, Shall I increase the sixe of the file or not as well as tell me one more thing what ahould be the size of the system file.
Thanks in Advance
To find if the Tablspace is fragmented:
SELECT TABLESPACE_NAME, TOTAL_EXTENTS, EXTENTS_COALESCED,
PERCENT_EXTENTS_COALESCED < 100
This will give the list of tablespaces that are fragmented. Then these tablespaces have to be coalesced to de-fragment them.
To de-fragment, you may use --
ALTER TABLESPACE (Tablespace Name) COALESCE;
Alternatively, you can set the PCTINCREASE of the tablespace to a non-zero value, so that SMON can automatically wake up to detect fragments and coalesce them.
Hope this helps.
Last edited by quester; 11-21-2002 at 01:04 AM.
Well i dont really get it...Linux cannot have a file greater than 2Gb...which version of the kernel are you running ?...if you are running a very old kernel ask your Linux admin to install a new kernel...
what version of database are you running...if you are on oracle8i then you create locally managed tablespaces and then do alter table table name move tablepsace and then rebuild your indexes...no need for cumbersome export import..
Thanks to both of you but my problem was I want to move data from all those datafiles which is having very less percentage in use I want to transafer all data into other existing datfile so will export and import solve htis problem or not?
And what about the System File do I need to increase the size of system file or not?
I am using Linux Red Hat 6.2 Oracle Server (220.127.116.11.0)
Well i suggest two things...
1)Take a cold backup of your database ask your sys admin to install redhat Linux 7.3 which ships with newer version of kernel 2.4 which has support for files greater than 2G
2)There is no need to do export import at all..
1)just create new locally_managed_tablespaces
2)Alter table tablename move locally_managed_tablepsaces
3)Rebuild your indexes in parallel
Definately the 2 method is much much faster and easier...You can plan this during weeknds when there is no much load on the database..
I will do the same
now can you please tell me about the system files which I ask you in my provious message, it always flucating bewteen 95% to 99% use.
Unfortunately for the system tablespace you cannot do anything...(excpet a hack of editing sql.bsq script which i dont advise on production systems).You can just add a datafile to that tablepsace...and just forget about that system tablespace...and do not create ant user objects into that tablespace and everything would be fine..
Thanks alot for the help
Click Here to Expand Forum to Full Width