space quota exceeded for tablespace system

[lgaorcl]

How come user get ora_1536: space quota exceeded for tablespace 'SYSTEM', though I never give them quota on system tablespace. The following is the query:

insert into
tpub.sgml_proc
(
sgml_proc_id,
proc_title_id,
content_id,
lan
)
select
'n306802',
'3963',
'170083',
lan
from
tpub.language_codes
/
tpub.sgml_proc
*
ERROR at line 2:
ORA-01536: space quota exceeded for tablespace 'SYSTEM'


Thank you!

[jdorlon]

Your tpub.sgml_proc table is in the SYSTEM tablespace.
You are getting the error because the table needs to extend
and there is no quota in the SYSTEM tablespace for that
user.

You should move that table - and any other table you have
created - out of the SYSTEM tablespace and grant whatever
quotas are needed on the other tablespaces....

-John

[Mr.Hanky]

I would run this query just to check all your users.

select username, default_tablespace, temporary_tablespace
from dba_users
order by 2

I would also be more careful in the future when creating tables.
MH

[lgaorcl]

It's not so easy. User tpub's default tablespace is tpubdata, and has no quota on system tablespace.

Table sgml_proc is in tablespace tpubdata.

[marist89]

to verify , run this query:

select owner, segment_name, tablespace_name
from dba_segments
where owner = 'TPUB'
and segment_name = 'SGML_PROC'

[lgaorcl]

Thank you!

Problem is resource role somehow got screwd up!

Lisa