Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: why create index uses system tablespace?

Re: why create index uses system tablespace?

From: <mjain_at_my-dejanews.com>
Date: Sat, 14 Nov 1998 01:27:58 GMT
Message-ID: <72imau$7t7$1@nnrp1.dejanews.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US