Ora Resize Tbs
main | resize tbs | DN | Crea_DB | Backup/Restore |
Table of Contents
LIST
List TBS_GB
set linesize 200 pagesize 5000 col tbs_name for a32 col current_gb for 999,999.9 col max_gb like current_gb col used_gb like current_gb col free_gb like current_gb col used_pct for 999.9 col need_94 for 9,999 col need_97 like need_94 variable block_b number; exec SELECT value into :block_b from v$parameter where name='db_block_size'; def GB = 1073741824; select tbs_name, round(current_b/&GB,1) current_gb, round(max_b/&GB,1) max_gb, round(used_b/&GB,1) used_gb, round(free_b/&GB,1) free_gb, round(used_pcent,1) used_pct, case when need_94_b <=0 then NULL else ceil(need_94_b/&GB) end need_94, case when need_97_b <=0 then NULL else ceil(need_97_b/&GB) end need_97 from (select tbs_name, current_b, max_b, used_b, free_b, used_pcent, case when free_b > max_b - max_b * .94 then NULL else used_b - max_b *.94 end need_94_b, case when free_b > max_b - max_b * .97 then NULL else used_b - max_b *.97 end need_97_b FROM (SELECT a.tablespace_name tbs_name, SUM(a.bytes) current_b, SUM(decode(b.maxextend, null, A.BYTES, b.maxextend*:block_b)) max_b, (SUM(a.bytes) - c.Free) used_b, (SUM(decode(b.maxextend, null, A.BYTES, b.maxextend*:block_b)) - (SUM(a.bytes) - c.Free)) free_b, (100*(SUM(a.bytes) - c.Free)/(SUM(decode(b.maxextend, null, A.BYTES, b.maxextend*:block_b)))) used_pcent FROM dba_data_files a, sys.filext$ b, (SELECT d.tablespace_name ,sum(nvl(c.bytes,0)) Free FROM dba_tablespaces d, dba_free_space c WHERE d.tablespace_name = c.tablespace_name(+) GROUP BY d.tablespace_name) c WHERE a.file_id = b.file#(+) AND a.tablespace_name = c.tablespace_name GROUP BY a.tablespace_name, c.Free) tbs_sizes) --WHERE used_pcent > 75 OR max_b/&GB >= 100 ORDER BY used_pct desc;
List all datafile of TBS with ID
set lines 200 col FILE_NAME for a80 select FILE_ID, file_name,bytes/1024/1024 MBytes, AUTOEXTENSIBLE, MAXBYTES/1024/1024 max_MB from dba_data_files where tablespace_name =UPPER('&TBSNAME') order by file_name;
ALTER
Resize DataFile by ID
alter database datafile &DF_ID resize &NEWSIZE_GB; -- Es: alter database datafile 131 resize 16g;
Resize MAXSIZE of AUTOEXTEND DataFile
alter database datafile &DF_ID autoextend on next 10m maxsize &NEWMAXSIZE_GB;
Add NOAUTOEXTEND DataFile
alter tablespace &TBSNAME add datafile '&df_name' size &SIZE;
Add AUTOEXTEND DataFile
alter tablespace &TBSNAME add datafile '&ROOTPATH' size &SIZE autoextend on next 100m maxsize &MAXSIZE;
Switch AUTOEXTEND ON
alter database datafile &df_num autoextend on maxsize &max_size; -- Es: alter database datafile 374 autoextend on maxsize 31g;
TEMP
TEMP TBS Usage
set line 200 SELECT tablespace_name, tablespace_size/1024/1024 TBS_SZ_MB, ALLOCATED_SPACE/1024/1024 ALLOCATED_MB, FREE_SPACE/1024/1024 FREE_MB FROM dba_temp_free_space;
ALL TEMP TBS free space
SET PAGESIZE 60 LINESIZE 300 SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;
select TEMP files
col tablespace_name for a24 col file_name for a64 select TABLESPACE_NAME, FILE_ID, file_name, BYTES/1024/1024 MB, STATUS, AUTOEXTENSIBLE AUTO, MAXBYTES/1024/1024 MAX_MB from dba_temp_files order by 1
resize TEMP by file_id
alter database tempfile &fileid resize &newsize;
resize TEMP by filename
alter database tempfile '&filename' resize &newsize;
LESS USED
List TBS with size
set lines 300 set pages 300 col tablespace for a30 col "MB Totali" for a20 col "MB Usati" for a20 col "% Utilizzo" for a10 SELECT d.tablespace_name "Tablespace", d.status "Stato", TO_CHAR((a.bytes / 1048576),'99,999,990.900') "MB Totali", TO_CHAR(((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),'99,999,990.900') "MB Usati", TO_CHAR((((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576)*100) / (a.bytes / 1048576),'999,999.9') "% Utilizzo" FROM sys.dba_tablespaces d, sys.sm$ts_avail a, sys.sm$ts_free f WHERE d.tablespace_name = a.tablespace_name AND f.tablespace_name (+) = d.tablespace_name order by 5 desc /
List one TBS with size
set lines 300 set pages 300 col tablespace for a30 col "MB Totali" for a20 col "MB Usati" for a20 col "% Utilizzo" for a10 SELECT d.tablespace_name "Tablespace", d.status "Stato", TO_CHAR((a.bytes / 1048576),'99,999,990.900') "MB Totali", TO_CHAR(((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),'99,999,990.900') "MB Usati", TO_CHAR((((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576)*100) / (a.bytes / 1048576),'999,999.9') "% Utilizzo" FROM sys.dba_tablespaces d, sys.sm$ts_avail a, sys.sm$ts_free f WHERE d.tablespace_name = UPPER('&TBSNAME') AND d.tablespace_name = a.tablespace_name AND f.tablespace_name (+) = d.tablespace_name order by 5 desc /
List all datafile of a specific TBS
set line 200 pages 2000 col TABLESPACE_NAME for a30 col file_name for a75 select tablespace_name, file_name, AUTOEXTENSIBLE, trunc(BYTES/1024/1024/1024) "GB", trunc(MAXBYTES/1024/1024/1024) "GB" from dba_data_files where tablespace_name like UPPER('&TBSNAME') order by 1,2;
Calculates GBs needed for TBS
set line 200 pages 5000 undef perc SELECT d.tablespace_name TBS, d.status "Stato", TO_CHAR( ceil(a.bytes / 1024/1024/1024),'999,999')||'G' "SIZE", TO_CHAR((((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576)*100) / (a.bytes / 1048576),'999,999.9') "USED%", case when f.bytes > a.bytes * (1-&&perc/100) then '' else to_char( ceil((a.bytes * (1-&perc/100) - f.bytes)/1024/1024/1024), '999,999') ||'G' end NEEDED FROM sys.dba_tablespaces d, sys.sm$ts_avail a, sys.sm$ts_free f WHERE d.tablespace_name = a.tablespace_name AND f.tablespace_name (+) = d.tablespace_name order by 4 desc;
List all datafile of TBS with ID
set lines 200 col FILE_NAME for a80 select FILE_ID, file_name,bytes/1024/1024 MBytes, AUTOEXTENSIBLE, MAXBYTES/1024/1024 max_MB from dba_data_files where tablespace_name = UPPER('&TBSNAME') order by file_name;
Resize DataFile by name
alter database datafile '&DFPATHNAME' resize &NEWSIZE_GB; -- Es: alter database datafile '/ora04_cap/CAP_PRO/index/cpit_data_detail_idx_14.dbf' resize 16g;
List TBS_MB
set linesize 200 pagesize 5000 col tbs_name for a32 col current_gb for 999,999.9 col max_gb like current_gb col used_gb like current_gb col free_gb like current_gb col used_pct for 999.9 col need_94 for 9,999 col need_97 like need_94 variable block_b number; exec SELECT value into :block_b from v$parameter where name='db_block_size'; def MB = 1048576; select tbs_name, round(current_b/&MB,1) current_gb, round(max_b/&MB,1) max_gb, round(used_b/&MB,1) used_gb, round(free_b/&MB,1) free_gb, round(used_pcent,1) used_pct, case when need_94_b <=0 then NULL else ceil(need_94_b/&MB) end need_94, case when need_97_b <=0 then NULL else ceil(need_97_b/&MB) end need_97 from (select tbs_name, current_b, max_b, used_b, free_b, used_pcent, case when free_b > max_b - max_b * .94 then NULL else used_b - max_b *.94 end need_94_b, case when free_b > max_b - max_b * .97 then NULL else used_b - max_b *.97 end need_97_b FROM (SELECT a.tablespace_name tbs_name, SUM(a.bytes) current_b, SUM(decode(b.maxextend, null, A.BYTES, b.maxextend*:block_b)) max_b, (SUM(a.bytes) - c.Free) used_b, (SUM(decode(b.maxextend, null, A.BYTES, b.maxextend*:block_b)) - (SUM(a.bytes) - c.Free)) free_b, (100*(SUM(a.bytes) - c.Free)/(SUM(decode(b.maxextend, null, A.BYTES, b.maxextend*:block_b)))) used_pcent FROM dba_data_files a, sys.filext$ b, (SELECT d.tablespace_name ,sum(nvl(c.bytes,0)) Free FROM dba_tablespaces d, dba_free_space c WHERE d.tablespace_name = c.tablespace_name(+) GROUP BY d.tablespace_name) c WHERE a.file_id = b.file#(+) AND a.tablespace_name = c.tablespace_name GROUP BY a.tablespace_name, c.Free) tbs_sizes) --WHERE used_pcent > 75 OR max_b/&MB >= 100 ORDER BY used_pct desc;
shrink LOGFILE
- Crea lo spazio per aggiungere almeno un gruppo di logfile della nuova dimensione. Se non ne hai, dovrai fare un gruppo in piu di logfile temporanei off-site.
- Aggiungi un gruppo di logfile della dimensione richiesta:
- switch e checkpoint q.b.
- droppa un gruppo di vecchi logfile
- vai sul disco e cancella fisicamente i logfile droppati
- Ripeti per tutti i gruppi
set line 172 pages 500 col member for a48 select member, group#, status, type, is_recovery_dest_file from v$logfile; select GROUP#, THREAD#, SEQUENCE#, MEMBERS, ARCHIVED, STATUS from v$log; alter database add logfile group &new_group ('&fname01', '&fname02') size &log_size; alter system switch logfile; alter system checkpoint;
page revision: 32, last edited: 28 Dec 2017 11:13