Adding space to a tablespace

by numstead
by numstead

One of the jobs I do the most as a DBA is added space to peoples schemas.  It’s not a big thing, just something I have to monitor and watch out for. Users don’t like it when they can’t save their data into the database.

Below is a script that simplifies the main tasks required to do this.

I have commented the script fairly well. But if you have any questions, please let me know.

-- format tablespace_name column for better display
COL t.tablespace_name format a10;

— show any tablespaces that are above 80% usage
SELECT t.tablespace_name,
(tsa.bytes / 1024)/1024 AS “Size (MB)”,
(round((1 – decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100)) AS percent
FROM sys.dba_tablespaces t, sys.sm$ts_avail tsa, sys.sm$ts_free tsf
WHERE t.tablespace_name = tsa.tablespace_name
AND ((round((1 – decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100)) > 80)
AND t.tablespace_name = tsf.tablespace_name (+)
ORDER BY percent DESC, t.status, t.tablespace_name;

— Format file_name column for better display
COL FILE_NAME format a50;

— Run this statement to determine the datafile names for a tablespace
— You will be prompted for the tablespace name.
— You can get these from the section above.
select FILE_NAME,
(BYTES/1024)/1024 as “SIZE (MB)”,
(MAXBYTES/1024)/1024 as “MAXSIZE (MB)”
from dba_data_files
where tablespace_name=’&1′;

— Run this statement to add another datafile to a tablespace
— You will be prompted for tablespace name
— and the datafile name (be sure to use the full path)
alter tablespace &1 add datafile ‘&2’ size 5M autoextend on next 10M maxsize 4096M;

— Run this statement to increase the same of a datafile
— You will be prompted for the datafile name (be sure to use the full path)
— and the new size. Use something like 100M
alter database datafile ‘&1’ resize &2;

NOTE : This script is NOT intended to be run as a whole, simply copy and paste the section(s) that you require.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s