ORACLE-Manage table spaces and data files

An Oracle tablespace is a logical partition where a data file can belong to only one table space, and a tablespace may have multiple data files.

In general, if an instance is assigned to more than one app, you need to create a different table space for each user to use their own tablespace.

I. Creation and authorization of table spaces

First look at the usage of the tablespace:

Select tablespace_name,sum(bytes)/1024x768/1024x768 as MB from Dba_data_files Group by Tablespace_name; --- remaining capacity (in case of fixed size)Select tablespace_name, COUNT (*) as extends, round (sum1024x768 1024x768 2 sum (blocks) as blocks from Dba_free_space Group by Tablespace_name;

--View table space and data file correspondence
Select Tablespace_name, file_name, bytes/1024/1024 from Dba_data_files;

--Querying table space to store objects and their owners
Select distinct Segment_type, owner, tablespace_name from dba_segments where tablespace_name= ' SC ';

1. Create a table space

you_tabs_name datafile  '/opt/oracle/oradata/orcl/ts_01.dbf' size 50m Autoextend on;

2. Create a temporary tablespace (typically used for data query sorting operations when there is a large amount of data in memory)

Tempfile ' /opt/oracle/oradata/temp02.dbf ' size 100m autoextend on; -- can add data file for newly created tablespace SQLtempfile'/opt/oracle/oradata/temp03.dbf' size 100m;

3. Create a user-specified default tablespace and temporary table space

Create useruname identified by "yourpwd" default tablespace ts_01 temporary tablespace temp2 profile Defaul T Quota unlimited on ts_01

4. You can query the default tablespace of a user after the creation is complete.

Select default_tablespace, Temporary_tablespace, d.username from  dba_users D where d.username=' SCOTT ';

5. If the user is already created, you can authorize the new tablespace to be used by the user

uname Quota Unlimited on  ts_01;
Alter user Scott default Tablespace ts_01; --Change the user's default tablespace

6. When a table space is no longer in use, you can offline the data file under the Tablespace (do not delete the data file at the operating system layer before you are offline)

' /opt/oracle/oradata/orcl/ts_01.dbf ' offline;

7. Set Tablespace as read-only for backup and recovery

table space is read-only and cannot be insert,update,delete, but indexes and directories can be deleted. The following conditions must be met: (1)    must be online (2)    cannot contain any rollback segments (3)    Tablespace cannot be in archive mode or data release SQL>alter tablespace tablespace_name read only; resume read-write status SQLwrite;

8. Renaming table Spaces

after the notconsistent name, the objects stored in the original table space are also notconsistent, including the user's permission to the table space to change the condition as well: ( 1 )    can only rename the general tablespace, cannot notconsistent system,sysaux rename (2)    must be in online status SQL>alter Tablespace old_name Rename to new_name;

Second, temporary table space operations

Temporal table spaces are typically used for sorting operations in query results with large data volumes (when memory usage is relatively small):

1. View the currently existing temporary tablespace

sql> Select tablespace_name,file_name,bytes/1024x768/1024x768 file_size,autoextensible from Dba_temp_files;
Sql> select name,bytes/1024/1024 from V$tempfile;

2. Create a temporary tablespace and change a user temp table space

sql> Create temporary tablespace temp2 tempfile '/opt/oracle/oradata/temp02.dbf ' size 100m autoextend on;--  You can add a data file to the newly created tablespace sql> alter tablespace temp2 add tempfile '/opt/oracle/oradata/temp03.dbf ' size 100m;

--Change user temp table space

sql> alter user Scott temporary tablespace temp1;

3. Modifiable temporary tablespace used by default for database management

sql> ALTER DATABASE default temporary tablespace Temp1;

4. If a temporary table space is not in use by the user, you can delete

sql> Drop Tablespace temp;
You can then delete the data file by using the operating system command

5. Change table space size or set auto-scale

Tempfile ' /opt/oracle/oradata/orcl/sc_temp_01.dbf '  tempfile ' opt/oracle/oradata/oracle/sc_temp_01.dbf ' autoextend on next 3M MaxSize Unlimited; 

6. Delete a data file

Tempfile ' /opt/oracle/oradata/orcl/sc_temp_01.dbf ' offline; // take the file offline first Tempfile '/opt/oracle/oradata/orcl/sc_temp_01.dbf ' drop;

-- completely delete a temporary tablespace and use it sparingly
Drop tablespace Temp1 including contents and datafiles

Third, undo table space

Undo Table Space Functions: fallback transaction, read consistency, transaction recovery (power outage, memory failure, background process failure, etc.), flashback operation,UNDO does not belong to any user , public by the library, the default is automatically grow until the disk is exhausted.

-- shows the undo table space being used show parameter undo_tablespace; -- query all UNDO table spaces Select tablespace_name from dba_tablespaces where contents='undo ';

1. Create an undo tablespace, set here fixed size 2G

' /xx/xxx/ ' size 2G;

2. Undo table Space Offline and deleted

Sql>alter system set undo_tablespace=UNDOTBS02; SQL>alter tablespace tablespace_name offline; SQL>drop tablespace undotbs02;

When you are offline, you must have an alternate undo table space . If a transaction is in use, it cannot be taken offline and the query SQL is as follows:

'undotbs02' and segment_id =


" ROLLBACK = t.addr and t.xidusn = R.usn; 

ORACLE-Manage table spaces and data files