Oracle Tablespaces
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Bigfile Tablespaces and other notesI highly recommend the use of BIGFILE tablespaces for almost everything: The operative word being "almost." If you use BIGFILE tablespaces use the SEGMENT keyword when creating your RMAN backup control file.

Do not use BIGFILE tablespaces to temp ... too great a possibility for locking affecting an entire application. My recommendation for VLDBs is multiple temp spaces created with normal tablespaces and segment your application so that different parts of the application are not competing for temp segments.

With respect to SYSAUX tablespace sizing be sure to click on the link, page bottom, for UTLSYXSZ_UTIL.
 
Data Dictionary Objects (partial list)
Tablespaces cdb_tablespaces
dba_tablespaces
ts$
user_tablespaces
Tablespace Quotas cdb_ts_quotas
dba_ts_quotas
tsq$
user_ts_quotas
Data Files cdb_data_files
dba_data_files
v_$backup_datafile
v_$datafile
v_$datafile_copy
v_$datafile_header
Free Space dba_free_space
Segments cdb_segments
dba_segments
v_$segment_statistics
Extents dba_extents
Blocks v_$database_block_corruption
Groups dba_tablespace_groups
SYSAUX Tablespace v_$sysaux_occupants
Temp Tablespace dba_temp_files
Undo Tablespace cdb_rollback_segs
cdb_undo_extents
dba_rollback_segs
dba_undo_extents
v_$rollstat
v_$undostat
Transportable Tablespaces transport_set_violations
Dictionary Management fet$
uet$
System Privileges
Alter Tablespace Drop Tablespace Unlimited Tablespace
Create Tablespace Manage Tablespace  
 
Permanent Tablespace
Permanent Tablespace On A File System Without Auto-extend CREATE [<BIGFILE | SMALLFILE>] TABLESPACE <tablespace_name>
DATAFILE '<path_and_file_name>'
SIZE <integer><K | M | G | T | P | E> [REUSE] AUTOEXTEND <OFF | ON>
BLOCKSIZE <bytes>
[<LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING | FORCE_LOGGING>]
[ENCRYPTION USING '<encryption_algorithm>'
 IDENTIFIED BY <password> [NO] SALT]
[DEFAULT <COMPRESS [FOR <ALL | DIRECT_LOAD> OPERATIONS] | NOCOMPRESS>]
[<ONLINE | OFFLINE>]
EXTENT MANAGEMENT LOCAL <AUTOALLOCATE | UNIFORM SIZE <extent_size>>
[SEGMENT SPACE MANAGEMENT <AUTO | MANUAL>]
[FLASHBACK <ON | OFF>];
CREATE BIGFILE TABLESPACE uwdata
DATAFILE 'c:\oracle\oradata\orabase\uwdata.dbf' SIZE 100M
AUTOEXTEND OFF
BLOCKSIZE 8192
FORCE LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
FLASHBACK ON;

desc dba_tablespaces

set linesize 121
col tablespace_name format a20

SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_in, segment_space_management
FROM dba_tablespaces;

desc dba_data_files

col file_name format a45

SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by
FROM dba_data_files;

SELECT dbms_metadata.get_ddl('TABLESPACE', 'UWDATA')
FROM dual;
Permanent Tablespace On A Raw Device CREATE TABLESPACE <tablespace_name>
DATAFILE '<path_and_file_name>'
SIZE <K | M | G | T | P | E>
BLOCKSIZE <bytes>
AUTOEXTEND OFF
EXTENT MANAGEMENT UNIFORM LOCAL SIZE <extent_size>
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE tools LOGGING
DATAFILE '/u01/oradata/' SIZE 127G
BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

SELECT tablespace_name, bigfile, block_size, status, contents, logging, allocation_type, segment_space_management
FROM dba_tablespaces
ORDER BY 1;
Oracle Managed Permanent Auto-extendable Tablespace CREATE TABLESPACE <tablespace_name>;
ALTER SYSTEM SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';

CREATE TABLESPACE user_data;
Oracle Managed Permanent Fixed Size Tablespace CREATE TABLESPACE <tablespace_name>
DATAFILE AUTOEXTEND OFF;
ALTER SYSTEM SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';

CREATE TABLESPACE user_data DATAFILE AUTOEXTEND OFF;
Set default tablespace type ALTER DATABASE SET DEFAULT <tablespace_type> TABLESPACE;
ALTER DATABASE DEFAULT BIGFILE TABLESPACE;

set linesize 121
col value$ format a20
col comment$ format a60

SELECT *
FROM props$
WHERE name LIKE '%DEF%'
ORDER BY name;
Set tablespace as the default ALTER DATABASE DEFAULT TABLESPACE <tablespace_name>;
ALTER DATABASE DEFAULT TABLESPACE uwdata;

set linesize 121
col value$ format a20
col comment$ format a60

SELECT *
FROM props$
WHERE name LIKE '%DEF%'
ORDER BY name;
 
SYSAUX Tablespace
Create SYSAUX Tablespace SELECT tablespace_name
FROM dba_tablespaces;

CREATE TABLESPACE sysaux
DATAFILE '/u01/oradata/sysaux01.dbf' SIZE 700M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Move SYSAUX Contents -- move must be done using the indicated procedure

col occupant_name format a30
col schema_name format a30
col move_procedure format a50

SELECT occupant_name, schema_name, move_procedure
FROM v_$sysaux_occupants
ORDER BY 1;
 
Undo Tablespace
Note: When working with the UNDO tablespace be sure to observe the values for the visible initialization parameters as well as "_autotune_undo"
Create An UNDO Tablespace CREATE UNDO TABLESPACE <tablespace_name>
DATAFILE '<path_and_file_name>'
SIZE <integer><K | M | G | T>
AUTOEXTEND <ON | OFF>
RETENTION <GUARANTEE | NOGUARANTEE>;
CREATE UNDO TABLESPACE undotbs02
DATAFILE '/u01/oradata/undotbs02.dbf
SIZE 50000M REUSE AUTOEXTEND ON
RETENTION NOGUARANTEE;

desc dba_undo_extents

SELECT segment_name, tablespace_name, status, SUM(bytes)
FROM dba_undo_extents
GROUP BY segment_name, tablespace_name, status;

ALTER SYSTEM SET undo_tablespace='UNDOTBS2' scope=BOTH;
Change The Current UNDO Tablespace -- identify existing tablespaces
SELECT tablespace_name
FROM dba_tablespaces
ORDER BY 1;

-- format SQL*Plus display
col name format a30
col value format a30

-- check current undo related parameters
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';

-- create the new undo tablespace
CREATE UNDO TABLESPACE testundo
DATAFILE '/u03/oradata/testundo.dbf'
SIZE 100M REUSE AUTOEXTEND ON
RETENTION NOGUARANTEE;

-- note that while it was created it is not being used
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';

-- switch undo tablespaces
ALTER SYSTEM SET UNDO_TABLESPACE = testundo SCOPE=BOTH;

-- verify the change has been performed
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';

-- remove the old file
DROP TABLESPACE <old_tablespace's_name> INCLUDING CONTENTS AND DATAFILES;
 
Temporary Tablespaces
Create Temporary Tablespace CREATE TEMPORARY TABLESPACE <tablespace_name>
TEMPFILE '<path_and_file_name>'
SIZE <integer><K | M | G | T | P>
AUTOEXTEND <ON | OFF>
TABLESPACE GROUP <group_name>
EXTENT MANAGEMENT LOCAL UNIFORM SIZE <extent_size>;

Note:
You cannot use the ALTER TABLESPACE statement, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace.
CREATE TEMPORARY TABLESPACE temp_new
TEMPFILE '/u01/oradata/tempnew01.dbf'
SIZE 1G AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;

SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_in
FROM dba_tablespaces;

desc dba_temp_files

SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by
FROM dba_temp_files;
Add Tempfile ALTER TABLESPACE <tablespace_name>
ADD TEMPFILE '<path_and_file_name>' SIZE <integer><M| G | T>;
ALTER TABLESPACE temp_new
ADD TEMPFILE '/u02/oradata/tempnew02.dbf' SIZE 20G;
Resize Tempfile ALTER DATABASE TEMPFILE '<file_name>'
RESIZE <integer><M | G | T>;
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' RESIZE 40G;
Drop Tempfile ALTER DATABASE TEMPFILE '<file_name>' DROP;
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' DROP;
Take Temporary Tablespace Off-line ALTER DATABASE TEMPFILE '<path_and_file_name>' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' OFFLINE;
Place Temporary Tablespace On-line ALTER DATABASE TEMPFILE '<path_and_file_name>' ONLINE;
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' ONLINE;
Changing the Default Temporary Tablespace ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <tablespace_name>;
col property_value format a30
col description format a55

SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

SELECT file_name, tablespace_name
FROM dba_temp_files;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempnew;

DROP TABLESPACE temp;
 
Transportable Tablespaces
Determine Transportability dbms_tts.transport_set_check(tablespace_name IN VARCHAR2, TRUE);
dbms_tts.transport_set_check('uwdata', TRUE);
View Violations; If Any SELECT * FROM TRANSPORT_SET_VIOLATIONS;
Generate A Transportable Set ALTER TABLESPACE <tablespace_name> READ ONLY;
ALTER TABLESPACE tools READ ONLY;
Export Tablespace

Although the Export utility is used, only data dictionary structural information (metadata) for the tablespaces is exported.
EXP TRANSPORT_TABLESPACE=Y
TABLESPACES=(<comma_delimited_list_of_tablespaces>)
TRIGGERS=Y CONSTRAINTS=N GRANTS=N FILE=<file_name>
EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2)
TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=expdat.dmp
Import Tablespace IMP TRANSPORT_TABLESPACE=<Y | N> FILE=<file_name>
DATAFILES=('<comma_delimited_list_of_data_files>)
TABLESPACES=(<comma_delimited_list_of_tablespaces>)
TTS_OWNERS=(<comma_delimited_list_of_schema_owners>)
FROMUSER=(from_user_list) TOUSER=(to_user_list)
IMP TRANSPORT_TABLESPACE=y FILE=expdat.dmp
DATAFILES=('/db/sales_jan','/db/sales_feb')
TABLESPACES=(sales_1,sales_2) TTS_OWNERS=(dcranney,jfee)
FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)
Import Parameter File TRANSPORT_TABLESPACE=y FILE=expdat.dmp
DATAFILES=('/db/sales_jan','/db/sales_feb')
TABLESPACES=(sales_1, sales_2)
TTS_OWNERS=(dcranney, jfee)
FROMUSER=(dcranney, jfee)
TOUSER=(smith, williams)
 
Alter Permanent Tablespace
Add Datafile ALTER TABLESPACE <tablespace_name> ADD DATAFILE <file_name>,
<integer><K | M | G | T>
[REUSE]
<autoextend> <ON | OFF>
NEXT <integer><K | M | G | T>
MAXSIZE <integer><K | M | G | T | UNLIMITED>;
ALTER TABLESPACE tools ADD DATAFILE '/u02/oracle/oradata/uwdata02.dbf' SIZE 25G
AUTOEXTEND OFF;

ALTER TABLESPACE tools ADD ADD DATAFILE 'c:\oracle\product\oradata\tools99.xxx' SIZE 10G
AUTOEXTEND ON;
Take Tablespace Off-line ALTER TABLESPACE <tablespace_name> OFFLINE;
ALTER TABLESPACE tools OFFLINE;
Place Tablespace On-line ALTER TABLESPACE <tablespace_name> ONLINE;
ALTER TABLESPACE tools ONLINE;
Make Read Only ALTER TABLESPACE <tablespace_name> READ ONLY;
ALTER TABLESPACE tools READ ONLY;

SELECT tablespace_name, status
FROM dba_tablespaces;

-- READ ONLY prevents DML ... not DDL including DROP and TRUNCATE.
Make a Tablespace Read Write ALTER TABLESPACE <tablespace_name> READ WRITE;
ALTER TABLESPACE tools READ WRITE;

SELECT tablespace_name, status
FROM dba_tablespaces;
Prepare Tablespace For Backup (archive logging must be active) ALTER TABLESPACE <tablespace_name> BEGIN BACKUP;
ALTER TABLESPACE tools BEGIN BACKUP;
End Tablespace Backup ALTER TABLESPACE <tablespace_name> END BACKUP;
ALTER TABLESPACE tools END BACKUP;
Rename Tablespace ALTER TABLESPACE <tablespace_name> RENAME TO <new_tablespace_name>;
SELECT tablespace_name
FROM dba_tablespaces;

SELECT table_name
FROM dba_tables
WHERE tablespace_name = 'USERS';

ALTER TABLESPACE users RENAME TO user_data;

SELECT tablespace_name
FROM dba_tablespaces;

SELECT table_name
FROM dba_tables
WHERE tablespace_name = 'USER_DATA';
 
Alter Undo Tablespace
Retention Guarantee ALTER TABLESPACE <tablespace_name> RETENTION <GUARANTEE | NOGUARANTEE>;
SELECT tablespace_name, retention
FROM dba_tablespaces;

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

select tablespace_name, retention
from dba_tablespaces;

ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

SELECT tablespace_name, retention
FROM dba_tablespaces;
 
Drop Tablespace
Drop Tablespace DROP TABLESPACE <tablespace_name>;
DROP TABLESPACE tools;
Drop Tablespace Including Contents DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;
DROP TABLESPACE tools INCLUDING CONTENTS;
Drop Tablespace Including Contents & Datafiles DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES;
Drop Tablespace Including Contents & Datafiles When There Are Referential Constraints DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;
DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Drop tablespace after datafile was accidentally dropped conn sys@pdbdev as sysdba

CREATE TABLESPACE badidea
DATAFILE 'c:\temp\badidea.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

SELECT tablespace_name
FROM dba_tablespaces;

SELECT file_name
FROM dba_data_files;

shutdown immediate;

-- in an operating system window drop the file c:\temp\badidea.dbf

startup

-- record the error message

shutdown immediate;

startup mount;

alter database datafile 'c:\temp\badidea.dbf' offline drop;

alter database open;

SELECT tablespace_name
FROM dba_tablespaces;

SELECT file_name
FROM dba_data_files;

DROP TABLESPACE badidea INCLUDING CONTENTS;

SELECT tablespace_name
FROM dba_tablespaces;

SELECT file_name
FROM dba_data_files;
 
Mandatory Tablespaces
SYSTEM: must be named SYSTEM in all versions Always named system and it used to be that every Oracle instance must have one, and only one system tablespace that contained the data dictionary tables and views. It was also where Oracle stored SQL programs such as stored procedures, functions, packages, and Java. As of 12c every container, CDB$ROOT, PDB$SEED, etc. contains its own SYSTEM tablespace with most of its contents existing as pointers back to CDB$ROOT.
Undo: can have any name: the default is UNDOTSP1) - version 9i or above Every Oracle instance must have one, and only one UNDO tablespace. The undo tablespace is a single large space into which Oracle stores and manager information for undo (rollback) and multi-versioning for all users and all transactions. This is as true in 12c as it was in 9i.
Temporary: can have any name but usually TEMP It used to be that every Oracle instance had one at more temp tablespace. The temp tablespace could have any name though the default name is TEMP was close to universal. This architecture is no longer true. Now, with a container database each container has one or more temp tablespaces. The temp tablespaces are used by to create temporary tables which it uses during processing of a request and for storing information for views and global temporary tables. Examples of transactions in which Oracle uses temp space are sorts and groupings.
One or more tablespaces for tables and indexes Every Oracle database may have one or more tablespaces holding segments such as tables, indexes, materialized views, and LOBS. These tablespaces can have any name but the default names are often like DATA01 and USERS and should, where possible be descriptive of their contents to avoid errors during maintenance.

It is advisable to spread I/O equally across multiple disks. One way not to accomplish this is to create separate tablespaces for tables and indexes though there is a large volume of nonsense about this in books and on the internet: Ignore it. If you have a decent NAS, SAN, or other storage device, and you've striped across every spindle you could find, as you should have done, separating tables and indexes has zero value with the sole excepetion that in a disaster recovery scenario you might wish to recover table data without its corresponding indexes.
 
Definitions
The Number Of Extents - Dictionary vs. Locally Managed Tablespaces The number of extents has never been an issue for a segment. A table couldn't care less whether it is contained in 5 extents or 5000. But what does care is the data dictionary, since 5000 extents means 5000 rows in a data dictionary cluster that's been sized to expect no more than 5. That introduces cluster chaining in the data dictionary, and if the chaining is bad enough, then performance impacts involving dictionary access will be measurable.

Since LMTs don't touch the data dictionary for the purposes of recording extent acquisition, they do not suffer from cluster chaining, and do not experience a resulting performance degradation.

The performance issue also relates to the reading of the extent map in a single I/O instead of multiple I/Os ... since the extent map is stored inside an Oracle block, which is of finite size, too many extents mean that the map has to be stored in multiple blocks ... and any operation that needs to consult the extent map would therefore need multiple I/O operations to do so. You would really need thousands of extents, though, before that became a major issue).

The key advantage of LMTs is that they avoid a potential single point of contention on the data dictionary (but you'd have to have dozens of segments all simultaneously extending before that was an issue in the first place). And (better) they mean fragmentation is a thing of the past. And (the real killer) concern about a reasonable number of extents is wasted concern (reasonable being in the high hundreds to low thousands).
Logging Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. LOGGING is the default. This clause is not valid for a temporary or undo tablespace.

The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.

The force logging clause is used to force logging and to disregard no-logging instructions issued during object creation. You cannot specify FORCE LOGGING for an undo or temporary tablespace.

This clause only affects the logging of object creation and has no effect on logging of DML statements.
Segment Space Management When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:

MANUAL
Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.

MANUAL is the default.

AUTO
This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.

Automatic segment-space management delivers better space utilization than manual segment-space management, and it is self tuning in that it scales with increasing the number of users, as well as instances. For a Real Application Clusters environment, automatic segment-space management allows for a dynamic affinity of space to instances, thus avoiding the hard partitioning of space inherent with using free list groups.

For many standard workloads, application performance when using automatic segment space management is better than the performance of a well tuned application using manual segment-space management.
 
Related Queries
List tablespaces, their files, allocated space, free space, and next free extent clear breaks
set linesize 141
set pagesize 60
break on tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL GB'
col kbytes_free heading 'TOTAL FREE|KBYTES'

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024/1024/1024 TABLESPACE_GB, SUM(fs.bytes)/1024/1024/1024 GBYTES_FREE, MAX(fs.bytes)/1024/1024/1024 NEXT_FREE_GB
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024/1024/1024
ORDER BY dd.tablespace_name, dd.file_name;
List datafiles, tablespace names, and size in GB col file_name format a50
col tablespace_name format a10

SELECT file_name, tablespace_name, ROUND(bytes/1024/1024/1024) GB
FROM dba_data_files
ORDER BY 1;
List tablespaces, size, free space, and percent free

Thanks to Michael Lehmann for this query
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
    
(
SELECT tablespace_name, SUM(bytes) FREE_SPACE,
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;
View For Schema Owner To Monitoring Free Space CREATE OR REPLACE VIEW freespace_view AS
SELECT tablespace_name, SUM(bytes/1024/1024) AVAILABLE_MB
FROM user_free_space
GROUP BY tablespace_name;
Another Statement For Tablespace Management set linesize 121

SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
  SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
  0 TOTAL_MB, 0 MAX_MB
  FROM dba_free_space
  GROUP BY tablespace_name
  UNION
  SELECT tablespace_name, 0 CURRENT_MB,
  SUM(bytes)/1024/1024 TOTAL_MB,
  SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
  FROM dba_data_files
  GROUP BY tablespace_name)
GROUP BY tablespace_name;
Yet Another Statement For Tablespace Management col tablespace_name format a15
col alloc_size format 999.999
col pct_used format 999.999
col free_space format 999.999
col maxnext format 999.999
col definitsz format 999.999
col defnextsz format 999.999

SELECT a.tablespace_name, a.datafile_sz, b.alloc_size,
(b.alloc_size)/a.datafile_sz*100 PCT_USED,
(a.datafile_sz-b.alloc_size) FREE_SPACE,
b.next_extent/1024/1024 MAXNEXT,
a.initial_extent/1024/1024 DEFINITSZ,
a.next_extent/1024/1024 DEFNEXTSZ
FROM (
 
SELECT a.tablespace_name, sum(b.bytes)/1024/1024 DATAFILE_SZ, a.initial_extent, a.next_extent
  FROM dba_tablespaces a, dba_data_files b
  WHERE a.tablespace_name = b.tablespace_name
  GROUP BY a.tablespace_name, a.initial_extent, a.next_extent
) A,
    (
  SELECT a.tablespace_name, sum(c.bytes)/1024/1024
  ALLOC_SIZE, MAX(c.next_extent) NEXT_EXTENT
  FROM dba_tablespaces a, dba_segments c
  WHERE a.tablespace_name = c.tablespace_name
  GROUP BY a.tablespace_name
) B
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY 1;
And Yet Another Statement For Tablespace Management SELECT dfs.tablespace_name, ddf.total_size, ddf.total_size - dfs.total_free TOTAL_USED,
dfs.total_free, (ddf.total_size-dfs.total_free)/ddf.total_size * 100 CAP, dfs.total_chunks, dfs.largest_chunk
FROM (
  SELECT a.tablespace_name, SUM(a.bytes)/1024/1024 TOTAL_FREE,
  COUNT(a.bytes) TOTAL_CHUNKS, MAX(a.bytes)/1024/1024 LARGEST_CHUNK
  FROM dba_free_space a
  GROUP BY a.tablespace_name) dfs,
    (
  SELECT b.tablespace_name, SUM(b.bytes)/1024/1024 TOTAL_SIZE
  FROM dba_data_files b
  GROUP BY b.tablespace_name ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
ORDER BY dfs.tablespace_name;
Calculation Of Minimum Tablespace Size (this takes a long time to run) SELECT SUBSTR(f.file_name,1,70) FILENAME,
MAX(e.block_id*(e.bytes/e.blocks)+e.bytes)/1024 MIN_SIZE
FROM dba_extents e, dba_data_files f
WHERE e.file_id = f.file_id
GROUP BY f.file_name;
Schemas In The SYSAUX Tablespace col occupant_name format a25
col schema_name format a20
col move_procedure format a30
col move_procedure_desc format a40
set linesize 131

SELECT occupant_name, schema_name, move_procedure, move_procedure_desc
FROM v_$sysaux_occupants;
Contiguous Space CREATE TABLE t_contig_space (
tablespace_name   VARCHAR2(30),
file_id           NUMBER,
block_id          NUMBER,
starting_file_id  NUMBER,
starting_block_id NUMBER,
blocks            NUMBER,
bytes             NUMBER)
tablespace uwdata;

CREATE OR REPLACE VIEW v_contig_space AS
SELECT SUBSTR(tablespace_name,1,20) TABLESPACE_NAME, starting_file_id, starting_block_id,
SUM(blocks) sum_blocks, COUNT(blocks) count_blocks, MAX(blocks) max_blocks,
SUM(bytes)/1024/1024 SUM_MB
FROM tl_contig_space
GROUP BY tablespace_name, starting_file_id, starting_block_id;

DECLARE
 CURSOR query IS
 SELECT *
 FROM dba_free_space
 ORDER BY tablespace_name, file_id, block_id;

 this_row     query%ROWTYPE;
 previous_row query%ROWTYPE;
 old_file_id  PLS_INTEGER;
 old_block_id PLS_INTEGER;
BEGIN
  OPEN query;
  FETCH query INTO this_row;
  previous_row := this_row;
  old_file_id := previous_row.file_id;
  old_block_id := previous_row.block_id;

  WHILE query%FOUND loop
    IF this_row.file_id = previous_row.file_id AND
      this_row.block_id = previous_row.block_id+previous_row.blocks
    THEN
      INSERT INTO tl_contig_space
      (tablespace_name, file_id, block_id, starting_file_id,
       starting_block_id, blocks, bytes)
      VALUES
      (previous_row.tablespace_name, previous_row.file_id,
       this_row.block_id, old_file_id, old_block_id, this_row.blocks,
       this_row.bytes);
    ELSE
      INSERT INTO tl_contig_space
      (tablespace_name, file_id, block_id, starting_file_id,
       starting_block_id, blocks, bytes)
      VALUES
      (this_row.tablespace_name, this_row.file_id,
       this_row.block_id, this_row.file_id, this_row.block_id,
       this_row.blocks, this_row.bytes);

      old_file_id := this_row.file_id;
      old_block_id := this_row.block_id;
    END IF;
    previous_row := this_row;
    FETCH query INTO this_row;
  END LOOP;
  COMMIT;
END;
/

col tablespace_name format a20
col sum_mb format 999.999

SELECT * FROM v_contig_space;

Related Topics
Backup Restore & Recovery
Compressed Tablespaces
Create Database
Data Files
DBMS_TTS
DBMS_UNDO_ADV
Encrypted Tablespaces
Resumable Transactions
Tablespace Groups
Transparent Data Encryption
Transportable Tablespaces
UTLSYXSZ_UTIL

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved