4 Replies Latest reply: Feb 22, 2013 1:51 PM by 977650 RSS

    increasing tablespace quota

    977650
      Hi

      i have a requirement in which i need to increase tablespace size for my schema. actually the tablespace that is given to me is shared by many other 'owners' too but i want the datasize just for me to be increased.

      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?
        • 1. Re: increasing tablespace quota
          sb92075
          974647 wrote:

          Can this be done?
          no
          • 2. Re: increasing tablespace quota
            Frank Kulash
            Hi,
            974647 wrote:
            ...
            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
            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.
            now how can i just increase quota for 'myself'?
            Can this be done? and if so can you plz provide me with syntax?
            ALTER USER myself    QUOTA x M    ON data_ts1;
            where x is the new number of megabytes.

            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.
            • 3. Re: increasing tablespace quota
              977650
              thnx
              • 4. Re: increasing tablespace quota
                Brian Bontrager
                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:
                select * from dba_ts_quotas where tablespace_name='DATA_TS1';
                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.