on 01-25-2011 9:51 AM
Hi All,
When checked in se14 for the table BPTIME_BP1030 the NEXT EXTENT size is 20480 which is considerably higher.The total size of the database is itself is around 55 gb.I am not able to reduce the size of the next extent of the table.Due to which i am getting a error during sp import.Please dont consider as cross post since i had raised a post in NWA for the sp error.I am not quite sure of the error.Its just a assumption.Please correct me if i am wrong.Thanks.
3 ETP399 CREATE UNIQUE INDEX "BPTIME_BP1030~0" ON "BPTIME_BP1030"
3 ETP399 ("CLIENT",
3 ETP399 "GUID_K")
3 ETP399 PCTFREE 10
3 ETP399 INITRANS 002
3 ETP399 TABLESPACE PSAPBAL
3 ETP399 NOCOMPRESS
3 ETP399 STORAGE (INITIAL 0000000000 K
3 ETP399 NEXT 0000000000 K
3 ETP399 MINEXTENTS 0000000000
3 ETP399 MAXEXTENTS 0000000000
3 ETP399 PCTINCREASE 0000
3 ETP399 FREELISTS 001)
3 ETP399
2WETP000 18:56:47: Retcode 1: error in DDL statement for "BPTIME_BP1030 " - repeat
2EETP345 18:56:59: Retcode 1: SQL-error "2220-ORA-02220: invalid MINEXTENTS storage option value" i
2EETP345 n DDL statement for "BPTIME_BP1030 "
2 ETP399 -
DB-ROLLBACK() -
2EETP334 18:56:59: error in DDL, nametab for "BPTIME_BP1030" not activated
2 ETP301 -
3 ETP361 "0" Shadow-Nametabs activated, DDL executed
2 ETP362 "2" Shadow-Nametab activations failed
Hi!
You can do an
alter table <tablename> storage ( next 1M minextents 2 maxextents UNLIMITED pctincrease 0);
minextents needs to be a number >= 1.
After creating the table/index, you are not allowed to change initial extent.
By the way, most of the databases havve tablespaces set up with extent management=local and a uniform size of 1M Due to this, Oracle would create needed extents with size = 1M until it past the size you set next extent in table to be..
Eg you set next = 10M and uniform size = 1M, the db creates 10x1M extents for you.
pctincrease should be set to = (zero)
maxextents should be a number larger than 254 (default) either unlimited or 1024 / 2048
Regards
Audun
DBA
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi!
From what I can read from your logs, you should change storage parameters for the table and indexes you got error reported
I would try in sqlplus as the user owning the table and indexes:
alter [table|index] <table_name | index_name> storage (next 1M minextents 2 maxextents UNLIMITED pctincrease 0);
If it's a big table, consider if you want next increased to 10M.
If your initial extent are too big, eg 400M, you can se problems allocating space when doing export/import or client|system copy.
Regards
Audun
Hi,
Check SAP Note 1536104 - ORA-02220: invalid MINEXTENTS storage option value for your problem.
Thanks
Sunny
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Sunny,
Thanks for the response.Have done as mentioned in the note.We are having problems activating it.Its throwing up the below error.Thanks.
ORA-02220: invalid MINEXTENTS storage option value
DDL time(___3): .........1 milliseconds
The SQL statement was not executed
Statements for Table BPTIME_BP1030 could not be executed
Hi,
One more thing forgot to mention database 11g.
Have done as mentioned.Please let me know if we can anything else.Thanks.
SQL> create table sapbal.user_indexes_backup tablespace psapbal as select * from sapbal.user_indexes;
Table created.
SQL> commit;
Commit complete.
SQL> drop synonym sapbal.user_indexes;
Synonym dropped.
.
SQL> commit;
Commit complete.
SQL> drop view sys.sap_user_indexes;
View dropped.
SQL> commit;
Commit complete.
User | Count |
---|---|
83 | |
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.