---增加数据文件

    ALTER TABLESPACE UNDOTBS2

    ADD DATAFILE 'E:\ORACLE\ORA92\ORCL9\UNDOTBS03.DBF' size 2M AUTOEXTEND ON NEXT 1M

    MAXSIZE UNLIMITED;

    --drop undo表空间

    DROP TABLESPACE UNDOTBS2; ---INCLUDING CONTENTS.

    --不指定undo表空间

    ALTER SYSTEM SET UNDO_TABLESPACE = '';

    --设置retention值:

    ALTER SYSTEM SET UNDO_RETENTION = 5;

    ---计算undo表空间的大小(计算机数据可以从V$UNDOSTAT 得到)

    UndoSpace = UR * UPS + overhead

    UndoSpace is the number of undo blocks

    UR is UNDO_RETENTION in seconds

    UPS is undo blocks for each second

    overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)

    例如:UNDO_RETENTION 2 hours, transaction rate (UPS) 200 undo blocks for each second,

    with a 4K block size则undo空间为:

    (2 * 3600 * 200 * 4K) = 5.8GBs.

    ---使用Oracle提供的报表查看锁的情况:

    @$ORACLE_HOME\rdbms\admin\utllockt.sql

    ---查询当前某个session的事务所使用的回滚段大小

    select b.sid,a.xidusn,a.used_ublk from v$transaction a,v$session b where a.addr=b.taddr;

    ---如果要介质恢复一个数据文件,先offline再恢复(在归档模式)

    alter database datafile 'E:\ORACLE\ORA92\ORCL9\UNDOTBS03.DBF' offline

    recover datafile 'E:\ORACLE\ORA92\ORCL9\UNDOTBS03.DBF'

    --

    -----查看Undo的大小

    set line 1000;

    set pages 1000;

    SELECT d.status "Status",

    d.tablespace_name "Name",

    d.contents "Type",

    d.extent_management "Extent Management",

    to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') "Total Size (M)",

    to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,

    '99999999.999') "Used (M)",

    to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Free (M)",

    to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),

    '990.00') "Used %"

    FROM sys.dba_tablespaces d,

    (SELECT tablespace_name, SUM(bytes) bytes

    FROM dba_data_files

    GROUP BY tablespace_name) a,

    (SELECT tablespace_name, SUM(bytes) bytes

    FROM dba_free_space

    GROUP BY tablespace_name) f

    WHERE d.tablespace_name = a.tablespace_name(+)

    AND d.tablespace_name = f.tablespace_name(+)

    AND NOT

    (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')

    UNION ALL

    SELECT d.status "Status",

    d.tablespace_name "Name",

    d.contents "Type",

    d.extent_management "Extent Management",

    to_char(nvl(a.bytes / 1024 / 1024, 0), '99999990.900') "Size (M)",

    to_char(nvl(t.bytes, 0) / 1024 / 1024, '99999999.999') "Used (M)",

    to_char((nvl(a.bytes / 1024 / 1024, 0)) -

    (nvl(t.bytes, 0) / 1024 / 1024),

    '99999999.999') "Free (M)",

    to_char(nvl(t.bytes / a.bytes * 100, 0), '990.00') "Used %"

    FROM sys.dba_tablespaces d,

    (SELECT tablespace_name, SUM(bytes) bytes

    FROM dba_temp_files

    GROUP BY tablespace_name) a,

    (SELECT tablespace_name, SUM(bytes_cached) bytes

    FROM v$temp_extent_pool

    GROUP BY tablespace_name) t

    WHERE d.tablespace_name = a.tablespace_name(+)

    AND d.tablespace_name = t.tablespace_name(+)

    AND d.extent_management LIKE 'LOCAL'

    AND d.contents LIKE 'TEMPORARY'

    ORDER BY "Used %" DESC;