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';
|
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
;
|
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
|
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
;
|
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;
|
Add Space By Resizing Data File | ||
|---|---|---|
|
Add Data File On Asm Disk |
|---|
define my_tablespace=TEMP
define my_tablespace=USERS
-- add new data files on asm disk
select distinct cmd from (
select 'alter tablespace &&my_tablespace add datafile ''+DATA'' size 32000M autoextend off;' cmd from dual
union all
select 'alter tablespace ' || tablespace_name || ' add datafile ''' || substr(file_name,1,instr(file_name,'/') -1)
|| ''' size ' || bytes/1024/1024 || 'M autoextend off;' cmd
from
dba_data_files
where tablespace_name = upper('&&my_tablespace')
)
;
|
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
;
|
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;
|
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
define my_tablespace=USERS
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 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');
|