5 Replies Latest reply: Feb 22, 2013 2:36 PM by JustinCave RSS

    Allocating more space for database

    977650
      Hi,

      I have a requirement to ask dba to allocate more space to my oracle database(11g). i know to do this he just have to do
      "alter tablespace tspc1 add data file 'data01.ora'"

      but my condition is that there are many schemas in same database, what i want him to do is to allocate more space to my schema alone and distribute rest space to all others. lets say current db has 4tb space, i want to add 2tb space more, i want to allocate my schema alone 1tb and rest all schemas 1 tb (total 2 tb)

      is this possible? if so please let me know the way out.

      Thanks
        • 1. Re: Allocating more space for database
          JustinCave
          Schemas are granted quota on tablespaces. Data files are added to tablespaces.

          Assuming that none of the schemas have been granted unlimited quota on the tablespace in question, you would simply have to ask the DBA to increase your schema's quota by 1 TB and the quota of all the other schemas on that tablespace by a total of 1 TB.

          Frequently, if you have this sort of environment, the DBA would create separate tablespaces for the different uses so that, for example, a tablespace would be created just for your schema.

          Justin
          • 2. Re: Allocating more space for database
            977650
            Thanks Justin for quick reply,

            yes you are right that none of schemas have been given unlimited tablespace. but still dba is bit hesitant to do this thing.

            can you tell me any scenario where such allocation(1 tb to my tablespace and rest to other) will be not recomended or not possible?

            Also if possible can you please give me the syntax to do so?

            Thanks
            • 3. Re: Allocating more space for database
              JustinCave
              974647 wrote:
              yes you are right that none of schemas have been given unlimited tablespace. but still dba is bit hesitant to do this thing.
              What makes the DBA hesitant?
              can you tell me any scenario where such allocation(1 tb to my tablespace and rest to other) will be not recomended or not possible?
              Talking about what is "recommended" will depend heavily on exactly what the business problem you are trying to solve is (not what technical approach you have decided to take). What is the relationship between the various schemas? Why are they using a single tablespace?
              Also if possible can you please give me the syntax to do so?
              ALTER USER your_schema_name QUOTA <<existing quota + 1 TB>> ON tablespace_name
              ALTER USER some_other_schema QUOTA <<existing quota + 1 TB>> ON tablespace_name
              Justin
              • 4. Re: Allocating more space for database
                977650
                Hi

                may be i should rephrase my question. actually the tablespace that i own is shared by many other 'owners' 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 when i ask him to just increase quota for 'myself', he is hesitant.

                Can this be done? and if so can you plz provide me with syntax?

                Thanks in advance

                Edited by: 974647 on Feb 22, 2013 11:07 AM

                Edited by: 974647 on Feb 22, 2013 11:08 AM
                • 5. Re: Allocating more space for database
                  JustinCave
                  974647 wrote:
                  may be i should rephrase my question. actually the tablespace that i own is shared by many other 'owners' but i want the datasize just for me to be increased.
                  Why from a business standpoint do the various users share that tablespace? Why from a business standpoint is it important that the newly allocated space only goes to one user?

                  It sounds from your description and without understanding what the initial motiviation for this setup was that a separate tablespace should be created just for your user (and anyone that you want to share your quota).
                  now when i ask him to just increase quota for 'myself', he is hesitant.
                  Why is he hesitant? I asked this earlier. Without understanding the business problem, it's hard to offer anything more.
                  Can this be done? and if so can you plz provide me with syntax?
                  It can. I provided the syntax in my last post. Is there some problem with what I posted?

                  Justin