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.