|
目录[-]
1.1查看undo表空间位置及使用大小
1.2查询回滚段使用状态
1.3创建新的undo表空间
1.4使用新的undo表空间
1.5查看原表空间使用情况
1.6删除原临时表空间同时删除数据文件
1.7重新创建原undo表空间
1.8查看当前undo表空间使用情况
1.9切换当前undo表空间为原undo表空间
1.10查看当前undo表空间使用状态
1.11删除undo2表空间
2切换temp表空间
2.1查询当前temp表空间使用情况
2.2查询temp表空间位置
2.3创建新的临时表空间
2.4修改默认temp表空间为新的临时表空间
2.5查看当前临时表空间使用情况
2.6删除原临时表空间
2.7查询哪些sql在使用临时表空间
2.8 kill掉这些sql语句
2.9查看hang住的session
2.10重新删除temp表空间
2.11查看当前临时表空间使用情况
2.12重新创建原临时表空间
2.13还原默认临时表空间为temp
2.14查看当前默认临时表空间
2.15查看临时表空间是否有sql在使用
2.16 kill这些sql语句
2.17删除temp2临时表空间
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.
(责任编辑：IT) |