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