Problems caused by changing the tablespace blob field in Oracle

Because blob fields are fields that store binary big data, and can be stored for a maximum of 4 GB. Therefore, it is stored differently from other fields. When blob is stored, it is only stored in

Because blob fields are fields that store binary big data, and can be stored for a maximum of 4 GB. Therefore, it is stored differently from other fields. When blob is stored, it is only stored in

The scenario is as follows:

All the original table data is stored in the users tablespace. Now you need to replace the tablespace with the ntbs tablespace.

When moving, tables with blob, clob, and long types fail to be replaced. Why?

Because blob fields are fields that store binary big data, and can be stored for a maximum of 4 GB. Therefore, it is stored differently from other fields. When blob is stored, it only stores a Data Pointer in it. The real data is separated from the table Storage and placed in another address space. Of course, this address space may be two independent tablespaces in the same tablespace as the table. But it is two different buckets.

Therefore, in the preceding scenario, the actual data of blob fields is stored in users tablespaces. The users who use blob fields do not have the users tablespaces permission and only have the ntbs tablespace permission.

1. solution:

Assign the users tablespace permission to the user, but the user cannot change the tablespace because the data of the blob field is still in the users tablespace.

Therefore, execute: alter table tname move tablespace ntbs blob (col_name) store as (tablesapce ntbs );

In this way, the data in the field col_name in the tname of the table containing blob is replaced with the table space ntbs.

2. If it is changed between different machines, for example, the dmp file exported from the users tablespace of machine A is imported to the ntbs tablespace of machine B.

In this case, you can create a table with blob fields in the ntbs tablespace of machine B. After the table is created, the actual data with blob fields is actually stored in the ntbs tablespace, then execute imp.

The same is true for clob and long types.