oracle 导入导出数据

单表导出：

exp educloud/123456  file=/home/oracle/EDU_ORG_USER_RELATION_ZHXY.dmp tables=EDU_ORG_USER_RELATION_ZHXY

单表导入：

imp 'hcloud/"123456"@educloud' file=EDU_ORG_USER_RELATION_ZHXY.dmp FULL=Y

整库导出：

expdp hcloud/123456 directory=DMP_DIR dumpfile=hcloud20201208.dmp logfile=hcloud_20201208.log full=y;

expdp hbcloud/123456 directory=DMP_DIR dumpfile=hbcloud_20201009.dmp logfile=hbcloud_20201009.log content=metadata_only;

默认是ALL

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}，当设置content为all 时,将导出对象定义及其所有数据.为data_only时,只导出对象数据,为metadata_only时,只导出对象定义

正常库导入：

impdp hcloud/'123456' directory=dmp_dir dumpfile=hcloud20201208.dmp logfile=hcloud20201208.log table_exists_action=replace remap_tablespace=DATA_DIDU_TBS:DATA_DIDU_TBS;

报错信息：impdb directory name DMP_DIR is invalid

CREATE DIRECTORY DMP_DIR AS '/home/oracle/dmp';

grant read,write on directory DMP_DIR to hcloud;

数据库导入导出命令：

expdp hcloud/123456 directory=DMP_DIR dumpfile=hcloud_`date +%Y%m%d`.dmp logfile=hcloud_`date +%Y%m%d`.log full=y;

impdp hcloud/123456 directory=DMP_DIR dumpfile=hbcloud_20201009.dmp logfile=hcloud_20201009.log table_exists_action=replace

导出数据报错：

ORA-31631: privileges are required

ORA-39161: Full database jobs require privileges

grant exp_full_database to 用户名;

#导出表结构：（表明，字段名，字段类型，字段描述）

select  t.table_name,t.colUMN_NAME,t.DATA_TYPE  ||  '('  ||  t.DATA_LENGTH || ')',t1.COMMENTS from User_tab_Cols t,User_Col_Comments t1  where t.table_name=t1.table_name and t.column_name = t1.column_name;