Oracle ORA-1693 Errors and table space movement of tables and indexes

The company developed software, customers can not upload large attachments, the page does not report any errors. View the JBoss log, also see no errors, the software itself to eliminate the fault.

When you view the Oracle database log, you receive an error message when uploading large attachments

Oea_1693:max # extents 4096 reached in lobsegment

nes.sys_lob0000024832c00008$$

Resolution process

On the Internet to find the information

ORA-1693 Max # Extents (string) reached in LOB segment string.string

Cause:a LOB segment tried to extend past maxextents.

Action:if Maxextents is less than the system Max, raise it. Otherwise, you are must re-create with larger initial, next or pctincrease parameters.

Change the maxextents of the table space to unrestricted, or the problem is to change the table's maxextents to unrestricted

ALTER TABLE t_id_check_accessories Storage (Maxextents Unlimited)

The same problem cannot be solved.

Later, when I looked at the table structure, I accidentally discovered that the table space used for the table was for users and did not use the tablespace test that I created for it, because the database was importing data with IMP, which might be the Oracle default tablespace that the user used when exp exported.

I decided to modify the table space on this table to test

Alter table t_id_check_accessories move tablespace test;

After modifying the table, the test system will normally transmit large attachments

Solve the problem or confused, why the table with the Users table space will appear ORA-01693 problem, change to test table space after normal, users and test maxextents are unrestricted.

It suddenly occurred to me that all the tables below the user are also in the Users table space, and decided to move all the tables to test because there are more tables, generate the required command with a query

Select ' ALTER TABLE ' | | table_name | | ' Move tablespace test; ' From User_tables;

And then execute the results generated by the query

Move the index under this user to the table space test

Select ' alter index ' | | index_name | | ' Rebuild tablespace test; '

After executing the command generated by this query, 4 tables were found to have an error

Ora-02327:cannot CREATE index on expression with datatype LOB

After checking the structure of the 4 tables, there is a LOB field that indicates that the index of the LOB field does not use the normal

Alter index INDEX_NAME rebuild Talespace tablespace_name

Online query Knowing that we are moving the LOB's index must use a specific syntax to move the LOB's object, as follows:

ALTER TABLE table_name Move

Tablespace new_tbsp

STORAGE (New_storage)

LOB (Lobcol)--The following parentheses contain the LOB field name

STORE as Lobsegment

(Tablespace new_tbsp

STORAGE (New_storage));

Check to see if there is an indexed status of ' unusable ' select index_name from user_indexes t where T.status = ' unusable ', or rebuild alter index index_name Rebuild

Use the above syntax when modifying these 4 indexes, normal!!!

All the problems are solved, but many places or confused (only blame themselves too much food) only later to strengthen the study, slowly understand. My blog address is: tianchi.csai.cn everyone to communicate Oh!