7 Replies Latest reply: Feb 27, 2013 12:49 PM by TSharma-Oracle RSS

    Quotas on tablespace

    792290
      how do i find if quota set to 0 for a particular user in a tablespace?

      Thanks...
        • 1. Re: Quotas on tablespace
          sb92075
          789287 wrote:
          how do i find if quota set to 0 for a particular user in a tablespace?

          Thanks...
          post SQL showing how you set quota for user on a tablespace

          Handle:     789287
          Status Level:     Newbie
          Registered:     Aug 18, 2010
          Total Posts:     228
          Total Questions:     97 (87 unresolved)

          why do you waste your time & our time here when you rarely get your questions answered?
          • 2. Re: Quotas on tablespace
            Rob_J
            This might help:
            SQL> select table_name from dict where table_name like '%QUOTA%';
            
            TABLE_NAME
            ------------------------------
            DBA_TS_QUOTAS
            USER_TS_QUOTAS
            
            SQL> desc DBA_TS_QUOTAS
             Name                                      Null?    Type
             ----------------------------------------- -------- ----------------
             TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
             USERNAME                                  NOT NULL VARCHAR2(30)
             BYTES                                              NUMBER
             MAX_BYTES                                          NUMBER
             BLOCKS                                             NUMBER
             MAX_BLOCKS                                         NUMBER
             DROPPED                                            VARCHAR2(3)
            • 3. Re: Quotas on tablespace
              Osama_Mustafa
              select username,tablespace_name,bytes/(1024*1024),max_bytes/(1024*1024) from dba_ts_quotas;
              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3396797633001
              http://www.oradba.ch/2011/04/find-user-with-unlimited-tablespace-quota/
              • 4. Re: Quotas on tablespace
                792290
                when i query using this statement
                select username,tablespace_name,bytes/(1024*1024),max_bytes/(1024*1024) from dba_ts_quotas;

                you can tell if we gave unlimited tablespace where -1 of max_bytes denotes.

                for example:

                you have system tablespace where TEST user is using by default, so protect that you alter the system tablespace to set the quota 0.

                in this case how do i tell that system Tablespace has 0 quota for TEST user?
                • 5. Re: Quotas on tablespace
                  TSharma-Oracle
                  how do i tell that system Tablespace has 0 quota for TEST user?
                  When you assign 0 quota , you will not able to see that entry of that username with that tablespace in dba_ts_quotas.

                  If you run, 'alter user test quota 0 on system', you will not able to see an entry of *'TEST'* username and '*SYSTEM'* tablespace together in dba_ts_quotas.
                  Your entry in dba_ts_quotas will be gone.
                  • 6. Re: Quotas on tablespace
                    792290
                    Thanks for all of your help, other than SB.
                    • 7. Re: Quotas on tablespace
                      TSharma-Oracle
                      You are welcome... SB was right in his own place maybe he was bit rude but he was right. People here in this forum loves to help but you should mark your answers either helpful,correct or Answered. You have 87 unanswered questions and most of them seems to be answered. It is your forum and it is your duty to keep your forum clean by marking your questions as answered.