Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Oracle SQL Developer: Extending a Tablespace

4 Mins Read

I get asked this question a LOT (especially from people looking to switch tools) – can your product help me extend a tablespace, i.e. make it bigger?

But then, I also get this: we hate tools because the Junior DBA pages us at 3 in the morning when the tablespace is full, and they can’t extend them, because they don’t have the GUI in front of them.

Let’s talk about the second problem first.

If you are a DBA, and you can’t figure out the SQL/syntax to alter a tablespace/datafile using the Oracle DOCs or even Google – you probably shouldn’t be a DBA. Ok, now that I’ve gone and said something controversial, let’s show how to use the GUI to make more room for your data.

By the way, if you need more TEMP space, make sure you’ve figured out why TEMP is filling up. If you don’t, it will probably just fill up again. Go read this nice post from our EM Lady, @dbakevlar on this subject. Oh, and I like this one too.

The DBA Console

View > DBA.

Add a connection.

Go to Storage.

You can click into a tablespace from this chart or open directly from the tree. USERS looks kinda full to me.
You can click into a tablespace from this chart or open directly from the tree. USERS looks kinda full to me.

OK, I’ve got the USERS tablespace open. Who are the space piggies?

This chart can take a few seconds to generate, lots of MATHS going on in the background.
This chart can take a few seconds to generate, lots of MATHS going on in the background.

OK, well now I know what I need to know. Let’s make this bigger.

I’ve got two choices. I can:

  1. Make the existing file bigger
  2. Add another file

This decision is probably made for you via procedures and policies in place. Let’s say we already have autoextend on, and we don’t want the file to grow beyond a certain size, and so we want to add a new file.

Adding a File to the Tablespace

There’s two ways to do this via the GUI. We can, from the Actions or Tree Context Menu:

  1. Edit the tablespace
  2. Add a datafile

Because I like the ability to see what’s going on whilst I add files, I’m going to show you #1.

Click on the Actions button in the editor toolbar, or right-click on the tablespace in the tree.

Yeah, that one.
Yeah, that one.

Which gives us the following dialog. I like this workflow, because the object editor allows me to copy the existing file(s) information – the name, directory path, etc. to help me define the new file. Copy and paste – hard to mess that up..or maybe harder to mess that up I should say.

Press the '+' button to add a file. Fill in the details. Note you can select which unit of size you want to do - I'm going with M for Megabytes.
Press the ‘+’ button to add a file. Fill in the details. Note you can select which unit of size you want to do – I’m going with M for Megabytes.

Now.

DO NOT HIT ‘OK.’

I wish ALTER BANK ACCOUNT ADD MONEY worked...
I wish ALTER BANK ACCOUNT ADD MONEY worked…

This will just send some pretty powerful, and potentially destructive DDL to your, probably, production database.

You are the paranoid, careful, and always testing DBA. Instead, I want you to click over to the DDL page. See the code. Read the code. TEST the code. Then decide if you want to hit ‘OK’ or copy it out to run via your production processes to follow your auditing or security procedures.

ALTER TABLESPACE USERS 
ADD 
DATAFILE '/u01/app/oracle/oradata/orcl/users02.df' SIZE 20971520;

Eureka, the Tablespace is Bigger

The editor will auto-refresh after the ALTER to show the updated info.

I've also opened the 'Manage Database' page from the connection context menu, and sent it to a new Document Tab group so I can see my tablespace details next to total database storage report.
I’ve also opened the ‘Manage Database’ page from the connection context menu, and sent it to a new Document Tab group so I can see my tablespace details next to total database storage report.

Also, if you get stuck, and you don’t have SQL Developer around, the Oracle Docs make finding this stuff pretty easy.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

7 Comments

  1. Mohammed Ismail Reply

    hi jeff,

    I have a doubt, suppose I have a tablespace of size 500M and it is exhausted, I extended it further to another 500M, now after this suppose I am cleaning up my DB by removing the unwanted data or users,

    will this tablespace still be occupied?

    if it is not how can I free the space further…

  2. Sam Reply

    Hi Jeff, I love your posts. I posted this on the sqldeveloper forum also, but when I click on the chart tab I get a “The data is zero” message. I’m in as a dba and there is definitely data in the tablespaces. sqldeveloper is 4.0.1 and the databases are 10g and 11g.
    Thanks in advance
    Sam

    • thatjeffsmith Post Author

      What do you get when you run this?

      [sql]
      SELECT tablespace_name,
      owner,
      ROUND(SUM(bytes/1024/1024),3)
      FROM dba_extents
      WHERE tablespace_name = :OBJECT_NAME
      GROUP BY tablespace_name,
      owner
      [/sql]

  3. Danilo Piazzalunga Reply

    Excellent! Tools support for DBA features is great for learning.

  4. Kent Graziano Reply

    But wait Jeff, I get an error when I try to look at the tablespaces…

    Oh yea, my user access does not have DBA privileges (probably a good thing).

    No doubt this question will come up – what exact privileges does one need to even see the tablespace definitions?

    • thatjeffsmith Post Author

      At minimum you need access to the dba_ views like dba_data_files and dba_tablespaces.

      We assume if you’re in the DBA panel, that you are indeed a DBA (with the DBA role)

Write A Comment