?
Solved

Why fragmented SYSTEM tablespace ?

Posted on 2001-06-17
6
Medium Priority
?
1,786 Views
Last Modified: 2012-06-27
I have just created a new database with a system tablespace sized at 130 MB.  Even though it is only 25% full and does not contain any auditing records it is still fragmented.  Why ?   Do I dare try to defragment it ?

                                            Fragmented segments with more than 2 extents

OWNER                SEGMENT_NAME                      SEGMENT_TYPE         EXTENTS
-------------------- --------------------------------- ----------------- ----------
SYS                  SOURCE$                           TABLE                     11
SYS                  SYSTEM                            ROLLBACK                  10
SYS                  IDL_UB2$                          TABLE                     10
SYS                  C_OBJ#                            CLUSTER                    9
SYS                  I_SOURCE1                         INDEX                      9
SYS                  IDL_UB1$                          TABLE                      9
SYS                  VIEW$                             TABLE                      7
SYS                  I_COL1                            INDEX                      6
SYS                  I_OBJ2                            INDEX                      6
SYS                  I_OBJAUTH1                        INDEX                      6
SYS                  IDL_SB4$                          TABLE                      6
SYS                  OBJ$                              TABLE                      6
SYS                  C_FILE#_BLOCK#                    CLUSTER                    5
SYS                  C_USER#                           CLUSTER                    5
SYS                  I_ARGUMENT1                       INDEX                      5
SYS                  I_OBJAUTH2                        INDEX                      5
SYS                  ARGUMENT$                         TABLE                      5
SYS                  COM$                              TABLE                      5
SYS                  OBJAUTH$                          TABLE                      5
SYS                  C_TS#                             CLUSTER                    4
SYS                  I_COL2                            INDEX                      4
SYS                  I_COM1                            INDEX                      4
SYS                  I_DEPENDENCY2                     INDEX                      4
SYS                  ACCESS$                           TABLE                      4
SYS                  DEPENDENCY$                       TABLE                      4
SYS                  IDL_CHAR$                         TABLE                      4
SYS                  TRIGGER$                          TABLE                      4
SYS                  I_CON1                            INDEX                      3
SYS                  I_DEPENDENCY1                     INDEX                      3
SYS                  I_OBJ1                            INDEX                      3
SYS                  I_SYSAUTH1                        INDEX                      3
SYS                  SEQ$                              TABLE                      3
SYS                  SYN$                              TABLE                      3
0
Comment
Question by:EugeneGardner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 2

Expert Comment

by:starous
ID: 6199903

Hi

I think it is looking good. One of my db is in state like that

segment          bytes          extents

I_SOURCE1     418914304     26
SOURCE$          953606144     23
IDL_UB1$     1333714944     23
IDL_UB2$     302833664     22
ARGUMENT$     107454464     17


and we can see no negative impact on performance.

 Talking about fragmentation is typical topic of big flamewars.



Staorous
0
 
LVL 1

Expert Comment

by:forquestion
ID: 6200385
When create new Oracle DB, both users system or sys temporary tablespace are system. Also, the default tablespace for 1st rollback segment is system. Both will cause the system tablespace becomes fragmented
0
 

Expert Comment

by:johnnyce
ID: 6200613
Hi, Eugene,

Fragmentation per se of the SYSTEM tablespace does not seem to be an issue, unless you have encountered a specific problem because of it.  Compared with the sizes of data tablespaces, the SYSTEM tablespace is usually very small, and the amount of I/O involved is also comparatively small.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Expert Comment

by:rmyufa
ID: 6202387
Hi!
The fragmentation is in objects, not in free space.
This fragmentation is result of starage clause - INITIAL EXTENT and NEXT EXTENT.
When the initial extent is full, then the next data will storaged in next extent - this next storage portion.
You can "defragment" object with re-creating it with new storage clause. For objects in tablespace SYSTEM this is not recomendet!
The another way is to make full export with compress extents and then make full import.

Regards,
pm
0
 
LVL 3

Accepted Solution

by:
myerpz earned 300 total points
ID: 6203373
My advice would be to leave it alone and not worry about it. Read the white paper : "How to stop defragmenting and start living" in the Internet DBA section of Oracle Technet and you will see what I mean.
0
 
LVL 1

Author Comment

by:EugeneGardner
ID: 6204238
Thanks for the replies.  I will be reading through the article Myerpz mentioned.  I eventually found it at:

http://technet.oracle.com/deploy/availability/pdf/defrag.pdf
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question