1748282 Members
4070 Online
108761 Solutions
New Discussion юеВ

tablespace fragmentation

 
SOLVED
Go to solution
steven chang_1
Regular Advisor

tablespace fragmentation

hello:

When I query dba_free_space to check bytes column, and I get
TABLESPACE_NAME BYTES
-------------------- ----------
SYSTEM 57344
SYSTEM 32768
SYSTEM 9854976
SYSTEM 52420608
TEMP 122880
TEMP 122880
TEMP 1843200
RBS1 1040384
TOOLS 1040384
OPC_TEMP 1024000
OPC_TEMP 1064960


and then I change the sql statement,
select tablespace_name,count(bytes)
from dba_free_spaces
group by tablespace_name

here the resultTABLESPACE_NAME COUNT(BYTES)
------------------------------ ------------
OPC_1 1
OPC_10 1
OPC_2 1
OPC_3 1
OPC_4 1
OPC_5 1
OPC_6 1
OPC_7 1
OPC_8 1
OPC_9 1
OPC_INDEX1 1

TABLESPACE_NAME COUNT(BYTES)
------------------------------ ------------
OPC_INDEX2 1
OPC_INDEX3 1
OPC_TEMP 2
RBS1 1
SYSTEM 4
TEMP 3
TOOLS 1

system ,temp,opc_temp got a value more than one, and metalink user says that it means tablespace get fragmentation.I am wondering why it is, and what is tablespace fragmentation
? I have an idea about "table fragmentation", but for tablespace, I really don't know.If you know about this , please give me some hints.
Thank you!!

steven chang


steven
5 REPLIES 5
Steven E. Protter
Exalted Contributor
Solution

Re: tablespace fragmentation

Oracle tables get fragmented over time, depending on how much writing and re-arranging gets done.

Every 3-6 months our DBA takes the databases offline and defragments them.

I personally have no idea what the procedure is though. That should be found by searching for defragment on metalink.oracle.com or technet.oracle.com

If you celebrate the holidays I hope they are good to you.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
steven chang_1
Regular Advisor

Re: tablespace fragmentation

here is the sql statement to tell if any tablespaces need defragmentation

steven chang
steven
Yogeeraj_1
Honored Contributor

Re: tablespace fragmentation

Merry Christmas Steven!

Start using Locally managed tablespaces (except for system tablespaces if before 9i) and say goodbye to fragmentation problem.

if you need further help, let us know

ho ho ho
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
zhuchao
Advisor

Re: tablespace fragmentation

Consider why do it before doing it.
Take a look at the following papers:

http://otn.oracle.com/deploy/availability/pdf/defrag.pdf

http://otn.oracle.com/deploy/availability/pdf/oow2000_same.pdf

Defragment is not necessary as most people think.
www.happyit.net
Jan Notredame
New Member

Re: tablespace fragmentation

All about Oracle database fragmentation in
http://www.orapub.com/cgi/genesis.cgi?p1=sub&p2=abs121
Tablespace freespace fragmentation is not a big issue. Check for row fragmentation.