Hi, My name is Jimmy Sam, If you need DBA help, please email to jimmy_sam001@yahoo.com for further discussion. Thanks.

top   prev   next  

Oracle Tablespace DDL

set pagesize 3000
set long 300000
set longchunk 300000
select dbms_metadata.get_ddl('TABLESPACE', 'TEMP' ) from dual;
-- select dbms_metadata.get_ddl('TABLESPACE', TEMPORARY_TABLESPACE) from dba_users where username='&&MYUSERNAME';


top   prev   next  

Display Tablespace Usage Information

set linesize 380 trimspool on pagesize 5000

column max_pct_used format 999.99
column max_pct_free format 999.99

-- select distinct contents from sys.dba_tablespaces;
clear columns
column tablespace format a33
column total_mb format 999,999,999,999.99
column used_mb format 999,999,999,999.99
column free_mb format 999,999,999.99
column pct_used format 999.99
column graph format a25 heading "GRAPH (X=5%)"
column status format a10
compute sum of total_mb on report
compute sum of used_mb on report
compute sum of free_mb on report
break on report
set linesize 350 trimspool on
set pages 300

select ts.tablespace_name, ts.status, ts.contents,
--  ts.logging
-- , ts.extent_management, ts.allocation_type, ts.plugged_in
-- , ts.block_size, ts.segment_space_management
-- , ts.force_logging
-- , ts.bigfile, ts.def_tab_compression
-- , tsg.group_name
-- , ts.encrypted
-- , ts.compress_for,
       size_info.megs_alloc, size_info.megs_free, size_info.megs_used,
       size_info.pct_free, size_info.pct_used, size_info.max,
       round(size_info.megs_used*100/size_info.max,2) max_pct_used,
       round((size_info.max - size_info.megs_used)*100/size_info.max,2) max_pct_free,
       (size_info.max - size_info.megs_used) max_megs_free 
From
      (
      select  a.tablespace_name,
             round(a.bytes_alloc / 1024 / 1024) megs_alloc,
             round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
             round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
             round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
            100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
             round(maxbytes/1048576) Max
      from  ( select  f.tablespace_name,
                     sum(f.bytes) bytes_alloc,
                     sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
              from dba_data_files f
              group by tablespace_name) a,
            (select ts.name tablespace_name, sum(fs.blocks) * ts.blocksize bytes_free
             from   DBA_LMT_FREE_SPACE fs, sys.ts$ ts
             where  ts.ts# = fs.tablespace_id
             group by ts.name, ts.blocksize) b
      where a.tablespace_name = b.tablespace_name (+)
      union all
      select h.tablespace_name,
             round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
             round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,
             round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,
             round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
             100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
             round(sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes) / 1048576)) max
      from   sys.v_$TEMP_SPACE_HEADER h, dba_temp_files f, sys.v_$Temp_extent_pool p
      where  p.file_id(+) = h.file_id
      and    p.tablespace_name(+) = h.tablespace_name
      and    f.file_id = h.file_id
      and    f.tablespace_name = h.tablespace_name
      group by h.tablespace_name
      ) size_info,
      sys.dba_tablespaces ts, sys.dba_tablespace_groups tsg
where ts.tablespace_name = size_info.tablespace_name
and   ts.tablespace_name = tsg.tablespace_name (+)
and ts.contents in ( 'TEMPORARY', 'UNDO' )
order by max_pct_used
;


top   prev   next  

Display Tablespace Usage With Graph

clear columns
column tablespace format a33
column total_mb format 999,999,999,999.99
column used_mb format 999,999,999,999.99
column free_mb format 999,999,999.99
column pct_used format 999.99
column graph format a25 heading "GRAPH (X=5%)"
column status format a10
compute sum of total_mb on report
compute sum of used_mb on report
compute sum of free_mb on report
break on report
set linesize 350 trimspool on
set pages 300
select  total.ts tablespace,
        dbat.status status,
        total.mb total_mb,
        NVL(total.mb - free.mb,total.mb) used_mb,
        NVL(free.mb,0) free_mb,
        DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used,
        CASE WHEN (total.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
        ELSE '['|| DECODE(free.mb,
                             null,'XXXXXXXXXXXXXXXXXXXX',
                             NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),'X'),20,'-'),
                '--------------------'))||']'
         END as GRAPH
from
        (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
        (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free,
        dba_tablespaces dbat
where total.ts=free.ts(+) and
      total.ts=dbat.tablespace_name
UNION ALL
select  sh.tablespace_name,
        'TEMP',
        SUM(sh.bytes_used+sh.bytes_free)/1024/1024 total_mb,
        SUM(sh.bytes_used)/1024/1024 used_mb,
        SUM(sh.bytes_free)/1024/1024 free_mb,
        ROUND(SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free)*100,2) pct_used,
        '['||DECODE(SUM(sh.bytes_free),0,'XXXXXXXXXXXXXXXXXXXX',
              NVL(RPAD(LPAD('X',(TRUNC(ROUND((SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free))*100,2)/5)),'X'),20,'-'),
                '--------------------'))||']'
FROM v$temp_space_header sh
GROUP BY tablespace_name
order by 1
/
ttitle off
clear columns



top   prev   next  

Max File Size Info

REM
REM max file size info
REM

set arraysize 15
set linesize 180 trimspool on
set pagesize 60
set trimout on
set verify off
set termout on
set feedback off
COLUMN xnam      format a18     heading   'TABLESPACE'           justify c
COLUMN xalloc    format 999,999 heading   'MB_ALLOC'             justify c
COLUMN xused     format 999,999 heading   'MB_USED'              justify c
COLUMN xfree     format 999,999 heading   'MB_FREE'              justify c
COLUMN xlargfree format 999,999 heading   'MB|LARGEST|FREE'      justify c
COLUMN xpctused  format 999,999 heading   'PCT|USED'             justify c
COLUMN mxpctused format 999,999 heading   'MX PCT|USED'          justify c
COLUMN xmaxsize  format 999,999 heading   'MAX MB|SIZE'          justify c
COLUMN xgrowth   format 999,999 heading   'MAX MB|FREE'          justify c
COLUMN sid       format a32     heading   'HOSTNAME|SID'         justify c

select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) xnam,
       MB_MAX_SIZE                                            xmaxsize,
       MB_ALLOC                                               xalloc,
       MB_ALLOC - nvl(MB_FREE,0)                              xused,
       MB_MAX_SIZE-(MB_ALLOC - nvl(MB_FREE,0) )               xgrowth,
       nvl(MB_FREE,0)                                         xfree,
--       nvl(MB_LARG_FREE,0)                                  xlargfree,
       (MB_ALLOC - nvl(MB_FREE,0))/MB_ALLOC*100               xpctused,
       (MB_ALLOC - nvl(MB_FREE,0))*100/MB_MAX_SIZE            mxpctused,
       HOST_NAME || ' ' || INSTANCE_NAME sid                 
from ( select sum(bytes)/1024/1024      MB_FREE,
              max(bytes)/1024/1024      MB_LARG_FREE,
              tablespace_name
       from sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024/1024      MB_ALLOC,
              sum(decode(nvl(maxbytes,bytes),0,bytes,maxbytes))/1024/1024   MB_MAX_SIZE,
              tablespace_name
       from sys.dba_data_files 
       group by tablespace_name ) b, v$instance vi
where a.tablespace_name (+) = b.tablespace_name
union
select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) xnam,
       MB_MAX_SIZE                                            xmaxsize,
       MB_ALLOC                                               xalloc,
       MB_ALLOC - nvl(MB_FREE,0)                              xused,
       MB_MAX_SIZE -  (MB_ALLOC - nvl(MB_FREE,0))             xgrowth,
       nvl(MB_FREE,0)                                         xfree,
--       nvl(MB_LARG_FREE,0)                                  xlargfree,
       (MB_ALLOC - nvl(MB_FREE,0))/MB_ALLOC*100               xpctused,
       (MB_ALLOC - nvl(MB_FREE,0))*100/MB_MAX_SIZE            mxpctused,
       HOST_NAME || ' ' || INSTANCE_NAME sid
from ( select sum(bytes)/1024/1024      MB_FREE,
              max(bytes)/1024/1024      MB_LARG_FREE,
              tablespace_name
       from sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024/1024      MB_ALLOC,
              sum(decode(nvl(maxbytes,bytes),0,bytes,maxbytes))/1024/1024   MB_MAX_SIZE,
              tablespace_name
       from sys.dba_temp_files
       group by tablespace_name ) b, v$instance vi
where a.tablespace_name (+) = b.tablespace_name
-- order by 5 desc
order by 8 asc
;


top   prev   next  

find the size of the largest free space in a tablespace

REM
REM find the size of the largest free space in a tablespace 
REM

-- define MY_TABLESPACE_NAME=SYSAUX
select nvl(df.tablespace_name, nvl(fs.tablespace_name,'UNKOWN')) name,
         to_char(kbytes_alloc,'999,999,990') kbytes,
         to_char(kbytes_alloc-nvl(kbytes_free,0),'999,999,990') used,
         to_char(nvl(kbytes_free,0),'999,999,990') free,
         to_char(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100,'990.9999') pct_used,
         to_char(nvl(largest,0),'999,999,990') largest
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from   dba_free_space
       where  tablespace_name='&&MY_TABLESPACE_NAME'
       group by tablespace_name ) df,
       ( select sum(bytes)/1024 Kbytes_alloc,
                tablespace_name
         from   dba_data_files
       where  tablespace_name='&&MY_TABLESPACE_NAME'
         group by tablespace_name ) fs;





top   prev   next  

Add Data File

-- change file size or add data file
define my_tablespace=USERS_TS  
define my_tablespace=TEMP1
define my_tablespace=TEMP 
set linesize 200 trimspool on 
set pagesize 5000
select cmd from 
(select file_name,'alter database datafile ''' || file_name 
|| ''' resize ' ||  bytes/1024/1024 || 'M;' cmd 
from dba_data_files where tablespace_name = upper('&my_tablespace') 
union
select file_name, 'alter tablespace ' || tablespace_name || ' add datafile ''' 
|| file_name || ''' size ' ||  to_char(bytes/1024/1024 ) || 'M;'  cmd 
from dba_data_files 
where tablespace_name = upper('&my_tablespace') )
union 
select cmd from 
(select file_name,'alter database tempfile ''' || file_name 
|| ''' resize ' ||  bytes/1024/1024 || 'M;' cmd 
from dba_temp_files where tablespace_name = upper('&my_tablespace') 
union
select file_name, 'alter tablespace ' || tablespace_name || ' add tempfile ''' 
|| file_name || ''' size ' ||  to_char(bytes/1024/1024 ) || 'M;'  cmd 
from dba_temp_files 
where tablespace_name = upper('&my_tablespace') )
order by cmd
;


top   prev   next  

change max file size and auto extend option for a file

REM
REM change max file size and auto extend option for a file
REM

REM egrep -i "ORA-01652|ORA-1652" alert_${ORACLE_SID}.log|sort -u
REM grep  -i  alter               alert_${ORACLE_SID}.log|sort -u

define my_tablespace=USERS_TS  
define my_tablespace=TEMP1
define my_tablespace=TEMP 
define my_tablespace_maxsize=20001
define my_tablespace_nextsize=5
column file_name noprint

set linesize 380 trimspool on set pagesize 5000
select file_name,cmd from
(
select file_name,'-- alter database datafile ''' || file_name
|| ''' AUTOEXTEND ON NEXT '|| f.INCREMENT_BY*p.value/(1024*1024) || 'M MAXSIZE ' || f.MAXBYTES/(1024*1024)  || 'M; rem existing' cmd
from dba_data_files f, v$parameter p where f.tablespace_name = upper('&my_tablespace') and p.name='db_block_size' 
union
select file_name,'alter database datafile ''' || file_name
|| ''' AUTOEXTEND ON NEXT ' || to_char(&&my_tablespace_nextsize) || 'M MAXSIZE ' || to_char(&&my_tablespace_maxsize) || 'M;' cmd
from dba_data_files f where tablespace_name = upper('&&my_tablespace')
)
union
(
select file_name,'-- alter database tempfile ''' || file_name
|| ''' AUTOEXTEND ON NEXT '|| f.INCREMENT_BY*p.value/(1024*1024) || 'M MAXSIZE ' || f.MAXBYTES/(1024*1024)  || 'M; rem existing' cmd
from dba_temp_files f, v$parameter p where f.tablespace_name = upper('&my_tablespace') and p.name='db_block_size' 
union
select file_name,'alter database tempfile ''' || file_name
|| ''' AUTOEXTEND ON NEXT ' || to_char(&&my_tablespace_nextsize) || 'M MAXSIZE ' || to_char(&&my_tablespace_maxsize) || 'M;' cmd
from dba_temp_files f where tablespace_name = upper('&&my_tablespace')
)
union
select file_name,cmd from
(select file_name,'alter database tempfile ''' || file_name
|| ''' resize ' ||  bytes/1024/1024 || 'M;' cmd
from dba_temp_files f where tablespace_name = upper('&&my_tablespace')
union
select file_name, 'alter tablespace ' || tablespace_name || ' add tempfile '''
|| f.file_name || ''' size ' ||  to_char(bytes/1024/1024 ) || 'M;'  cmd
from dba_temp_files f
where tablespace_name = upper('&my_tablespace') )
order by file_name,cmd;



top   prev   next  

Shrink Data File Size

-- Using outer join for dba_extents is empty case
-- Still tuning

set pagesize 5000
set linesize 1000 
set trimspool on 
set term on verify off feedback off echo on
prompt set term on verify on feedback on echo on
select 'alter database datafile ''' ||a.file_name || ''' resize ' ||  
CEIL(nvl(b.hwm,a.increment_by+1)*t.block_size/(1024*1024)) || 'M' || ';' || chr(10) || 
-- for_dos_only chr(13) ||
'-- shrink amount ' || (blocks-nvl(b.hwm,a.increment_by+1))*t.block_size/(1024*1024) ||'M' xx 
from dba_data_files a,
       ( select file_id, max(block_id+blocks) hwm
           from dba_extents
          group by file_id ) b,
     dba_tablespaces  t
where 
t.tablespace_name = a.tablespace_name 
and a.file_id = b.file_id(+) 
and (a.blocks-nvl(b.hwm,a.increment_by)+1)*t.block_size/(1024*1024) > 1 + (increment_by+1)*t.block_size/(1024*1024) 
-- and a.tablespace_name = 'my_tablespace'
-- and upper(a.file_name) like '%v%'
order by b.hwm;
set term on verify on feedback on echo on


1): no rman backup on both primary db and standby db
2): to alert_sid.log
# to alert_sid.log
# echo "LAST RUN OF CHECK ALERT SCRIPT"  `date`
# LAST RUN OF CHECK ALERT SCRIPT Sunday, November 18, 2018 10:48:27 PM EST


-- shrink file size based on hwm ( high water marker )
set pagesize 5000
set linesize 1000 
set trimspool on 
set term on verify off feedback off echo on
prompt set term on verify on feedback on echo on
select 'alter database datafile ''' ||file_name || ''' resize ' ||  
CEIL(hwm*t.block_size/(1024*1024)) || 'M' || ';' || chr(10) || 
-- for_dos_only chr(13) ||
'-- shrink amount ' || (blocks-hwm+1)*t.block_size/(1024*1024) xx 
from dba_data_files a,
       ( select file_id, max(block_id+blocks) hwm
           from dba_extents
          group by file_id ) b,
     dba_tablespaces  t
where 
t.tablespace_name = a.tablespace_name 
and a.file_id = b.file_id 
and (blocks-hwm+1)*t.block_size/(1024*1024) > 10
-- and a.tablespace_name = 'my_tablespace'
-- and upper(a.file_name) like '%v%'
order by hwm;
set term on verify on feedback on echo on


-- hwm
select 'alter database datafile ''' ||file_name || ''' resize ' || hwm*8192/(1024*1024) || 'M' 
|| '; -- shrink amount ' || (blocks-hwm+1)*8192/(1024*1024) xx 
from dba_data_files a,
       ( select file_id, max(block_id+blocks) hwm
           from dba_extents
          group by file_id ) b
where a.file_id = b.file_id
-- and tablespace_name = 'my_tablespace'
and file_name like '%v9%'
order by hwm;
select 'alter database datafile ''' ||file_name || ''' resize ' || hwm*8192/(1024*1024) || 'M'
from dba_data_files a,
       ( select file_id, max(block_id+blocks) hwm
           from dba_extents
          group by file_id ) b
where a.file_id = b.file_id
and tablespace_name = '&my_tablespace'
and file_name like '%v3%'
order by hwm;

set linesize 200 trimspool on
set pagesize 200

select 'alter database datafile ''' || file_name || ''' resize ' ||  to_char(bytes/1024/1024) || 'M;' resize
from dba_data_files where tablespace_name = upper('&MY_TABLESPACE_NAME');

select 'alter tablespace ' || tablespace_name || ' add datafile ''' || file_name || ''' size ' ||  
to_char(bytes/1024/1024 ) || 'M;'  add_file
from dba_data_files
where tablespace_name = upper('&MY_TABLESPACE_NAME');


top   prev   next  

home