SQL> select object_name from user_objects;
no rows selected
SQL> select segment_name from user_segments;
no rows selected
SQL> create table test1 as select * from ecds.MSG_MESSAGELOG;
Table created.
SQL> select segment_name,segment_type from user_segments;
SEGMENT_NAME SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
TEST1 TABLE
SYS_IL0000324358C00006$$ LOBINDEX
SYS_LOB0000324358C00006$$ LOBSEGMENT
SQL> desc test1
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID NOT NULL VARCHAR2(50)
M_MESGTYPE VARCHAR2(20)
M_MESGSTATE VARCHAR2(100)
M_OPERATEDATE TIMESTAMP(6)
M_SUCCESSFULDATE TIMESTAMP(6)
M_MESGCONTENT CLOB
M_SENDORRECEIVE VARCHAR2(100)
M_REPEATTIMES NUMBER(38)
M_WORKDATE DATE
M_ORIGSENDER VARCHAR2(100)
M_ORIGSENDDATE DATE
M_MESGID VARCHAR2(100)
M_FK_MESSAGESAVETIMEID VARCHAR2(100)
M_MSGTABLEID VARCHAR2(100)
M_CREDTTM TIMESTAMP(6)
M_ACCTSVCR VARCHAR2(100)
M_MSGID4SUM VARCHAR2(100)
M_ACCPTRSVCR VARCHAR2(100)
SQL> select min(m_origsenddate),max(m_origsenddate) from test1;
MIN(M_ORIGSE MAX(M_ORIGSE
------------ ------------
25-JUN-10 20-OCT-13
SQL> select count(*) from test1 a
2 where a.m_origsenddate >= date'2011-12-31';
COUNT(*)
----------
679430
SQL> select count(*) from test1 a;
COUNT(*)
----------
1004647
SQL> select segment_name,bytes/1024/1024 from user_segments;
SEGMENT_NAME BYTES/1024/1024
--------------------------------------------------------------------------------- ---------------
TEST1 1088
SYS_IL0000324358C00006$$ .1875
SYS_LOB0000324358C00006$$ 4464
SQL> delete from test1 a where a.m_origsenddate >= date'2011-12-31';
679430 rows deleted.
SQL> commit;
Commit complete.
----------------------------------------------------------------------------------------------------
SQL> select segment_name,bytes/1024/1024 from user_segments;
SEGMENT_NAME BYTES/1024/1024
--------------------------------------------------------------------------------- ---------------
TEST1 1088
SYS_IL0000324358C00006$$ 29
SYS_LOB0000324358C00006$$ 4464
SQL> alter table test1 move;
Table altered.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes/1024/1024 from user_segments;
SEGMENT_NAME BYTES/1024/1024
--------------------------------------------------------------------------------- ---------------
TEST1 360
SYS_IL0000324358C00006$$ 29
SYS_LOB0000324358C00006$$ 4464
SQL> select segment_name,bytes/1024/1024,segment_type from user_segments;
SEGMENT_NAME BYTES/1024/1024 SEGMENT_TYPE
--------------------------------------------------------------------------------- --------------- ------------------
TEST1 360 TABLE
SYS_IL0000324358C00006$$ 29 LOBINDEX
SYS_LOB0000324358C00006$$ 4464 LOBSEGMENT
一个Lob字段会自动创建索引
SQL> alter table test1 modify lob(M_MESGCONTENT) (shrink space);
Table altered.
SQL> select segment_name,bytes/1024/1024,segment_type from user_segments;
SEGMENT_NAME BYTES/1024/1024 SEGMENT_TYPE
--------------------------------------------------------------------------------- --------------- ------------------
TEST1 360 TABLE
SYS_IL0000324358C00006$$ 29 LOBINDEX
SYS_LOB0000324358C00006$$ 1348.6875 LOBSEGMENT
此时空间回收