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.
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…
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
What do you get when you run this?
WHERE tablespace_name = :OBJECT_NAME
GROUP BY tablespace_name,
Excellent! Tools support for DBA features is great for learning.
I’m glad you like it, thanks for sharing your experience here!
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?
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)