Maximum Extents - Query 1
From: GopalaKrishnan K [mailto:gopalak@wiproge.med.ge.com]
Sent: Saturday, June 12, 1999 6:58 AM
Subject: Entent Problem
Hi
Here we are using Oracle Financials 11.0
with oracle database 8.0.5.
I am getting MAXIMUM NUMBER OF EXTENTS REACHED
in some X table and Y index.
What is the best way to over come this problem
other than setting the MAX EXTENTS unlimited
Any light for this
Cheers!
Gopalakrishnan K
Date: Mon, 14 Jun 1999 08:27:47 -0400
From: "Cotten, Jim" Jim.Cotten@respironics.com
Subject: RE: Entent Problem
Managing extents is basic DBA work. Multiple extents are not
necessarily a problem, however the act of extending has some overhead
and exceeding maximum extents is a problem that should not happen.
Setting all tables and indexes to maximum extents is not a good solution
until you have analyzed the extent size for each table and index.
I recommend that you make extents sizes as multiples of a base size,
i.e. 16K, 64K, 128K, etc. This will make it easier for freed extents to
be used by another object. You can have serious extent problems with
less than maximum extents and plenty of free space in the tablespace if
the extents are not sized correctly.
Next, you should analyze the growth rate of your tables and indexes. If
you have objects which are extending frequently maybe you should
increase the next extents size to slow down the rate at which extents
are added. Make sure that the object is actually growing in number of
records added as object with a high addition and deletion rate need
different management than do objects which get rows added and few
deletions.
This is a very cursory answer to your problem. I recommend that someone
at your site get into the Oracle DBA classes as this is basic stuff. If
extents are a problem, you may have other issues which are more serious
but unknown. Get the Oracle DBA Handbook from Oracle Press, the Tuning
Oracle book and they might help you. Contact me directly if you have
further questions.
Maximum Extents - Query 2
Date: Tue, 3 Aug 1999 12:37:29 -0700
From: miked@artesynsolutions.com
Subject: max extents
My DBA is out and I have a max extent error.
Can someone help me with the script to fix this. I
know a little sql. My current setting for this
table is:
TABLE_NAME (MTL_MATERIAL_TRANSACTIONS)
INITIAL_EXTENT (16384)
NEXT_EXTENT (4194304)
MIN_EXTENT (1)
MAX_EXTENT (50)
PCT_INCREASE (0)
I would like to set the Max extent to 75.
I know the following:
Alter table MTL_MATERIAL_TRANSACTIONS
That's about it.
Thanks in advance.
Date: Tue, 3 Aug 1999 12:51:11 -0700
From: Karen Blackwell KBlackwell@rockshox.com
Subject: RE: max extents
Alter table MTL_MATERIAL_TRANSACTIONS storage (maxextents 75) ;
HTH.
Date: Tue, 3 Aug 1999 12:53:20 -0700
From: Karen Blackwell KBlackwell@rockshox.com
Subject: RE: max extents
Odds are pretty good (in my experience) that you will also run into a max
extents on the indexes for this table. I would advise checking them.
You may also need to check the amount of space available in your
table_space
(INV_DATA) as this *may* also lead to this error message.
Good luck.
Date: Tue, 03 Aug 1999 16:01:54 -0400
From: Mohan Thanikanti mthanika@camail2.harvard.edu
Subject: Re: max extents
Try this....
Alter table table_name
allocate extent size 5k;
Date: Tue, 03 Aug 1999 15:37:56 -0500
From: Michael Foret michaelforet@mindspring.com
Subject: Re: max extents
No... that is incorrect.. it is
alter table table_name storage(max_extents 75);
Mohan Thanikanti wrote:
Date: Tue, 3 Aug 1999 19:13:54 -0500
From: "Voss, Tracey Lee" tracey.voss@au.unisys.com
Subject: RE: max extents
ALTER TABLE 'table_name' MAXEXTENTS 75
Your next extent is 4Mb and you are adding 25 extra extents. This means you
are allowing this table to increase by another 100Mb...might pay to check
you will have room in your tablespace for that growth.
cheers
Tracey
Date: Wed, 4 Aug 1999 08:05:47 +0800 (SGT)
From: Rudy Wahab rudy@fcm.ed.fujitsu.co.jp
Subject: Re: max extents
Mike,
Other replies also work. But, Everytime I hit that kind of error,
I'll do this
alter table table_name
storage (maxextents unlimited)
This will allow maxextents to grow without limitation and I will
never hit that error again.
Good luck
Rudy Wahab Phone: 07-454 2111 ext 211/313/218
System Analyst email: rudy@fcm.ed.fujitsu.co.jp
Information Services Section
Fujitsu Components (M) Sdn. Bhd.
Batu Pahat, Johor.
Malaysia
Date: Wed, 4 Aug 1999 22:08:39 MYT-8
From: philip@aleytys.pc.my
To: Rudy Wahab rudy@fcm.ed.fujitsu.co.jp, oraapps-l@cpa.qc.ca
Subject: Re: max extents
On the other hand your DBA might decide to hit you with a clue-by-four.
Don't do that unless you really know what and why you are doing that.
Philip
p.s. How's the weather down in JB?
From: Rudy Wahab[SMTP:rudy@fcm.ed.fujitsu.co.jp]
Sent: Thursday, August 05, 1999 5:03 AM
Subject: Re: max extents
My DBA is myself. Do you have a good reason why my method is not
recommended? As far as I know, it will not do any harm on the system. But,
I may miss something here. Care to share it with me.
Date: Thu, 5 Aug 1999 11:22:49 -0400
From: "Mark W. Farnham" mwf@rsiz.com
Subject: RE: max extents
Rudy:
If your next extent size is very small you may create a performance
problem. If this causes reads smaller than the multiblock read size
(usually 64K) to be broken up, that would be the case. The other issue is
the sheer size of sys.uet$, where all extents are recorded.
This could particularly be the case in Oracle Applications where a module
was previously installed only for shared purposes with a very small size
factor and has now been upgraded to actual use.
These issues are unlikely to actually cause you problems, but you should
certainly examine the next extent size, pctincrease, and free space
available when you hit the prevailing maxextents of a segment. In general
it is a good idea to track the expansion of segments so that you can do
reasonable capacity planning..
In conjunction with a reasonable next extent size, etc., I don't know of
any problems with making the maxextents unlimited.
Mark
Extent monitoring / Conc. Mgr. Indexes
Date: Tue, 22 Jun 1999 10:33:30 +0300
From: mohammed alshaarawi m_sharawy@tccbce.com
Subject: Concurrent Manager Indexes
I have the following error
APP-01091 AFPPRD has encountered a fatal error. ORA-01654:
UNABLE TO EXTEND INDEX APPLSYS.FND_CONCURRENT_REQUESTS_N2
I solved this problem by SQL Plus by Dropping and createing the index.
But I am not satisfy with this problem, I would like to know when the index
will reach its maxetents.
Any help thanx
shaarawi
Date: Tue, 22 Jun 1999 06:53:17 -0400
From: "Cotten, Jim" Jim.Cotten@respironics.com
Subject: RE: Concurrent Manager Indexes
There is a canned alert provided by Oracle which will do this. Look for
the DBA alerts and activate it. Your DBA should be monitoring this type
of database activity.
Date: Tue, 22 Jun 1999 09:33:24 -0500
From: Michael Foret michaelforet@mindspring.com
Subject: Re: Concurrent Manager Indexes
make a script that issues the following command and cron it (or AT if on
NT), and have it mail you the results or just check it each day.
select * from dba_segments where (max_extents - extents) < 10;
This will tell you the name of all segment in the database that is
within 10 segments of reaching max_extents. From there, it's your
decision on how to fix it, but it will tell you about when it will
happen. What are your initial, next, and max_extents set to..?
HTH,
Mike
Date: Tue, 22 Jun 1999 08:55:56 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
Subject: RE: Concurrent Manager Indexes
To get the idea about the indexes on all the segments where the max_extents
hits it 80% capacity. You can use what ever the percentage you want.
Here is the script.
col segment_name format a20
col "Perc_Used" format 999.999
select file_id,ds.segment_name,ds.extents AS Extents_Filled,ds.max_extents,
(ds.extents/ds.max_extents)*100 "Perc_Used",SUM(de.blocks) AS Blocks,
ds.initial_extent,ds.next_extent,ds.min_extents, (ds.bytes/1024/1024) as
Total_MB
from dba_extents de, dba_segments ds
where ds.segment_name = de.segment_name and
ds.segment_type = de.segment_type and
ds.segment_type in ('TABLE','INDEX')
group by file_id,ds.segment_name,ds.max_extents,
(ds.extents/ds.max_extents)*100,ds.extents,
ds.initial_extent,ds.next_extent,ds.min_extents,
ds.bytes/1024/1024
having trunc((ds.extents/ds.max_extents)*100) =80
/
Your dba can fix it for you.
hope this helps.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
x-------------x
Date: Fri, 2 Jul 1999 14:33:59 -0400
Date: Fri, 2 Jul 1999 11:58:46 -0700
From: "Orth, Tom (torth)" torth@sequent.com
Subject: RE: Questions about Extents size......
Hello,
The table you are interested in is DBA_FREE_SPACE owned by SYS.
It sounds like your problem is not some much a full extent but the
inability to create another extent, which Oracle will do automatically
if one fills up. Or, it could be that you have reached the maximum
number of extents specified for that table. It sounds like you need
to do some analysis to determine how your tables will be growing
over time, and plan the space in your table spaces accordingly.
One more thing. To see how the individual table is doing, look at
dba_tables,
or all_tables (both views of the same thing). There you can see the number
of empty blocks and other statistical information about your tables.
FRM-41836: disk I/O error
Date: Thu, 8 Jul 1999 15:37:52 -0500
From: david.shaenfield@natinst.com
Subject: error: FRM-41836: disk I/O error on temporary record buffer file
We are getting this error: "FRM-41836: disk I/O error on temporary record
buffer file C:\TEMP\e\2.TMP" at random times when users are using Oracle Service
and Oracle Sales and Marketing applications. Most of the users use these
applications through a Window Terminal Server connection, but it has also
happened on local installations. We use version 10.7 of the apps.
What is the fix for this?
thanks,
David Shaenfield
National Instruments
Date: Thu, 8 Jul 1999 15:45:39 -0500
From: "Logan, Ernie" Ernie_Logan@bmc.com
Subject: RE: error: FRM-41836: disk I/O error on temporary record buffer f
It usually means that the temp space on the workstation / terminal server is
full or close to it.
Oracle connection process eating memory
Date: Tue, 29 Jun 1999 18:24:46 -0700
From: John Peters jrpeters@ix.netcom.com
To: oraapps-dba@cpa.qc.ca
Subject: Oracle connection process sizes in Rel8 databases
Has anyone seen the Oracle database processes associated with
connections
take a large amount of memory. We are running on HP/UX and opening a
SQL*Plus session without any activity starts a database process which
consumes 180MB of memory. This only seems to happen when SQL*Plus
sessions
are started from within UNIX. Sessions through SQL*Net do not exhibit
this
behavior. The SQL*Plus session takes a normal amount of memory but the
database processes it starts is way larger than it should need to be.
I went to a Solaris installation and found that the same SQL*Plus
session
consumes 174MB or memory. Keep in mind these sessions have not even
processed
a command yet, you are just sitting at the SQL prompt.
These are both Oracle 8.0.X installs.
Any ideas would be greatly appreciated.
Thanks,
John Peters
Date: Wed, 30 Jun 1999 09:53:45 +0100
From: Graham Duggan GJDuggan@mail.com
Subject: RE: Oracle connection process sizes in Rel8 databases
I guess you are using a process like top or ps to see the process size
and/or resident set size.
The short answer is don't believe these numbers. The process size
reports memory associated with the process and this includes shared
memory segments the process attaches to. As Oracle process connect to
the shared memory segments of the SGA, Oracle processes will report as
being at least the size of the SGA. Note that this memory is "shared"
and us used by all Oracle processes for that instance so actual physical
usage is not as great as you imagine.
This has been the case for every Oracle on every UNIX I have ever used.
Graham.