OraFAQ Forum: Server Administration » Tablespace storage settings

Home » RDBMS Server » Server Administration » Tablespace storage settings

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tablespace storage settings [message #56978]

Sat, 10 May 2003 17:40

Deepak
Messages: 111
Registered: December 1999

Senior Member

I am getting this error message when trying to change the storage settings for the tablespace DATA I created.

SQL> alter tablespace data default storage (initial 2m next 1m);
alter tablespace data default storage (initial 2m next 1m)
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy

Any help highly appreciated.

Thanks
Deepak

Report message to a moderator

Re: Tablespace storage settings [message #56979 is a reply to message #56978]

Sun, 11 May 2003 23:23

Deepa
Messages: 269
Registered: November 2000

Senior Member

you cannot change the initial and next extent size using alter tablespace command.
Once created these values cannot be changed.

Report message to a moderator

Re: Tablespace storage settings [message #56980 is a reply to message #56979]

Mon, 12 May 2003 04:57

Naveen
Messages: 75
Registered: June 2001

Member

Hi Deepa,

Default storage clause can be used to alter the default storage settings for newely created objects. However you cannot specify inital clause for it. See below a para from documentation.

DEFAULT storage_clause
specifies the new default storage parameters for objects subsequently created in the tablespace. For a dictionary-managed temporary table, Oracle considers only the NEXT parameter of the storage_clause. See the "storage_clause".

Restriction: You cannot specify this clause for a locally managed tablespace.

Hope this code helps.

SVRMGR> alter tablespace users default storage(inital 10k next 10k);
alter tablespace users default storage(inital 10k next 10k)
*
ORA-02143: invalid STORAGE option
SVRMGR>alter tablespace users default storage(next 10k);
Statement processed.

Regards----Naveen.

Report message to a moderator

Re: Tablespace storage settings [message #56981 is a reply to message #56980]

Mon, 12 May 2003 06:05

Deepak
Messages: 111
Registered: December 1999

Senior Member

Naveen,

I think that if you have gave the extent management to be local, you cannot alter the default storage values. Please let me know.

Thanks
Deepak

Report message to a moderator

Re: Tablespace storage settings [message #56983 is a reply to message #56981]

Mon, 12 May 2003 07:53

Naveen
Messages: 75
Registered: June 2001

Member

Hi Deepak,

Yes you are correct. If the tablespace is locally managed (Whether Autoallocate or Uniform)then you cannot use default storage clause.

Regards---Naveen

Report message to a moderator

Re: Tablespace storage settings [message #57002 is a reply to message #56978]

Tue, 13 May 2003 11:17

Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida

Senior Member

Of coarse you can change the DEFAULT STORAGE setting for a tablespace. The error above means something else.

% oerr ora 25143
25143, 00000, "default storage clause is not compatible with allocation policy"
// *Cause: default storage clause was specified for a tablespace with
//         AUTOALLOCATE or UNIFORM policy
// *Action:  Omit the storage clause

Report message to a moderator

Previous Topic:	compute stats
Next Topic:	Flow in SGA

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 02:16:21 CDT 2024