Home » RDBMS Server » Server Administration » How to increase the space quote on a tablespace?
icon8.gif  How to increase the space quote on a tablespace? [message #195701] Sun, 01 October 2006 09:43 Go to next message
lnreddy
Messages: 6
Registered: October 2005
Location: Pune, India
Junior Member
Hi,

I got the following ORA error when i am creating a new table in Oracle 9i database.

ORA-01536: Space quota exceeded for tablespace 'SYSTEM'
(Logged on user: Scott with dba privileges)

could somebody help me out a solution for this.

Thanks in advance
LN Reddy
Re: How to increase the space quote on a tablespace? [message #195702 is a reply to message #195701] Sun, 01 October 2006 10:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
User SCOTT needs more quota on tablespace SYSTEM.
Had DBA role be properly assigned, by default user SCOTT should have a RESOURCE role granted, which has unilimited quota on all tablespaces.

-- for a specific quota
alter user scott quota yourChoice_in_megs on system;
-- For unlimited quota
alter user scott quota unlimited on system;

But first,
NO custom user should have any rights on SYSTEM tablespace.
If this database is for fun/learning it is Ok.
Else,
user SCOTT has no buiness to do with SYSTEM tablespace.

[Updated on: Sun, 01 October 2006 11:15]

Report message to a moderator

Re: How to increase the space quote on a tablespace? [message #195703 is a reply to message #195701] Sun, 01 October 2006 10:40 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
ALTER USER scott QUOTA UNLIMITED ON system;

Note that SYSTEM is not the best tablespace for user tables!
Re: How to increase the space quota on a tablespace? [message #195706 is a reply to message #195702] Sun, 01 October 2006 11:13 Go to previous messageGo to next message
lnreddy
Messages: 6
Registered: October 2005
Location: Pune, India
Junior Member
Mahesh,

when i tried with

ALTER USER SCOTT QUOTA UNLIMITED ON SYSTE:

system showing ORA-01031 insufficient privileges

but when i checked in SYSTEM_PRIVILEGES_MAP for scott
it has the following privileges:

Privilege name property

-163. ALTER RESOURCE COST 0
-227. ADMINISTER RESOURCE MANAGER 1
and many more------------

Re: How to increase the space quota on a tablespace? [message #195707 is a reply to message #195706] Sun, 01 October 2006 11:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As i said before,
SCOTT has never been granted with DBA privs/roles.
Login in as a DBA or SYSDBA and do this.
Re: How to increase the space quota on a tablespace? [message #195708 is a reply to message #195707] Sun, 01 October 2006 11:21 Go to previous messageGo to next message
lnreddy
Messages: 6
Registered: October 2005
Location: Pune, India
Junior Member
Mahesh,

I am practesing on Oracle 9i and i know the default user scott/tiger only. so i connected with scott.

if you could help me in how to connect with DBA privs (default) and password in oracle that could be helpful for me.

thanks in advance
Re: How to increase the space quota on a tablespace? [message #195711 is a reply to message #195708] Sun, 01 October 2006 11:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
All required information is documented and is free.
Oracle adminstration guide is very good resource to start.
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96521/toc.htm
Re: How to increase the space quota on a tablespace? [message #195712 is a reply to message #195711] Sun, 01 October 2006 11:48 Go to previous message
lnreddy
Messages: 6
Registered: October 2005
Location: Pune, India
Junior Member
thanks a ton mahesh
i did it.
Previous Topic: ORA-12750 - Cannot access NLS data files or invalid environment specified
Next Topic: Shared Server Configuration Problem
Goto Forum:
  


Current Time: Fri Dec 09 03:49:57 CST 2016

Total time taken to generate the page: 0.21986 seconds