| Problem In Permission [message #252847] |
Fri, 20 July 2007 04:49  |
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 #252881 is a reply to message #252847] |
Fri, 20 July 2007 08:21  |
joy_division
Messages: 4963
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 |
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 |
 |
Michel Cadot
Messages: 68686
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 #253008 is a reply to message #252847] |
Sat, 21 July 2007 07:54 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
