when ever you create database by default 5 tablespaces are created
system # database stores its metadata
sysaux # stores AWR and statistics of database
temp # Used for sorting operations on database
undo # used for holding rollback segments
users # default tablespace for any user on database
Managing Default Tablespaces on oracle database #
Adding additional Space to System #
SQL> alter tablespace system add datafile '/u01/app/oracle/oradata/em12c/system02.dbf' size 300m;
Adding additional space to sysaux #
SQL> ALTER TABLESPACE sysaux add datafile '/u01/app/oracle/oradata/em12c/sysaux02.dbf' size 300m;
Tablespace altered.
Managing Temporary Tablespace #
SQL> create temporary tablespace newtemp tempfile '/u01/app/oracle/oradata/em12c/newtemp01.tmp' size 100m;
Tablespace created.
Make newly created temporary as default temporary tablespace for oracle database as follows
SQL> alter database default temporary tablespace newtemp;
Database altered.
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.
creating New Undo Tablespace #
SQL> create undo tablespace newundotb datafile '/u01/app/oracle/oradata/em12c/newundotb01.dbf' size 100m;
Tablespace created.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = newundotb;
Query To check Free and Used Space
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
--Nikhil Tatineni--
--Oracle In memory--
