How to import oralce data from one tablespace to another tablespace (original)

 

User: whnaproject tablespace: whnaproject

New User: wniec new tablespace: wniec

 

Requirement: Copy all the database tables and data in user whnaproject to the wniec user in the new tablespace wniec.

 

General process: Use the exp command to export the user's whnaproject table and data, and then use the IMP command to import the exported table and data to the new user wniec.

 

Problem: User whnaproject tables and data can be imported to the new user wniec. However, the imported table in wniec still belongs to the tablespace where the user whnaproject was originally exported to whnaproject, instead of the new tablespace wniec I want.

 

Solution:

1. Select * From user_tables can query the tablespace information of the table to which the current user belongs.

2. Modify the tablespace of the User table: alter table table name move tablespace new tablespace name; you can use the batch update method as follows:

Select 'alter table' | table_name | 'move tablespace wniec; 'from user_tables where tablespace_name <> 'wniec'

This ensures that the table structure and data can be transferred to the new tablespace. In other ways, only the table structure can be transferred and data cannot be copied.

3. If the field types in the User table contain "long", "blob", and "clob", the above move statement cannot be used directly to move the table. If the table does not have data, you can modify the tablespace when directly recreating the table. If you have any data, you can refer to the 4 and 5 methods for processing.

4. Tables whose field type is "long" cannot be processed in the move mode. They can only be processed in the copy mode and are not verified yet.

5. "blob" and "clob" are of the lob type. You can refer to the following type of Processing Method for moving. They are not verified yet.

 

Note:

1. The move operation can only be performed when the database is idle.

2. Remember to re-create the index after moving.

3. Moving the tablespace will change the rowid. If you use its operation in the program, it may cause a strange problem and it is difficult to recover.

 

--------------------------------------- Refer --------------------------------------------

Batch change of table and index tablespaces in Oracle

Batch change of table and index tablespaces in Oracle
1. query all tables under the current user
Select 'alter table' | table_name | 'move tablespace tablespacename; 'from user_all_tables;
Select 'alter table' | table_name | 'move tablespace tablespacename; 'from user_tables;
 

2. query all indexes of the current user
Select 'alter Index' | index_name | 'rebuild tablespace tablespacename; 'from user_indexes;

3. execute batch query results under the current user!

 

To put it simply, perform the following steps:

-- Query all tables, copy the query results, and execute SQL
Select 'alter table' | table_name | 'move tablespace name; 'from user_all_tables

-- Query all indexes, copy the query results, and execute SQL
Select 'alter Index' | index_name | 'rebuild tablespace name; 'from user_indexes

-- Process lob type
Alter table table name move tablespace name lob (Field 1) store as (tablespace name)

-- Modify the tablespace of a partition table
Alter table table name move partition name 1 tablespace name;
Alter table table name move partition name 2 tablespace name;

 

 

 

View All unusable Indexes
Select *
From user_indexes
Where status not in ('valid', 'n'/')
Order by index_name;

-- Description: displays unusable indexes for the specified schema or all schemas.
-- Requirements: access to the DBA views.
-- Call Syntax: @ unusable_indexes (schema-name or all)

Set verify off

Select owner,
Index_name
From dba_indexes
Where owner = decode (upper ('& 1'), 'all', owner, upper (' & 1 '))
And status not in ('valid', 'n'/')
Order by owner, index_name;