切换undo表空间和temp表空间

 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.

转载于:https://my.oschina.net/sniperLi/blog/366061

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值