Oracle Tablespace Management

Tablespace usage

set linesize 1234 pages 1234

col tablespace format a24

col free heading ‘Free(Mb)’ format 99999999.9

col total heading ‘Total(Mb)’ format 999999999.9

col used heading ‘Used(Mb)’ format 99999999.9

col pct_free heading ‘Pct|Free’ format 99999.9

col largest heading ‘Largest(Mb)’ format 99999.9

compute sum of total on report

compute sum of free on report

compute sum of used on report

break on report

select substr(a.tablespace_name,1,24) tablespace,

round(sum(a.total1)/1024/1024, 1) Total,

round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1) used,

round(sum(a.sum1)/1024/1024, 1) free,

round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1) pct_free,

round(sum(a.maxb)/1024/1024, 1) largest,

max(a.cnt) fragments

from

(select tablespace_name, 0 total1, sum(bytes) sum1,

max(bytes) MAXB,

count(bytes) cnt

from dba_free_space

group by tablespace_name

union

select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files

group by tablespace_name

union

select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_temp_files

group by tablespace_name) a

group by a.tablespace_name

/

Check for all the datafiles for a given tablespace

col file_name for a50;

select file_name,tablespace_name,bytes/1024/1024,status from dba_data_files where tablespace_name=’&TABLESPACE_NAME’;

Tablespace usage of given tablespace name

select*from dba_tablespace_usage_metrics where tablespace_name=’&TABLESPACE_NAME’;

Resize tablespace

ALTER DATABASE datafile ‘/oracle/NXD/sapdata10/sr3db_3/sr3db.data3’ resize 15000M;

Add datafile to given tablespace

ALTER DATABASE datafile ‘+DATA/d2d/sand_doc_dflt_datall01.dbf’ resize 2G;

Leave a Reply

Your email address will not be published. Required fields are marked *