oracle创建临时任务,【存储管理】创建临时表空间组、创建临时表空间组及使用...

任务描述：创建默认临时表空间组TMP_GRP，组成员temp1,temp2，数据文件大小10M

实验开始：

--查看当前数据库默认临时表空间是什么？

SQL> descdatabase_properties

NameNull?Type

-----------------------------------------

-------- ----------------------------

PROPERTY_NAMENOT NULL

VARCHAR2(30)

PROPERTY_VALUEVARCHAR2(4000)

DESCRIPTIONVARCHAR2(4000)

SQL> selectproperty_valuefromdatabase_propertieswhereproperty_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE

--------------------------------------------------------------------------------

TEMP

--查看当前数据库有哪些类型的表空间

SQL>desc dba_tablespaces

NameNull?Type

-----------------------------------------

-------- ----------------------------

TABLESPACE_NAMENOT NULL

VARCHAR2(30)

BLOCK_SIZENOT NULL NUMBER

INITIAL_EXTENTNUMBER

NEXT_EXTENTNUMBER

MIN_EXTENTSNOT NULL NUMBER

MAX_EXTENTSNUMBER

MAX_SIZENUMBER

PCT_INCREASENUMBER

MIN_EXTLENNUMBER

STATUSVARCHAR2(9)

CONTENTSVARCHAR2(9)

LOGGINGVARCHAR2(9)

FORCE_LOGGINGVARCHAR2(3)

EXTENT_MANAGEMENTVARCHAR2(10)

ALLOCATION_TYPEVARCHAR2(9)

PLUGGED_INVARCHAR2(3)

SEGMENT_SPACE_MANAGEMENTVARCHAR2(6)

DEF_TAB_COMPRESSIONVARCHAR2(8)

RETENTIONVARCHAR2(11)

BIGFILEVARCHAR2(3)

PREDICATE_EVALUATIONVARCHAR2(7)

ENCRYPTEDVARCHAR2(3)

COMPRESS_FORVARCHAR2(12)

SQL> selecttablespace_name,contentsfromdba_tablespaces;

TABLESPACE_NAMECONTENTS

------------------------------ ---------

SYSTEMPERMANENT

SYSAUXPERMANENT

UNDOTBS1UNDO

TEMPTEMPORARY

USERSPERMANENT

EXAMPLEPERMANENT

6 rows selected.

——创建临时表空间组TMP_GRP，查看临时表空间 (dba_temp_files视图)或(v$tempfile视图)

SQL> desc v$tempfile

NameNull?Type

-----------------------------------------

--------

FILE#NUMBER

CREATION_CHANGE#NUMBER

CREATION_TIMEDATE

TS#NUMBER

RFILE#NUMBER

STATUSVARCHAR2(7)

ENABLEDVARCHAR2(10)

BYTESNUMBER

BLOCKSNUMBER

CREATE_BYTESNUMBER

BLOCK_SIZENUMBER

NAME

VARCHAR2(513)

——查看默认临时表空间：

SQL>select FILE#,STATUS,BYTES/1024/1024

m,NAME from v$tempfile;

FILE#

STATUSMNAME

---------------------------------------------

1ONLINE29/u01/app/oracle/oradata/ORA11GR2/temp01.dbf

——创建临时表空间组temp_grp,包含临时表空间temp1、temp2：

SQL> create temporary tablespace temp1 tempfile'/u01/app/oracle/oradata/ORA11GR2/temp_01.dbf'

size 10m tablespace group tem_grp;

Tablespace created.

SQL> createtemporary tablespace temp2 tempfile '/u01/app/oracle/oradata/ORA11GR2/temp_02.dbf' size 10mtablespace group tem_grp;

Tablespace created.

--查看表空间组情况

SQL>select tablespace_name,contents fromdba_tablespaces;

TABLESPACE_NAMECONTENTS

------------------------------ ---------

SYSTEMPERMANENT

SYSAUXPERMANENT

UNDOTBS1UNDO

TEMPTEMPORARY

USERSPERMANENT

EXAMPLEPERMANENT

TEMP1TEMPORARY

TEMP2TEMPORARY

8 rows selected.

SQL>select * from dba_tablespace_groups;

GROUP_NAMETABLESPACE_NAME

------------------------------

------------------------------

TEM_GRPTEMP1

TEM_GRPTEMP2

--修改临时表空间组TMP_GRP为数据库默认临时表空间

SQL>alter databasedefault

temporary tablespace tem_grp;

Database altered.

——验证查看：

SQL>select property_valuefrom database_properties whereproperty_name =

'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE

--------------------------------------------------------------------------------

TEM_GRP