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.
OK, I’ve got the USERS tablespace open. Who are the space piggies?
OK, well now I know what I need to know. Let’s make this bigger.
I’ve got two choices. I can:
- Make the existing file bigger
- 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:
- Edit the tablespace
- 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.
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.
DO NOT HIT ‘OK.’
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.
Also, if you get stuck, and you don’t have SQL Developer around, the Oracle Docs make finding this stuff pretty easy.