473,323 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Oracle 9 - tablespace default storage

Hi, I am having a strange problem with the storage parameters in Oracle
9.2.0.1 database.
I create a tablespace with some default storage parameters but when i am
adding a table to the tablespace i can not override the NEXT statement?
No matter what i set the NEXT storage parameters to, it is always the same
as the tablespace default, the INITIAL is overriden just fine.
I have included my script below, can someone please point out what could be
wrong.

Thanks in advance!
Jens Hamann

CREATE TABLESPACE JHH_DATA DATAFILE 'C:\OraDb\JHH_DATA_00.dbf' SIZE 50M
AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K NEXT 24K
MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);
CREATE TABLESPACE JHH_INDEX DATAFILE 'C:\OraDb\JHH_IDX00.dbf' SIZE 50M
AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K NEXT 24K
MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);
CREATE TEMPORARY TABLESPACE JHH_TEMP TEMPFILE 'C:\OraDb\JHH_TEMP00.dbf' SIZE
50M AUTOEXTEND ON NEXT 50M MAXSIZE 4000M;
CREATE USER JHH IDENTIFIED BY JHH;
GRANT DBA, CONNECT TO JHH;
ALTER USER JHH DEFAULT TABLESPACE JHH_DATA TEMPORARY TABLESPACE JHH_TEMP;
ALTER USER JHH QUOTA UNLIMITED ON JHH_DATA QUOTA UNLIMITED ON JHH_INDEX
QUOTA UNLIMITED ON JHH_TEMP;

CREATE TABLE JHH.LARGE_TABLE (
COLUMN_1 NUMBER (5) NOT NULL,
COLUMN_2 NUMBER (5),
COLUMN_3 VARCHAR2 (50),
CONSTRAINT PK_LARGE_TABLE
PRIMARY KEY ( COLUMN_1 )
USING INDEX
TABLESPACE JHH_INDEX PCTFREE 10
STORAGE ( INITIAL 10M NEXT 10M PCTINCREASE 0 ))
TABLESPACE JHH_DATA
PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255
STORAGE (
INITIAL 10M NEXT 10M PCTINCREASE 0
MINEXTENTS 1 MAXEXTENTS 2147483645 )
NOCACHE;

CREATE TABLE JHH.SMALL_TABLE (
COLUMN_1 NUMBER (5) NOT NULL,
COLUMN_2 NUMBER (5),
COLUMN_3 VARCHAR2 (50),
CONSTRAINT PK_SMALL_TABLE
PRIMARY KEY ( COLUMN_1 )
USING INDEX
TABLESPACE JHH_INDEX PCTFREE 10
STORAGE ( INITIAL 64K NEXT 64K PCTINCREASE 0 ))
TABLESPACE JHH_DATA
PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255
STORAGE (
INITIAL 64K NEXT 64K PCTINCREASE 0
MINEXTENTS 1 MAXEXTENTS 2147483645 )
NOCACHE;
Jul 19 '05 #1
1 20313
"Jens H. Hamann" <jh*@agramkow.dk> wrote in message news:<40**********************@dread14.news.tele.d k>...
Hi, I am having a strange problem with the storage parameters in Oracle
9.2.0.1 database.
I create a tablespace with some default storage parameters but when i am
adding a table to the tablespace i can not override the NEXT statement?
No matter what i set the NEXT storage parameters to, it is always the same
as the tablespace default, the INITIAL is overriden just fine.
I have included my script below, can someone please point out what could be
wrong.

Thanks in advance!
Jens Hamann

CREATE TABLESPACE JHH_DATA DATAFILE 'C:\OraDb\JHH_DATA_00.dbf' SIZE 50M
AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K NEXT 24K
MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);
CREATE TABLESPACE JHH_INDEX DATAFILE 'C:\OraDb\JHH_IDX00.dbf' SIZE 50M
AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K NEXT 24K
MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);
CREATE TEMPORARY TABLESPACE JHH_TEMP TEMPFILE 'C:\OraDb\JHH_TEMP00.dbf' SIZE
50M AUTOEXTEND ON NEXT 50M MAXSIZE 4000M;
CREATE USER JHH IDENTIFIED BY JHH;
GRANT DBA, CONNECT TO JHH;
ALTER USER JHH DEFAULT TABLESPACE JHH_DATA TEMPORARY TABLESPACE JHH_TEMP;
ALTER USER JHH QUOTA UNLIMITED ON JHH_DATA QUOTA UNLIMITED ON JHH_INDEX
QUOTA UNLIMITED ON JHH_TEMP;

CREATE TABLE JHH.LARGE_TABLE (
COLUMN_1 NUMBER (5) NOT NULL,
COLUMN_2 NUMBER (5),
COLUMN_3 VARCHAR2 (50),
CONSTRAINT PK_LARGE_TABLE
PRIMARY KEY ( COLUMN_1 )
USING INDEX
TABLESPACE JHH_INDEX PCTFREE 10
STORAGE ( INITIAL 10M NEXT 10M PCTINCREASE 0 ))
TABLESPACE JHH_DATA
PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255
STORAGE (
INITIAL 10M NEXT 10M PCTINCREASE 0
MINEXTENTS 1 MAXEXTENTS 2147483645 )
NOCACHE;

CREATE TABLE JHH.SMALL_TABLE (
COLUMN_1 NUMBER (5) NOT NULL,
COLUMN_2 NUMBER (5),
COLUMN_3 VARCHAR2 (50),
CONSTRAINT PK_SMALL_TABLE
PRIMARY KEY ( COLUMN_1 )
USING INDEX
TABLESPACE JHH_INDEX PCTFREE 10
STORAGE ( INITIAL 64K NEXT 64K PCTINCREASE 0 ))
TABLESPACE JHH_DATA
PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255
STORAGE (
INITIAL 64K NEXT 64K PCTINCREASE 0
MINEXTENTS 1 MAXEXTENTS 2147483645 )
NOCACHE;


In locally managed tablespaces next will always be equal to init, by
design.
In Oracle 9i locally managed tablespaces are the default.
IMO, you shouldn't bother, especially if the tablespaces are created
with autoallocate. You don't want explicit storage clauses anymore:
they cause maintenance.

Sybrand Bakker
Senior Oracle DBA
Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Philip D Heady | last post by:
Ok I'm a newbie to using Oracle9i w/ PHP, been a MySQL guy... What is the proper sql statement to use insert tables, etc. as below.? I don't know much about how OciParse works. I can connect...
5
by: Srini | last post by:
In our Oracle installation on SCO Unix, we have reached almost 100% of tablespace allocated to Oracle. Now we have deleted a large table from the database after taking backup elsewhere. Now if we...
4
by: susmita_ganguly | last post by:
Hi I am trying to upgrade from oracle 8i to oracle 9i on the same server ..I don't know much abt migration . Can anyone help me out. Thanks. Susmita
4
by: Ulrich Sprick | last post by:
Hi all, (DB2 V7.1 for WinNT) I am looking for a way to determine the free space in my tablespace (containers), but I can't find out. The tablespace in question is a system managed tablespace in...
6
by: robert | last post by:
just got out of a "class" on EXPLAIN, from a 390/v6 maven. was told that ACCESSTYPE = 'R' in the plan_table meant a TableSpace scan. hmmmm. next step up (or down, depending on your outlook)...
56
by: Ashish Patankar | last post by:
I want to migrate my Oracle 10g database to Db2. I want some documentation for the comparision between these to databases. I also want to know which features of Oracle 10g are supported by Db2 and...
2
by: Vinod Sadanandan | last post by:
All, Below listed are the new features in Oracle 11g ,please join me in this discussion to generate a testcase and analyze each of the listed features . Precompilers:...
1
by: Jens H. Hamann | last post by:
Hi, I am having a strange problem with the storage parameters in Oracle 9.2.0.1 database. I create a tablespace with some default storage parameters but when i am adding a table to the tablespace...
3
by: Justin | last post by:
What is the difference between automatic and DMS Auto-resize tablespace?
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.