ITPub博客

首页 > Linux操作系统 > Linux操作系统 > undo表空间和temp表空间切换维护步骤

undo表空间和temp表空间切换维护步骤

原创 Linux操作系统 作者:cnaning 时间:2013-02-01 13:26:51 0 删除 编辑
 大家好!
 
今天把原来整理的日常维护脚本重新测试整理下,分享给大家。
 
正好今天有个测试需要清理,发现找资料很麻烦,以后还是发博客比较靠谱!
 
话不多说,下面是具体步骤,请广大朋友指正:
 

1 切换undo表空间

1.1查看undo表空间位置及使用大小

 

SQL>  col FILE_NAME for a60

SQL>  select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';

 

FILE_NAME                                                    BYTES/1024/1024

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

/u01/app/oracle/oradata/lottery/undotbs01.dbf                           2048   

注:

1.2查询回滚段使用状态

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks

  2      from v$rollstat order by rssize;

 

       USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS

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

         0          0            .000358582             .000358582          0

        25          0            .049797058             .049797058         15

        22          0            .050773621             .050773621          0

        19          0            .051750183             .051750183         61

        23          0            .060539246             .060539246          0

        13          0            .061515808             .061515808          0

        15          0            .078117371             .078117371          0

        12          0            .078117371             .078117371          0

        10          0            .078422546             .078422546          0

         4          0            .080070496             .080070496          0

         1          0            .086112976             .086112976         71

 

       USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS

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

         7          0            .086845398             .086845398          0

        11          0            .093742371             .093742371          0

         5          0            .101554871             .101554871          0

         3          0            .101554871             .101554871          0

         9          0            .101554871             .101554871          0

         8          0            .101676941             .101676941          0

         6          0            .101676941             .101676941          0

         2          0            .101860046             .101860046          0

 

19 rows selected.

 

1.3创建新的undo表空间

SQL> CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS02" DATAFILE

'/u01/app/oracle/oradata/lottery/undotbs21.dbf' SIZE 10M

AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

 

Tablespace created.

 

1.4使用新的undo表空间

SQL> alter system set undo_tablespace=UNDOTBS02 scope=both;

 

System altered.

 

1.5查看原表空间使用情况

等带SHRINKS全部为0时删除原undo表空间

SQL> set line 200

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks

  2        from v$rollstat order by rssize;

 

       USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS

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

        28          0 ONLINE                     .000114441             .000114441          0

        44          0 ONLINE                     .000114441             .000114441          0

        29          0 ONLINE                     .000114441             .000114441          0

        30          0 ONLINE                     .000114441             .000114441          0

        31          0 ONLINE                     .000114441             .000114441          0

        32          0 ONLINE                     .000114441             .000114441          0

        33          0 ONLINE                     .000114441             .000114441          0

        34          0 ONLINE                     .000114441             .000114441          0

        35          0 ONLINE                     .000114441             .000114441          0

        36          0 ONLINE                     .000114441             .000114441          0

        37          0 ONLINE                     .000114441             .000114441          0

 

       USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS

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

        38          0 ONLINE                     .000114441             .000114441          0

        39          0 ONLINE                     .000114441             .000114441          0

        40          0 ONLINE                     .000114441             .000114441          0

        41          0 ONLINE                     .000114441             .000114441          0

        42          0 ONLINE                     .000114441             .000114441          0

        43          0 ONLINE                     .000114441             .000114441          0

         0          0 ONLINE                     .000358582             .000358582          0

 

18 rows selected.

 

1.6删除原临时表空间同时删除数据文件

SQL> drop tablespace undotbs1 including contents and datafiles;

 

Tablespace dropped.

 

1.7重新创建原undo表空间

SQL> CREATE SMALLFILE UNDO TABLESPACE "undotbs1" DATAFILE

'/u01/app/oracle/oradata/lottery/undotbs01.dbf' SIZE 10M

AUTOEXTEND ON NEXT 100M MAXSIZE 16G;

 

Tablespace created.

 

1.8查看当前undo表空间使用情况

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks

  2      from v$rollstat order by rssize;

 

       USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS

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

        28          0            .000114441             .000114441          0

        44          0            .000114441             .000114441          0

        29          0            .000114441             .000114441          0

        30          0            .000114441             .000114441          0

        31          0            .000114441             .000114441          0

        32          0            .000114441             .000114441          0

        33          0            .000114441             .000114441          0

        34          0            .000114441             .000114441          0

        35          0            .000114441             .000114441          0

        36          0            .000114441             .000114441          0

        37          0            .000114441             .000114441          0

 

       USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS

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

        38          0            .000114441             .000114441          0

        39          0            .000114441             .000114441          0

        40          0            .000114441             .000114441          0

        41          0            .000114441             .000114441          0

        42          0            .000114441             .000114441          0

        43          0            .000114441             .000114441          0

         0          0            .000358582             .000358582          0

 

18 rows selected.

 

1.9 切换当前undo表空间为原undo表空间

SQL> alter system set undo_tablespace=undotbs1 scope=both;

 

System altered.

 

1.10 查看当前undo表空间使用状态

 

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks

  2        from v$rollstat order by rssize;

 

       USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS

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

         1          0 ONLINE                     .000114441             .000114441          0

        17          0 ONLINE                     .000114441             .000114441          0

         2          0 ONLINE                     .000114441             .000114441          0

         3          0 ONLINE                     .000114441             .000114441          0

         4          0 ONLINE                     .000114441             .000114441          0

         5          0 ONLINE                     .000114441             .000114441          0

         6          0 ONLINE                     .000114441             .000114441          0

         7          0 ONLINE                     .000114441             .000114441          0

         8          0 ONLINE                     .000114441             .000114441          0

         9          0 ONLINE                     .000114441             .000114441          0

        10          0 ONLINE                     .000114441             .000114441          0

 

       USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS

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

        11          0 ONLINE                     .000114441             .000114441          0

        12          0 ONLINE                     .000114441             .000114441          0

        13          0 ONLINE                     .000114441             .000114441          0

        14          0 ONLINE                     .000114441             .000114441          0

        15          0 ONLINE                     .000114441             .000114441          0

        16          0 ONLINE                     .000114441             .000114441          0

         0          0 ONLINE                     .000358582             .000358582          0

 

18 rows selected.

 

1.11 删除undo2表空间

SQL> drop tablespace UNDOTBS02 including contents and datafiles;

 

Tablespace dropped.

 

 

2 切换temp表空间

2.1查询当前temp表空间使用情况

SQL> select username,temporary_tablespace from dba_users;

 

USERNAME                       TEMPORARY_TABLESPACE

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

MGMT_VIEW                      TEMP

SYS                            TEMP

SYSTEM                         TEMP

DBSNMP                         TEMP

SYSMAN                         TEMP

LOTTERY                        TEMP

SIMULATOR_TJ                   TEMP

LOTTERY_GXLD                   TEMP

SIMULATOR_GX                   TEMP

LOTTERY_TJLD                   TEMP

OUTLN                          TEMP

 

USERNAME                       TEMPORARY_TABLESPACE

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

FLOWS_FILES                    TEMP

MDSYS                          TEMP

ORDSYS                         TEMP

EXFSYS                         TEMP

WMSYS                          TEMP

APPQOSSYS                      TEMP

APEX_030200                    TEMP

OWBSYS_AUDIT                   TEMP

ORDDATA                        TEMP

CTXSYS                         TEMP

ANONYMOUS                      TEMP

 

USERNAME                       TEMPORARY_TABLESPACE

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

XDB                            TEMP

ORDPLUGINS                     TEMP

OWBSYS                         TEMP

SI_INFORMTN_SCHEMA             TEMP

OLAPSYS                        TEMP

SCOTT                          TEMP

ORACLE_OCM                     TEMP

XS$NULL                        TEMP

MDDATA                         TEMP

DIP                            TEMP

APEX_PUBLIC_USER               TEMP

 

USERNAME                       TEMPORARY_TABLESPACE

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

SPATIAL_CSW_ADMIN_USR          TEMP

SPATIAL_WFS_ADMIN_USR          TEMP

 

35 rows selected.

 

2.2 查询temp表空间位置

SQL> select name from v$tempfile;

 

NAME

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

/u01/app/oracle/oradata/lottery/temp01.dbf

 

2.3 创建新的临时表空间

SQL> create temporary tablespace temp2 tempfile

'/u01/app/oracle/oradata/lottery/temp02.dbf' size 20m;

 

Tablespace created.

 

2.4 修改默认temp表空间为新的临时表空间

SQL>  alter database default temporary tablespace temp2;

 

Database altered.

 

2.5 查看当前临时表空间使用情况

SQL> select username,temporary_tablespace from dba_users;

 

USERNAME                       TEMPORARY_TABLESPACE

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

OUTLN                          TEMP2

SYSTEM                         TEMP2

SYS                            TEMP2

MGMT_VIEW                      TEMP2

OLAPSYS                        TEMP2

SI_INFORMTN_SCHEMA             TEMP2

OWBSYS                         TEMP2

ORDPLUGINS                     TEMP2

XDB                            TEMP2

ANONYMOUS                      TEMP2

CTXSYS                         TEMP2

 

USERNAME                       TEMPORARY_TABLESPACE

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

ORDDATA                        TEMP2

OWBSYS_AUDIT                   TEMP2

APEX_030200                    TEMP2

APPQOSSYS                      TEMP2

WMSYS                          TEMP2

EXFSYS                         TEMP2

ORDSYS                         TEMP2

MDSYS                          TEMP2

FLOWS_FILES                    TEMP2

SYSMAN                         TEMP2

DBSNMP                         TEMP2

 

USERNAME                       TEMPORARY_TABLESPACE

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

SPATIAL_WFS_ADMIN_USR          TEMP2

SPATIAL_CSW_ADMIN_USR          TEMP2

APEX_PUBLIC_USER               TEMP2

DIP                            TEMP2

MDDATA                         TEMP2

XS$NULL                        TEMP2

ORACLE_OCM                     TEMP2

SCOTT                          TEMP2

LOTTERY_TJLD                   TEMP2

SIMULATOR_GX                   TEMP2

LOTTERY_GXLD                   TEMP2

 

USERNAME                       TEMPORARY_TABLESPACE

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

SIMULATOR_TJ                   TEMP2

LOTTERY                        TEMP2

 

35 rows selected.

 

2.6 删除原临时表空间

这里数据库hang住,因为临时表空间正在被使用

SQL>  drop tablespace temp including contents and datafiles;

 

2.7查询哪些sql在使用临时表空间

开启另外一个session查询

SQL> select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))as space,

  2  tablespace,segtype,sql_text

  3  from v$sort_usage su,v$parameter p,v$session se,v$sql s

  4  where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash

  5  and s.address=su.sqladdr

  6  order by se.username,se.sid;

 

USERNAME  SID SERIAL#  TABLESPACE SQL_TEXT                                                                       

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

SYS      187   55619    TEMP2     select username,temporary_tablespace from dba_users

SYS      187   55619    TEMP2     select username,temporary_tablespace from dba_users

 

2.8 kill掉这些sql语句

alter system kill session '187,55619';

 

2.9 查看hang住的session

SQL>  drop tablespace temp including contents and datafiles;

 

 

 drop tablespace temp including contents and datafiles

*

ERROR at line 1:

ORA-00028: your session has been killed

ORA-00028: your session has been killed

 

2.10 重新删除temp表空

hang住的session删除

SQL> drop tablespace temp including contents and datafiles;

ERROR:

ORA-03114: not connected to ORACLE

 

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

oracle@db100:/u01/app/oracle/oradata/lottery$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sun Oct 21 22:23:05 2012

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> drop tablespace temp including contents and datafiles;

 

Tablespace dropped.

 

2.11 查看当前临时表空间使用情况

SQL> select username,temporary_tablespace from dba_users;

 

USERNAME                       TEMPORARY_TABLESPACE

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

OUTLN                          TEMP2

SYSTEM                         TEMP2

SYS                            TEMP2

MGMT_VIEW                      TEMP2

OLAPSYS                        TEMP2

SI_INFORMTN_SCHEMA             TEMP2

OWBSYS                         TEMP2

ORDPLUGINS                     TEMP2

XDB                            TEMP2

ANONYMOUS                      TEMP2

CTXSYS                         TEMP2

 

USERNAME                       TEMPORARY_TABLESPACE

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

ORDDATA                        TEMP2

OWBSYS_AUDIT                   TEMP2

APEX_030200                    TEMP2

APPQOSSYS                      TEMP2

WMSYS                          TEMP2

EXFSYS                         TEMP2

ORDSYS                         TEMP2

MDSYS                          TEMP2

FLOWS_FILES                    TEMP2

SYSMAN                         TEMP2

DBSNMP                         TEMP2

 

USERNAME                       TEMPORARY_TABLESPACE

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

SPATIAL_WFS_ADMIN_USR          TEMP2

SPATIAL_CSW_ADMIN_USR          TEMP2

APEX_PUBLIC_USER               TEMP2

DIP                            TEMP2

MDDATA                         TEMP2

XS$NULL                        TEMP2

ORACLE_OCM                     TEMP2

SCOTT                          TEMP2

LOTTERY_TJLD                   TEMP2

SIMULATOR_GX                   TEMP2

LOTTERY_GXLD                   TEMP2

 

USERNAME                       TEMPORARY_TABLESPACE

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

SIMULATOR_TJ                   TEMP2

LOTTERY                        TEMP2

 

35 rows selected.

 

2.12 重新创建原临时表空间

SQL> create temporary tablespace temp tempfile

'/u01/app/oracle/oradata/lottery/temp01.dbf' SIZE 10M

AUTOEXTEND ON NEXT 100M MAXSIZE 16G;

 

Tablespace created.

 

2.13 还原默认临时表空间为temp

SQL>  alter database default temporary tablespace temp;

 

Database altered.

 

2.14 查看当前默认临时表空间

SQL> select username,temporary_tablespace from dba_users;

 

USERNAME                       TEMPORARY_TABLESPACE

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

OUTLN                          TEMP

SYSTEM                         TEMP

SYS                            TEMP

MGMT_VIEW                      TEMP

OLAPSYS                        TEMP

SI_INFORMTN_SCHEMA             TEMP

OWBSYS                         TEMP

ORDPLUGINS                     TEMP

XDB                            TEMP

ANONYMOUS                      TEMP

CTXSYS                         TEMP

 

USERNAME                       TEMPORARY_TABLESPACE

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

ORDDATA                        TEMP

OWBSYS_AUDIT                   TEMP

APEX_030200                    TEMP

APPQOSSYS                      TEMP

WMSYS                          TEMP

EXFSYS                         TEMP

ORDSYS                         TEMP

MDSYS                          TEMP

FLOWS_FILES                    TEMP

SYSMAN                         TEMP

DBSNMP                         TEMP

 

USERNAME                       TEMPORARY_TABLESPACE

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

SPATIAL_WFS_ADMIN_USR          TEMP

SPATIAL_CSW_ADMIN_USR          TEMP

APEX_PUBLIC_USER               TEMP

DIP                            TEMP

MDDATA                         TEMP

XS$NULL                        TEMP

ORACLE_OCM                     TEMP

SCOTT                          TEMP

LOTTERY_TJLD                   TEMP

SIMULATOR_GX                   TEMP

LOTTERY_GXLD                   TEMP

 

USERNAME                       TEMPORARY_TABLESPACE

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

SIMULATOR_TJ                   TEMP

LOTTERY                        TEMP

 

35 rows selected.

 

2.15 查看临时表空间是否有sql在使用

SQL> select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))as space,

  2  tablespace,segtype,sql_text

  3  from v$sort_usage su,v$parameter p,v$session se,v$sql s

  4  where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash

  5  and s.address=su.sqladdr

  6  order by se.username,se.sid;

 

USERNAME  SID SERIAL#  TABLESPACE SQL_TEXT                                                                       

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

SYS      187   55739    TEMP2     select username,temporary_tablespace from dba_users

SYS      187   55739    TEMP2     select username,temporary_tablespace from dba_users

 

 

2.16 kill这些sql语句

alter system kill session '187,55739';

 

2.17 删除temp2临时表空间

切换成功

SQL> drop tablespace temp2 including contents and datafiles;

 

Tablespace dropped.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12457158/viewspace-753738/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-12

  • 博文量
    36
  • 访问量
    217217