| RE: Autoextend on [message #19467] |
Wed, 20 March 2002 18:57  |
dhnish
Messages: 26
Registered: March 2002
|
Junior Member |
|
|
Hi,
Created a datafile using following commands:
alter tablespace datspace
add datafile '/data/oradata/baanIV/datdbaanIV.dbf' size
500M autoextend on next 100M maxsize 1000M;
From the initial 500M , does this mean it will automatically increase from 600 , 700 till 1000M before its considered full?
Pls assist
Thank you
|
|
|
|
| Re: RE: Autoextend on [message #19476 is a reply to message #19467] |
Thu, 21 March 2002 01:21  |
Senthil
Messages: 68
Registered: December 1999
|
Member |
|
|
|
That means..."initial"ly it starts with 500M and when its full, allocates "next" 100M until it reaches the "maxsize". Once it reaches 1000M...throws error.
|
|
|
|
|
|
| Re: RE: Autoextend on [message #19489 is a reply to message #19483] |
Thu, 21 March 2002 10:55 |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
PCTINCREASE is used to define the size of extents but not the size of datafiles. So PCTINCREASE doesn't affect in any way the size of a datafile.
Mike
|
|
|
|
|
|
| Re: RE: Autoextend on [message #19517 is a reply to message #19483] |
Fri, 22 March 2002 18:59 |
dhnish
Messages: 26
Registered: March 2002
|
Junior Member |
|
|
Hi
Thanks for the prompt reply.
I have few more questions, hope you can help me.
Q1)
Currently I have 20 datafiles created in my system.
How am I to know which one was created with PCTINCREASE =0 ? so that in future I could alter the size .
Q2)
My data growth seems to be somewhere between 800Mb - 1000M a month , is this normal ? Could there be fragmentation? How can I justify this?
Q3)
Calculation of data size , is this the right formula to use:
(total size of harddisk*2) - (total size of existing datafiles) / ( growth in a month ) ?
The digit 2 indicates maximum size for each datafiles in Oracle7.3.4 is 2Gb.
Pls assist
Thank you
|
|
|
|
| Re: RE: Autoextend on [message #19524 is a reply to message #19517] |
Sat, 23 March 2002 08:20 |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
Hi,
Q1) PCTINCREASE is not related in any way to datafiles.
PCTINCREASE is related to segments (tables). For each tablespace there is a default value for PCTINCREASE. This default value is only used if you don't specify it when you create a segment.
To see the default value for each tablespace you can have a loot at the view DBA_TABLESPACES. To change the default value you can use the command: ALTER TABLESPACE tablespace_name STORAGE( PCTINCREASE 0); .
It is recommanded, for fragemtation reasons that you set PCT_INCREASE=0 and use only one size for the initial an next extent per tablespace.
Q2) With the given information it is not possible to me to answer this question.
Q3) I don't realy understand what you want you mean by "calculation of data size". But because the size of the harddisks certainly doesn't affect the "data size" I doubt that your formula is correct.
HTH
Mike
|
|
|
|
|
|
| Re: RE: Autoextend on [message #19532 is a reply to message #19483] |
Mon, 25 March 2002 22:51 |
dhnish
Messages: 26
Registered: March 2002
|
Junior Member |
|
|
Hi
Pls help me on this:
created this script :
select owner,segment_name,tablespace_name,extents,next_extent,pct_increase from dba_segments where extents>10
Result:
BAAN TTFGLD410770$IDX1 IDXSPACE 27 424,716,288 50
BAAN TTFGLD410770$IDX2 IDXSPACE 28 637,075,456 50
BAAN TTFGLD410770 DATSPACE 31 ############ 50
Question:
i) The table tfgld410 has 8 million records. Are my extents too many?
ii) How do you address the term extents and next_extent?
iii) Should my pct_increase be 0?
Is there any way for me to know how much more the segments can extent (any max.figure) ?
Pls help
Thank you
.d
|
|
|
|
| Re: RE: Autoextend on [message #19537 is a reply to message #19532] |
Tue, 26 March 2002 08:48 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
I. Entents are not really a problem any more says Oracle. You can run into a problem when it reaches MAXEXTENTS (defined by the tablespace storage parameters). You can rebuild/recreate the index when that happens:
alter index INDEXNAME rebuild storage(INITIAL 100M NEXT 1M PCTINCREASE 0%);
You can get the actual size by taking advantage of ORA--03230 without causing any harm to the database. Here is a script that will do it for you. You can use this on any segment just modify it as needed:
set heading on
set echo off
set verify off
set pages 200
ACCEPT idxname prompt 'Enter Index Name: '
ACCEPT idxowner prompt 'Enter Index Owner: '
select sum(bytes) TOTAL from dba_segments where segment_name=UPPER('&idxname');
alter index &idxowner..&idxname deallocate unused keep 1000000000;
ACCEPT ttlbytes prompt 'Enter Total Bytes: '
ACCEPT uublocks prompt 'Enter Number of Unused Blocks: '
select &ttlbytes-(2048*&uublocks) from dual;
SAMPLE:
SQL> @get_idx_size.sql
Enter Index Name: I_ACCESS1
Enter Index Owner: sys
TOTAL
----------
262144
alter index sys.I_ACCESS1 deallocate unused keep 1000000000
*
ERROR at line 1:
ORA-03230: segment only contains 10 blocks of unused space above high water
mark
Enter Total Bytes: 262144
Enter Number of Unused Blocks: 10
262144-(2048*10)
----------------
241664
This index has 14 extents so if I wanted to rebuild it in one I would:
alter index I_ACCESS1 rebuild storage (initial 264K);
Rebuild is much faster and takes less resources than create.
II. There is a lot of information on this so you should do some searches on it but in a nutshell segments extend as they need more space. These extents can be controlled with INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, and PCTINCREASE. The best place to start is with Oracle docs and MetaLink.
III. PCTINCREASE can bite you so personnaly I do not use it. This clause is exponential. This parameter adds to the total not a percentage of NEXT. So that means all your extents are not equal. Many say they should be for best performance. Like I said there are many articles on this subject so do some searching.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
