COUNT(*)
----------
67
SQL> alter table hr.t04311_big shrink space;
Table altered.
SQL> select count(*) from dba_extents e where e.owner='HR' and e.segment_name='T04311_BIG';
COUNT(*)
----------
1
如果有索引，在收缩段的时候可以加上cascade关键字，或者使用下面的命令来单独收缩，重建索引
SQL> alter index hr.EMP_LAST_NAME_IDX coalesce;
Index altered
SQL> alter index hr.EMP_LAST_NAME_IDX rebuild online;
Index altered.
三：由于空间问题，设置会话暂停，默认超时时间为7200秒
SQL> revoke unlimited tablespace from hr;
Revoke succeeded
SQL> grant resumable to hr;
Grant succeeded.
SQL> create table t100 as select * from departments;
create table t100 as select * from departments *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
SQL> alter session enable resumable timeout 5;
Session altered.
SQL> create table t100 as select * from departments;
create table t100 as select * from departments
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01536: space quota exceeded for tablespace 'USERS'
SQL> select status,sql_text from dba_resumable;
STATUS SQL_TEXT
---------- ------------------------------
SUSPENDED create table t100 as select *
from departments
利用触发器来自动处理由于空间问题导致的会话挂起
SQL> create tablespace tbs04311_small datafile '/u01/app/oradata/ora10g/tbs04311_small01.dbf' size 512k autoextend off;
Tablespace created.
SQL> grant unlimited tablespace to hr;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> create table t04311_t( a number ) tablespace tbs04311_small storage ( initial 1M);
create table t04311_t( a number ) tablespace tbs04311_small storage ( initial 1M)
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01659: unable to allocate MINEXTENTS beyond 7 in tablespace TBS04311_SMALL
SQL> conn /as sysdba
SQL> CREATE OR REPLACE TRIGGER SYS.TRG_SUSPEND
2 AFTER SUSPEND
3 ON DATABASE
4 declare
5 v_size number;
6 pragma AUTONOMOUS_TRANSACTION;
7 begin
8 select bytes into v_size from dba_data_files where file_name='/u01/app/oradata/ora10g/tbs04311_small01.dbf';
9 v_size := v_size + 1048576;
10 execute immediate 'alter database datafile ''/u01/app/oradata/ora10g/tbs04311_small01.dbf'' resize '||v_size;
11 commit;
12* end;
SQL> /
Trigger created.
SQL> select * from user_errors;
no rows selected
SQL> select bytes from dba_data_files where tablespace_name=upper('tbs04311_small');
BYTES
----------
524288
SQL> conn hr/hr
Connected.
SQL> alter session enable resumable;
Session altered.
SQL> create table t04311_t( a number ) tablespace tbs04311_small storage ( initial 1M);
Table created.
SQL> select bytes from dba_data_files where tablespace_name=upper('tbs04311_small');
BYTES
----------
1572864
四.传输表空间
可以跨平台，跨越indian format，但要求字符集必须一致，COMPATIBLE参数必须大于10.0.0，传输之前需要将表空间置于只读状态
源服务器端：
C:\>set ORACLE_SID=winorcl2
C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三8月3 13:23:27 2011
Copyright (c) 1982, 2005, Oracle. All rights re