Nethence Newdoc Olddoc Lab Your IP BBDock  


Warning: those guides are mostly obsolete, please have a look at the new documentation.

UnixWindowsOracleObsoleteHardwareDIYMechanicsScriptsConfigs

Tablespaces
 
 
Tablespaces' administration
Note. see the database creation doc to create a tablespace
 
Look for tablespaces,
select tablespace_name, status, contents
from dba_tablespaces
order by tablespace_name;
 
Check autoextend is enabled,
descr dba_data_files
set linesize 140
column file_name format a40;
select file_name, tablespace_name, autoextensible
from dba_data_files;
 
Eventually enable autoextend,
alter database datafile '/u02/oradata/dbname/datafile.dbf'
autoextend on;
 
Drop tablespaces,
drop tablespace tsname;
drop tablespace tsnametmp;
and as oracle user,
rm -f /u02/oradata/dbname/tsname.dbf
rm -f /u02/oradata/dbname/tsnametmp.dbf
Ref http://www.orafaq.com/wiki/Oracle_database_Security_FAQ
 
 
Tablespaces' space usage
Normal tablespaces
Query tablespaces' sizes,
descr dba_data_files
select file_name, tablespace_name, bytes/(1024*1024) "Size MB"
from dba_data_files;
 
Query tablespaces's free space,
descr dba_free_space
--select tablespace_name, bytes/(1024*1024) "Free MB"
--from dba_free_space;
select tablespace_name, sum(bytes)/(1024*1024) "Free MB"
from dba_free_space group by tablespace_name;
 
Query all togeather (size and free space),
select dba_data_files.tablespace_name,
dba_data_files.bytes/(1024*1024) "Size MB",
sum(dba_free_space.bytes)/(1024*1024) "Free MB"
from dba_free_space, dba_data_files
where dba_data_files.tablespace_name = dba_free_space.tablespace_name
group by dba_data_files.tablespace_name, dba_data_files.bytes
order by tablespace_name;
Note. 'sum' and 'group by' to sum up and group the 4 UNDOTBS1 lines
Note. there's also the 'dba_free_space_coalesced' table.
 
 
Temporary tablespaces
Query temporary tablespaces' sizes,
descr dba_temp_files
select file_name, tablespace_name, bytes/(1024*1024) "Size MB"
from dba_temp_files;
 
Query temporary tablespaces's free space,
descr v$temp_space_header
select tablespace_name, bytes_free/(1024*1024) "Free MB"
from v$temp_space_header;
 
Query all togeather (size and free space),
select dba_temp_files.tablespace_name,
dba_temp_files.bytes/(1024*1024) "Size MB",
v$temp_space_header.bytes_free/(1024*1024) "Free MB"
from dba_temp_files, v$temp_space_header
where dba_temp_files.tablespace_name = v$temp_space_header.tablespace_name
group by dba_temp_files.tablespace_name,
    dba_temp_files.bytes,
    v$temp_space_header.bytes_free
order by tablespace_name;
 

(obsolete, see the new doc)