Home > TroubleShooting > ORA-01536: space quota exceeded for tablespace ‘SGDM_TBS’

ORA-01536: space quota exceeded for tablespace ‘SGDM_TBS’

December 22, 2009 Leave a comment Go to comments

Symptoms

1. Logon as user account to create a test table

SQL> create table test as select * from all_tables;
create table test as select * from all_tables
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘SGDM_TBS’

2. Check space quota on the tablespace ‘SGDM_TBS’

SQL> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
—————————— ———- ———- ———- ———- —
SGDM_TBS                         33030144          0       4032          0 NO

Causes

The user account has no quota unlimited privilege on its default tablespace ‘SGDM_TBS’

Fixed

Logon as dba; grant quota unlimited on its tablespace

SQL>  alter user epg quota unlimited on SGDM_TBS ;

User altered.

SQL> select tablespace_name,bytes,max_bytes, username from  dba_ts_quotas ;

TABLESPACE_NAME                     BYTES  MAX_BYTES USERNAME
—————————— ———- ———- ——————————
SYSAUX                           16318464         -1 OLAPSYS
SYSAUX                           12582912         -1 WK_TEST
SYSAUX                          169213952         -1 SYSMAN
SYSAUX                             458752         -1 FLOWS_FILES
SGDM_TBS                         85590016         -1 EPG

Max_bytes=-1 means that user EPG has quota unlimited on tablespace SGDM_TBS

Advertisements
Categories: TroubleShooting
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: