This content has been marked as final. Show 4 replies
974647 wrote:That's showing you the space already being used. The quotas for each user are obviously at least that large, but the query above doesn't say anything more about quotas. Query dba_ts_quotas to see the quota information.
SELECT OWNER,TABLESPACE_NAME,SUM(BYTES/1024/1024) "Allocated Size in MB"
FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'DATA_TS1'
GROUP BY OWNER,TABLESPACE_NAME ORDER BY OWNER;
will give this result
OWNER | TABLESPACE_NAME| Allocated Size in MB
30026882 | DATA_TS1 | 170.9375
30042534 | DATA_TS1 | 0.1875
myself | DATA_TS1 | 106951.875
now how can i just increase quota for 'myself'?
Can this be done? and if so can you plz provide me with syntax?
where x is the new number of megabytes.
ALTER USER myself QUOTA x M ON data_ts1;
There is no way to guarantee that other users won't use the newly created space if they have sufficiently large quotas. If you want to make some space available only to a given user, then you need a tablespace where nobody except that user has a quota.
Your query doesn't look at quotas at all - it is looking at space used (allocated to specific segments) within the tablespace. Those segments will grow as needed as contents of the table/index/LOB they support increase.
To look at tablespace quotas:
If any users listed in that query have max_bytes=-1 then they have been granted unlimited quota on the tablespace. If you increase the tablespace (add a data file, for example) all those users will have access to the added space. If everyone is governed by a quota on the tablespace, then a single schema owner's quota can be increased without affecting the others' quotas.
select * from dba_ts_quotas where tablespace_name='DATA_TS1';