更改数据库的默认临时表空间 及用户的默认临时表空间
Jul212015
之前发现数据库临时表空间设置不对，其他同事怕在线修改会有问题，我确定这个不会有啥问题，就在UAT测试库上操作一把，当时库很忙，数据库版本11.2.0.4。用户的默认临时表空间如下：
SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users order by created; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ SYS SYSTEM IVALUE_TEMP SYSTEM SYSTEM IVALUE_TEMP OUTLN SYSTEM IVALUE_TEMP DIP USERS IVALUE_TEMP ORACLE_OCM USERS IVALUE_TEMP DBSNMP SYSAUX IVALUE_TEMP APPQOSSYS SYSAUX IVALUE_TEMP WMSYS SYSAUX IVALUE_TEMP EXFSYS SYSAUX IVALUE_TEMP CTXSYS SYSAUX IVALUE_TEMP ANONYMOUS SYSAUX IVALUE_TEMP XDB SYSAUX IVALUE_TEMP XS$NULL USERS IVALUE_TEMP SI_INFORMTN_SCHEMA SYSAUX IVALUE_TEMP ORDSYS SYSAUX IVALUE_TEMP ORDDATA SYSAUX IVALUE_TEMP ORDPLUGINS SYSAUX IVALUE_TEMP MDSYS SYSAUX IVALUE_TEMP OLAPSYS SYSAUX IVALUE_TEMP MDDATA USERS IVALUE_TEMP SPATIAL_WFS_ADMIN_USR USERS IVALUE_TEMP SPATIAL_CSW_ADMIN_USR USERS IVALUE_TEMP FLOWS_FILES SYSAUX IVALUE_TEMP APEX_PUBLIC_USER USERS IVALUE_TEMP APEX_030200 SYSAUX IVALUE_TEMP OWBSYS SYSAUX IVALUE_TEMP OWBSYS_AUDIT SYSAUX IVALUE_TEMP MIGBI TS_MIG TEMP CHGSHSIF TS_FUL_IF TEMP CHGSHS TS_BSC TEMP PRODTASK TS_TASK TEMP VOUCHER TS_BSC TEMP IVLMC TS_MC TEMP MIGJOB TS_MIG TEMP IVL_READ USERS TEMP BK_IVL TS_BSC TEMP BI_PLAN TS_BSC TEMP MONITOR USERS IVALUE_TEMP MONITOR1 USERS IVALUE_TEMP IVL_DEBUG TS_FUL TEMP ZABBIX TS_TASK IVALUE_TEMP MIGBK MIGBK TEMP MIGSP MIGSP TEMP IVL2IF TS_BSC IVALUE_TEMP DBMON TS_BSC IVALUE_TEMP 45 rows selected.
先把数据库的默认临时表空间从临时表空间组IVALUE_TEMP调整为TEMP。
SQL> alter database default temporary tablespace temp; Database altered.
此时会发现，所有使用临时表空间组的用户的临时表空间都变成了TEMP，这是因为这些用户在创建时没有指定默认的临时表空间，所有在将数据库的默认临时表空间设置成IVALUE_TEMP时，这些用户就使用IVALUE_TEMP临时表空间组，设置数据库的默认临时表空间为TEMP，这些用户的默认临时表空间就变成TEMP，而那些在创建时指定默认临时表空间的用户，不会因为数据库的默认临时表空间发生变更而改变。
SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users order by created; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ SYS SYSTEM TEMP SYSTEM SYSTEM TEMP OUTLN SYSTEM TEMP DIP USERS TEMP ORACLE_OCM USERS TEMP DBSNMP SYSAUX TEMP APPQOSSYS SYSAUX TEMP WMSYS SYSAUX TEMP EXFSYS SYSAUX TEMP CTXSYS SYSAUX TEMP ANONYMOUS SYSAUX TEMP XDB SYSAUX TEMP XS$NULL USERS TEMP SI_INFORMTN_SCHEMA SYSAUX TEMP ORDSYS SYSAUX TEMP ORDDATA SYSAUX TEMP ORDPLUGINS SYSAUX TEMP MDSYS SYSAUX TEMP OLAPSYS SYSAUX TEMP MDDATA USERS TEMP SPATIAL_WFS_ADMIN_USR USERS TEMP SPATIAL_CSW_ADMIN_USR USERS TEMP FLOWS_FILES SYSAUX TEMP APEX_PUBLIC_USER USERS TEMP APEX_030200 SYSAUX TEMP OWBSYS_AUDIT SYSAUX TEMP OWBSYS SYSAUX TEMP MIGBI TS_MIG TEMP CHGSHS TS_BSC TEMP CHGSHSIF TS_FUL_IF TEMP PRODTASK TS_TASK TEMP VOUCHER TS_BSC TEMP IVLMC TS_MC TEMP MIGJOB TS_MIG TEMP IVL_READ USERS TEMP BK_IVL TS_BSC TEMP BI_PLAN TS_BSC TEMP MONITOR USERS TEMP MONITOR1 USERS TEMP IVL_DEBUG TS_FUL TEMP ZABBIX TS_TASK TEMP MIGBK MIGBK TEMP MIGSP MIGSP TEMP IVL2IF TS_BSC TEMP DBMON TS_BSC TEMP 45 rows selected.
然后把业务用户的默认临时表空间调整为IVALUE_TEMP临时表空间组。
SQL> alter user CHGSHS TEMPORARY TABLESPACE IVALUE_TEMP; User altered. SQL> alter useR CHGSHSIF TEMPORARY TABLESPACE IVALUE_TEMP; User altered. SQL> alter useR MIGBI TEMPORARY TABLESPACE IVALUE_TEMP; User altered. SQL> alter useR VOUCHER TEMPORARY TABLESPACE IVALUE_TEMP; User altered. SQL> alter useR PRODTASK TEMPORARY TABLESPACE IVALUE_TEMP; User altered. SQL> alter useR IVLMC TEMPORARY TABLESPACE IVALUE_TEMP; User altered. SQL> ALTER USER MIGJOB TEMPORARY TABLESPACE IVALUE_TEMP; User altered. SQL> C/MIGJOB/IVL_READ 1* ALTER USER IVL_READ TEMPORARY TABLESPACE IVALUE_TEMP SQL> / User altered. SQL> C/IVL_READ/BK_IVL 1* ALTER USER BK_IVL TEMPORARY TABLESPACE IVALUE_TEMP SQL> / User altered. SQL> C/BK_IVL/BI_PLAN 1* ALTER USER BI_PLAN TEMPORARY TABLESPACE IVALUE_TEMP SQL> / User altered. SQL> C/BI_PLAN/MONITOR 1* ALTER USER MONITOR TEMPORARY TABLESPACE IVALUE_TEMP SQL> / User altered. SQL> C/MONITOR/MONITOR1 1* ALTER USER MONITOR1 TEMPORARY TABLESPACE IVALUE_TEMP SQL> / User altered. SQL> C/MONITOR1/IVL_DEBUG 1* ALTER USER IVL_DEBUG TEMPORARY TABLESPACE IVALUE_TEMP SQL> / User altered. SQL> C/IVL_DEBUG/ZABBIX 1* ALTER USER ZABBIX TEMPORARY TABLESPACE IVALUE_TEMP SQL> / User altered. SQL> ALTER USER MIGBK TEMPORARY TABLESPACE IVALUE_TEMP; User altered. SQL> C/MIGBK/MIGSP 1* ALTER USER MIGSP TEMPORARY TABLESPACE IVALUE_TEMP SQL> / User altered. SQL> C/MIGSP/IVL2IF 1* ALTER USER IVL2IF TEMPORARY TABLESPACE IVALUE_TEMP SQL> SQL> SQL> / User altered. SQL> C/IVL2IF/DBMON 1* ALTER USER DBMON TEMPORARY TABLESPACE IVALUE_TEMP SQL> / User altered. SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users order by created; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ SYS SYSTEM TEMP SYSTEM SYSTEM TEMP OUTLN SYSTEM TEMP DIP USERS TEMP ORACLE_OCM USERS TEMP DBSNMP SYSAUX TEMP APPQOSSYS SYSAUX TEMP WMSYS SYSAUX TEMP EXFSYS SYSAUX TEMP CTXSYS SYSAUX TEMP ANONYMOUS SYSAUX TEMP XDB SYSAUX TEMP XS$NULL USERS TEMP ORDSYS SYSAUX TEMP MDSYS SYSAUX TEMP SI_INFORMTN_SCHEMA SYSAUX TEMP ORDPLUGINS SYSAUX TEMP ORDDATA SYSAUX TEMP OLAPSYS SYSAUX TEMP MDDATA USERS TEMP SPATIAL_WFS_ADMIN_USR USERS TEMP SPATIAL_CSW_ADMIN_USR USERS TEMP FLOWS_FILES SYSAUX TEMP APEX_PUBLIC_USER USERS TEMP APEX_030200 SYSAUX TEMP OWBSYS_AUDIT SYSAUX TEMP OWBSYS SYSAUX TEMP MIGBI TS_MIG IVALUE_TEMP CHGSHS TS_BSC IVALUE_TEMP CHGSHSIF TS_FUL_IF IVALUE_TEMP IVLMC TS_MC IVALUE_TEMP VOUCHER TS_BSC IVALUE_TEMP PRODTASK TS_TASK IVALUE_TEMP MIGJOB TS_MIG IVALUE_TEMP IVL_READ USERS IVALUE_TEMP BK_IVL TS_BSC IVALUE_TEMP BI_PLAN TS_BSC IVALUE_TEMP MONITOR USERS IVALUE_TEMP MONITOR1 USERS IVALUE_TEMP IVL_DEBUG TS_FUL IVALUE_TEMP ZABBIX TS_TASK IVALUE_TEMP MIGBK MIGBK IVALUE_TEMP MIGSP MIGSP IVALUE_TEMP IVL2IF TS_BSC IVALUE_TEMP DBMON TS_BSC IVALUE_TEMP 45 rows selected.
这样，当用户新创建临时段的时候就会使用新指定的临时表空间了。在线操作并没什么问题。
更改数据库的默认临时表空间 及用户的默认临时表空间：等您坐沙发呢！
发表评论
