topic Re: tablespace fragmentation in Operating System - HP-UX

tablespace fragmentation

steven chang_1 — Wed, 24 Dec 2003 23:18:31 GMT

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

Re: tablespace fragmentation

Steven E. Protter — Wed, 24 Dec 2003 23:27:50 GMT

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

Re: tablespace fragmentation

steven chang_1 — Thu, 25 Dec 2003 01:49:58 GMT

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

steven chang

Re: tablespace fragmentation

Yogeeraj_1 — Thu, 25 Dec 2003 02:14:08 GMT

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

Re: tablespace fragmentation

zhuchao — Fri, 26 Dec 2003 03:11:03 GMT

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.

Re: tablespace fragmentation

Jan Notredame — Fri, 26 Dec 2003 07:04:49 GMT

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.