oracledba.help
System

Standard Tablespaces

AutoExtend

ALTER TABLESPACE <TablespaceName>
AUTOEXTEND [ON|OFF] NEXT <nnnM|nnnG|UNLIMITED>
MAXSIZE <nnnM|nnnG|UNLIMITED>|OFF>;

ALTER TABLESPACE x15 AUTOEXTEND ON NEXT 64m MAXSIZE 10g;
ALTER TABLESPACE x15 AUTOEXTEND ON NEXT 64m MAXSIZE UNLIMITED;
ALTER TABLESPACE x15 AUTOEXTEND OFF;

Show AutoExend Status for All Standard Tablespaces 

COL tablespace_name FORMAT a25
COL file_name       FORMAT a50
COL autoextensible  FORMAT a15
COL maxbytes        FORMAT 999,999,999,999,999
SELECT tablespace_name, file_name, autoextensible, maxbytes 
FROM dba_Data_files
ORDER BY tablespace_name;

Show AutoExend Status for Temp Tablespace(s) 

 SELECT tablespace_name, autoextensible FROM dba_temp_files;

Create

CREATE [BIGFILE] TABLESPACE <TablespaceName>
DATAFILE 'PathToDataFile.dbf'
SIZE <nnnM|nnnG>  [REUSE]
AUTOEXTEND [ON|OFF] NEXT <nnnM|nnnG> MAXSIZE <nnnM|nnnG|UNLIMITED>
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
LOGGING|NOLOGGING;

CREATE BIGFILE TABLESPACE HR
DATAFILE '/oradata/dbf/hr.dbf'
SIZE 5g REUSE
AUTOEXTEND ON NEXT 25m MAXSIZE 10g
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
LOGGING;

ASM Example 

CREATE BIGFILE TABLESPACE hr 
DATAFILE '+DATA' 
SIZE 5gm AUTOEXTEND ON NEXT 25m MAXSIZE 10g;

Example File Created: +DATA/ORADB/DATAFILE/hr.308.958462801

Display

 COL file_id         FORMAT 999999
 COL tablespace_name FORMAT a25
 COL file_name       FORMAT a60

-- By Name 

 SELECT DISTINCT tablespace_name FROM dba_data_files ORDER BY tablespace_name;

-- By File ID 

 SELECT file_id,tablespace_name,file_name FROM dba_data_files;

-- By Size 

 SELECT tablespace_name, to_char(sum(bytes), '999,999,999,999') "Bytes" 
 FROM dba_segments 
 GROUP BY owner,tablespace_name;

-- Space: Used, Free, Total, Pct. Free 

 col "Tablespace" for a22
 col "Used MB" for 99,999,999
 col "Free MB" for 99,999,999
 col "Total MB" for 99,999,999

 SELECT df.tablespace_name "Tablespace",totalusedspace "Used MB",
 (df.totalspace - tu.totalusedspace) "Free MB",
 df.totalspace "Total MB",
 round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
 "Pct. Free"
 from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace 
 from dba_data_files 
 group by tablespace_name) df,
 (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
 from dba_segments 
 group by tablespace_name) tu
 where df.tablespace_name = tu.tablespace_name ;

-- Does it contain any data? 

 SELECT segment_name, segment_type FROM user_segments 
 WHERE tablespace_name = '&v_MyTablespaceName';

Drop

  1. ALTER TABLESPACE <TablespaceName> OFFLINE;
  2. Ensure normal operations of the database and applications still functioning properly.
  3. DROP TABLESPACE TableSpaceName [INCLUDING CONTENTS AND DATAFILES][CASCADE CONSTRAINTS]; 
 ALTER TABLESPACE x15 OFFLINE;
 DROP TABLESPACE x15 INCLUDING CONTENTS AND DATAFILES;

Move

Move (12c Online)

  • In 12c automatically moves datafile online now.
  • You can view Long Operations to see the status with this option.

ALTER DATABASE MOVE DATAFILE '<SrcPath2File>' TO '<DestPath2File>' [KEEP] [REUSE]; 

 -- QC
 COLUMN file_name FORMAT A70
 SELECT file_id, file_name FROM dba_data_files ORDER BY file_id;

 -- Files Based DB Example
 ALTER DATABASE MOVE DATAFILE 
 '/u01/oradata/ORADB/datafile/users.dbf' 
 TO 
 '/u03/oradata/ORADB/datafile/test/users.dbf' 
 KEEP REUSE;

 -- OMF Example
 ALTER DATABASE MOVE DATAFILE '/u01/oradata/ORADB/datafile/o1_mf_users_f0s181c0_.dbf';
 Above moves file to DB_CREATE_FILE_DEST location.  Changes OMF name if already exists.

 -- QC
 SELECT file_id, file_name FROM dba_data_files ORDER BY file_id;

Move (File Based)

  1. ALTER TABLESPACE <TablespaceName> OFFLINE NORMAL;
  2. Copy source_datafile to destination_datafile location.
  3. ALTER TABLESPACE RENAME DATAFILE 'SourceDataFileLocation' TO 'DestinationDataFileLocation';
  4. ALTER TABLESPACE <TablespaceName> ONLINE;

Move (ASM)

-- Get Datafile Name 

 SELECT file_name FROM dba_data_files;

-- Get Diskgroup Name 

 SELECT name FROM v$asm_diskgroup;

-- Set Datafile Offline: 

 ALTER DATABASE datafile '+GRP_OLD/DB1/DATAFILE/users.288.929089335' offline;

-- Copy the Datafile to New Diskgroup (using RMAN) 

 RMAN> COPY DATAFILE '+GRP_OLD/DB1/DATAFILE/users.288.929089335' TO '+GRP_NEW';

  Note file name change in copy operation (snippet below).
  input datafile file number=00005 name=+GRP_OLD/DB1/DATAFILE/users.288.929089335
  output file name=+GRP_NEW/DB1/DATAFILE/users.256.929457939

-- Re-name the data file: 

 ALTER DATABASE rename file '+GRP_OLD/DB1/DATAFILE/users.288.929089335'  
 TO '+GRP_NEW/DB1/DATAFILE/users.256.929457939';

   After Oracle renames the ASM database file in the data dictionary, 
   it will remove the original ASM database file.

-- Rename the RMAN data file: 

 RMAN> switch datafile '+GRP_NEW/DB1/DATAFILE/users.256.929457939' TO COPY;

-- Use RMAN recovery to the new data file: 

 RMAN> recover datafile '+GRP_NEW/DB1/DATAFILE/users.256.929457939';

-- Put the data file online: 

 RMAN> alter database datafile '+GRP_NEW/DB1/DATAFILE/users.256.929457939' online;

-- Delete the old ASM file from the old diskgroup (if need be). 

 ALTER DISKGROUP GRP_OLD DROP FILE '+GRP_OLD/DB1/DATAFILE/users.288.929089335';

Delete original\source datafile at the OS level after appropriate confirmation that all is working OK for your environment.

Move (Undo TS)

  1. Create a new UNDO TABLESPACE.
  2. Change configuration to point all new transaction to new UNDO tablespace.
  3. Offline then delete original UNDO tablespace when there are no more transactions using it. 
 CREATE BIGFILE UNDO TABLESPACE UNDO1
 DATAFILE 'c:/oradata1/dbf/undo1.dbf'
 SIZE 5g AUTOEXTEND ON NEXT 100m MAXSIZE UNLIMITED
 EXTENT MANAGEMENT LOCAL;

 ALTER SYSTEM SET UNDO_TABLESPACE=UNDO1 SCOPE=BOTH;

 ALTER TABLESPACE undo_old OFFLINE NORMAL;

 SELECT tablespace_name,status FROM dba_data_files WHERE tablespace_name LIKE '';

 Wait a day ...

 DROP TABLESPACE undo_old INCLUDING CONTENTS AND DATAFILES;

All the active transactions will remain using the actual tablespace. New transactions will be addressed to the new undo tablespace. Once the active transactions finish, you can put your old tablespace offline and then drop it, until then it will be "PENDING OFFLINE".

Offline\Online

ALTER TABLESPACE <TablespaceName> <ONLINE|OFFLINE> [NORMAL]; 

 ALTER TABLESPACE users OFFLINE NORMAL;

Quota

ALTER USER <UserName> QUOTA <nnnM|nnnG|UNLIMITED> ON <TablespaceName>;

 ALTER USER scott QUOTA unlimited ON users;

Resize

-- BFTS 

ALTER TABLESPACE <TablespaceName> RESIZE <nnnM|nnnG>; 

 ALTER TABLESPACE hr RESIZE 15g;
 ALTER TABLESPACE DATA RESIZE 1320000m;

-- Legacy 

ALTER DATABASE DATAFILE 'PathToDataFile.dbf' RESIZE <nnnM|nnnG>; 

 ALTER DATABASE DATAFILE '/oradata/dbf/hr.dbf' RESIZE 15g;

For Legacy Tablepaces you must resize the corresponding datafile(s).

<- System