Ora Resize Tbs

main

resize tbs

DN

Crea_DB

Backup/Restore

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

1. 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.
2. Aggiungi un gruppo di logfile della dimensione richiesta:
3. switch e checkpoint q.b.
4. droppa un gruppo di vecchi logfile
5. vai sul disco e cancella fisicamente i logfile droppati
6. 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;