Oracle : Opérations sur les Tablespaces

Lister les tablespaces

SELECT tablespace_name FROM dba_tablespaces;

Lister les tablespaces et l'espace alloué / occupé

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;

Lister tous les tablespaces et leur datafile

SELECT * FROM DBA_DATA_FILES
 
-- Pour un tablesspace en particulier :
SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE 'CIVIINDX';

Redimensionner un tablespace

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;

Purge des tables dans tablespace SYSAUX

Nettoyage tables AWR

SQL> CONNECT / AS sysdba 
 
SQL> @?/rdbms/admin/catnoawr.sql 
 
SQL> @?/rdbms/admin/catawrtb.sql

Nettoyage statistiques

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