oracle 第14章表空间管理

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;