1、oracle11g数据导出
–创建逻辑目录
create or replace directory expdp as '/ora01/app/oracle';
–授权
grant read,write on directory expdp to system;
–查询
select * from dba_directories;
–导出
expdp ZI001/ZI001@orclpdb.dbaora.com DIRECTORY=DATA_PUMP_DIR dumpfile=ZB.dmp
logfile=ZB.log full=y
–删除
drop directory expdp;
--统计表空间文件大小
select 'CREATE TABLESPACE ' || b.NAME || chr(39)||'LOGGING'||
' DATAFILE ' || chr(39) || a.NAME || chr(39) || ' SIZE ' ||
a.BYTES/1024/1024 || 'M'||chr(39) ||'AUTOEXTEND ON NEXT 5M MAXSIZE
16000M ;'
from v$datafile a, v$tablespace b
where a.ts# = b.TS# And
b.INCLUDED_IN_DATABASE_BACKUP = 'YES'
Union All
select 'CREATE TEMPORARY TABLESPACE' || b.NAME || ' TEMPFILE '
|| chr(39) || a.NAME || chr(39) || ' SIZE ' ||
a.BYTES/1024/1024||'M'||chr(39) ||'AUTOEXTEND ON NEXT 5M MAXSIZE
16000M ;'
from v$tempfile a, v$tablespace
b
where a.ts# = b.TS# And
b.INCLUDED_IN_DATABASE_BACKUP = 'NO'
Union All
select 'ALTER DATABASE DATAFILE ' || chr(39) || a.NAME ||
chr(39) || ' AUTOEXTEND ON ;'
from v$datafile a, v$tablespace b where a.ts# = b.TS# And
b.INCLUDED_IN_DATABASE_BACKUP = 'YES'
Union All
select 'ALTER DATABASE TEMPFILE ' || chr(39) || a.NAME ||
chr(39) || ' AUTOEXTEND ON ;'
from v$tempfile a, v$tablespace b
where a.ts# = b.TS# And
b.INCLUDED_IN_DATABASE_BACKUP = 'NO'
2、数据导入Oracle19C
–登录cdb
sqlplus / as sysdba
–查询当前容器
show con_name
–查询容器中所有pdb
show pdbs
–创建pdb数据库
CREATE pluggable DATABASE pdborcl admin USER pdbadmin
identified BY oracle roles=(dba)
file_name_convert=('/opt/oracle/oradata/ORA19C/pdbseed',
'/opt/oracle/oradata/ORA19C/pdborcl');
–切换到pdb数据库
alter session set container=pdborcl;
–打开数据库
alter database open
–创建目录
mkdir /home/oracle/dmp
并将导出的DMP文件存放到该目录下
create or replace directory expdp as '/home/oracle/dmp';
grant read,write on directory expdp to pdbadmin;
–查询
select * from dba_directories;
–建表空间
create tablespace qin datafile
'/opt/oracle/oradata/ORA19C/pdborcl/qin01.DBF' size 20M autoextend
on;
create tablespace wang datafile
'/opt/oracle/oradata/ORA19C/pdborcl/wang01.DBF' size 20M autoextend
on;
–使用impdp导入数据
impdp lmy/lmy@orcle directory=DATA_PUMP_DIR dumpfile=cszymx20221121_202_%U.dmp log=impdp.log remap_schema=SI0001:lmy version='12.2.0.1.0' remap_tablespace=TS_SI0001:xxxxx TABLE_EXISTS_ACTION=replace;
