-
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
-
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
I'm stmontgo and I approve of this message
-
How about trying DEFAULT STORAGE
clause in create tablespace Statement.
Also Check your dba_tablespace views.
-- Dilip
-
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]
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
|