2015-10-19
目录
#查看表空间 SQL> desc dba_tablespaces; SQL> select tablespace_name,status,contents,logging from dba_tablespaces; #创建表空间 SQL> create tablespace user_data datafile '/u01/app/oracle/oradata/orcl/userdata1.dbf' size 100M; SQL> select tablespace_name,logging,status from dba_tablespaces; #查看数据文件 SQL> desc dba_data_files; SQL> col file_name for a45; SQL> col tablespace_name for a10; SQL> select file_name,tablespace_name,status from dba_data_files where tablespace_name='USER_DATA'; 或 SQL> desc v$datafile; SQL> col name for a45; SQL> select name,status from v$datafile; #创建数据字典管理的表空间 #SYSTEM表空间的Extent必须是DICTIONARY,oracle 11g r2已经不支持 SQL> create tablespace beijing_data datafile '/u01/app/oracle/oradata/orcl/beijing01.dbf' size 100M,'/u01/app/oracle/oradata/orcl/beijing02.dbf' size 100M,'/u01/app/oracle/oradata/orcl/beijing03.dfb' size 100M minimum extent 20k extent management dictionary default storage(initial 20k next 20k maxextents 500 pctincrease 0); #创建本地管理的表空间 SQL> create tablespace shanghai_data datafile '/u01/app/oracle/oradata/orcl/shanghai01.dbf' size 100M extent management local uniform size 1M; SQL> create tablespace JS_data datafile '/u01/app/oracle/oradata/orcl/JS01.dbf' size 100M,'/u01/app/oracle/oradata/orcl/JS02.dbf' size 100M extent management local uniform size 1M; #创建还原表空间 SQL> create undo tablespace user_undo datafile '/u01/app/oracle/oradata/orcl/user_undo.dbf' size 30M; SQL> select tablespace_name,status,contents,logging,extent_management from dba_tablespaces; #创建临时表空间 SQL> create temporary tablespace user_temp tempfile '/u01/app/oracle/oradata/orcl/user_temp.dbf' size 20M extent management local uniform size 1M; #查看临时文件 SQL> desc v$tempfile; SQL> col name for a45; SQL> select status,enabled,name from v$tempfile; #查看默认临时表空间 SQL> col property_name for a30; SQL> col property_value for a20; SQL> col description for a40; SQL> select * from database_properties where property_name like 'DEFAULT%'; #切换临时表空间 SQL> alter database default temporary tablespace user_temp; #删除当前默认临时表空间 SQL> alter database default temporary tablespace temp; SQL>drop tablespace user_temp; #创建大文件表空间 SQL> create bigfile tablespace bfile datafile '/u01/app/oracle/oradata/orcl/bfile.dbf' size 2G; #查看数据文件 SQL> col tablespace_name for a20; SQL> col file_name for a45; SQL> select tablespace_name,file_name from dba_data_files; #查看表空间 SQL> select tablespace_name,contents,extent_management,segment_space_management from dba_tablespaces; #修改默认表空间类型为大表 SQL> alter database set default bigfile tablespace; #修改大文件表空间大小 SQL> alter tablespace bfile resize 4G; #修改大文件表空间自动扩展 SQL> alter tablespace bfile autoextend on next 1G; #查看默认表空间类型 SQL> desc database_properties; SQL> col property_name for a20; SQL> col property_value for a20; SQL> col description for a30; SQL> select * from database_properties where property_name like 'DEFAULT_TBS_TYPE'; PROPERTY_NAME PROPERTY_V DESCRIPTION -------------------- ---------- ---------------------------------------- DEFAULT_TBS_TYPE BIGFILE Default tablespace type #修改表空间状态 1.将表空间bfile设为脱机状态 SQL> alter tablespace bfile offline; 2.将表空间bfile设为联机状态 SQL> alter tablespace bfile online; 3.将表空间bfile设为只读状态 SQL> alter tablespace bfile read only; 4.将只读表空间bfile恢复正常状态 SQL> alter tablespace bfile read write; #验证 SQL> select tablespace_name,status,contents,logging from dba_tablespaces; 或 SQL> col name for a50; SQL> select file#,name,status from v$datafile; #修改表空间大小 1.创建表空间时自动扩展 SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/orcl/tbs1.dbf' size 100M autoextend on; 2.修改表空间自动扩展 SQL> alter database datafile '/u01/app/oracle/oradata/orcl/tbs1.dbf' autoextend on next 1M; 3.向表空间增加数据文件 SQL>alter tablespace tbs1 add datafile '/u01/app/oracle/oradata/orcl/tbs2.dbf' size 50M; 4.修改表空间数据文件 SQL> alter database datafile '/u01/app/oracle/oradata/orcl/tbs1.dbf' resize 100M; #验证 SQL> col tablespace_name for a15; SQL> col file_name for a45; SQL> select tablespace_name,file_name,autoextensible from dba_data_files; #删除表空间 SQL> drop tablespace tbs1 including contents and datafiles; #迁移系统表空间 SQL> select tablespace_name,file_name from dba_data_files; SQL> conn system/oracle as sysdba; SQL> shutdown immediate; SQL> host cp /u01/app/oracle/oradata/orcl/system01.dbf /u02/app/oracle/oradata/orcl/system02.dbf; SQL> startup mount; SQL> alter database rename file '/u01/app/oracle/oradata/orcl/system01.dbf' '/u02/app/oracle/oradata/orcl/system02.dbf' ; SQL> recover database; SQL> alter database open; SQL> select tablespace_name,file_name from dba_data_files; #迁移非系统表空间 SQL> alter tablespace tbs1 offline; SQL> host cp /u01/app/oracle/oradata/orcl/tbs01.dbf /u02/app/oracle/oradata/orcl/tbs02.dbf; SQL> alter database rename file '/u01/app/oracle/oradata/orcl/tbs01.dbf' '/u02/app/oracle/oradata/orcl/tbs02.dbf' ; SQL> alter tablespace tbs1 online; SQL> select tablespace_name,file_name from dba_data_files;
创建表空间
/*分为四步 */ /*第1步:创建临时表空间 */ create temporary tablespace user_temp tempfile '/u01/app/oracle/oradata/orcl/user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; /*第2步:创建数据表空间 */ create tablespace user_data logging datafile '/u01/app/oracle/oradata/orcl/user_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; /*第3步:创建用户并指定表空间 */ create user username identified by password default tablespace user_data temporary tablespace user_temp; /*第4步:给用户授予权限 */ grant connect,resource,dba to username;
参考资料
[1] 林树泽.Oracle 11g R2 DBA操作指南[M].北京:清华大学出版社,2013
[2] oracle创建表空间
[3] oracle 创建表空间详细介绍
[5] ORACLE表空间管理维护
[8] Oracle建立表空间和用户
[10] oracle表空间表分区详解及oracle表分区查询使用方法
[11] ORACLE DBA学习笔记--表空间的管理(tablespace)
[12] oracle区管理和段空间管理详细介绍
[13] oracle 表空间管理
[14] ORACLE表空间管理
[15] Oracle基本表空间管理命令
[16] Oracle管理表空间和数据文件详解