Oracle tips

gistfile1.sql

-- Tablespaces size and free space
select b.tablespace_name, tbs_size Size_Mb, a.free_space Free_Mb
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,0) as free_space
       from dba_free_space group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
       from dba_data_files group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;

-- List datafiles
SELECT * FROM dba_data_files;

-- Add datafile to tablespace
alter tablespace XDM_INDEXES  add datafile 'C:\APP\ADMINISTRATOR\ORADATA\XDM\XDM_INDEXES_3.DBF'
size 512m autoextend on next 100m maxsize 2048m;

-- Calculate indexes size
select segment_name, owner, round(sum(bytes)/1024/1024 ,0) as size_MB
from dba_segments
where tablespace_name = 'XDM_INDEXES'
group by segment_name, owner
order by size_MB desc;


-- size of a schema in MB
SELECT sum(bytes)/1024/1024 AS MB FROM dba_segments WHERE owner = 'XDMDEMO';


-- size of a schema's segment types
SELECT segment_type, sum(bytes)/1024/1024 AS MB FROM dba_segments WHERE owner = 'XDMDEMO' GROUP BY segment_type;


-- size of all schemas in MB
SELECT owner, sum(bytes)/1024/1024 AS MB FROM dba_segments GROUP BY owner ORDER BY MB DESC;