"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;
old 15: where username = upper('&&1')
new 15: where username = upper('bing')
old 19: nvl(q.username, upper('&&1')) username,
new 19: nvl(q.username, upper('bing')) username,
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;
old 3: nvl(q.username, upper('&&1')) username,
new 3: nvl(q.username, upper('bing')) username,
old 17: where username = upper('&&1')
new 17: where username = upper('bing')
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> 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 case when abs(q.bytes - q.max_bytes) < 10240000 then 'WARNING'
25 when abs(q.bytes - q.max_bytes) < 1024000 then 'ALERT'
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;
old 15: where username = upper('&&1')
new 15: where username = upper('bing')
old 19: nvl(q.username, upper('&&1')) username,
new 19: nvl(q.username, upper('bing')) username,
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS STATUS
------------------------------ --------- ---------- ----------- ---------- ----------- -------
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>
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;
old 3: nvl(q.username, upper('&&1')) username,
new 3: nvl(q.username, upper('bing')) username,
old 18: where username = upper('&&1')
new 18: where username = upper('bing')
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.
0 comments:
Post a Comment