Managing Tablesspace and data file

Objectives

Ater completing this lession, you should be able to do the following :

• descibe the logical structure of the database
• create tablespaces
• change the size of tablespaces
• allocate space for temporary segments
• change the status of tablespaces
• change the storage settings of tablespace
• Implement Oracle Managed Files

oracle stores data logically in tablespace and physically in data files .

1.Database Architecture

A small database might need only the SYSTEM tablespace ;Oracle recommends that you create additional tablespaces to storeuser data,user indexes,undo segments,temporary segments separate from data dictionary , This gives you more flexibility in various database administration operations and reduces contention among dictionary objects and schema objects for the same data files.

The physical structure includes the control files, online redo log files, and data files that make up the database .

The logical structure includes tablespaces, segments, extents, and data blocks.

Tablespaces ( logic ) ( 分为 permanent , undo , temporary , 其中 undo 和 temporary 是用来临时存储的 )

• The data in an Oracle database are stored in tablespaces.

• A tablespace can belong to only one database at a time .

• Each tablespace consistes of one or more operating system files, which are called data files.

• A tablespace may consist of zero or more segments.

• Tablespaces con be brought online while the database is running .

• Except for the SYSTEM tablespace or a tablespace with an active undo segment, tablespaces can be taken offline, leaving the database running.

• tablespace 可以是只读的, 也可以是读写的.

Data files ( phsical )

• each tablespace in an Oracle database consists of one or more files called data files .
• A data file can belong to only one tablespace .
• orecle服务器 创建一个 datafile 在磁盘上给指定的 tablespace + a small amount of overhead.
• 可以动态增长. dba 可以修改 datafile 大小。

Segments ( logic )

• A segment is the space allocated for a specific logical storage structure within a tablespace . ( 在一个tablespace内, segment 分配给逻辑的对象，例如table )
• A tablespace may consist of one or more segments.
• A segment cannot span tablespaces ; however , a segment can span multiple data files that belong to the same tablespace . ( 可以跨越datafile ，可以理解，一个table 没准存储了很大数据，一个datafile难以招架 )
• Each segment is made up of one or more extents .

Extents ( logic )

• One or more extents make up a segment .
  • When a segment is created , it consistes of at least one extent .
  • As the segment grows, extents get added to the segment .
  • The DBA can manually add extents to a segment .
• An extent is a set of contiguous Oracle blocks .
• An extent cannot span a data file but must exist in one data file.

Data Blocks ( logic )

• unit ( logic )
• One data block corresponds to one or more opearting system blocks allocated from an existing data file .
• DB_BLOCK_SIZE parameter confirm the data blocks size .
• The maximum data block size is dependent on the operating system .

2. System and Non-system Tablespace

SYSTEM tablespace

• Created with the database
• contains the data dictionary
• contains the system undo segment
• Should not contain user data, although it is allowed .
  

Non-SYSTEM tablespace

• Separate undo , temporary , application data, application index segments.
• separate data by backup requirements.
• separate dynamic and static data .
• Ease space administration.
• Control amount of space allocated to a user

permanent , undo , temporary ( 另外划分方法 )

3. Create tablespace 语法

CREATE TABLESPACE userdata( tablespace name )

                 DATAFILE '/u01/oradata/userdata01.dbf ' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M ; 

4. 管理空间 in Tablespace

Tablespace extents can be managed with data dictionary tables orbitmaps . When you create a tablespace, you choose one of these methods of space management. You cannot alter the method at a later time. ( 创建时, 就要指定管理方法 )

btimap 管理方法 :  bitmap in each datafile to keep track of the free or used status of blocks in that data file . ( 默认管理方法 )

Dictionary管理方法 : the Oracle server updates the appropriate tables in the data dictionary whenever an extent is allocated or deallocated .

create tablespace userdata02 datafile '/u01/oradata/userdata02.dbf' size 50M extent managementdictionary | local autoallocate | uniform size 256k ;

全部用  bitmap  方法管理 ( local management )

bitmap对比dictionary的好处 : ( dictionary 是默认的管理办法 )

• 避免递归管理
• 没有在 data dictionary 中记录, 减少了冲突.
• 自动跟踪附近的空闲区域, eliminating the need to coalesce free extents .
• 自动被系统管理.
• Change to the extent bitmaps do not generate undo information because they do not update tables in the data dictionary .

dictionary 对比 bitmap 管理 tablespace 的好处 :

• Segments in dictionary managed tablespaces can have a customized storage, this is more flexible than locally managed tablespaces but much less efficient.
• alter database tablespace ( 可以改变 tablespace , locally managed tablespaces 是不能改变的  )

使用 bitmap 管理不能使用 alter database 改变大小 ( The storage settings for locally managed tablespaces cannot be altered .

Undo Tablespace

• Used to store undo segments.
• cannot contain any other objects.
• Extents are locally managed
• Can only use the datafile and extent management clauses of the create tablespace command

create undo tablespace undo1 datafile '/u01/oradata/undo101.dbf' size 40M ;

create undo tablespace undo002 datafile '/u01/oradata/undo002.dbf' size 40m extent management local;

保存修改之前的内容，undo

Temporary Tablespace ( SORT_AREA_SIZE )

• Used for sort operations
• Cannot contain any permanent objects.
• locally managed extents recommended

Create temporary tablespace temp tempfile '/u01/oradata/temp01.dbf' size 50M extent management local uniform size 10M ;

Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory, The sort segment of a given temporary tablespace is created at the time of the first sort operation of the instance . The sort segment expands by allocation extents until the segment size is equal to or greater than the storage demands of all of the active sorts running on that instance.

tempfiles 与 datafiles 区别 :

• Tempfiles are always set to NOLOGGING mode.
• you cannot make a tempfile read-only.
• you cannot rename a tempfile.
• you cannot create a tempfile with the alter database command.
• tempfiles are required for read-only database. ( 排序肯定是查询数据时，使用 )
• Media recovery does not recover tempfiles.
• Backup controlfile does not generate any information for tempfiles.
• Create controlfile cannot specify any information about tempfiles.

如果创建数据库时, 不指定 temporary tablespace 就会默认使用 system tablespace , 同时 alert_sid.log stating that the system tablespace is the default temporary tablespace . ( warning )   , 或者  alter database default temporary tablespace temp ;

Default temporary tablespace 限制 :

• It cannot be droped until after a new default is made available .
• It cannot be taken offline.
• you cannot alter the default temporary tablespace to a permanent tablespace .

 )

 

修改默认的 temporary tablespace

alter database default temporary tablespace temp;

 

 Offline Status

 

Some tablespace must be online ( system , tablespace with active undo segments , default temporary )

alter tablespace userdata OFFLINE;

alter tablespace userdata ONLINE;

alter tablespace userdata ( tablespace name ) read only ;

 

Read ONLY tablespace

alter tablespace userdata READ ONLY;

会出发 checkpoint

里边的 object 就会变成只读

Objects can be drop from tablespace ( 虽然是只读的，但是也是可以删除的 , 但是表内的内容不能修改

 

drop tablespace userdata including contents and datafiles ;

• A tablespace that still contains data cannot be dropped without the INCLUDING CONTENTS option , This option may generate a lot of undo when the tablespace contains many objects.
• After a tablespace has been dropped, its data is no longer in the database .
• When a tablespace is dropped , only the file  pointers in the control file of the associated database are dropped, The operating system files still exist and must be deleted explicitly using the appropriate operating system command unless the AND DATAFILES clause is used.
• Even if a tablespace is switched to read-only, it can still be dropped, along with segments within it .
• It is recommended that you take the tablespace offline before dropping it to ensure that no transaction access any of the segments in the tablespace .
• 有的时候删除，还不如就放在那里，状态改成不使用。
• include contents  删除segment, 及连内容也删除， include contents and datafiles 表示不仅仅将逻辑的tablespace , 内容segment , 而且实际的文件datafiles也要一起删除。
• temporary tablespace 同样的删除方法, drop tablespace temphaha including contents and datafiles ;
  

Storage settings  for locally managed tablespaces cannot be alerted . ( 因为都是使用 local management , 这个是针对 tablespace)

Resizing a Tablespace

You can enlarge a tablespace in two ways :

• Change the size of a data file , either automatically or manually. ( manually 可以变小, 变小时要看datafile的存储情况，比如有个500M的datafile,你想变成200M,这时就要看这个 datafile 的真正存储情况了。如果它存储的内容小于200M,OK 没问题，如果 200M 就完了。报错 )
  
• Add a data file to a tablespace .

alter database datafile '/u01/oradata/moveofgod/userdata01.dbf' size 200M autoextendon | off next 10M MAXSIZE 500M;

alter database datafile '/u01/oradata/userdata02.dbf' resize 200M;

alter tablespace userdata add datafile '/u01/oradata/moveofgod/userdata03.dbf' size 200M;

Moving data files

一共有2种办法 :

第一种: The tablespace must be offline . The target data files must exist .

alter tablespace userdata RENAME datafile '/u01/oradata/userdata01.dbf' TO '/u01/oradata/userdata02.dbf' ; 

Following process :

1. Take the tablespace offline.
2. Use an operating system command to move or copy the files. ( 自己要在操作系统层上，真正的将文件COPY过去 )
   
3. Execute the ALTER TABLESPACE RENAME DATAFILE command.
4. Bring the  tablespace online.
5. Use an operating system command to delete the file if necessary.

The source filenames must match the names stored in the control file .

第二种 : The database must be mounted , The target data file must exist .

alter database rename file '/u01/oradata/system01.dbf' To '/u03/oradata/system01.dbf' ;

Following process :

1. Shut down the database.
2. use an operating system command to move the files. ( 一样要自己移动 )
3. Mount the database.
4. Execute the Alter database RENAME FILE command.
5. Open the database.

 Because the SYSTEM tablespace cannot be taken offline, you must use this method to move data files in the SYSTEM tablespace .

5. Configuring Oracle Managed Files for Tablespace Creation

Creating a tablespace with OMF requires the configuration of one initialization parameter.

alter system set db_create_file_dest = '/u01/oradata/db01' ;

create tablespace apps2_data datafile size 20M ; ( 不需要再指定 datafile, 因为 datafile 已经在 db_create_file_dest中指定了)

 

6. Obtaining Tablespace Information

• Tablespace information :
  • DBA_TABLESPACES
  • V$TABLESPACE
• Data file information :
  • DBA_DATA_FILES
  • V$datafile
• Temp file information
  • DBA_TEMP_FILES
  • V$TEMPFILE

 以上: 发现 DBA的都是复数形式，即 s 结尾，而 v$ 动态视图都是单数形式