Oracle Tips and Tricks — David Fitzjarrell

March 11, 2009

How Much Wood Could A Woodchuck Chuck …

Filed under: General — dfitzjarrell @ 14:46

Some of the most interesting questions pass through http://www.google.com, like this one:

“how to know how much table space is allocated for a user in oracle”

How DO we know how much space a given user account can consume in a tablespace? Two views can produce that report, DBA_TS_QUOTAS and DBA_DATA_FILES. Let’s see how that can be done.

DBA_TS_QUOTAS provides information on which tablespaces a given user can use and how much space they can consume:

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) (available in 11g)

SQL>

This view lists the tablespaces for which a user has an assigned quota, so not all of the tablespaces in the database will be listed for non-DBA users. MAX_BYTES reports the actual size of the granted quota and if that quota is UNLIMITED the value of MAX_BYTES is -1. Likewise for MAX_BLOCKS, which translates the MAX_BYTES column into blocks based upon the db_block_size parameter set at database creation.

Onward and upward to DBA_DATA_FILES:

SQL> desc dba_data_files
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL>

The columns of interest in this view are TABLESPACE_NAME, BYTES and BLOCKS since we’ll sum the last two columns, by tablespace, to obtain the total configured space available.

Now it’s time to put this all together and return our report. We’ll use subquery factoring to assist in this query (available in 9i and later releases of Oracle):

SQL> with ttlbytes as (
  2     select tablespace_name, sum(bytes) ttl_bytes, sum(blocks) ttl_blocks
  3     from dba_data_files
  4     group by tablespace_name
  5  ),
  6  userquotas as(
  7     select
  8     TABLESPACE_NAME,
  9     USERNAME,
 10     BYTES,
 11     max_bytes,
 12     blocks,
 13     MAX_BLOCKS
 14     from dba_ts_quotas
 15     where username = upper('&&1')
 16  )
 17  select
 18  s.tablespace_name,
 19  nvl(q.username, upper('&&1')) username,
 20  nvl(q.bytes,0) bytes,
 21  case when q.MAX_BYTES = -1 then s.ttl_bytes else nvl(q.max_bytes,0) end max_bytes,
 22  nvl(q.BLOCKS,0) blocks,
 23  case when q.MAX_BLOCKS = -1 then s.ttl_blocks else nvl(q.max_blocks,0) end max_blocks
 24  from userquotas q full outer join ttlbytes s
 25        on (q.tablespace_name = s.tablespace_name)
 26  order by q.username, s.tablespace_name;

TABLESPACE_NAME                USERNAME        BYTES   MAX_BYTES     BLOCKS  MAX_BLOCKS
------------------------------ ---------- ---------- ----------- ---------- -----------
TOOLS                          BING                0    10485760          0        1280  
USERS                          BING         17235968  2899247104       2104      353912
EXAMPLE                        BING                0           0          0           0
STATDATA                       BING                0           0          0           0
SYSAUX                         BING                0           0          0           0
SYSTEM                         BING                0           0          0           0
UNDOTBS1                       BING                0           0          0           0

7 rows selected.

SQL>

Notice the report returns all of the tablespaces in the database, with user information for the tablespaces the given user can utilize.

Can this be done for a database release earlier than 9i? You bet (but, really, you should upgrade to a supported release of Oracle):

SQL> select
  2  s.tablespace_name,
  3  nvl(q.username, upper('&&1')) username,
  4  nvl(q.bytes,0) bytes,
  5  decode(q.MAX_BYTES, -1, s.ttl_bytes, nvl(q.max_bytes,0)) max_bytes,
  6  nvl(q.BLOCKS,0) blocks,
  7  decode(q.MAX_BLOCKS, -1, s.ttl_blocks, nvl(q.max_blocks,0)) max_blocks
  8  from (
  9     select
 10     TABLESPACE_NAME,
 11     USERNAME,
 12     BYTES,
 13          max_bytes,
 14     blocks,
 15     MAX_BLOCKS
 16     from dba_ts_quotas
 17     where username = upper('&&1')
 18  ) q, (
 19     select tablespace_name, sum(bytes) ttl_bytes, sum(blocks) ttl_blocks
 20     from dba_data_files
 21     group by tablespace_name
 22  ) s
 23  where q.tablespace_name (+)= s.tablespace_name
 24  order by q.username, s.tablespace_name;

TABLESPACE_NAME                USERNAME           BYTES   MAX_BYTES     BLOCKS  MAX_BLOCKS
------------------------------ ------------- ---------- ----------- ---------- -----------
TOOLS                          BING                   0    10485760          0        1280
USERS                          BING            17235968  2899247104       2104      353912
EXAMPLE                        BING                   0           0          0           0
STATDATA                       BING                   0           0          0           0
SYSAUX                         BING                   0           0          0           0
SYSTEM                         BING                   0           0          0           0
UNDOTBS1                       BING                   0           0          0           0

7 rows selected.

SQL>

So answering the question listed at the start of this post is fairly easy and straightforward. And you get even more information than originally requested since you also see the tablespaces for which a given user has no quota assigned. Pretty slick if you ask me.

This report query can be modified to also flag when a user is close to reaching the limit on space in a tablespace. The 9i and later version becomes:

SQL> SQL> with ttlbytes as (
  2     select tablespace_name, sum(bytes) ttl_bytes, sum(blocks) ttl_blocks
  3     from dba_data_files
  4     group by tablespace_name
  5  ),
  6  userquotas as(
  7     select
  8     TABLESPACE_NAME,
  9     USERNAME,
 10     bytes,
 11     nvl(max_bytes,0) max_bytes,
 12     nvl(blocks,0) blocks,
 13     nvl(max_blocks,0) MAX_BLOCKS
 14     from dba_ts_quotas
 15     where username = upper('&&1')
 16  )
 17  select
 18  s.tablespace_name,
 19  nvl(q.username, upper('&&1')) username,
 20  nvl(BYTES,0) bytes,
 21  case when nvl(q.MAX_BYTES,0) = -1 then s.ttl_bytes else nvl(q.max_bytes,0) end max_bytes,
 22  nvl(blocks, 0) blocks,
 23  case when nvl(q.MAX_BLOCKS,0) = -1 then s.ttl_blocks else nvl(q.max_blocks,0) end max_blocks,
 24  case when nvl(q.bytes,0) > 0 and abs(nvl(q.max_bytes,0) - nvl(q.bytes,0))  0 and abs(nvl(q.max_bytes,0) - nvl(q.bytes,0)) between 102400 and 10240000 then 'WARNING'
 26       else 'OK' end status
 27  from userquotas q full outer join ttlbytes s
 28        on (q.tablespace_name = s.tablespace_name)
 29  order by q.username, s.tablespace_name;

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS STATUS
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
INDX                           BINGNORFER                              0  524288000          0      64000 OK
USERS                          BINGNORFER                      320864256  524288000      39168      64000 OK
DFLT                           BINGNORFER                              0          0          0          0 OK
SYSAUX                         BINGNORFER                              0          0          0          0 OK
SYSTEM                         BINGNORFER                              0          0          0          0 OK
UNDOTBS1                       BINGNORFER                              0          0          0          0 OK

6 rows selected.

SQL>

And for releases earlier than 9i:

SQL> select
  2  s.tablespace_name,
  3  nvl(q.username, upper('&&1')) username,
  4  nvl(q.bytes,0) bytes,
  5  decode(q.MAX_BYTES, -1, s.ttl_bytes, nvl(q.max_bytes,0)) max_bytes,
  6  nvl(q.BLOCKS,0) blocks,
  7  decode(q.MAX_BLOCKS, -1, s.ttl_blocks, nvl(q.max_blocks,0)) max_blocks,
  8  decode(sign(decode(decode(q.max_bytes, -1, s.ttl_bytes, q.max_bytes) - q.bytes, 0, -1, 1)), -1,'ALARM', 'OK') status
  9  from (
 10     select
 11     TABLESPACE_NAME,
 12     USERNAME,
 13     BYTES,
 14          max_bytes,
 15     blocks,
 16     MAX_BLOCKS
 17     from dba_ts_quotas
 18     where username = upper('&&1')
 19  ) q, (
 20     select tablespace_name, sum(bytes) ttl_bytes, sum(blocks) ttl_blocks
 21     from dba_data_files
 22     group by tablespace_name
 23  ) s
 24  where q.tablespace_name (+)= s.tablespace_name
 25  order by q.username, s.tablespace_name;

TABLESPACE_NAME                USERNAME         BYTES   MAX_BYTES     BLOCKS  MAX_BLOCKS STATU
------------------------------ ----------- ---------- ----------- ---------- ----------- -----
TOOLS                          BING                 0    10485760          0        1280 OK
USERS                          BING          17235968  2899247104       2104      353912 OK
EXAMPLE                        BING                 0           0          0           0 OK
STATDATA                       BING                 0           0          0           0 OK
SYSAUX                         BING                 0           0          0           0 OK
SYSTEM                         BING                 0           0          0           0 OK
UNDOTBS1                       BING                 0           0          0           0 OK

7 rows selected.

SQL>

Without having CASE the indicator is rudimentary, stating that either there IS space to use or there ISN’T space available. But, it works as well as it can given the limitations of the 8.1.7 and earlier SQL engines.

How much ‘wood’ can a ‘woodchuck’ chuck? That depends upon how much ‘wood’ is left to chuck. And you’ll know exactly in releases 9.0.1 and later. And, well, approximately in releases 8.1.7 and earlier (talk about ancient history…), which is, of course, better than not knowing at all.

So if you’re still on 8.1.7.4 seriously consider upgrading to 10.2.0 or 11.1. [If you’re still on 7.3 or 8.0, maybe you should open a museum.] Because you’d really rather know exactly how much space your users have left.

You would.

Really.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: