Oracle | Tablespace

Oracle Tablespaces Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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$
Dictionary Management	uet$

System Privileges

Alter Tablespace	Drop Tablespace	Unlimited Tablespace
Create Tablespace	Manage Tablespace

Permanent Tablespace

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 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 with Autoextend Enabled 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 10G 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;