Create and manage temporary, permanent, and undo tablespaces

If you have met any of the following conditions, then Oracle Database creates Oracle Managed Files for you, as appropriate, when no file specification is given in the create operation:

  • You have included any of the DB_CREATE_FILE_DEST, DB_REDOVERY_FILE_DEST, or DB_CREATE_ONLINE_LOG_DEST_n initialization parameters in your initialization parameter file.
  • You have issued the ALTER SYSTEM statement to dynamically set any of DB_RECOVERY_FILE_DEST, DB_CREATE_FILE_DEST, or DB_CREATE_ONLINE_LOG_DEST_n initialization parameters
  • You have issued the ALTER SESSION statement to dynamically set any of the DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST, or DB_CREATE_ONLINE_LOG_DEST_n initialization parameters.

Related documentation is here

General create tablespace syntax:

 

The following example sets the default location for datafile creations to /u01/app/oradata and then creates a tablespace tbs_1 with a datafile in that location. The datafile is 100 MB and is autoextensible with an unlimited maximum size.

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = ‘/u01/app/oradata’;

SQL> CREATE TABLESPACE tbs_1;

 

This example creates a tablespace named tbs_2 with a datafile in the directory /u01/app/oradata. The datafile initial size is 400 MB, and because the SIZE clause is specified, the datafile is not autoextensible.

The following parameter setting is included in the initialization parameter file:

DB_CREATE_FILE_DEST = ‘/u01/app/oradata’

The following statement is issued at the SQL prompt:

SQL> CREATE TABLESPACE tbs_2 DATAFILE SIZE 400M;

 

This example creates a tablespace named tbs_3 with an autoextensible datafile in the directory /u01/app/oradata with a maximum size of 800 MB and an initial size of 100 MB:

The following parameter setting is included in the initialization parameter file:

DB_CREATE_FILE_DEST = ‘/u01/app/oradata’

The following statement is issued at the SQL prompt:

SQL> CREATE TABLESPACE tbs_3 DATAFILE AUTOEXTEND ON MAXSIZE 800M;

 

The following example sets the default location for datafile creations to /u01/app/oradata and then creates a tablespace named tbs_4 in that directory with two datafiles. Both datafiles have an initial size of 200 MB, and because a SIZE value is specified, they are not autoextensible

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = ‘/u01/app/oradata’;

SQL> CREATE TABLESPACE tbs_4 DATAFILE SIZE 200M, SIZE 200M;

 

The following example creates an undo tablespace named undotbs_1 with a datafile in the directory /u01/app/oradata. The datafile for the undo tablespace is 100 MB and is autoextensible with an unlimited maximum size.

The following parameter setting is included in the initialization parameter file:

DB_CREATE_FILE_DEST = ‘/u01/app/oradata’

The following statement is issued at the SQL prompt:

SQL> CREATE UNDO TABLESPACE undotbs_1;

 

This example adds an Oracle managed autoextensible datafile to the tbs_1 tablespace. The datafile has an initial size of 100 MB and a maximum size of 800 MB.

The following parameter setting is included in the initialization parameter file:

DB_CREATE_FILE_DEST = ‘/u01/app/oradata’

The following statement is entered at the SQL prompt:

SQL> ALTER TABLESPACE tbs_1 ADD DATAFILE AUTOEXTEND ON MAXSIZE 800M;

 

The following statements move a datafile

Mounted database:

SQL> ALTER database rename file ‘sourcefilename’ to ‘destinationfilename’;

Opened database:

SQL> alter tablespace MYTBS rename datafile ‘sourcedatafile’ to ‘destdatafile’;
alter tablespace toto rename datafile ‘sourcedatafile’ to ‘destdatafile’;
*
ERROR at line 1:
ORA-01525: error in renaming data files
ORA-01121: cannot rename database file 6 – file is in use or recovery
ORA-01110: data file 6: ‘sourcedatafile’

SQL> alter tablespace MYTBS offline;

Tablespace altered.

SQL> alter tablespace MYTBS rename datafile ‘sourcedatafile’ to ‘destdatafile’;
alter tablespace toto rename datafile ‘sourcedatafile’ to ‘destdatafile’;
*
ERROR at line 1:
ORA-01525: error in renaming data files
ORA-01141: error renaming data file 6 – new file
‘destdatafile’ not found
ORA-01110: data file 6: ‘sourcedatafile’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

[oracle]mv sourcedatafile destdatafile
SQL> alter tablespace MYTBS rename datafile ‘sourcedatafile’ to ‘destdatafile’;

Tablespace altered.

SQL> alter tablespace MYTBS online;

Tablespace altered.

 

Drop tablespace

SQL> drop tablespace MYTBS including contents and datafiles;

 

 

Useful tables:

V$TEMPFILE
DBA_TEMP_FILES
V$DATAFILE
DBA_DATA_FILES

V$SORT_SEGMENT
V$TEMPSEG_USAGE
DBA_TEMP_FREE_SPACE

Useful packages

DBMS_SPACE_ADMIN

 

 

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

2 thoughts on “Create and manage temporary, permanent, and undo tablespaces

  1. BestTabitha

    I have noticed you don’t monetize your website, don’t waste
    your traffic, you can earn extra bucks every month because you’ve got
    high quality content. If you want to know how to make extra bucks, search for: Ercannou’s essential adsense alternative

    Reply
  2. BestKelle

    I see you don’t monetize your site, don’t waste your traffic,
    you can earn extra cash every month. You can use the best adsense alternative for any type of website (they approve all websites), for more details
    simply search in gooogle: boorfe’s tips monetize your website

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *