ThatJeffSmith

Oracle SQL Developer: Extending a Tablespace

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.