OBJECT IS NOT USING THE DEFAULT PARAMETERS

HI FOLKS

I HAVE CREATED A TABLE WITH THE STORAGE PARAMETER BUT IT IS NOT CONSIDERING THAT PARAMETERS IT IS PICKING UP THE TABLESPACES PARAMETERES

WHY IS IT HAPPENING EVEN THE EXTENT MANAGEMENT IS ALSO DISCTIONARY.

SHAILENDRA

ANY SUGGESTION

SHAILENDRA

Quote:

---

Originally posted by shailendra
BUT IT IS NOT CONSIDERING THAT PARAMETERS IT IS PICKING UP THE TABLESPACES PARAMETERES

---

Did you confirmed quering dba_extents and dba_segments dictionaries?

Sameer

YES I HAVE CONFIRMED


SHAILENDRA

Can you paste your create table code with storage parameters and output from these two data dictionaries for your segment

CREATE TABLESPACE (TABLESPACE_NAME) DATAFILE (FULL PATH) SIZE 1000M
STORAGE (INITIAL 100K NEXT 100K)

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPE) STORAGE(INITIAL 10K NEXT 10K)

is it a locally managed tablespace?

steve

How about trying DEFAULT STORAGE
clause in create tablespace Statement.

Also Check your dba_tablespace views.

Go through te following examples and check your tables for the storage parametres.

DB_BLOCK_SIZE = 8192 for all following examples.. Version, 8.1.7

Code:

---

INITIAL:

The default value is the size of **5** data blocks. In
tablespaces with manual segmentspace management, the
 minimum value is the size of **2** data blocks plus one
data block for each free list group you specify.  In ablespaces
with automatic segmentspace management, the minimum
value  is 5 data blocks. The maximum value depends on your
operating system.


CASE:1
---------------------------------

CREATE TABLESPACE junk DATAFILE 'D:\ORACLE\ORADATA\ORCL\junk01.DBF' SIZE 100M ;

SELECT tablespace_name, initial_extent, next_extent,
min_extents, max_extents,  status, extent_management,
allocation_type FROM dba_tablespaces WHERE tablespace_name = 'JUNK'

Default 5 block are taken

TABLESPACE INITIAL NEXT  MIN_EXT MAX_EXT    STATUS  EXTENT_MAN  ALLOC_TYPE
----------------------------------------------------------------------------
JUNK      40960  40960  1      505        ONLINE  DICTIONARY  USER

CREATE TABLE tab_junk (id NUMBER, name VARCHAR2(100)) TABLESPACE junk;

CREATE TABLE tab_junk1 (id NUMBER, name VARCHAR2(100)) STORAGE(INITIAL 10K NEXT 10K) TABLESPACE junk

SELECT segment_name, tablespace_name,
bytes, blocks FROM dba_extents WHERE segment_name IN ('TAB_JUNK','TAB_JUNK1')

SEGMENT_NAME  TABLESPACE_NAME  BYTES    BLOCKS
-------------- ----------------------- ----------
TAB_JUNK      JUNK              40960          5*
TAB_JUNK1      JUNK              16384          2**

* Default 5 blocks are allocated, no storage parameters
specified, tablespace storage settings
are used.

** Manual storage allocation, default 2 blocks are allocated.
tablespace storage settings are not used.
---------------------------------------------------------------------------------

CASE:2

CREATE TABLESPACE junk
DATAFILE 'D:\ORACLE\ORADATA\ORCL\junk01.DBF' SIZE
100M DEFAULT STORAGE (INITIAL 100K NEXT 100K
MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)

SELECT tablespace_name, initial_extent, next_extent,
min_extents, max_extents,  status, extent_management,
allocation_type FROM dba_tablespaces WHERE
tablespace_name = 'JUNK'

Specified storage parameters are used


TABLESPACE INITIAL NEXT  MIN_EXT MAX_EXT    STATUS  EXTENT_MAN  ALLOC_TYPE
----------------------------------------------------------------------------
JUNK      106496  106496 1      2147483645 ONLINE  DICTIONARY  USER

CREATE TABLE tab_junk (id NUMBER, name VARCHAR2(100))
TABLESPACE junk;

CREATE TABLE tab_junk1 (id NUMBER, name VARCHAR2
(100)) STORAGE(INITIAL 10K NEXT 10K) TABLESPACE junk

SELECT segment_name, tablespace_name,
bytes, blocks FROM dba_extents WHERE segment_name IN
('TAB_JUNK','TAB_JUNK1')

SEGMENT_NAME  TABLESPACE_NAME  BYTES    BLOCKS
-------------- ----------------------- ----------
TAB_JUNK      JUNK            122880        15*
TAB_JUNK1      JUNK              16384          2**

* Default 5 blocks are allocated, no storage parameters
specified, tablespace storage settings
are used.

In dictionary-managed tablespaces, if MINIMUM EXTENT was
specified for the tablespace when it was created, then Oracle
rounds the value of INITIAL up to the specified MINIMUM
EXTENT size if necessary. If MINIMUM EXTENT was not
specified, then Oracle rounds the INITIAL extent
size for segments created in that tablespace up to the
minimum value (see preceding paragraph), or to multiples
of 5 blocks if the requested size is greater than 5 blocks.

Therefore the size for the first table is 122880 (15 block)

** Manual storage allocation, default 2 blocks are allocated.
tablespace storage settings are not used.

---

Query your table and check the output for your tables.. Hope it comes correct

HTH

Sameer

[Edited by Sameer on 08-27-2002 at 05:39 AM]