ORA-25143: default storage clause is not compatible with allocation policy
When I set extent management to local I'm getting this error message: "ORA-25143: default storage clause is not compatible with allocation policy." How can I resolve this problem?
#--- Executing SQL statement: create tablespace pin00 datafile 'D:/ORACLE/ORADATA/TRAINER/pin00.dbf' size 600M reuse autoextend on extent management dictionary default storage ( initial 64K next 64K pctincrease 0 ); #------------- SQL Results: Executing sqlplus -s system/training@trainer < "e:/infranet/setup/temp/tmp.sql" create tablespace pin00 datafile 'D:/ORACLE/ORADATA/TRAINER/pin00.dbf' size 600M reuse autoextend on extent management dictionary default storage ( initial 64K next 64K pctincrease 0 ) * ERROR at line 1: ORA-12913: Cannot create dictionary managed tablespaceWhen I set extent management to local I'm getting this error message.
SQL> create tablespace pin00 datafile SQL> 'D:/ORACLE/ORADATA/TRAINER/pin00.dbf' size 600M reuse autoextend on SQL> extent management local default storage( initial 64K next 64K SQL> pctincrease 0 ); create tablespace pin00 datafile 'D:/ORACLE/ORADATA/TRAINER/pin00.dbf' size 600M reuse autoextend on extent management local default storage ( initial 64K next 64K pctincrease 0 ) * ERROR at line 1: ORA-25143: default storage clause is not compatible with allocation policyHow can I resolve this problem?
If your database was created with a locally managed SYSTEM tablespace, then you cannot create dictionary-managed tablespaces. And I would recommend locally managed tablespaces (LMTs) every time.
As the ORA-25143 error states, your DEFAULT STORAGE clause is not compatible with your allocation policy. I would change the SQL statement to be the following:
CREATE TABLESPACE pin00 DATAFILE 'D:/ORACLE/ORADATA/TRAINER/pin00.dbf' SIZE 600M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 20000M EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT STORAGE (PCTINCREASE 0);
The first thing I would do is to limit the datafile maximum size. This way, a rogue process will not cause the datafile to extend farther than you may want it to.
The part of the DEFAULT STORAGE clause that you do not need with LMTs is the INITIAL and NEXT extent sizes. Since the tablespace is locally managed, Oracle will figure out these extent sizes for you.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments