Augmenter un tablespace

retour vers Oracle

si la taille du tablespace est insuffisante mais le dataFile correspondant suffisant:

 ALTER TABLESPACE ora_data DEFAULT STORAGE ( INITIAL 100K 
                                   NEXT 100K 
			     MINEXTENTS 1 
			     MAXEXTENTS 300 
			    PCTINCREASE 1);

si le TableSpace est en autoextend augmentez le DataFile correspondant:

• Etape 1: vérifier l’état des TBS et rechercher le nom du fichier à modifier.

Exemple de requete generique reutilisable:

select a.tablespace_name Tablespace,
	   b.tablespace_name  "Nom TBS",
     ddf.file_name  "Nom Fichier",
     ddf.autoextensible   "Auto extend",
	   sum(b.bytes)/1024/1024/count( distinct a.file_id||'.'||a.block_id )      	Total,
	   round(sum(b.bytes)/1024/1024/count( distinct a.file_id||'.'||a.block_id ) -
	   sum(a.bytes)/1024/1024/count( distinct b.file_id ),2)			  used,
	   round(sum(a.bytes)/1024/1024/count( distinct b.file_id ),2)                   Libre,
	   round(100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
			   (sum(a.bytes)/count( distinct b.file_id ) )) /
	   (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2) Pourcentage 
from sys.dba_free_space a, 
sys.dba_data_files b, 
dba_data_files ddf 
where a.tablespace_name = b.tablespace_name
and   b.tablespace_name=ddf.tablespace_name
group by  a.tablespace_name, 
          b.tablespace_name,
          ddf.file_name,
          ddf.autoextensible
order by Pourcentage desc;

Résultat de la commande:

--UNDOTBS		UNDOTBS				/oradata/ORA12004/undo/ora12004_undotbs01.dbf	 2500	 2110,75     389,25	84,43
--J2_DAT_01		J2_DAT_01			/oradata/ORA12004/data01/j2_aa_data02.dbf	27500	20189,69    7310,31	73,42
--J2_DAT_01		J2_DAT_01			/oradata/ORA12004/data01/j2_aa_data01.dbf	27500	20189,69    7310,31	73,42
--SYSAUX		SYSAUX				/oradata/ORA12004/system/ora12004_sysaux01.dbf	  400	  290,10     109,81	72,55
--SYSTEM		SYSTEM				/oradata/ORA12004/system/ora12004_system01.dbf	  400	  257,75     142,25	64,44
--TOOLS			TOOLS				/oradata/ORA12004/audi/j2_aa_audit01.dbf	  500	  253,81     246,19	50,76
--J2_IND_01		J2_IND_01			/oradata/ORA12004/indx01/j2_aa_indx01.dbf	61440	25433,88   36006,13	41,40
--J2_IND_01		J2_IND_01			/oradata/ORA12004/indx01/j2_aa_indx02.dbf	61440	25433,88   36006,13	41,40
--J2_IND_01		J2_IND_01			/oradata/ORA12004/indx01/j2_aa_indx03.dbf	61440	25433,88   36006,13	41,40
--J2_IND_ARCH_01	J2_IND_ARCH_01		/oradata/ORA12004/indxarch01/j2_aa_indx01_arch.dbf	17000	 3762,06   13237,94	22,13
--J2_DAT_ARCH_01	J2_DAT_ARCH_01		/oradata/ORA12004/dataarch01/j2_aa_data01_arch.dbf	 5500	  920,06    4579,94	16,73

• Etape 2 : Modifier la taille du DataFile

ALTER DATABASE DATAFILE '/oradata/ORA12004/undo/ora12004_undotbs01.dbf' RESIZE 2500M;