====== Oracle : Opérations sur les Tablespaces ======
\\ 

==== Lister les tablespaces ====
<code sql>SELECT tablespace_name FROM dba_tablespaces;</code>
\\ 

==== Lister les tablespaces et l'espace alloué / occupé ====
<code sql>
SELECT A.tablespace_Name, A.Alloue, B.Occupe, C.Libre
FROM (select tablespace_name, sum(bytes)/1024/1024 AS ALLOUE from dba_data_files group by tablespace_name) a,
(select tablespace_name, Sum(bytes)/1024/1024 AS OCCUPE from dba_segments group by tablespace_name) b,
(select tablespace_name, Sum(bytes)/1024/1024 AS LIBRE from dba_free_space group by tablespace_name) c
WHERE B.tablespace_Name = A.tablespace_Name
AND C.Tablespace_Name = B.Tablespace_Name;
</code>
\\ 
==== Lister tous les tablespaces et leur datafile ====
<code sql>
SELECT * FROM DBA_DATA_FILES

-- Pour un tablesspace en particulier :
SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME like 'CIVIINDX';
</code>
\\ 

==== Redimensionner un tablespace ====
<code sql>
ALTER DATABASE DATAFILE '/u03/civitest/cividata1.dbf' RESIZE 3000M;
ALTER DATABASE DATAFILE '/u03/civitest/cividata2.dbf' RESIZE 3000M;
ALTER DATABASE DATAFILE '/u03/civitest/cividata3.dbf' RESIZE 3000M;
ALTER DATABASE DATAFILE '/u03/civitest/cividata4.dbf' RESIZE 3000M;
</code>
\\ 

==== Purge des tables dans tablespace SYSAUX ====

=== Nettoyage tables AWR ===
<code sql>
SQL> connect / as sysdba 

SQL> @?/rdbms/admin/catnoawr.sql 

SQL> @?/rdbms/admin/catawrtb.sql
</code>

=== Nettoyage statistiques ===

[[http://jhdba.wordpress.com/2009/05/19/purging-statistics-from-the-sysaux-tablespace/]]