Home » RDBMS Server » Server Administration » Problem In Permission
Problem In Permission [message #252847] Fri, 20 July 2007 04:49 Go to next message
subhajit
Messages: 14
Registered: December 2006
Junior Member
Hello All,

I am facing a problem in my database.
1. A is my table name, size is huge.
2. usr is the user, permission is normal, not DBA.

Now the problem is like this,

update A set
dno='D-00001',
counter='NR981',
checked='Y'
where appnumber='100123';

SQL>/
Update A set
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USR'

then, at the same time when I run the following query,
update A set
dno='D-00001',
counter='RN981',
checked='Y'
where appnumber='100123';
Its run successfully.

Then when I give DBA permission to 'USR'. Then every query runs fine with any values.

Plzz tell me why?

Re: Problem In Permission [message #252856 is a reply to message #252847] Fri, 20 July 2007 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You user can't update because he exceeds his quota.
When you gave DBA you remove this limit then he can update.

Regards
Michel
Re: Problem In Permission [message #252881 is a reply to message #252847] Fri, 20 July 2007 08:21 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
And the reason the second update is working is because it is operating on a much smaller number of rows, hence no additional extent will be needed in the tablespace.
Re: Problem In Permission [message #252897 is a reply to message #252881] Fri, 20 July 2007 09:24 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
Then when I give DBA permission to 'USR'. Then every query runs fine with any values.


You bounded yours user while your created by giving him default tablespace and quota on different tablesspaces where as Dba can access each and every tablespace .

When you applied DML as simple user tablespace filled and that user have no more free space thats why oracle server throws the error but when you ran the same command as DBA it runs successsfully beacuse if one tablespace get full it start writing in other.

SQL> create user test identified by test;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> select username,DEFAULT_TABLESPACE from user_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST                           DATA

SQL> create table test
  2  tablespace DATA01
  3  as
  4  select * from dba_users;

Table created.

SQL>


In above example i didnt gave any quota any default tablespace to test but i grant only DBA to test he can use every tablespace.

[Updated on: Fri, 20 July 2007 11:01]

Report message to a moderator

Re: Problem In Permission [message #252908 is a reply to message #252897] Fri, 20 July 2007 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST                           SYSTEM


Aaaargh this hurts! default tablespace SYSTEM!
Why your database default tablespace is not another one?

Quote:
SQL> create table test
  2  tablespace sysaux
  3  as
  4  select * from dba_users;


Aaah! This also hurts! Putting your stuff in SYSAUX tablespace!

Please, create a user tablespace for user objects (don't name it USER, of course).

Regards
Michel

[Updated on: Fri, 20 July 2007 10:44]

Report message to a moderator

Re: Problem In Permission [message #252909 is a reply to message #252908] Fri, 20 July 2007 10:45 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Thanks michel i know but that was an example.
Re: Problem In Permission [message #252915 is a reply to message #252909] Fri, 20 July 2007 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but this is example read by dozen of persons, some juniors that copy and paste examples and think what is posted by a senior can't be wrong.

Regards
Michel
Re: Problem In Permission [message #252917 is a reply to message #252915] Fri, 20 July 2007 11:00 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Got you.Sorry for mishapp.
Is it ok now?

[Updated on: Fri, 20 July 2007 11:02]

Report message to a moderator

Re: Problem In Permission [message #252925 is a reply to message #252917] Fri, 20 July 2007 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice! Being senior is a great responsability.
More you post more you have to pay attention of what you post.

Regards
Michel
Re: Problem In Permission [message #253008 is a reply to message #252847] Sat, 21 July 2007 07:54 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
2. usr is the user, permission is normal, not DBA.

Reason: when you grant DBA role to any user then "UNLIMITED TABLESPACE" privileges is also granted and user have unlimited space on tablespace.


SQL> create user tttt identified by tttt;

User created.

SQL> grant dba to tttt;

Grant succeeded.


SQL> conn tttt/tttt
Connected.


SQL> select * from session_privs where privilege like '%UNLIMITED%';

PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE


Regards
Taj
Previous Topic: Shutdown normal
Next Topic: Failed to create standby database, how do I resovle it?
Goto Forum:
  


Current Time: Thu Dec 08 08:31:17 CST 2016

Total time taken to generate the page: 0.08816 seconds