Oracle Full Learning Handbook table spaces & Management control files and log files & Table learning Notes

Table Space

Table space is the largest logical structure in Oracle data structures

• Basic table Space
• Temporary tablespace: Temporary data generated during sorting and summarization
• Large file tablespace: storing large data
• Non-standard chunk table space
• Undo Table Space: Store undo Data

Basic table Space

Create a tablespace named TEMP0301

Create tablespace temp0301datafile ' F:\SQL\use0301.dbf ' size 20mautoextend on next 10m MaxSize Unlimited; 

Create tablespace temp0302datafile ' F:\SQL\use0302.dbf ' size 10mautoextend on next 10m maxsize 100mextent man Agement Local uniform size 800k;  

Create a manual way to manage a segment's tablespace

Create smallfile tablespace temp0303datafile ' F:\SQL\use0303.dbf ' size 10mextent management local uniform size 25 6ksegment space management manual; 

modifying table spaces

Add a new data file to the Tablespace

Modify data File Size

If you need to increase the tablespace, the other way is to increase the size of the existing data file in the tablespace.

By querying the data dictionary view Dba_free_space, you can understand the idle partitioning of the tablespace.

Select tablespace_name,file_id, Block_id,bytes,blocksfrom dba_free_space;

ALTER DATABASE datafile ' F:\SQL\use0101.dbf 'resize 100m;

To modify the auto-extended properties of a data file

Autoextend

Table Space State Properties

By setting the Tablespace state, you can limit the availability of data

• Offline offline: Unable to access data in table space, this time can be upgraded and maintained
• Online: The corresponding data in the tablespace can be accessed
• Read only (online status)
• Read-write read write (online status)

Alter

Alter tablespace temp0301 offline;alter tablespace temp0301 online;alter tablespace temp0301 Read write; 

Moving data files

When you increase the tablespace, you can move the data file and save the file to another disk if there is not enough space on the disk where the data file resides.

To move a data file, proceed as follows:

1. Modify table space to offline state
2. Copying a data file to another disk
3. To modify the name of a data file by using the ALTER TABLESPACE rename statement
4. Modify the Tablespace state to online

EXAMPLE: Save the F-drive use0101 to the D-drive

Copy the F-drive use0101.dbf to the D drive

Alter'F:\SQL\use0101.dbf' to'D:\OracleTest\ USE0101.DBF';

alter tablespace temp0301 online;

Delete Table space

DROP tablespace

Temporary table space

Temporary table spaces are primarily used to provide temporary workspaces for operations such as sorting or summarizing.

Create a temporal tablespace TEMP0304

Create Temporary  'F:\SQL\temp0304.dbf'onnext  10m maxsize 100mextent management Local;

Attention:

• Temporary table spaces can only be used to store temporary data and cannot store persistent data
• Because the files in the temporary tablespace are temporary files, the data dictionary dba_data_files no longer records information about the temporary files. Temporary files are stored in the data dictionary view v$tempfile.

Temporary tablespace management is uniform, so you cannot use the Autoallocate keyword to define how the extents are managed when you create a temporary table space.

Modifying a temporary table space

Modifying a temporary tablespace mainly has the following actions:

• Add temporary files
• To modify the size of a temporary file
• Modify the status of a temporary file

Add temporary files

Alter tablespace temp0304 Add ' F:\SQL\tempfile0306.dbf ' size 10m;

Modify temporary file size

Alter Database Tempfile ' F:\SQL\tempfile0306.dbf ' resize 20m;

You can also modify the status to offline or online

Temporary table space groups

In Oracle 11g, users can create temporary tablespace groups that can contain one or more temporary table spaces in a temporary table space group.

To create a temporary tablespace group, you need to use the Group keyword

Create Temporary  'f:\SQL\tempgroup0307.dbf'group group0301;

After you create a temporary table space group, you can do the following:

Get information about a temporary table space

Select *  from Dba_tablespace_groups;

Add a temporary tablespace to a temporary table space group

Create Temporary  'F:\SQL\tempgroup0302.dbf'group group0301;

Oracle Full Learning Handbook table spaces & Management control files and log files & Table learning Notes