Oracle表空间和数据文件管理

数据库模型DatabaseTablespace               DatafileSegmentExtentOracle Block             OS block创建表空间SQL> create tablespace user_data  2  datafile 'd:\userdata\userdata1.dbf' size 100m  3  ;表空间已创建。SQL> select tablespace_name,logging,status from dba_tablespaces;TABLESPACE_NAME                LOGGING   STATUS------------------------------ --------- ---------SYSTEM                         LOGGING   ONLINESYSAUX                         LOGGING   ONLINEUNDOTBS1                       LOGGING   ONLINETEMP                           NOLOGGING ONLINEUSERS                          LOGGING   ONLINEEXAMPLE                        NOLOGGING ONLINEINDEX_TBS                      LOGGING   ONLINEUSER_DATA                      LOGGING   ONLINE已选择8行。创建本地表空间加上Extent management localUniform size 1m;查看表空间的类型SQL> select tablespace_name,contents from dba_tablespaces;TABLESPACE_NAME                CONTENTS------------------------------ ---------SYSTEM                         PERMANENTSYSAUX                         PERMANENTUNDOTBS1                       UNDOTEMP                           TEMPORARYUSERS                          PERMANENTEXAMPLE                        PERMANENTINDEX_TBS                      PERMANENTUSER_DATA                      PERMANENT已选择8行。创建临时表空间SQL> show parameter temp;NAME                                 TYPE        VALUE------------------------------------ ----------- -----------------------sec_max_failed_login_attempts        integer     10SQL> create temporary tablespace user_temp  2  tempfile 'd:\userdata\user_temp.dbf' size 20m  3  extent management local  4  uniform size 1m  5  ;表空间已创建。切换表空间SQL> alter database default temporary tablespace user_temp;数据库已更改。创建大文件表空间SQL> create bigfile tablespace bigfiletbs  2  datafile 'd:\userdata\bfile_tbs01.dbf'  3  size 2g;表空间已创建。查看表空间的大小SQL> run;  1* select tablespace_name ,file_name,bytes/(1024*1024*1024)G from dba_data_filesTABLESPACE_NAME                FILE_NAME           G------------------------------ -------------------------------------------------- ----------USERS                          D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  .004882813UNDOTBS1                       D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF    .2734375SYSAUX                         D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF  .714477539SYSTEM                         D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF  .673828125EXAMPLE                        D:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF   .09765625INDEX_TBS                      D:\INDEX_TBS   .09765625USER_DATA                      D:\USERDATA\USERDATA1.DBF   .09765625BIGFILETBS                     D:\USERDATA\BFILE_TBS01.DBF           2已选择8行。是表空间脱机联机SQL> alter tablespace user_data offline;表空间已更改。SQL> alter tablespace user_data online;表空间已更改。设置表空间只读和可写SQL> alter tablespace user_data read  only;表空间已更改。SQL> alter tablespace user_data read write;表空间已更改。修改表空间的大小第一就是设置为自动扩展的模式SQL> create tablespace manager_tbs1  2  datafile 'd:\userdata\tbs1.dbf'  3  size 100m  4  autoextend on ;表空间已创建。增加数据文件SQL> alter tablespace manager_tbs1  2  add datafile 'd:\userdata\tbs2.dbf'  3  size 20m;修改数据文件大小SQL> alter database  2  datafile 'd:\userdata\tbs2.dbf' resize 100m;数据库已更改。删除表空间SQL> drop tablespace manager_tbs1 including contents and datafiles;表空间已删除。