Oracle Modifying Tablespace methods

First, the use of imp/exp. Pilot out the source library, then create a new library to create a table space, and then import. (this is said to be possible, provided that the new library cannot have a tablespace with the same name as the Source library.) Pending verification! ）

Second, use the script to modify. As far as we know, it is necessary to modify the length ofthe space of the tableAndThe index space of the table, if it involves the table of the Bolb field, the way of modification is different!
the modified script under normal circumstances:
1. Modificationsthe space of the table
ALTER TABLE table_name move tablespace tablespacename

Querying all tables under the current user
Select ' ALTER TABLE ' | |  table_name | | '  Move tablespace tablespacename; ' FromUser_all_tables;

2. ModificationsThe index space of the table
ALTER index index_name rebuild tablespace tablespacename

Querying all indexes under the current user
Select ' Alter index ' | | index_name | | ' rebuild tablespace tablespacename; ' Fromuser_indexes;

You can use a script to execute the results of a query so that it can be processed in batches!

a table with a BLOB field is not normally the case:
refer to the following document

I have executed, is feasible, but do not know that there is no missing the database of other objects. Whether there is still an object table space needs to be modified, need to further discover or have a database expert to help explain it is greatly appreciated! There are no problems with the current changes in the project!

The syntax for the next Oracle ALTER TABLE MOVE is examined in the transplant:

ALTER TABLE table_name MOVE [ONLINE] tablespace_name;
You can migrate a table to a new table space by using the above statement.

If you are porting a lob dictionary, you need to refer to the following syntax:
ALTER TABLE table_name LOB (Lob_item) STORE as [lob_segment]
(
Tablespace Tablespace_name
(STORAGE ...)
enable| DISABLE STORAGE in ROW
CHUNK integer
Pctversion integer
RETENTION
Freepools integer
cache| nocache| CACHE READS
INDEX Lobindexname

(Tablespace tablesapce_name

((STORAGE ...))
)

....

Annotations:

LOB (Lob_item): LOB fields in a table
STORE as [lob_segment]: Each LOB field automatically creates a separate segment after the table is created, and you can manually specify a segment name with this parameter
Tablespace_name:lob field new storage table space
(STORAGE ...): Specify storage properties for Tablespace_name
Enable STORAGE in row: If Enable STORAGE in row is set then Oracle will automatically store less than 4000bytes of data in the row, which is the default value for Oracle, For LOB fields larger than 4000 bytes saved in the LOB segment (same as disable storage in row), 36-84 bytes of control information will be retained in the table segment. For disable storage in Row,oracle, the LOB field is saved separately in the LOB segment, and only a 20-byte pointer is reserved at the row location. For this part of the Disable storage in row (that is, this part of the data that is stored separately in the LOB segment), Undo is simply the record pointer and the associated LOB index changes, and if DML operations such as update operations occur, the original data remains in the LOB segment.

DISABLE STORAGE in row: If this property is DISABLE, the LOB data is stored out-of-line, only the LOB-worthy pointer is stored in the row, and this property is in the table
Can only be changed when the move table is created
CHUNK: is a very special property, the operation of the LOB data (insert or update), because of how much storage space allocated, the specified value is preferably a multiple of the database block, and the specified value cannot be greater than the value of next in the Tablespace interval, or Oracle will return an error, If this value has been set previously, then the value specified in the later period cannot be changed.

Storage as (CHUNK bytes) indicates that for this part of the disable storage in row, the size of the smallest LOB block must be an integer multiple of the database block (db_block_size). A chunk keeps a maximum of one row of LOB data, that is, if you set a 32K chunk, but if the LOB field size is only 4 K, it will occupy 32K of space

Storage as (CACHE|NOCAHCE) indicates whether LOB segments are allowed to pass buffer cache and cache. The default is NoCache, which means direct read and direct write, without passing the data buffer of the database. So, by default, for this part of the data that is saved separately in the LOB segment, it is read directly when a physical read occurs, such as direct path read (LOB)

Storage as (NoCache logging |nocache nologging), the logging/nologging attribute is only valid for NoCache, the default is logging, and if it is nologging mode, for Save the log section outside the line, and the redo log will not be logged when DML operations such as update.

Pctversion Integer, RETENTION: All Oracle is used to manage the LOB field image data. During the update of the LOB data,
Oracle does not use the undo tablespace space, but instead divides a space from the table space in which the LOB field is located to make the mirror space,
The size of this space is controlled by the pctversion parameter, and the default value is 10, which represents 10% of the partitioned table space as the mirror space.
The cell size of each mirror space is specified by the chunk parameter, and pctversion can be used in manual undo mode and automatic undo mode environment.
Retention applies the undo_retention in automatic undo mode to manage the LOB mirror space through time.
Pctversion and retention cannot be specified at the same time. It is recommended that the database use the retention parameter under automatic undo mode.
Freepools integer: Specify the free list to log segment. Integer is the number of instances in the RAC environment. 1 in a single instance environment. Oracle defaults with automatic undo mode
Freepools to manage the list of free blocks. Unless we specify the Freelist groups parameter in the storage configuration of the table.
cache| nocache| Cache READS: Specifies whether the LOB block is cached in database buffer.
Index Lobindexname (tablespace tablesapce_name (STORAGE ...): Specify index storage parameters for LOB columns
Example:
Sql> Show Parameter Db_create_file_dest

sql> Create tablespace test datafile size 100M autoextend off;
Sql> CREATE TABLE Test (a varchar2 (+), B clob, D blob) pctfree tablespace test;

Sql> desc Test
Sql> SELECT segment_name,tablespace_name,segment_type from dba_segments WHERE tablespace_name= ' TEST ';

We found that each LOB field has a separate logsegment and lobindex;

Sql> Set Linesize 200
Col table_name format A5
Col column_name format A5
SELECT B.table_name,
A.segment_name,
B.index_name,
A.segment_type,
B.column_name,
A.tablespace_name,
B.chunk,
B.cache,
B.freepools,
B.pctversion,
B.retention
From dba_segments A,dba_lobs b
WHERE A.segment_name = B.segment_name
and a.tablespace_name = ' TEST '
/

Sql>

From the above results we can observe the various properties of the LOB field.
Let's move to another table space on the LOB field

sql> Create tablespace lob_test datafile size 100M autoextend off;

sql> ALTER TABLE TEST MOVE LOB (B) STORE as Test_b (
Tablespace lob_test
DISABLE STORAGE in ROW
CHUNK 16384
RETENTION
Freepools 1
NOCACHE);
sql> ALTER TABLE TEST MOVE LOB (D) STORE as Test_d (
Tablespace lob_test
DISABLE STORAGE in ROW
CHUNK 16384
RETENTION
Freepools 1
NOCACHE);
Sql> SELECT segment_name,tablespace_name,segment_type from dba_segments WHERE tablespace_name= ' TEST ';

Sql> Set Linesize 200
Col table_name format A5
Col column_name format A5
SELECT B.table_name,
A.segment_name,
B.index_name,
A.segment_type,
B.column_name,
A.tablespace_name,
B.chunk,
B.cache,
B.freepools,
B.pctversion,
B.retention
From dba_segments A,dba_lobs b
WHERE A.segment_name = B.segment_name
and a.tablespace_name = ' lob_test '
/sql>

In some complex cases, it may be necessary to migrate the tables together.
ALTER TABLE table_name MOVE [Tablespace_name] LOB (Lob_item) store as [Lobsegmentname] (tablespace tablespace_name ...);
Porting a lob in a partition
ALTER TABLE table_name MOVE partition [Partition_name] LOB (Lob_item) store as [Logsegmentname] (tablespace_name ...);
Migrating Partitioned Tables
ALTER TABLE table_name MOVE partition [Partition_name] tablespace_name LOB (Lob_item) store as [Logsegmentname] (Tablespac E_name ...);
If you do not need to modify lobsegmentname, you can migrate multiple columns at the same time
ALTER TABLE table_name move LOB (LOB_ITEM1,LOB_ITEM2,LOB_ITEM3 ...) store as [Lobsegmentname] (tablespace tablespace_name .....);



LOB segments can also use move to restructure the data, and the following statements move the table and LOB fields to the specified tablespace:

ALTER TABLE table_name MOVE [tablespace Tbs_name]

LOB (LOB_FIELD1,LOB_FIELD2) store as (Tablespace new_tbs_name);

If the LOB field is in the partitioned table, increase the partition keyword, as

ALTER TABLE table_name MOVE [partition PartName] [tablespace tbs_name]

LOB (field) store as (Tablespace new_tbs_name);



Reasonable storage of LOB columns in the database can not only improve performance, but also effectively manage storage space.

Oracle Modifying Tablespace methods