Select Page

Art of BI: Extending Oracle Tablespace – Quick and Dirty

Christian Screen | | September 12, 2010

I was working on quick project and kept bumping an a “failure to initiate auto-extend…” message that was rather irritating.  I typically use SQLDeveloper over Toad and there is not immediate built-in functionality to manage tablespaces in that tool.  A quick Google search gave me some results and I figured I would quickly post the two step extension process here for later reference.

Step 1

Run the PL/SQL code to determine the current size of the tablespace in question. Notice how memory is divided by 1024 to get at the canonical data size.  The tablespace I was using for my project was the SYSTEM tablespace and it is referenced within the code in this post.  If you are using the USERS or a custom tablespace simple substitute your tablespace where you see SYSTEM.

[sourcecode language=’sql’]

select
file_name,
autoextensible,
(bytes/1024)/1024 usedmb,
(maxbytes/1024)/1024 maxmb,
round((((bytes/1024)/1024)
/ ((maxbytes/1024)/1024)) * 100) used_pct
from dba_data_files
where tablespace_name = ‘SYSTEM’;

[/sourcecode]

Step 2

As we determined in the previous step, we now know how much of the tablespace is being used and what the current size and max sizes are.  To extend the space run the following query based on the information provided from the Step 1.  I want to increase my maxsize to a value that I know is within the bounds of my current volume but will still allow my data operations to continue without being held hostage by the error that started this discussion. To do that I pick a value such as 2000 MB and multiple it twice by 1024 (i.e.: 2000 x 1024 x 1024) .  The result of that math is the value I place for the maxsize in the following statement.

[sourcecode language=’sql’]

alter database datafile ‘G:ORACLEXEORADATAXESYSTEM.DBF’ autoextend on maxsize 2097152000;

[/sourcecode]

Run the statement above and the tablespace maxsize will be set as desired.  Re-run the statement provided in Step 1 to see the values have changed and your percentage of use should now be changed as well.

References

http://garycoy.com/?page_id=13

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Hyperion Myth #9: SOX Audit Requests Are Time-consuming

With serious financial penalties, SOX audits can be intimidating — but they don’t have to be. Find out how you can use Datavail’s software to automatically prove SOX compliance.

Jonathan Berry | March 13, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS