Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: more 8.1: defrag the drive

Re: more 8.1: defrag the drive

From: joel garry <joel-garry_at_home.com>
Date: 26 Jan 2007 10:58:57 -0800
Message-ID: <1169837937.055641.289260@m58g2000cwm.googlegroups.com>

On Jan 26, 9:41 am, "mmccaws2" <mmcc..._at_comcast.net> wrote:
> On Jan 26, 2:23 am, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
>
>
>
>
>
> > On Jan 25, 3:27 am, "mmccaws2" <mmcc..._at_comcast.net> wrote:
>
> > > The drive where the 8.1 Oracle database files reside is about 70% full,
> > > about 5GB drive. The disk analysis shows that there is over 40k
> > > fragments on the drive. One of the tables is about 2Gb. Coworker has
> > > a good suggestion that may reduce down time, it sounds good. I'm
> > > looking for whether or not it's fraught with potential problems.
>
> > > let me know what you think
>
> > > 1st - shut down app and stop Oracle
> > > 2nd - copy two largest files(combined about 3Gb) to another drive.
> > > 3rd - (on Windows 2000 server) defrag drive using windows defrag
> > > 4th - copy two files back to drive
> > > and at last restart oracle and app
>
> > > So, there should not be any problems with the database.
>
> > > ThanksIf it were me, I would search for the listener log. You will likely
> > find that the listener log is quite a large file, yet started life as a
> > very small file. As the file grew in size, it became severely
> > fragmented. To see if this file is the source of most of the 40,000
> > fragments on the drive, shut down the listener when few people are
> > using the system (hint: search for LSNRCTL in the documentation), and
> > copy the listener log file to another drive. After the file is copied,
> > delete it from its original location. If you then copy the listener
> > log file back to its original location, the file will be defragmented
> > automatically, assuming that there is a large enough area of adjacent
> > free space on the drive and the drive is formatted with NTFS. After
> > the file is copied back, restart the listener. Note: you do not _need_
> > to copy the listener log file back to its original location, as a new
> > file will be automatically created.
>
> > After fixing the listener log file, you should see the number of file
> > fragments drop considerably. If the number of file fragments still
> > appears to be a problem (and no users are connected to the databases
> > AND you have a good backup), connect to each database using SQLPLUS and
> > execute SHUTDOWN IMMEDIATE; Once each database is down, shut down the
> > listener, and all Oracle related services in the Control Panel Services
> > applet. Copy (do not cut and paste) the Oracle related folders to
> > another drive. Once copied, verify that the files have been moved,
> > then delete the Oracle related folders from their original locations.
> > Then copy the Oracle related folders from the other drive back to their
> > original locations. Once complete, restart the server (easier to
> > explain and accomplish than restarting all Oracle related items).
>
> > No defrag is needed in the above approach. The only operation with
> > risk is the copy of the Oracle related files to another drive, followed
> > by a deleted.
>
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.Thanks for the details. I took the advice of some of the other
> posters, and have been making queries to see if I can find out what if
> there is a particular entry source that is most common. I have found
> on item. Now I'm trying to get the vendor to provide how the table
> fields are created so I can better determine the entries. I found that
> one name appeared in over 200,000 entries of the half meg records.
> Since this table was truncated earlier this week, that seems worth
> looking into.
>
> I'll discuss your aproach with the SE that has been helping me.

Charles made some good suggestions. (I tend to forget about the listener
log since my configuration rarely uses the listener, and in other configurations
I like to rotate the log automatically).

Truncating a table releases space within the tablespace, but does not remove data file space from the disk.

A couple of important things:

It is possible for user objects to be put into the system tablespace. This
is a very bad thing. Use the following query:

select index_name,owner from dba_indexes where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM')
/

If there is anything there owned by any users, you need to get a DBA in there
immediately to start fixing it. Also check table_name from dba_tables.

If you've made many thousands of extents in DMT tablespaces, that makes certain tables in the dictionary very large. While the space can be reused
after releasing (like with a truncate), it is possible that these tables can wind up
sparsely populated and cause performance issues at odd times.

The way to determine if a tablespace is DMT or LMT:

select tablespace_name, extent_management from dba_tablespaces;

If extent_management is LOCAL, that's an LMT, a good thing, you don't have to worry about extents much. Otherwise, you need a dba.

(Lurkers note, some of the above is specific to 8i, though it can apply to later versions, there are additional considerations).

jg

--
@home.com is bogus.
Googlebomb still works:
http://accordionguy.blogware.com/blog/_archives/2007/1/26/2684989.html
Received on Fri Jan 26 2007 - 12:58:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US