on 07-31-2007 2:49 PM
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
Hi,
Is this answer your questions?
if yes please change status of the message and reward points
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ahmed,
Above is the result posted by Amit Jain.
Suman
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
<a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses009.htm#i997450">here</a>
peter
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The syntax should be the following:
alter tablespace NAME default storage initial (or next) 1024k
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.