Detailed description of Oracle cross-platform migration structure without data migration

This article describes Oracle cross-platform migration, such as migrating data from Oracle to other types of databases, but retaining the original structure. This reduces the workload of DBAs. I hope this article will help you better understand Oracle cross-platform migration.

1. Export related information in the original database:

 
 

  
  
expdp system/sywg1234 directory=test dumpfile=test.dump logfile=test.log   
  
  
schemas=SYWG,GAZX,WEBCALL,HQ,SJCK,SJPZ CONTENT=METADATA_ONLY 
 
 

2. tablespace information of the original database:

Export the tablespace information in TOAD, edit the export script, and delete the system information. Change the Directory and file size as needed.

3. perform operations in the target database:

Copy the dump file exported from the original database to a directory in the target database;

Create an import directory;

 
 

  
  
impdp system/sywg1234 directory=test dumpfile=test.dump logfile=test_imp.log   
  
  
CONTENT=METADATA_ONLY exclude=statistc 
 
 

Note:

There is a big problem at this time, that is, even if the table structure is imported and there is no data, it still occupies a large number of tablespaces, which is indeed a big problem.

4. After importing data to the target database, analyze all tables:

-- Generate a statistical analysis script:

 
 

  
  
select 'analyze table '||owner||'.'||table_name ||' compute  statistics; 'from all_tables  
  
  
where OWNER in ('GAZX','SJCK','SJPZ','SYWG','HQ','WEBCALL') 
 
 

--- Execute the script generated above to update statistics

5. Compress a large number of tablespaces occupied by empty tables:

--- View the space occupied by the table,

 
 

  
  
SELECT OWNER,segment_name,SEGMENT_TYPE,BYTES/1024/1024 FROM DBA_SEGMENTS  
  
  
WHERE OWNER='GAZX' 
  
  
ORDER BY 4 DESC 
 
 

--- Check whether data exists in the table

 
 

  
  
SELECT * FROM GAZX.GSGG 
 
 

--- Check whether the table allows row_movement

 
 

  
  
select a.row_movement,a.* from all_tables a 
  
  
where  a.owner='GAZX'  AND a.TABLE_NAME='GSGG' 
 
 

--- If not, row_movement is enabled.

 
 

  
  
alter table GAZX.GSGG enable row movement; 
 
 

Generate scripts for batch processing:

 
 

  
  
SELECT 'ALTER TABLE GAZX.'||SEGMENT_NAME||' enable row movement;' 
  
  
FROM DBA_SEGMENTS  
  
  
WHERE OWNER='GAZX' AND SEGMENT_TYPE='TABLE' 
 
 

---- Space occupied by the compressed table

 
 

  
  
ALTER TABLE  GAZX.GSGG SHRINK SPACE CASCADE; 
 
 

Generate scripts for batch processing:

 
 

  
  
SELECT 'ALTER TABLE GAZX.'||SEGMENT_NAME||' SHRINK SPACE CASCADE;' 
  
  
FROM DBA_SEGMENTS  
  
  
WHERE OWNER='GAZX' AND SEGMENT_TYPE='TABLE' 
 
 
 

---- After the table is compressed, compress the data file.

6. Check the original and target databases:

Proofread after import:

--- Check the number of tables

 
 

  
  
select  OWNER,COUNT(TABLE_NAME) from all_tables  
  
  
where OWNER in ('GAZX','SJCK','SJPZ','SYWG','HQ','WEBCALL')  
  
  
GROUP BY OWNER 
 
 

--- Check the number of Indexes

 
 

  
  
SELECT OWNER,COUNT(TABLE_NAME) FROM ALL_INDEXES  
  
  
where OWNER in ('GAZX','SJCK','SJPZ','SYWG','HQ','WEBCALL')  
  
  
GROUP BY OWNER