Oracle Tablespaces Version 21c |
---|
General Information | ||||||||||||||||||||||||||||||||||||||||||||||
Library Note |
|
|||||||||||||||||||||||||||||||||||||||||||||
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) |
|
|||||||||||||||||||||||||||||||||||||||||||||
System Privileges |
|
|||||||||||||||||||||||||||||||||||||||||||||
Permanent Tablespace | ||||||||||||||||||||||||||||||||||||||||||||||
Permanent Tablespace On A File System Without Auto-extend | CREATE [<BIGFILE | SMALLFILE>] TABLESPACE <tablespace_name> |
|||||||||||||||||||||||||||||||||||||||||||||
CREATE BIGFILE TABLESPACE uwdata |
||||||||||||||||||||||||||||||||||||||||||||||
Permanent Tablespace On A Raw Device | CREATE TABLESPACE <tablespace_name> |
|||||||||||||||||||||||||||||||||||||||||||||
CREATE TABLESPACE tools LOGGING |
||||||||||||||||||||||||||||||||||||||||||||||
Oracle Managed Permanent Auto-extendable Tablespace | CREATE TABLESPACE <tablespace_name>; |
|||||||||||||||||||||||||||||||||||||||||||||
ALTER SYSTEM SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata'; |
||||||||||||||||||||||||||||||||||||||||||||||
Oracle Managed Permanent Fixed Size Tablespace | CREATE TABLESPACE <tablespace_name> |
|||||||||||||||||||||||||||||||||||||||||||||
ALTER SYSTEM SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata'; |
||||||||||||||||||||||||||||||||||||||||||||||
Set default tablespace type | ALTER DATABASE SET DEFAULT <tablespace_type> TABLESPACE; |
|||||||||||||||||||||||||||||||||||||||||||||
ALTER DATABASE DEFAULT BIGFILE TABLESPACE; |
||||||||||||||||||||||||||||||||||||||||||||||
Set tablespace as the default | ALTER DATABASE DEFAULT TABLESPACE <tablespace_name>; |
|||||||||||||||||||||||||||||||||||||||||||||
ALTER DATABASE DEFAULT TABLESPACE uwdata; |
||||||||||||||||||||||||||||||||||||||||||||||
SYSAUX Tablespace | ||||||||||||||||||||||||||||||||||||||||||||||
Create SYSAUX Tablespace | SELECT tablespace_name |
|||||||||||||||||||||||||||||||||||||||||||||
Move SYSAUX Contents | -- move must be done using the indicated procedurecol occupant_name format a30 |
|||||||||||||||||||||||||||||||||||||||||||||
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> |
|||||||||||||||||||||||||||||||||||||||||||||
CREATE UNDO TABLESPACE undotbs02 |
||||||||||||||||||||||||||||||||||||||||||||||
Change The Current UNDO Tablespace | -- identify existing tablespaces |
|||||||||||||||||||||||||||||||||||||||||||||
Temporary Tablespaces | ||||||||||||||||||||||||||||||||||||||||||||||
Create Temporary Tablespace | CREATE TEMPORARY TABLESPACE <tablespace_name> 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 |
||||||||||||||||||||||||||||||||||||||||||||||
Add Tempfile | ALTER TABLESPACE <tablespace_name> |
|||||||||||||||||||||||||||||||||||||||||||||
ALTER TABLESPACE temp_new |
||||||||||||||||||||||||||||||||||||||||||||||
Resize Tempfile | ALTER DATABASE TEMPFILE '<file_name>' |
|||||||||||||||||||||||||||||||||||||||||||||
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 |
||||||||||||||||||||||||||||||||||||||||||||||
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 |
|||||||||||||||||||||||||||||||||||||||||||||
EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2) |
||||||||||||||||||||||||||||||||||||||||||||||
Import Tablespace | IMP TRANSPORT_TABLESPACE=<Y | N> FILE=<file_name> |
|||||||||||||||||||||||||||||||||||||||||||||
IMP TRANSPORT_TABLESPACE=y FILE=expdat.dmp |
||||||||||||||||||||||||||||||||||||||||||||||
Import Parameter File | TRANSPORT_TABLESPACE=y FILE=expdat.dmp |
|||||||||||||||||||||||||||||||||||||||||||||
Alter Permanent Tablespace | ||||||||||||||||||||||||||||||||||||||||||||||
Add Datafile with Autoextend Enabled | ALTER TABLESPACE <tablespace_name> ADD DATAFILE <file_name>, |
|||||||||||||||||||||||||||||||||||||||||||||
ALTER TABLESPACE tools ADD DATAFILE '/u02/oracle/oradata/uwdata02.dbf' SIZE 25G |
||||||||||||||||||||||||||||||||||||||||||||||
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; -- 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; |
||||||||||||||||||||||||||||||||||||||||||||||
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 |
||||||||||||||||||||||||||||||||||||||||||||||
Alter Undo Tablespace | ||||||||||||||||||||||||||||||||||||||||||||||
Retention Guarantee | ALTER TABLESPACE <tablespace_name> RETENTION <GUARANTEE | NOGUARANTEE>; |
|||||||||||||||||||||||||||||||||||||||||||||
SELECT tablespace_name, retention |
||||||||||||||||||||||||||||||||||||||||||||||
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 |
|||||||||||||||||||||||||||||||||||||||||||||
DROP TABLESPACE tools
INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; |
||||||||||||||||||||||||||||||||||||||||||||||
Drop tablespace after datafile was accidentally dropped | conn sys@pdbdev as sysdba |
|||||||||||||||||||||||||||||||||||||||||||||
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 |
|||||||||||||||||||||||||||||||||||||||||||||
List datafiles, tablespace names, and size in GB | col file_name format a50 |
|||||||||||||||||||||||||||||||||||||||||||||
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, |
|||||||||||||||||||||||||||||||||||||||||||||
View For Schema Owner To Monitoring Free Space | CREATE OR REPLACE VIEW freespace_view AS |
|||||||||||||||||||||||||||||||||||||||||||||
Another Statement For Tablespace Management | set linesize 121 |
|||||||||||||||||||||||||||||||||||||||||||||
Yet Another Statement For Tablespace Management | col tablespace_name format a15 |
|||||||||||||||||||||||||||||||||||||||||||||
And Yet Another Statement For Tablespace Management | SELECT dfs.tablespace_name, ddf.total_size,
ddf.total_size - dfs.total_free TOTAL_USED, |
|||||||||||||||||||||||||||||||||||||||||||||
Calculation Of Minimum Tablespace Size (this takes a long time to run) | SELECT SUBSTR(f.file_name,1,70) FILENAME, |
|||||||||||||||||||||||||||||||||||||||||||||
Schemas In The SYSAUX Tablespace | col occupant_name format a25 |
|||||||||||||||||||||||||||||||||||||||||||||
Contiguous Space | CREATE TABLE t_contig_space ( |
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-2023 Daniel A. Morgan All Rights Reserved | |||||||||
|
||||||||||