Hi all,
Good Afternoon..
I have some doubts' in extents allocation in oracle. below is the example i did. of course this post is litle big big to read. sorry for that.
SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
--------------------------------------------------------------------------------
CREATE TABLESPACE "USERS" DATAFILE
'C:\UPGRADE\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF' SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
so now the tablespace is created with auto matic extent management and space management. so if table is not created with stoage parameters oracle will use the default storage parameters of tablespace.
SQL> select dbms_metadata.get_ddl('TABLE','TB1','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','TB1','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."TB1"
( "ID" NUMBER,
"NAME" VARCHAR2(20)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "USERS";
So now this tables is using automatic extent management as from the tablespace. when ever a segment is full next exetent should increase with 50% more. but when i checked the table, it was showing like below:
SQL> SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE FROM USER_SEGMENTS WHERE SEGMENT_NAME=
'TB1';
SEGMENT_NAME TABLESPACE_NAME BYTES EXTENTS INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
-------------------- ------------------------------ ---------- ----------
TB1 USERS 65536 1 65536 1048576
After inserting 7mb and also showing the saming this.
SQL> SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE FROM USER_SEGMENTS WHERE SEGMENT_NAME=
'TB1';
SEGMENT_NAME TABLESPACE_NAME BYTES EXTENTS INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
-------------------- ------------------------------ ---------- ---------- -------------- ----------- ------------
TB1 USERS 7340032 22 65536 1048576
can you please let me know hot it's actually allocating the extents and when space is increaing.
thanks .