1 2 Previous Next 25 Replies Latest reply: Mar 22, 2010 8:10 AM by Rafi (Oracle DBA) RSS

    Creating user results in quota -1

    Rafi (Oracle DBA)
      Hi All,
      If we create any user its quota is -1 What can be the reason for this.

      Is the dba_ts_quotas or user_ts_quotas will provide me the quota related details of any user or we can use any other views.Can you please provide your useful suggestions



      Best regards,

      Rafi.
      http://rafioracledba.blogspot.com/
        • 1. Re: Creating user results in quota -1
          asifkabirdba
          Check dba_ts_quotas; - max_bytes shows –1. that means unlimited.


          Regards
          Asif Kabir
          • 2. Re: Creating user results in quota -1
            NavneetU
            Hi,

            Whenever we give a Quota unlimited on some tablespace to some user, -1 is set in the max_bytes column of the user_ts_quotas view.
            SQL> create user test identified by test default tablespace users;
            
            User created.
            
            SQL> grant create session,create table to test;
            
            Grant succeeded.
            
            SQL> conn test/test
            Connected.
            SQL> select * from user_ts_quotas;
            
            no rows selected
            
            SQL> conn / as sysdba
            Connected.
            SQL> alter user test quota unlimited on users;
            
            User altered.
            
            SQL> conn test/test
            Connected.
            
            SQL> select * from user_ts_quotas;
            
            TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
            ------------------------------ ---------- ---------- ---------- ---------- ---
            USERS                                   0         -1          0         -1 NO
            
            SQL> create table test (abc varchar2(10));
            
            Table created.
            
            SQL> select * from user_ts_quotas;
            
            TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
            ------------------------------ ---------- ---------- ---------- ---------- ---
            USERS                               65536         -1          8         -1 NO
            Regards
            • 3. Re: Creating user results in quota -1
              Lubiez Jean-Valentin
              Hello,


              QUOTA -1 means that the QUOTA is unlimited.


              Hope this help.
              Best regards,
              Jean-Valentin
              • 4. Re: Creating user results in quota -1
                Pavan Kumar
                Hi,

                User are had the Option to connect to the schema but he cannot create the Objects, perhaps if we are going to
                modify existing quota with Zero, its nothing but revoked from particular tablespace

                - Pavan Kumar N
                ORACLE 9i/10g - OCP
                http://www.oracleinternals.blogspot.com
                • 5. Re: Creating user results in quota -1
                  Rafi (Oracle DBA)
                  Thanks every one for the usefull suggestion.But still I would like to know if I create any user and making any tablespace eg default .What will be quota for this?

                  Is it Zero.Can you please let me know some thing on this.




                  Best regards,

                  Rafi
                  • 6. Re: Creating user results in quota -1
                    Aman....
                    You should had referred the docs for the same, the answer is there
                    http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/statviews_5068.htm#REFRN23294

                    HTH
                    Aman....
                    • 7. Re: Creating user results in quota -1
                      695836
                      Hi Rafi,
                      You are 9i,10g,11g OCP holder and RAC certifed also.
                      These things are taught in these courses.
                      • 8. Re: Creating user results in quota -1
                        Rafi (Oracle DBA)
                        Hi Aman,
                        Thanks very much but I know that if we give unlimited quota than the max_bytes column value is -1.I just want to confirm if we do not give any quota what quota will a user allocate? Hope you get me? Is it 0 bytes in max_bytes ?


                        Best regards,

                        Rafi.
                        • 9. Re: Creating user results in quota -1
                          asifkabirdba
                          If any users haven't specified to any default tablespace then

                          select *
                          from dba_ts_quotas;

                          This query won't return any rows about that schema.

                          After assigning a default tablespace it will show from the above query.


                          Regards
                          Asif Kabir
                          • 10. Re: Creating user results in quota -1
                            Rafi (Oracle DBA)
                            Hi All & Hashmi,
                            Please do not misinterpret my question.I want to know the reason why the value is like tha?I know I'm OCP in 9i,10g & 11g & RAC expert too I don't meen what the values as Each one will be having their own views and reason and will be good in one or the other things.I feel the forum also mean the same.



                            Best regards,

                            Rafi.
                            • 11. Re: Creating user results in quota -1
                              Pavan Kumar
                              Hi,

                              By Default from ORACLE Doc's Says MAX_BYTES - User's quota in bytes, or -1 if no limit
                              and ther is chance of my MAX_BYTES column to be 0, perhaps If we alter manually

                              alter user user_name quota 0k on users_tbs;

                              0k quota in tablesapce, not unlimited quota on tablespace is not valid

                              - Pavan Kumar N
                              ORACLE 9i/10g - OCP
                              http://www.oracleinternals.blogspot.com
                              • 12. Re: Creating user results in quota -1
                                Rafi (Oracle DBA)
                                Hi Asif,
                                You got my question I feel thanks very much.I want to know why the query is not resulting in 0 why it is resulting in no rows.
                                Hope everyone get me now?



                                Best regards,

                                Rafi.
                                • 13. Re: Creating user results in quota -1
                                  695836
                                  Hi,
                                  Ok..
                                  no quota means nothing.When user is not given any quota,its quota related information does not exist in dba_ts_quotas.
                                  SQL> create user test identified by test default tablespace users temporary tablespace temp;
                                  
                                  User created.
                                  
                                  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)
                                  
                                  SQL> select * from dba_ts_quotas where username='TEST';
                                  
                                  no rows selected
                                  
                                  SQL> alter user test quota 10m on users;
                                  
                                  User altered.
                                  
                                  SQL>  select * from dba_ts_quotas where username='TEST';
                                  
                                  TABLESPACE_NAME                USERNAME                            BYTES
                                  ------------------------------ ------------------------------ ----------
                                   MAX_BYTES     BLOCKS MAX_BLOCKS DRO
                                  ---------- ---------- ---------- ---
                                  USERS                          TEST                                    0
                                    10485760          0       1280 NO
                                  
                                  
                                  SQL> alter user test quota unlimited on users;
                                  
                                  User altered.
                                  
                                  SQL>  select * from dba_ts_quotas where username='TEST';
                                  
                                  TABLESPACE_NAME                USERNAME                            BYTES
                                  ------------------------------ ------------------------------ ----------
                                   MAX_BYTES     BLOCKS MAX_BLOCKS DRO
                                  ---------- ---------- ---------- ---
                                  USERS                          TEST                                    0
                                          -1          0         -1 NO
                                  -1 is just awhat oracle used to denote unlimited quota.
                                  • 14. Re: Creating user results in quota -1
                                    asifkabirdba
                                    This is how oracle corporation design that view, we are just the user of oracle database software. Only oracle corporation can answer your question.


                                    Regards
                                    Asif Kabir
                                    1 2 Previous Next