Activities

April 2011
M T W T F S S
« Mar   Jun »
 123
45678910
11121314151617
18192021222324
252627282930  

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

While restoring a dump to my Oracle database, I was hit by an error which related to table quota. I have verified the table space which showing enough storage and still I’m getting such error.

Error at Command Line:1 Column:0
Error report:
SQL Error: ORA-01536: space quota exceeded for tablespace ‘TEMPLE’
01536. 00000 – “space quota exceeded for tablespace ‘%s'”
*Cause: The space quota for the segment owner in the tablespace has
been exhausted and the operation attempted the creation of a
new segment extent in the tablespace.
*Action: Either drop unnecessary objects in the tablespace to reclaim
space or have a privileged user increase the quota on this
tablespace for the segment owner.

This is something related to user permission against a tables space . The following command will solve the issue.

ALTER USER <user name> quota unlimited on <table space>;

For testing I use to create a table,

CREATE TABLE customers
   (
       id                NUMBER,
       credit_limit      NUMBER,
       email             VARCHAR2(30)
    );

and got the response

Table created.
0.08 seconds

Then I dropped it

DROP TABLE customers

The following command would help you to extend the table space.

ALTER TABLESPACE mytbl_space
ADD DATAFILE '/usr/lib/oracle/xe/oradata/XE/mytbl_space_02.dbf'
SIZE 900M;

This will extend the tablespace to 900Mb by adding new file to the existing data file.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>