Tablespace maxsize vs. datafile size

Dear experts,

I just ran into an ORA-01654 error, since my tablespace maxsize was reached.
Hence I increased the datafile size and set it to 4G.
What I'm wondering is whether I also have to redefine the tablespace maxsize to 4G:

Alter tablespace MYTBS maxsize 4000m;

When querying dba_tablespaces I find that maxsize is still at 2000m whereas the data_file listed under dba_data_files is correctly shown with 4000m.

Maybe you can enlighten me with your knowledge!

Many thanks,

Seb
skahlert2010Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OP_ZaharinCommented:
- dba_datafiles does not hold information on the tablespace size, see the doc: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4157.htm.

- dba_data_files is the view that holds the datafile size (http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3083.htm). the following is the query that i usually used to check my tablespace and datafile size:

SELECT a.tablespace_name, TO_CHAR (a.total_size) || 'MB' total_space,
       TO_CHAR (b.free_space) || 'MB' free_space,
       TO_CHAR ((a.total_size - b.free_space)) || 'MB' used_space,
       TO_CHAR (total_extensible_size) || 'MB' total_extensible_size
  FROM (SELECT   tablespace_name, ROUND (SUM (BYTES) / 1048576, 0) total_size,
                 ROUND (SUM (maxbytes) / 1048576, 0) total_extensible_size
            FROM (SELECT tablespace_name, BYTES, maxbytes
                    FROM dba_data_files
                  UNION ALL
                  SELECT tablespace_name, BYTES, maxbytes
                    FROM dba_temp_files)
        GROUP BY tablespace_name) a,
       (SELECT   tablespace_name, ROUND (SUM (BYTES) / 1048576, 0) free_space
            FROM dba_free_space
        GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OP_ZaharinCommented:
- sorry typo for the 1st sentence above. i mean the following:

- the dba_tabblespaces view does not hold information on the tablespace size, see the doc: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4157.htm.
0
skahlert2010Author Commented:
Thank you for the query and links! Very, very helpful!

I know that dba_data_files does only display information about the data_file.
So resizing the datafile or adding an additional datafile to increase the tablespace size suffices I assume?

Basically the resize can be done at the tablespace as well as at the database level, right?
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

OP_ZaharinCommented:
"So resizing the datafile or adding an additional datafile to increase the tablespace size suffices I assume? "
- yes it is sufficient. and the resize will reflect the datafile and tablespace.
0
skahlert2010Author Commented:
Great answer and clarification!

Many thanks for your support!

0
skahlert2010Author Commented:
Thank you! Question has been answered completely with the last reply!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.