重用表空间-优快云博客

Reusespace

简介：The user tablespace has been allocated more space ,we need to reusethe free space to allocate to other tablespaces.

Steps :check the tablespace current statusand usage.

SQL> descdba_lobs
Name                                                         Null? Type
------------------------------------------------------------------------- --------------------------------------------
OWNER                                                               VARCHAR2(30)
TABLE_NAME                                                            VARCHAR2(30)
COLUMN_NAME                                                           VARCHAR2(4000)
SEGMENT_NAME                                                          VARCHAR2(30)
TABLESPACE_NAME                                                       VARCHAR2(30)
INDEX_NAME                                                            VARCHAR2(30)
CHUNK                                                               NUMBER
PCTVERSION                                                            NUMBER
RETENTION                                                            NUMBER
FREEPOOLS                                                            NUMBER
CACHE                                                               VARCHAR2(10)
LOGGING                                                               VARCHAR2(7)
ENCRYPT                                                               VARCHAR2(4)
COMPRESSION                                                           VARCHAR2(6)
DEDUPLICATION                                                         VARCHAR2(15)
IN_ROW                                                               VARCHAR2(3)
FORMAT                                                               VARCHAR2(15)
PARTITIONED                                                           VARCHAR2(3)
SECUREFILE                                                            VARCHAR2(3)

SQL>select  segment_name,segment_type,tablespace_name,bytes/1024/1024,blocks,extents  fromuser_segments
  2  wheresegment_type='LOBSEGMENT'
  3 order by bytes desc;

select owner,segment_name,segment_type,bytes from dba_segments a where a.tablespace_name='USERS'

SELECT A.TABLE_NAME, A.COLUMN_NAME, B.SEGMENT_NAME, B.SEGMENT_TYPE, B.TABLESPACE_NAME, B.BYTES / 1024 / 1024, B.BLOCKS, B.EXTENTS FROM USER_LOBS A, USER_SEGMENTS B WHERE A.SEGMENT_NAME = B.SEGMENT_NAME ORDER BY B.BYTES DESC;

Select Count(*) From sndfc_exchange_log_info t Where to_char(modi_date,'yyyymmdd')='20110529'; ---checkthe table row number

Select Count(*) From sndfc_exchange_log_info t;-----checkcurrent table row number

SELECT segment_name,segment_type,blocksFROM dba_segments WHERE tablespace_name='USERS';

SEGMENT_NAME SEGMENT_TYPE BLOCKS

------------------------------ ----------------------------

TEST TABLE 896

TMS_ORDER TABLE 107520

SYS_C005896 INDEX 4992

SYS_C005897 INDEX 8192

TMS_ORDER_PICK_UP_TYPE_LOG TABLE 8

TMS_ORGANIZATION_USER TABLE 24

SYS_C005942 INDEX 8

TMS_PASSINFO TABLE 5632

SYS_C005951 INDEX 1536

TMS_WMS_CODE_MAP TABLE 8

SYS_C006090 INDEX 8

SMS_LOG TABLE 1024

SYS_C006419 INDEX 72

SMS_ACCOUNT_INFO TABLE 8

SYS_C005598 INDEX 8

TMS_CARRIER_SERVICE_TYPE TABLE 8

SYS_C005804 INDEX 8

SYS_C005805 INDEX 8

TMS_INTERFACE_FDF_LOG TABLE 384

SYS_C005861 INDEX 24

BMS_FEE_DATA_LOG TABLE 104

SYS_C005465 INDEX 24

PASSWORD_HISTORY TABLE 24

SYS_C005544 INDEX 8

SYS_EXPORT_SCHEMA_01 TABLE 24

SYS_IL0000065453C00045$$ LOBINDEX 8

SYS_EXPORT_SCHEMA_02 TABLE 24

SYS_IL0000065663C00045$$ LOBINDEX 8

SYS_LOB0000065453C00045$$ LOBSEGMENT 8

SYS_LOB0000065663C00045$$ LOBSEGMENT 8

SYS_C006959 INDEX 8

SYS_MTABLE_00000FFAD_IND_1 INDEX 16

SYS_MTABLE_00000FFAD_IND_2 INDEX 8

SYS_C006964 INDEX 8

SYS_MTABLE_00001007F_IND_1 INDEX 16

SYS_MTABLE_00001007F_IND_2 INDEX 8

SYS_EXPORT_SCHEMA_03 TABLE 24

SYS_IL0000067263C00045$$ LOBINDEX 8

SYS_LOB0000067263C00045$$ LOBSEGMENT 8

SYS_EXPORT_SCHEMA_04 TABLE 24

SYS_IL0000067284C00045$$ LOBINDEX 8

SYS_LOB0000067284C00045$$ LOBSEGMENT 56

SYS_C006969 INDEX 8

SYS_MTABLE_0000106BF_IND_1 INDEX 16

SYS_MTABLE_0000106BF_IND_2 INDEX 8

SYS_C006978 INDEX 8

SYS_MTABLE_0000106D4_IND_1 INDEX 16

SYS_MTABLE_0000106D4_IND_2 INDEX 8

THORN_INTERFACE_LOG TABLE 1065728

SYS_IL0000087640C00011$$ LOBINDEX 1920

SYS_LOB0000087640C00011$$ LOBSEGMENT 1449344

SYS_IL0000087640C00008$$ LOBINDEX 1408

SYS_LOB0000087640C00008$$ LOBSEGMENT 1862400

SYS_C007075 INDEX 21504

INDEX_LEDGER_001 INDEX 6656

INDEX_LEDGER_002 INDEX 8

Steps:

1. Move table

2. Rebuild indexes (and move them to thenew tablespace if you wish so)

3. Move the lob segment

4. Enable row movement

5. Shrink the table and lob segment

1. --moving the table to the newtablespace

2. alter table table_name move tablespace new_tablespace;

4. --rebuilding the index(es) and movingthem to the new tablespace

5. alter index index_name rebuild tablespace new_tablespace;

6. --moving the lobsegment(s) to the newtablespace

7. alter table table_name move lob (lob_column) store as

8. (tablespace new_tablespace);

9. --shrinking the table and lobsegment

10. alter table table_name enable row movement;

11. alter table table_name shrink space cascade;

12. alter table table_name modify lob (lob_column)(shrink space);

Solution 1:

a) Move table

SQL> select table_name,tablespace_name,num_rows,last_analyzed from user_tables
  2  where table_name='T';
SQL> select count(*) from t;
  COUNT(*)
----------
      1000
SQL> analyze table t compute statistics;
SQL> select table_name,tablespace_name,num_rows,last_analyzed from user_tables
  2  where table_name='T';
user_table中的num_rows是最后一次analyze取得的数据。
SQL> alter table t move tablespace test;
表已更改。
SQL> select table_name,tablespace_name,num_rows,last_analyzed from user_tables
  2  where table_name='T';
SQL> select index_name,status from user_indexes where table_name='T';
INDEX_NAME                     STATUS
------------------------------ --------
T_INDEX3                       UNUSABLE
SQL> alter index t_index3 rebuild;
SQL> select index_name,status from user_indexes where table_name='T';
INDEX_NAME                     STATUS
------------------------------ --------
T_INDEX3                       VALID
SQL>  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t');

b) Rebuild index to othertablespace

select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || 'REBUILD TABLESPACE ' || OWNER || ';' from DBA_INDEXES WHERE OWNER IN('OWNER1','OWNER2')

select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || 'MOVE TABLESPACE ' || OWNER || ';' from DBA_TABLES WHERE OWNER IN('OWNER1','OWNER2')

alter index <index_name> rebuild tablespace<tablespace_name>;-----rebuild index in the other tablespaces.

*****************************************************************

LOBSEGMENT的系统命名规则是

SYS_LOB(10 digit object_id)C(5 digit col#)$$ 表段
SYS_IL(10 digit object_id)C(5 digit col#)$$ 索引

*****************************************************************

c) LOBINDEX

1,必须新建一张临时表table_new，然后把需要的记录移动到该表，把table重命名为table_old 再TRUNCATE原先的table_old表释放空间,然后把table_new重命名为table（注意主键和索引等约束）。 (rebuild index)

2,可以对LOB字段进行压缩存储
ALTER TABLE <表名> MODIFY LOB(<列名>) (COMPRESS);

3, --alter table <your_table> move lob (<lob_column>) store as(tablespace <tablespace>);

d) LOBSEGMENT

Expdp/impdp the object----------------为了释放出空间，我把此表导出，重建，再导入。结果空间释放。

CREATE TABLE TEST
(
TEST_ID NUMBER NOT NULL,
TEST_NAME CLOB,
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
)
/
SQL> ALTER TABLE TEST MOVE TABLESPACE EXAMPLE;
Above command will move the table to new tablespace but will not move the CLOBsegment and it will still be in original tablespace. This is because LOB datais stored outside of the table.
Check the tablespace of the CLOB column by issuing following sql.
SQL> SELECT index_name, tablespace_name
FROM user_indexes WHERE table_name = ‘TEST’;

INDEX_NAME TABLESPACE_NAME
—————————— —————-
SYS_IL0000073575C00002$$ USERS

In order to move CLOB column to different tablespace, we have to issuefollowing command.
SQL> ALTER TABLE TEST MOVE LOB(TEST_NAME) STORE AS (TABLESPACE EXAMPLE);

In above example, TEST_NAME is the CLOB column which we want to move to newtablespace and EXAMPLE is target tablespace. Above command will successfullymove LOB segments to the new tablespace. We can verify it by issuing same sqlagain.
SQL> SELECT index_name, tablespace_name
FROM user_indexes WHERE table_name = ‘TEST’;
INDEX_NAME TABLESPACE_NAME
—————————— —————
SYS_IL0000073575C00002$$ EXAMPLE

ALTER TABLEowner.table_name MOVE LOB (column_name) STORE AS (tablespace_name)
command.
You cannot specify a tablespace for the LOBINDEX -- it is automatically createdand moved with the LOBSEGMENT.

For partional table----example

ALTER TABLE PartitionTableName EXCHANGE PARTITION PartitionName WITH TABLE NonPartitionTableName WITHOUT VALIDATION;

ALTER TABLE PartitionTableName

MOVE PARTITION PartitionName TABLESPACE NewTableSpace

LOB segment and LOB Index segment still exist in tablespace where nonpartition table was created.

alter table <tname> move partition <pname> lob (<cname>) store as ( tablespace <tablespace_name> )

Example to move the LOB

column table_name format a20

column column_name format a20

column lob_tablespace format a20

column table_tablespace format a20

drop table jw

Table dropped

create table jw (x int, y clob) tablespace users

Table created

insert into jw values(1, rpad('*',30000,'*'))

1 row inserted

insert into jw values(2, rpad('*',30000,'*'))

1 row inserted

insert into jw values(3, rpad('*',30000,'*'))

1 row inserted

commit

Commit complete

select l.table_name, l.column_name, s.tablespace_name lob_tablespace, t.tablespa

from user_lobs l, user_segments s, user_segments t

where l.table_name = 'JW'

and l.segment_name = s.segment_name

and t.segment_name = 'JW'

and t.segment_type = 'TABLE'

TABLE_NAME COLUMN_NAME LOB_TABLESPACE TABLE_TABLESPACE

-------------------- -------------------- -------------------- -----------------

JW Y USERS USERS

1 row selected

alter table jw move lob(y) store as (tablespace data_lob)

Table altered

select l.table_name, l.column_name, s.tablespace_name lob_tablespace, t.tablespa

from user_lobs l, user_segments s, user_segments t

where l.table_name = 'JW'

and l.segment_name = s.segment_name

and t.segment_name = 'JW'

and t.segment_type = 'TABLE'

TABLE_NAME COLUMN_NAME LOB_TABLESPACE TABLE_TABLESPACE

-------------------- -------------------- -------------------- -----------------

JW Y DATA_LOB USERS

1 row selected

Moving a LOB, without moving the table works just fine.

For example:

Create new tablespace: The tablespace is needed temporarily to get all the remaining objects. The parameters must fit the existing tablespace.

CREATE TABLESPACE new_tablespace
   DATAFILE 'file_name' SIZE xxxK REUSE
   AUTOEXTEND OFF
   [EXTENT MANAGEMENT LOCAL UNIFORM SIZE xxxK]
   PERMANENT
   ONLINE;

Move all objects from old tablespace to new tablespace: All remaining objects are move from the old tablesapce (with the obsolete datafile(s)) to the new created tablespace using corresponding commands for tables, partitions, or indexes.

ALTER TABLE table_name MOVE TABLESPACE
  new_tablespace;
ALTER TABLE table_name MOVE PARTITION
  partition_name TABLESPACE new_tablespace;
ALTER INDEX index_name REBUILD TABLESPACE
  new_tablespace;

Drop old tablespace: Now the old tablespace should be empty and can be removed. This deletes the obsolete datafile(s) from data dictionary too! To be sure removing an empty tablespace you must omit the option INCLUDING CONTENTS.

DROP TABLESPACE old_tablespace;

Recreate old tablespace: The original tablespace can be created again, reusing all the existing OS files minus the obsolete datafile(s).

Move all objects back from new tablespace to old tablespace: All objects in the temporary tablespace must be moved back to the original tablespace.

Drop new tablespace: The temporary tablespace (created in step 1) can be deleted.

Check for invalid or unusable objects: Even if all objects are valid you might have INVALID or UNUSABLE indexes. Rebuild them!

-- Select invalid objects
COLUMN object_name format a32
SELECT owner,
       SUBSTR(object_name, 1, 32) "OBJECT_NAME",
       status
FROM dba_objects
WHERE status != 'VALID'
   AND owner NOT IN ('SYS','ORDSYS','ORDPLUGINS')
ORDER BY 1, 2;
--
-- Select invalid indexes
SELECT owner, table_name, index_name, status
FROM dba_indexes
WHERE status != 'VALID'
   AND partitioned != 'YES'
   AND owner NOT IN ('SYS','ORDSYS','ORDPLUGINS')
ORDER BY 1, 2, 3;
--
-- Select unusable partitioned indexes
SELECT index_owner, partition_name, index_name, status
FROM dba_ind_partitions
WHERE status != 'USABLE'
   AND index_owner NOT IN
       ('SYS','ORDSYS','ORDPLUGINS')
ORDER BY 1, 2, 3;