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 此时空间回收