expdp和impdp 实战

1 查询需要导出数据的用户 

select username,default_tablespace from dba_users where username  like '%USERNAME%';

2 查看原来表空间大小

set linesize 9999 pagesize 9999

SELECT total.tablespace_name,
       Round(total.MB, 2)           AS Total_MB,
       Round(total.MB - free.MB, 2) AS Used_MB,
       Round(( 1 - free.MB / total.MB ) * 100, 2)
       || '%'                       AS Used_Pct
FROM   (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
        FROM   dba_free_space
        GROUP  BY tablespace_name) free,
       (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
        FROM   dba_data_files
        GROUP  BY tablespace_name) total
WHERE  free.tablespace_name = total.tablespace_name  and free.tablespace_name in ('PSSM_DATA')
order by 4;

3 新库上创建表空间和用户

create tablespace PSSM_DATA datafile '/data/app/oracle/oradata//PSSM_DATA.dbf' size 1G;
create user PSSM_USER identified by "XXXX" default tablespace PSSM_DATA;

4 原数据库导出数据

nohup expdp \'/ as sysdba\' directory=EXPDP_BACKUP parallel=10 dumpfile=PSSM_USER_20240514_%U.dmp logfile=exp_PSSM_USER_20240514.log  compression=all schemas=PSSM_USER &
 

5 传输数据到新机器

scp PSSM_USER_20240514_* oracle@ip:/data/dump

6 数据导入

nohup impdp \'/ as sysdba\' directory=DIR_DUMP dumpfile=PSSM_USER_20240514_%U.dmp logfile=imp_PSSM_USER_20240514.log &