This discussion is archived
7 Replies Latest reply: Feb 27, 2013 10:49 AM by TSharma-Oracle RSS

Quotas on tablespace

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

Thanks...
  • 1. Re: Quotas on tablespace
    sb92075 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks for all of your help, other than SB.
  • 7. Re: Quotas on tablespace
    TSharma-Oracle Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points