Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: why create index uses system tablespace?
It seems the Oracle User which is creating the index,
when it was created it was never assigned a temporary
tablespace.
When you create a user without assigning a temporary tablespace to that user then Oracle defaults temporary tablespace to SYSTEM. To correct this problem you should alter the user to explictly assign a tablespace other than SYSTEM.
You can use following SQL to alter the user:-
ALTER USER <USERNAME> TEMPORARY TABLESPACE <TEMP_TABLESPACE_NAME>;
example will be, If I have user called MANOJ and a tablespace for temporary segments created already with name TEMP_TS then DBA (SYSTEM user) can issue following statement:
ALTER USER MANOJ TEMPORARY TABLESPACE TEMP_TS; When you are doing this you should also check DEFAULT TABLESPACEs and TEMPORARY TABLESPACEs for all users. For this you can use following query:-
SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS; This query will list all Oracle Users and their default tablespaces and temporary tablesapces.
Only SYS user should have SYSTEM tablespace as DEFAULT_TABLESPACE, all other users should have their DEFAULT_TABLESPACE other than SYSTEM tablespace. And TEMPORARY_TABLESPACE for all users should never be SYSTEM tablespace. If you find results different than what I mentioned then you should get alerted and take corrective action, you may need an expert help.
Hope this helps,
Manoj Jain
Oracle Certified Professional - OCP DBA
Chauncey Certified Oracle7 DBA
In article <72iftd$2j9$1_at_nnrp1.dejanews.com>,
badstreetboy_at_my-dejanews.com wrote:
> When I tried to create an index on a large table, I found that it used a large
> amount of size in the SYSTEM tablespace during the process. Sometimes it even
> give me an error message: ORA01652: unable to extend temp segment by num in
> tablespace SYSTEM.
>
> Why is that happen and how to prevent that?? THanks!!
>
> BSB
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Nov 13 1998 - 19:27:58 CST