-
initial extent size of tablespaces
hi
while creating tablespaces we can specify the default storage parameters.
first tablespace app_data-
when i give the initial size as 256k, next as 256k and pctincrease as 0. logically it means that the third extent would have 256k as its size.
second tablespace app_data1
if i change the pctincrease to 50% then why does it change the initial extent size as per the output that i received.
------------------------------------------------
the first query is:
create tablespace app_data
datafile 'e:\oracle\oradata\db1\app_data.dbf' size 5m
default storage( initial 20k next 20k minextents 2 pctincrease 0
maxextents 20)
blocksize 4k minimum extent 20k logging online permanent
the output is:
TABLESPACE_NAME | BLOCK_SIZE | INITIAL_EXTENT | NEXT_EXTENT| MIN_EXTENTS | MAX_EXTENTS| PCT_INCREASE | MIN_EXTLEN |STATUS CONTENTS | LOGGING | EXTENT_MAN | ALLOCATIO | PLU SEGMEN
------ ---------- --------- --- -------------------------------------
APP_DATA | 4096 | 20480 | 20480 |
1 | 2147483645| 0 | 20480 |ONLINE PERMANENT | LOGGING | LOCAL | UNIFORM | NO MANUAL
--------------------------------------------------------------------
the second query is:
create tablespace app_data1
datafile 'e:\oracle\oradata\db1\app_data1.dbf' size 5m
default storage( initial 20k next 20k minextents 2 pctincrease 50
maxextents 20)
blocksize 4k minimum extent 20k logging online permanent
the output is:
TABLESPACE_NAME | BLOCK_SIZE | INITIAL_EXTENT | NEXT_EXTENT| MIN_EXTENTS | MAX_EXTENTS| PCT_INCREASE | MIN_EXTLEN |STATUS CONTENTS | LOGGING | EXTENT_MAN | ALLOCATIO | PLU SEGMEN
------ ---------- --------- --- -----------------------------------
APP_DATA1 | 4096 | 65536 | |
1 | 2147483645| | 65536 |ONLINE PERMANENT | LOGGING | LOCAL | SYSTEM | NO MANUAL
-------------------------------------------------------------------
why does the allocation change to system in the second case.
thanks
Last edited by ser; 05-29-2003 at 03:01 AM.
-
Extent Management is LOCAL. And Allocation Type is SYSTEM
Initial, Next have no significance in LMT.
Read the manuals.
Last edited by abhaysk; 05-29-2003 at 03:42 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
The reason you are seeing this is is because in the first example you extent sizes are uniform, yet in your second example this would not be true hence the allocation type is defined as system which has a default extent size of 64k
Take a look at this document for clarity
http://support.oracle.co.uk/metalink...T&p_id=93771.1
HTH
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
Thats because the tablespace APP_DATA1 is not a uniform extent tablespace, its an auto allocate or SYSTEM Managed LMT. So no matter what for a SYSTEM or Autoallocate LMT the first few extents will be 64k in size.
Look at the ALLOCATION_TYPE column value of dba_tablespaces. You will see that its not uniform allocation but SYSTEM allocated.
HTH
Amar
"There is a difference between knowing the path and walking the path."
-
by default the extent management is local in oracle9i.
but in case when i give default storage and no extent management clause as i gave while creating the tablespace what does it use( local or dictionary)
if i specify default storage clause and extent management local it gives me an error that default storage specifications cannot be specified for locally managed extents.
jovery i do not have access to metalink , hence i couldn't access it. moreover both the examples used the same size hence it is not clear as to why the server selected system for the second example and uniform for the first.
-
Originally posted by ser
by default the extent management is local in oracle9i.
but in case when i give default storage and no extent management clause as i gave while creating the tablespace what does it use( local or dictionary)
LOCAL
if i specify default storage clause and extent management local it gives me an error that default storage specifications cannot be specified for locally managed extents.
Expected normal behavior.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
Originally posted by ser
moreover both the examples used the same size hence it is not clear as to why the server selected system for the second example and uniform for the first.
PCTINCREASE 0 ( in 1st case ).
RTM in Technet.ORACLE.com
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by ser
by default the extent management is local in oracle9i.
but in case when i give default storage and no extent management clause as i gave while creating the tablespace what does it use( local or dictionary)
Remember that when the system tablespace is Locally managed tablespace then all tablespaces created will be locally managed. You are confusing yourself with the Allocation Type. The question should be whether it would be an uniform allocated or system(auto) allocated tablespace. If you do not mention uniform clause while creating the tablespace the tablespace will be System allocated ie the extent management is autoallocate.
In Auto allocate the first few extents size will be 64K then 1M, then 8M and then 64M and so on. But in case of uniform allocation the tablespace will have uniform extents only ie extents of same size.
Originally posted by ser
if i specify default storage clause and extent management local it gives me an error that default storage specifications cannot be specified for locally managed extents.
You don't need to give storage clause, that does not work in Locally Managed Tablespaces, its either uniform extent or system(autoallocate).
Code:
sys@ACME.WORLD> create tablespace uniform_tbs datafile 'E:\ORACLE\ORADATA\ACME\uniform_tbs.dbf' size 10M
2 extent management local uniform size 2M;
Tablespace created.
sys@ACME.WORLD> exec print_table('select * from dba_tablespaces where tablespace_name=''UNIFORM_TBS''');
TABLESPACE_NAME : UNIFORM_TBS
BLOCK_SIZE : 8192
INITIAL_EXTENT : 2097152
NEXT_EXTENT : 2097152
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE : 0
MIN_EXTLEN : 2097152
STATUS : ONLINE
CONTENTS : PERMANENT
LOGGING : LOGGING
FORCE_LOGGING : NO
EXTENT_MANAGEMENT : LOCAL
ALLOCATION_TYPE : UNIFORM
PLUGGED_IN : NO
SEGMENT_SPACE_MANAGEMENT : MANUAL
DEF_TAB_COMPRESSION : DISABLED
-----------------
PL/SQL procedure successfully completed.
See here i have mentioned uniform extent of 2M so all extent allocation will always be 2M for uniform_tbs tablespace.
Code:
sys@ACME.WORLD> create tablespace system_tbs datafile 'E:\ORACLE\ORADATA\ACME\system_tbs.dbf' size 10M
2 extent management local;
Tablespace created.
sys@ACME.WORLD> exec print_table('select * from dba_tablespaces where tablespace_name=''SYSTEM_TBS''');
TABLESPACE_NAME : SYSTEM_TBS
BLOCK_SIZE : 8192
INITIAL_EXTENT : 65536
NEXT_EXTENT :
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
MIN_EXTLEN : 65536
STATUS : ONLINE
CONTENTS : PERMANENT
LOGGING : LOGGING
FORCE_LOGGING : NO
EXTENT_MANAGEMENT : LOCAL
ALLOCATION_TYPE : SYSTEM
PLUGGED_IN : NO
SEGMENT_SPACE_MANAGEMENT : MANUAL
DEF_TAB_COMPRESSION : DISABLED
-----------------
PL/SQL procedure successfully completed.
See here i have not mentioned uniform extent clause so the extent allocation will always be autoallocate for system_tbs tablespace.
Originally posted by ser
jovery i do not have access to metalink , hence i couldn't access it. moreover both the examples used the same size hence it is not clear as to why the server selected system for the second example and uniform for the first.
The server selected uniform for the first because the INITIAL=NEXT and PCTINCREASE=0 is what a Uniform extent will have ie all extents are uniform. But in the second case you have INITAIL=NEXT and PCTINCREASE=50, here because of the clause PCTINCREASE=50 the extents allocations cannot be uniform. So the only other option left is Autoallocate and server makes it an System(Autoallocate) managed LMT.
Do not use storage clauses as it doesnot work with LMT. It has to be either UNIFORM LMT or SYSTEM MANAGED LMT.
And do not confuse yourself between Dictionary managed and SYSTEM Managed they are two different things.
HTH
Last edited by adewri; 05-30-2003 at 03:12 AM.
Amar
"There is a difference between knowing the path and walking the path."
-
Amar,
Your print_table procedure is cool. Wondering what happens if more that one row selected ??
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
Originally posted by SANJAY_G
Amar,
Your print_table procedure is cool. Wondering what happens if more that one row selected ??
It will come below one another
try it
Code:
CREATE OR REPLACE procedure print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
begin
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl);
for i in 1 .. l_colCnt loop
dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
end loop;
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
dbms_output.put_line( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
exception
when others then
dbms_sql.close_cursor( l_theCursor );
RAISE;
end;
/
HTH
Amar
"There is a difference between knowing the path and walking the path."
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|