cancel
Showing results for 
Search instead for 
Did you mean: 

Alter the tablespace storage parameter

0 Kudos

Hi,

I want to alter the tablespace storage parameter from 1024 to 200 KB. Can anyone please provide me what is the procedure? Its very urgent.

Suman

Accepted Solutions (0)

Answers (8)

Answers (8)

Former Member
0 Kudos

Hi,

Is this answer your questions?

if yes please change status of the message and reward points

Thanks

Former Member
0 Kudos

Hi,

SYSTEM DICTIONARY USER MANUAL

PSAPR3E LOCAL SYSTEM MANUAL

PSAPR3E620 LOCAL SYSTEM MANUAL

PSAPR3EUSR LOCAL SYSTEM MANUAL

PSAPTEMP LOCAL UNIFORM MANUAL

PSAPTF60D LOCAL SYSTEM MANUAL

PSAPUNDO LOCAL SYSTEM MANUAL

SYSAUX LOCAL SYSTEM AUTO

PSAPR3E700 LOCAL UNIFORM AUTO

You cannot alter your user tablespaces as they are locally managed. Oracle automatically manage the storage parameters next. the only way to change the next is to export your data, recreate the TPS and import the data. also the fact that you have UNIFORM mean that all objects in this tps will be created with uniform size of extents.

let gave an example:

- create table XYZ in PSAPR3E700 , this table will have UNFORM size of extents

- create table UVW with initial 1G and next 2G in PSAPR3E620: Oracle will create it with 64M initial as it is the meximum of extents size in LMTS tablespaces and next witl be adjusted automatically by oracle

Hope this will answer your question

Thanks

0 Kudos

Hi Ahmed,

Above is the result posted by Amit Jain.

Suman

Former Member
0 Kudos

SQL> select tablespace_name, EXTENT_MANAGEMENT, ALLOCATION_TYPE, SEGMENT_SPACE_M

ANAGEMENT from dba_tablespaces;

Output of the sql

TABLESPACE_NAME EXTENT_MAN ALLOCATIO SEGMEN

-


-


-


-


SYSTEM DICTIONARY USER MANUAL

PSAPR3E LOCAL SYSTEM MANUAL

PSAPR3E620 LOCAL SYSTEM MANUAL

PSAPR3EUSR LOCAL SYSTEM MANUAL

PSAPTEMP LOCAL UNIFORM MANUAL

PSAPTF60D LOCAL SYSTEM MANUAL

PSAPUNDO LOCAL SYSTEM MANUAL

SYSAUX LOCAL SYSTEM AUTO

<u><b>PSAPR3E700 LOCAL UNIFORM AUTO</b></u>

Message was edited by:

Amit Jain

Former Member
0 Kudos

Hi,

Probably your tablespace is locally managed. you cannot alter some storage parameters for LMTS tablespaces.

try this and send the result

select tablespace_name, EXTENT_MANAGEMENT, ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

Thanks

0 Kudos

Hi,

While altering the tablespace I am getting the following error as it is maintained locally.

SQL> ALTER TABLESPACE PSAPR3E700 DEFAULT STORAGE ( INITIAL 3072k NEXT 1024K MINEXTENTS 1 MAXEXTENTS 720 PCTINCREASE 0 );

ALTER TABLESPACE PSAPR3E700 DEFAULT STORAGE ( INITIAL 3072k NEXT 1024K MINEXTENTS 1 MAXEXTENTS 720 PCTINCREASE 0 )

*

ERROR at line 1:

ORA-25143: default storage clause is not compatible with allocation policy

How can I correct it? Please help.

Suman

Former Member
0 Kudos

<a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses009.htm#i997450">here</a>

peter

david_malinconici2
Participant
0 Kudos

The syntax should be the following:

alter tablespace NAME default storage initial (or next) 1024k