SOLVED

ORA-01536: Space Quota Exceeded For Tablespace

Asked by OracleDba••14 views•oracle
1
2
3
4
5
ORA-01536: Space Quota Exceeded For Tablespace

Problem :

While creating a table or inserting data into a table, user may get this error : ORA-01536: space quota exceeded for tablespace.
#oracle#error

Solutions(1)

Accepted Solution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Tablespace quota

is the storage allocated for an user in a tablespace. Once the user reaches the max allocated space it will throw an error like this.

SQL> select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username='LEARNOMATE';

TABLESPACE_NAME USERNAME BYTES/1024/1024 MAX_BYTES/1024/1024

----------------------- -------- --------------- -------------------

USER LEARNOMATE 12 20

Here LEARNOMATE user can use maximum upto 20MB space of USER table space. Currently it used upto 12MB.

SQL> create table TEST tablespace USER as select * from dba_objects;

create table TEST tablespace USER as select * from dba_objects

*

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'USER'

Now to fix it , increase the quota for that user.

SQL> alter user LEARNOMATE quota 50M on USER;

User altered.

SQL> select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username='LEARNOMATE';

TABLESPACE_NAME USERNAME BYTES/1024/1024 MAX_BYTES/1024/1024

----------------------- -------- --------------- -------------------

USER LEARNOMATE 12 50

Now create the object

SQL> create table TEST tablespace USER as select * from dba_objects;

Table created.

SQL> select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username='LEARNOMATE';

TABLESPACE_NAME USERNAME BYTES/1024/1024 MAX_BYTES/1024/1024

----------------------- -------- --------------- -------------------

USER LEARNOMATE 24 50

Object created successfully.
OracleDba•

Post Your Solution