Oracle----库操作

 查看数据库编码

select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';

windows连接oracle

sqlplus / as sysdba  本机超级管理员登录
sqlplus 用户/密码  本机用户
sqlplus 用户/密码@//172.16.2.17:1521/orcl  远程登录

Linux连接oracle

比windows多一步 先su -oracle 用户

数据库expdp/IMPDP迁移

前提准备

Linux 先要切换成oracle用户，Windows不需要任何操作，直接执行下面语句

expdp数据导出

expdp 用户名/密码@ip地址/实例  [属性]
#
ip地址不写默认就是本地

• userid=test/test            --导出的用户，本地用户!!
• directory=dmpfile          --导出的逻辑目录，一定要在oracle中创建完成的，并且给用户授权读写权限
• dumpfile=xx.dmp      --导出的数据文件的名称，如果想在指定的位置的话可以写成dumpfile=/home/oracle/userxx.dmp
• logfile=xx.log          --日志文件，如果不写这个参数的话默认名称就是export.log，可以在本地的文件夹中找到
• schemas=userxx       --使用dblink导出的用户不是本地的用户，需要加上schema来确定导出的用户，类似于exp中的owner，但还有一定的区别
• EXCLUDE=TABLE:"IN('T1','T2','T3')"     --exclude 可以指定不导出的东西，table，index等，后面加上不想导出的表名
• network_link=db_local          --这个参数是使用的dblink来远程导出，需要指定dblink的名称

创建逻辑目录

create directory [mydata] as ['逻辑目录路径']; 
#例如： mydata最好大写
create directory MYDATA as '/data/oracle/oradata/mydata/';
#查看directory
select * from dba_directories　　

我们可以先连接装了oracle的机器，指定表进行导出，此时不需要输入ip地址，也不必担心本地oracle和服务器oracle版本问题

expdp ticm/ticm@trssdb tables=AShareCalendar,CBondAnalysisCNBD1 dumpfile=ticm_%U.dmp directory=HOME4 logfile=expdp.log PARALLEL=4;

更多场景

1)导出用户及其对象
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp directory=dump_dir logfile=expdp.log;

2)导出指定表
expdp scott/tiger@orcl tables=emp,dept dumpfile=expdp.dmp directory=dump_dir logfile=expdp.log;

3)按查询条件导
expdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp tables=empquery='where deptno=20' logfile=expdp.log;

4)按表空间导
expdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=temp,example logfile=expdp.log;

5)导整个数据库
expdp scott/123@127.0.0.1/orcl directory=dump_dir dumpfile=ly.dmp full=y  logfile=expdp.log;

IMPDP数据导入

场景

1)导入用户（从用户scott导入到用户scott）
impdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp schemas=scott logfile=impdp.log;

2)导入表（从scott用户中把表dept和emp导入到system用户中）
impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp tables=scott.dept,scott.emp remap_schema=scott:system logfile=impdp.log table_exists_action=replace (表空间已存在则替换);

3)导入表空间
impdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=example logfile=impdp.log;

4)导入整个数据库
impdb system/manager@orcl directory=dump_dir dumpfile=full.dmp full=y logfile=impdp.log;

日常使用

#把用户jcpt中所有的表导入到lyxt用户下
impdp lyxt/lyxt123@127.0.0.1/orcl directory=mydata dumpfile=LY.DMP   remap_schema=jcpt:lyxt logfile=ims20171122.log table_exists_action=replace

#追加数据
#append是为表增加数据;（注意是表，不是表空间）
#truncate是截断表，然后为其增加新数据；
#replace是删除已存在表，重新建表并追加数据;
impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp schemas=system table_exists_action=append logfile=impdp.log;

多个dmp

• paralle，通常设置为CPU的个数

impdp ticm/ticm directory = DMPDIR  dumpfile = ticm_%U.dmp logfile = ticm_impdp.log parallel = 4 cluster = n

　　

数据库exp/imp迁移

注意和数据泵导入不一样，file指定的是具体目录下的dmp，可以不用加后缀

关键词
USERID 用户名/口令 FULL 导出整个文件 (N)
BUFFER 数据缓冲区大小 OWNER 所有者用户名列表
FILE 输出文件 (EXPDAT.DMP) TABLES 表名称列表
COMPRESS 导入到一个区 (Y) RECORDLENGTH IO 记录的长度
GRANTS 导出权限 (Y) INCTYPE 增量导出类型
INDEXES 导出索引 (Y) RECORD 跟踪增量导出 (Y)
DIRECT 直接路径 (N) TRIGGERS 导出触发器 (Y)
LOG 屏幕输出的日志文件 STATISTICS 分析对象 (ESTIMATE)
ROWS 导出数据行 (Y) PARFILE 参数文件名
CONSISTENT 交叉表的一致性 (N) CONSTRAINTS 导出的约束条件 (Y)
OBJECT_CONSISTENT 只在对象导出期间设置为读的事务处理 (N)
FEEDBACK 每 x 行的显示进度 (0)
FILESIZE 每个转储文件的最大大小
FLASHBACK_SCN 用于将会话快照设置回以前状态的 SCN
FLASHBACK_TIME 用于获取最接近指定时间的 SCN 的时间
QUERY 用于导出表的子集的 select 子句
RESUMABLE 遇到与空格相关的错误时挂起 (N)
RESUMABLE_NAME 用于标识可恢复语句的文本字符串
RESUMABLE_TIMEOUT RESUMABLE 的等待时间
TTS_FULL_CHECK 对 TTS 执行完整的或部分相关性检查
TABLESPACES 要导出的表空间列表
TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)
TEMPLATE 调用 iAS 模式导出的模板名
在没有警告的情况下成功终止导出。

IMP

#完全
IMP SYSTEM/MANAGER BUFFER=64000 FILE=C:FULL.DMP FULL=Y      
#用户模式
IMP SONIC/SONIC    BUFFER=64000 FILE=C:SONIC.DMP FROMUSER=SONIC TOUSER=SONIC           这样用户SONIC的所有对象被导入到文件中。必须指定FROMUSER、TOUSER参数，这样才能导入数据。      
#表模式
EXP SONIC/SONIC    BUFFER=64000 FILE=C:SONIC.DMP OWNER=SONIC TABLES=(SONIC)           这样用户SONIC的表SONIC就被导入。

#导入一个完整数据库
imp system/manager file=bible_db log=dible_db full=y ignore=y

#获取帮助
imp help=y
#导入一个完整数据库
imp system/manager file=bible_db log=dible_db full=y ignore=y
#导入一个或一组指定用户所属的全部表、索引和其他对象
imp system/manager file=seapark log=seapark fromuser=seapark
imp system/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)
#将一个用户所属的数据导入另一个用户
imp system/manager file=tank log=tank fromuser=seapark touser=seapark_copy
imp system/manager file=tank log=tank fromuser=(seapark,amy) touser=(seapark1, amy1)
#导入一个表
imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)
#从多个文件导入
imp system/manager file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4) log=paycheck,filesize=1G full=y
#使用参数文件
imp system/manager parfile=bible_tables.par
bible_tables.par参数文件：
#Import the sample tables used for the Oracle8i Database Administrator's
#Bible.
fromuser=seapark touser=seapark_copy file=seapark log=seapark_import
#增量导入(9i中已经取消)
imp system./manager inctype= RECTORE FULL=Y FILE=A

　　

https://blog.csdn.net/fans_z/article/details/100831299

EXP

#完全
EXP SYSTEM/MANAGER BUFFER=64000 FILE=C:FULL.DMP FULL=Y           如果要执行完全导出，必须具有特殊的权限      
#用户模式
EXP SONIC/SONIC    BUFFER=64000 FILE=C:SONIC.DMP OWNER=SONIC           这样用户SONIC的所有对象被输出到文件中。      
#表模式
EXP SONIC/SONIC    BUFFER=64000 FILE=C:SONIC.DMP OWNER=SONIC TABLES=(SONIC)           这样用户SONIC的表SONIC就被导出  

 

Oracle dmp文件导入（还原）到不同的表空间和不同的用户下

impdp解决办法

将test1导出dmp文件数据移到test2用户及表空间test2_data
impdp test2/test245 directory=dpdata1 dumpfile=XXX.dmp 
remap_schema=testl:test2 remap_tablespace=ts_test1_prod:test2_data full=y

exp/imp解决方法

1、dmp文件包含导出时候表空间的信息，导入时候如果没有创建相应的表空间会提示表空间不存在
2、如果几个表导出，导入时将错误忽略 ignore=y即可

3、用transport_tablespace参数，transport tablespace将一个表空间下的数据移到另一个表空间

3.1、使用transport_tablespace导出数据
    exp 用户名 transport_tablespace=y tablespaces=(TEST_TS) file=/opt/b.dmp
    exp test1 file=test1.dmp(导出不加transport_tablespace参数也可以的)

3.2、在另外一台服务器上将数据移到test1相同用户名下的不同表空间test2
    imp test1 transport_tablespace=y file=a.dmp datafiles=/u01/app/oracle/
    oradata/gbksl/test2.dbf ignore=y full=y;

3.3、在另外一台服务器上将test1导出dmp文件数据移到相同表空间下的不同用户test2
    imp test2 file=/db/test1.dmp  fromuser=test1 touser=test2 ignore=y  
    
3.4、在另外一台服务器上将test1导出dmp文件数据移到test2用户下（同时修改用户及表空间名，此方法已经验证过用exp导出，imp导入到不同用户不可行）
    imp test2 transport_tablespace=y file=/opt/test1.dmp fromuser=test1 
    touser=test2 datafiles='/u01/app/oracle/oradata/gbksl/test2.dbf' 
    ignore=y;

　　

https://blog.csdn.net/gty931008/article/details/86160808