Oracle space management

List all tablespaces and the files that belong to them

COLUMN TABLESPACE_NAME FORMAT A40
COLUM BYTES/1024*1024 FORMAT 999,999,999,999
SELECT FILE_NAME,TABLESPACE_NAME, BYTES/1024*1024
FROM dba_data_files

Archive Log Mode Status
/* log mode of databases */
SELECT name, log_mode FROM v$database;


Find overall size of the database

COLUMN sum(bytes)/1024/1024 FORMAT 999,999,999,999
select sum(bytes)/1024/1024 from dba_data_files;

Size of all TEMP files

select nvl(sum(bytes),0)/1024/1024 from dba_temp_files;


To shrink your current tablespaces to the HWM

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/


column cmd format a75 word_wrapped
select 'alter database datafile '''file_name''' resize '
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents group by file_id ) b
where a.file_id = b.file_id(+) and
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/


Moving datafiles

1. Shutdown database
2. Take a full cold backup (optional)
3. Copy the datafiles from the existing filesystem to the new one (you can always delete the file from original filesystem later)
4. Startup mount instance
5. For each datafile, ALTER DATABASE RENAME TO
6. ALTER DATABASE OPEN;
7. ALTER DATABASE BACKUP CONTROLFILE TO TRACE; -- since we changed the database structure and must preserve it.
8. Delete the file(s) from the original filesystem.

Some datafiles can be removed while the database is running, but only if they:

1. Are not part of the SYSTEM tablespace
2. Do not contain temporary segments
3. Do not contain rollback segments

To move the datafiles of a tablespace from one filesystem to another, without shutting down the database:

1. ALTER TABLESPACE ABC OFFLINE;
2. Copy the required files to the new location
3. For each datafile, ALTER TABLESPACE ABC ONLINE;
4. ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
5. Delete the file(s) from the original filesystem.