OCM考试大纲针对表空间的要求:
1、创建普通表空间
sql> create tablespace ts1 datafile ‘$ORACLE_BASE/oradata/orcl/datafile/ts1.dbf’ size 100m
autoextend on next 10m maxsize 200m extent management local uniform size 128k
space segment space management auto;
-- extent management local、space segment space management auto是默认的。
2、创建undo表空间
sql> create undo tablespace undo_ts2 datafile 'u01/app/oracle/oradata/orcl/datafile/undo_ts2.dbf' size 100m autoextend on next 10m maxsize 200m;
3、创建临时表空间
sql> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE'u01/app/oracle/oradata/orcl/datafile/temp02.dbf'
SIZE 50M autoextend on next 10m maxsize 200m;
4、创建临时表空间组
alter tablespace temp1 tablespace group temp_grp;
alter tablespace temp2 tablespace group temp_grp;
查看数据库当前默认临时表空间:
select property_name,property_value from database_properties where property_name like '%TEMP%'
-------------------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
修改数据库默认临时表空间:
alter database default temporary tablespace temp2;
只修改某用户的默认临时表空间:
alter user u1 temporary tablespace temp;
5、创建bigfile表空间
create bigfile tablespace bigts datafile '/u01/app/oracle/oradata/orcl/datafile/bigts.dbf' size 50G;
6、修改datafile大小
sql> alter database datafile ''u01/app/oracle/oradata/orcl/datafile/ts1.dbf' resize 500m;
7、重命名datafile
sql> alter tablespace datafile '''u01/app/oracle/oradata/orcl/datafile/ts1.dbf' to '''u01/app/oracle/oradata/orcl/datafile/ts1_new.dbf' ;
sql>