管理表空间和数据文件

1. 管理表空间和数据文件

2. 目标 • 学完这个课程，你应该可以掌握你下内容: • 定义表空间和数据文件的目的 • 创建表空间 • 管理表空间 • 使用Oracle Managed Files (OMF)创建和管理表空间

3. 表空间和数据文件 • Oracle在表空间里存储逻辑信息数据，在数据文件里存储物理信息数据 • 表空间: • 只能同时属于一个数据库 • 可以由一个或多个数据文件组成 • 可以被更进一步的划分为细小的逻辑存储单元 • 数据文件: • 只能同时属于一个表空间和同一个数据库 • 它是用户对象数据的容器 Database Tablespace Datafiles

4. 表空间的类型 • SYSTEM表空间 • Created with the database • Contains the data dictionary • Contains the SYSTEM undo segment • 非SYSTEM表空间 • Separatesegments • Eases space administration • Controls amount of space allocated to a user

5. Creating Tablespaces • A tablespace is created using the command: • CREATE TABLESPACE CREATE TABLESPACE userdata DATAFILE '/u01/oradata/userdata01.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

6. 表空间中的空间管理 • 本地管理表空间: • Free extents managed in the tablespace • Bitmap is used to record free extents • Each bit corresponds to a block or group of blocks • Bit value indicates free or used • 数据字典管理的表空间: • Free extents are managed by the data dictionary • Appropriate tables are updated when extents are allocated or deallocated

7. 本地管理表空间 • Reduced contention on data dictionary tables • No undo generated when space allocation or deallocation occurs • No coalescing required CREATE TABLESPACE userdata DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

8. 数据字典管理表空间 • Extents are managed in the data dictionary • Each segment stored in the tablespace can have a different storage clause • Coalescing required CREATE TABLESPACE userdata DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (initial 1M NEXT 1M PCTINCREASE 0);

9. Undo 表空间 • Used to store undo segments • Cannot contain any other objects • Extents are locally managed • Can only use the DATAFILE and EXTENT MANAGEMENT clauses CREATE UNDO TABLESPACE undo1 DATAFILE '/u01/oradata/undo01.dbf' SIZE 40M;

10. Temporary 表空间 • Used for sort operations • Cannot contain any permanent objects • Locally managed extents recommended CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u01/oradata/temp01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

11. Default Temporary Tablespace • Specifies a database-wide default temporary tablespace • Eliminates using SYSTEM tablespace for storing temporary data • Can be created by using: • CREATE DATABASE • Locally managed • ALTER DATABASE ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

12. Creating a Default Temporary Tablespace • During database creation: CREATE DATABASE DBA01 LOGFILE GROUP 1 ('/$HOME/ORADATA/u01/redo01.log') SIZE 100M, GROUP 2 ('/$HOME/ORADATA/u02/redo02.log') SIZE 100M, MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 MAXINSTANCES 1 DATAFILE '/$HOME/ORADATA/u01/system01.dbf' SIZE 325M UNDO TABLESPACE undotbs DATAFILE '/$HOME/ORADATA/u02/undotbs01.dbf' SIZE 200 DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/$HOME/ORADATA/u03/temp01.dbf' SIZE 4M CHARACTER SET US7ASCII

13. Creating a Default Temporary Tablespace • After database creation: • To find the default temporary tablespace for the database query DATABASE_PROPERTIES ALTER DATABASE DEFAULT TEMPORARY TABLESPACE default_temp2; SELECT * FROM DATABASE_PROPERTIES;

14. 默认临时表空间上的限制 • 默认临时表空间不能进行如下操作: • Dropped until after a new default is made available • Taken offline • Altered to a permanent tablespace

15. 只读表空间 • Use the following command to place atablespace in read only mode • Causesa checkpoint • Data available only for read operations • Objects can be dropped from tablespace ALTER TABLESPACE userdata READ ONLY;

16. Taking a Tablespace Offline • 不能进行有效的数据访问 • 不能够被Offline的表空间: • SYSTEM tablespace • Tablespaces with active undo segments • Default temporary tablespace • To take a tablespace offline: • To bring a tablespace online: ALTER TABLESPACE userdata OFFLINE; ALTER TABLESPACE userdata ONLINE;

17. 改变存储设置 • Using ALTER TABLESPACE command to change storage settings: • 本地管理表空间的存储设置不能被改变 ALTER TABLESPACE userdata MINIMUM EXTENT 2M; ALTER TABLESPACE userdata DEFAULT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 999);

18. Resizing a Tablespace • A tablespace can be resized by: • Changing the size of a datafile: • Automatically using AUTOEXTEND • Manually using ALTER TABLESPACE • Adding a datafile using ALTER TABLESPACE

19. Enabling Automatic Extension of Datafiles • Can be resized automatically with the following commands: • CREATE DATABASE • CREATE TABLESPACE • ALTER TABLESPACE … ADD DATAFILE • Example: • Query the DBA_DATA_FILES view to determine whether AUTOEXTEND is enabled. CREATE TABLESPACE user_data DATAFILE '/u01/oradata/userdata01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

20. Manually Resizing a Datafile • Manually increase or decrease a datafile size using ALTER DATABASE • Resizing a datafile adds more space without adding more datafiles • Manual resizing of a datafile reclaims unused space in database • Example: ALTER DATABASE DATAFILE '/u03/oradata/userdata02.dbf' RESIZE 200M;

21. 增加数据文件到表空间 • Increases the space allocated to a tablespace by adding additional datafiles • ADD DATAFILE clause is used to add a datafile • Example: ALTER TABLESPACE user_data ADD DATAFILE '/u01/oradata/userdata03.dbf' SIZE 200M;

22. 在数据库中移动数据文件的方法 • ALTER TABLESPACE • Tablespace must be offline • Target datafiles must exist • Steps to rename a datafile: • Take the tablespace offline. • Use an OS command to move or copy the files. • Execute the ALTER TABLESPACE RENAME DATAFILE command. • Bring the tablespace online. • Use an OS command to delete the file if necessary. ALTER TABLESPACE userdata RENAME DATAFILE '/u01/oradata/userdata01.dbf' TO '/u02/oradata/userdata01.dbf';

23. 在数据库中移动数据文件的方法 • ALTER DATABASE • Database must be mounted • Target datafile must exist ALTER DATABASE RENAME FILE '/u01/oradata/system01.dbf' TO '/u03/oradata/system01.dbf';

24. Dropping Tablespaces • Cannot drop a tablespace if it: • Is the SYSTEM tablespace • Has active segments • INCLUDING CONTENTS drops the segments • INCLUDING CONTENTS AND DATAFILES deletes datafiles • CASCADE CONSTRAINTS drops all referential integrity constraints DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES;

25. Managing Tablespaces Using OMF • Define the DB_CREATE_FILE_DEST parameter in one of the following ways: • Initialization parameter file • Setdynamically using ALTER SYSTEM command • When creating the tablespace: • Datafile is automatically created and located in DB_CREATE_FILE_DEST • Default size is 100 MB • AUTOEXTEND is set to UNLIMITED ALTER SYSTEM SET db_create_file_dest = '/u01/oradata/dba01';

26. Managing Tablespaces with OMF • Creating an OMF tablespace: • Adding an OMF datafile to an existing tablespace: • Dynamically changing default file location: • Dropping a tablespace includes deleting OS files: CREATE TABLESPACE text_data DATAFILE SIZE 20M; ALTER TABLESPACE text_data ADD DATAFILE; ALTER SYSTEM SET db_create_file_dest = '/u01/oradata/dba01';

27. 获得表空间的信息 • 可以通过查询以下数据字典视图来获得表空间和数据文件的信息: • Tablespaces: • DBA_TABLESPACES • V$TABLESPACE • Datafile information: • DBA_DATA_FILES • V$DATAFILE • Temp file information: • DBA_TEMP_FILES • V$TEMPFILE

28. Summary • In this lesson, you should have learned how to: • Use tablespaces to separate data • Create various types of tablespaces • Manage tablespaces • Manage tablespaces using OMF

29. Practice 8 Overview • This practice covers the following topics: • Creating tablespaces • Modifying tablespaces • Configuring for and creating a tablespace using OMF