{{parent page="Oracle"}}

=== Listing tablespace storage usage ===
%%(sql)
column file_name format A40;
column tablespace_name format A10;
select b.file_id, b.tablespace_name, b.file_name, b.bytes/1048575 as AllocatedMb,
(b.bytes-sum(nvl(a.bytes,0)))/1048576 as UsedMb,
sum(nvl(a.bytes,0))/1048576 as FreeMb,
sum(nvl(a.bytes,0))/(b.bytes)*100 as FreePct
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id and b.tablespace_name = 'DSR'
group by b.tablespace_name,b.file_name,b.file_id,b.bytes;
%%

===List all tablespace usage===
%%(sql)
set linesize 200
set pagesize 200
column file_name format A50;
column tablespace_name format A13;

SELECT b.file_id, b.tablespace_name, b.file_name, b.bytes/1048575 AS AllocatedMb,
(b.bytes-sum(nvl(a.bytes,0)))/1048576 AS UsedMb,
sum(nvl(a.bytes,0))/1048576 AS FreeMb,
sum(nvl(a.bytes,0))/(b.bytes)*100 AS FreePct
FROM dba_free_space a,dba_data_files b
WHERE a.file_id=b.file_id
GROUP BY b.tablespace_name,b.file_name,b.file_id,b.bytes
ORDER BY b.tablespace_name;
%%

===Listing temp tablespace usage===
%%(sql)
SELECT tablespace_name, SUM(bytes_used)/1048576 UsedMB, SUM(bytes_free)/1048576 as TotalMB
FROM V$temp_space_header
GROUP BY tablespace_name;
%%

=== Create tablespace ===
%%(sql)
create tablespace xxx_data logging
datafile '+DATA/xxx/xxx01.dbf' size 500m autoextend on next 50m maxsize 5000m extent management local;

create user xxxdba identified by yyy
default tablespace coaction quota unlimited on xxx_data;

grant CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE ROLE, CREATE PROCEDURE,
CREATE ANY SYNONYM, CREATE PUBLIC SYNONYM, CREATE ANY SEQUENCE to xxxdba;
%%

=== Adding temp tablespace to a specific tablespace ===
%%(sql)
ALTER TABLESPACE TEMP ADD TEMPFILE '/path/temp01.dbf'
SIZE 512M REUSE AUTOEXTEND OFF;
%%