1

Query:

Select ROUND (SUM (d.bytes) / (1024 * 1024 * 1024)) AS size1, 
ROUND (SUM (d.maxbytes) / (1024 * 1024 * 1024)) AS size2
From dba_data_files d where tablespace_name = 'TABLESPACE_NAME'

Output:

size1: 70, size2: 64

As you can see the max bytes allowed for the data files for a particular table-space is only 64GB however it has somehow extended to 70GB!!

Scenario: At a given point the table-space got completely filled (64GB) and I saw 100% used in one of my routine checks. So I went ahead and temporarily truncated some unwanted tables to free up some space. I didn't encounter the cannot extend error anywhere in any of my processes (although it might have occurred) but what bugs me is that the total size of the table-space just increased from 64GB(max) where it should have stopped to 70(gb).

Please advise how it could have happened or any option I have missed !! and where is the extra 6 GB being stored.

Thanks in advance.

Edit:

Output of :

select bytes,maxbytes,autoextensible from dba_data_files where tablespace_name = 'TABLESPACE_NAME';

15728640000 4294967296 YES

30303846400 32212254720 YES

28626124800 32212254720 YES

migrated from stackoverflow.com Jun 2 '18 at 17:24

This question came from our site for professional and enthusiast programmers.

1

MAXBYTES is 0 for datafiles that are not autoextensible, but BYTES is greater than 0. Use this:

Select ROUND (SUM (d.bytes) / (1024 * 1024 * 1024)) AS size1, 
ROUND (SUM (decode(d.maxbytes, 0, d.bytes, d.maxbytes)) / (1024 * 1024 * 1024)) AS size2
From dba_data_files d where tablespace_name = 'TABLESPACE_NAME';

But it is possible to manually resize a datafile above maxbytes.

SQL> select file_id, bytes, maxbytes from dba_data_files;

   FILE_ID      BYTES   MAXBYTES
---------- ---------- ----------
         1  954204160 3.4360E+10
         3 1142947840 3.4360E+10
         5  608174080 3.4360E+10
         7    5242880   10485760

SQL> alter database datafile 7 resize 20M;

Database altered.

SQL> select file_id, bytes, maxbytes from dba_data_files;

   FILE_ID      BYTES   MAXBYTES
---------- ---------- ----------
         1  954204160 3.4360E+10
         3 1142947840 3.4360E+10
         5  608174080 3.4360E+10
         7   20971520   10485760

I updated the query:

Select ROUND (SUM (d.bytes) / (1024 * 1024 * 1024)) AS size1, 
ROUND (SUM (greates(d.bytes, d.maxbytes)) / (1024 * 1024 * 1024)) AS size2
From dba_data_files d where tablespace_name = 'TABLESPACE_NAME';
  • Hi, actually I only have 3 data-files for that table-space & all of them are auto-extensible. I still tried your query to get the same result. – pOrinG Jun 4 '18 at 7:25
  • @pOrinG Then post the result of select bytes, maxbytes, autoextensible from dba_data_files where tablespace_name = 'TABLESPACE_NAME';. – Balazs Papp Jun 4 '18 at 13:08
  • Hi, I have edited my question to add the output. – pOrinG Jun 4 '18 at 15:10
  • @pOrinG Ok, I updated my answer. – Balazs Papp Jun 4 '18 at 15:16
  • Hi, but that's the thing. No one manually resized it. I am 100% sure of it. The database is managed by me, as in I am not the dba but I hold all the passwords & connections and no-one else can login let alone resize without my presence. Hence my dilemma. Please check the scenario I mentioned in the question. – pOrinG Jun 4 '18 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.