Oracle tablespace modification method

1. Use imp/exp. Export the source database first, create a new database, create the tablespace, and then import it. (It is said that this is acceptable, provided that the new database cannot contain tablespaces with the same name as the source database. To be verified !)

2. Use the script for modification. According to the current situation, it must be modifiedTable spaceAndTable index spaceIf the table involves the bolb field, the modification method is different!
Under normal circumstances, modify the script:
1. ModifyTable space
Alter table table_name move tablespace tablespacename

Query all tables under the current user
Select 'alter table' | table_name | 'move tablespace tablespacename; 'fromUser_all_tables;

2. ModifyTable index space
Alter index index_name rebuild tablespace tablespacename

Query all indexes of the current user
Select 'alter Index' | index_name | 'rebuild tablespace tablespacename; 'fromUser_indexes;

You can use scripts to execute the query results so that they can be processed in batches!

Abnormal tables with BLOB fields:
Refer to the following documents

I have already performed this operation. It is feasible, but I do not know whether other objects in the database are missed. Whether the table space with objects still exists needs to be modified. We are very grateful if you need further discovery or help from database experts! The current modification has not encountered any problems in the project!

Note the Oracle alter table move syntax after migration:

Alter table table_name move [Online] tablespace_name;
The preceding statement can be used to port a table to a new tablespace,

To port the lob dictionary, 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 .....))
)

....

Note:

Lob (lob_item): the lob field in the table
Store as [lob_segment]: After a table is created for each lob field, the system automatically creates a separate segment. You can use this parameter to manually specify a segment name.
Tablespace_name: New Storage tablespace In the lob Field
(Storage...): Specifies the storage attribute of tablespace_name.
Enable storage in row: If enable storage in row is set, Oracle automatically stores data smaller than bytes in rows. This is the default value of oracle, for a lob field larger than 4000 bytes stored in the lob segment (same as disable storage in row), the control information of 36-84 bytes is retained in the table segment. For disable storage in row, Oracle saves the lob field separately in the lob segment, and only keeps a pointer of 20 bytes at the row position. For this part, which is equivalent to disable storage in row (that is, the data separately stored in the lob segment), undo only records changes in the pointer and related lob indexes, if a DML operation such as an update operation occurs, the raw data is retained in the lob segment.

Disable storage in row: If this attribute is disable, the lob data is stored outside the row, and only the lob value pointer is stored in the row.
After the table is created, it can only be changed when the table is moved.
Chunk: it is a very special attribute. For a lob data operation (insert or update), the specified value is preferably a multiple of the database blocks because of the storage space allocated, in addition, the specified value cannot be greater than the next value in the tablespace interval. Otherwise, Oracle will return an error. If this value has been set before, the specified value cannot be changed later.

Storage as (chunk bytes) indicates that for the part of disable storage in row, the minimum size of the lob block must be an integer multiple of the database block (db_block_size. A chunk can retain at most one row of Lob data. That is to say, if you set a 32 K chunk, but the size of the lob field is only 4 K, it will also occupy 32 K space.

Storage as (Cache | nocahce) indicates whether to allow the lob segment to pass through the buffer cache and cache. The default value is nocache, which indicates direct read and direct write without passing through the data buffer of the database. Therefore, by default, this part of data separately stored in the lob segment is directly read when physical reads occur, such as direct path read (LOB)

Storage as (nocache logging | nocache nologging), the logging/nologging attribute only applies to the nocache mode. The default value is logging. If it is nologging, redo logs are not recorded during DML operations such as update.

Pctversion integer and Retention: Oracle is used to manage lob field image data. During lob data update,
Oracle does not use the Undo tablespace space. Instead, it divides a space from the tablespace where the lob field is located to create an image space,
The size of this space is controlled by the pctversion parameter. The default value is 10, which indicates that 10% of the tablespace is divided as the image space,
The unit size of each image space is specified by the chunk parameter. The pctversion can be used in the manual undo mode and automatic undo mode environments.
Retention applies the undo_retention In the UNIC undo mode to manage the lob image space by time.
Pctversion and retention cannot be specified at the same time. We recommend that you use the retention parameter in the automatic undo mode database.
Freepools INTEGER: Specify Free List. RAC as the number of instances for log segment. 1 for a single instance. Oracle uses
Freepools to manage the list of idle blocks. Unless the freelist groups parameter is specified in the storage configuration of the table.
Cache | nocache | cache reads: Specifies whether the lob block is cached in the database buffer.
Index lobindexname (tablespace tablesapce_name (storage...): Specify the index storage parameter for the lob Column
Example:
SQL> show parameter db_create_file_dest

SQL> Create tablespace test datafile size 100 m autoextend off;
SQL> Create Table Test (A varchar2 (100), B clob, d BLOB) pctfree 10 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 attributes of the lob field.
Next we move the lob field to another tablespace.

SQL> Create tablespace lob_test datafile size 100 m autoextend off;

SQL> alter table test move lob (B) store as test_ B (
Tablespace lob_test
Disable storage in row
Chunks 16384
Retention
Freepools 1
Nocache );
SQL> ALTER TABLE test move lob (d) store as test_d (
Tablespace lob_test
Disable storage in row
Chunks 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 situations, you may need to port tables together.
Alter table table_name move [tablespace_name] lob (lob_item) store as [lobsegmentname] (tablespace tablespace_name .....);
Lob in the transplanted Partition
Alter table table_name move partition [partition_name] lob (lob_item) store as [logsegmentname] (tablespace_name .....);
Port a partition table
Alter table table_name move partition [partition_name] tablespace_name lob (lob_item) store as [logsegmentname] (tablespace_name .....);
If you do not need to modify the lobsegmentname, You can port multiple columns at the same time.
Alter table table_name move lob (lob_item1, lob_item2, lob_item3...) store as [lobsegmentname] (tablespace tablespace_name .....);

The lob segment can also be used to restructure data. The following statement moves the table and the lob field 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 Partition Table, add the partition keyword, as shown in figure

Alter table table_name move [partition partname] [tablespace tbs_name]

Lob (field) store as (tablespace new_tbs_name );

Reasonable storage of Lob columns in databases not only improves performance, but also effectively manages buckets.