Oracle - Notes about tablespaces

mail

How to resize a tablespace ?

Resizing a tablespace is actually a matter of :
  • for a smallfile tablespace : adding/resizing a datafile
  • for a bigfile tablespace : resizing the unique datafile
To do so :

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 200M;

Specify the new size without the B of the unit : 200M, 3G, ...

mail

How to delete a tablespace ?

DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE MY_TABLESPACE; drops the tablespace but leaves the datafile(s) in place, which leads to an awkward situation for Oracle. This is neither logical nor completely clear to me, but I'd rather not use this anymore. Use the "including" version instead. (source)
mail

How to detect whether a tablespace is a smallfile or a bigfile tablespace ?

tablespaceName='SYSTEM'; ORACLE_SID='COCNAT'; echo "$ORACLE_SID.$tablespaceName :"; echo "SELECT bigfile FROM dba_tablespaces WHERE tablespace_name='$tablespaceName';" | sqlplus / as sysdba | grep -A2 BIGFILE

COCNAT.SYSTEM :
BIGFILE
------------
NO
mail

How to list existing tablespaces ?

Solution 1 (source) :

SET linesize 200
COL "STATUS" FORMAT A10
COL "TABLESPACE_NAME" FORMAT A20
COL "FILE_NAME" FORMAT A80
SELECT
FILE_ID, STATUS, TABLESPACE_NAME, FILE_NAME
FROM
DBA_DATA_FILES
ORDER BY FILE_ID;
FILE_ID	STATUS		Tablespace	FILE_NAME
------- --------------- --------------- -----------------------------------------------------------
1	AVAILABLE	SYSTEM		/appli/oracle/MGNNAT/oradata01/MGNNAT_system_01.dbf
2	AVAILABLE	SYSAUX		/appli/oracle/MGNNAT/oradata01/MGNNAT_sysaux_01.dbf
3	AVAILABLE	UNDO		/appli/oracle/MGNNAT/oradata02/MGNNAT_undo_01.dbf
4	AVAILABLE	TOOLS		/appli/oracle/MGNNAT/oradata04/MGNNAT_tools_01.dbf
5	AVAILABLE	USERS		/appli/oracle/MGNNAT/oradata03/MGNNAT_users_01.dbf
6	AVAILABLE	MGN_DATA	/appli/oracle/MGNNAT/oradata01/MGNNAT_data.dbf

6 rows selected.

Solution 2 :

set linesize 2000

col TABLESPACE_NAME format a20
col BLOCK_SIZE format 9999999
col STATUS format a9
col CONTENTS format a9
col ALLOCATION_TYPE format a9
col SEGMENT_SPACE_MANAGEMENT format a15
col BIGFILE format a3
col file_name format a60
col size format a50

select t.TABLESPACE_NAME, t.BLOCK_SIZE, t.CONTENTS, t.STATUS, t.ALLOCATION_TYPE, t.SEGMENT_SPACE_MANAGEMENT, t.BIGFILE, f.file_name, f.bytes/(1024*1024) as "SIZE MiB"
from dba_tablespaces t
inner join dba_data_files f
on t.TABLESPACE_NAME=f.TABLESPACE_NAME
union
select t.TABLESPACE_NAME, t.BLOCK_SIZE, t.CONTENTS, t.STATUS, t.ALLOCATION_TYPE, t.SEGMENT_SPACE_MANAGEMENT, t.BIGFILE, f.file_name, f.bytes/(1024*1024) as "SIZE MiB"
from dba_tablespaces t
inner join dba_temp_files f
on t.TABLESPACE_NAME=f.TABLESPACE_NAME;
Tablespace           BLOCK_SIZE CONTENTS  STATUS    ALLOCATIO SEGMENT_SPACE_M BIG FILE_NAME                                                      SIZE MiB
-------------------- ---------- --------- --------- --------- --------------- --- ------------------------------------------------------------ ----------
MGN_DATA                   8192 PERMANENT ONLINE    SYSTEM    AUTO            NO  /appli/oracle/MGNNAT/oradata01/MGNNAT_data.dbf                     2048
SYSAUX                     8192 PERMANENT ONLINE    SYSTEM    AUTO            NO  /appli/oracle/MGNNAT/oradata01/MGNNAT_sysaux_01.dbf                1024
SYSTEM                     8192 PERMANENT ONLINE    SYSTEM    MANUAL          NO  /appli/oracle/MGNNAT/oradata01/MGNNAT_system_01.dbf                1024
TEMP                       8192 TEMPORARY ONLINE    UNIFORM   MANUAL          NO  /appli/oracle/MGNNAT/oradata05/MGNNAT_temp_01.dbf                  2048
TOOLS                      8192 PERMANENT ONLINE    UNIFORM   AUTO            NO  /appli/oracle/MGNNAT/oradata04/MGNNAT_tools_01.dbf                  300
UNDO                       8192 UNDO      ONLINE    SYSTEM    MANUAL          NO  /appli/oracle/MGNNAT/oradata02/MGNNAT_undo_01.dbf                  1024
USERS                      8192 PERMANENT ONLINE    SYSTEM    AUTO            NO  /appli/oracle/MGNNAT/oradata03/MGNNAT_users_01.dbf                  256

7 rows selected.
mail

How to create a tablespace ?

CREATE TABLESPACE MY_TABLESPACE DATAFILE 'myDatafile.dat' [tablespaceOptions];