I need to take dump of a user (including tables, procedures ,etc.) as FILENAME.dmp.
我需要将用户转储(包括表,程序等)作为FILENAME.dmp。
If I create a new user and import that FILENAME.dmp, then everything should be created.
如果我创建一个新用户并导入该FILENAME.dmp,那么应该创建所有内容。
How can I create this dump file?
如何创建此转储文件?
Don't tel me to use the Run > EXP or Run > IMP functions because, due to some problem, that feature is not working for me.
不要打电话给我使用Run> EXP或Run> IMP功能,因为由于某些问题,该功能对我不起作用。
5 个解决方案
#1
EXP (export) and IMP (import) are the two tools you need. It's is better to try to run these on the command line and on the same machine.
EXP(导出)和IMP(导入)是您需要的两个工具。尝试在命令行和同一台机器上运行它们会更好。
It can be run from remote, you just need to setup you TNSNAMES.ORA correctly and install all the developer tools with the same version as the database. Without knowing the error message you are experiencing then I can't help you to get exp/imp to work.
它可以从远程运行,您只需要正确设置TNSNAMES.ORA并安装所有与数据库版本相同的开发人员工具。在不知道您遇到的错误消息的情况下,我无法帮助您使exp / imp工作。
The command to export a single user:
导出单个用户的命令:
exp userid=dba/dbapassword OWNER=username DIRECT=Y FILE=filename.dmp
This will create the export dump file.
这将创建导出转储文件。
To import the dump file into a different user schema, first create the newuser in SQLPLUS:
要将转储文件导入其他用户模式,请首先在SQLPLUS中创建新用户:
SQL> create user newuser identified by 'password' quota unlimited users;
Then import the data:
然后导入数据:
imp userid=dba/dbapassword FILE=filename.dmp FROMUSER=username TOUSER=newusername
If there is a lot of data then investigate increasing the BUFFERS or look into expdp/impdp
如果有大量数据,则调查增加BUFFERS或查看expdp / impdp
Most common errors for exp and imp are setup. Check your PATH includes $ORACLE_HOME/bin, check $ORACLE_HOME is set correctly and check $ORACLE_SID is set
exp和imp的最常见错误是设置。检查你的PATH包含$ ORACLE_HOME / bin,检查$ ORACLE_HOME是否设置正确并检查$ ORACLE_SID是否已设置
#2
Just to keep this up to date:
只是为了保持最新:
The current version of SQLDeveloper has an export tool (Tools > Database Export) that will allow you to dump a schema to a file, with filters for object types, object names, table data etc.
当前版本的SQLDeveloper有一个导出工具(工具>数据库导出),允许您将模式转储到文件,包含对象类型,对象名称,表数据等的过滤器。
It's a fair amount easier to set-up and use than exp and imp if you're used to working in a GUI environment, but not as versatile if you need to use it for scripting anything.
如果您习惯于在GUI环境中工作,那么设置和使用比使用exp和imp更容易,但如果您需要使用它来编写任何脚本,那么它就不是那么通用了。
#3
Just as an update this can be done by using Toad 9 also.Goto Database>Export>Data Pump Export wizard.At the desitination directory window if you dont find any directory in the dropdown,then you probably have to create a directory object.
就像更新一样,这也可以通过使用Toad 9来完成。转到数据库>导出>数据泵导出向导。如果在下拉列表中找不到任何目录,则在desitination目录窗口中,您可能必须创建一个目录对象。
CREATE OR REPLACE DIRECTORY data_pmp_dir_test AS '/u01/app/oracle/oradata/pmp_dir_test';
See this for an example.
请参阅此示例。
#4
Export (or datapump if you have 10g/11g) is the way to do it. Why not ask how to fix your problems with that rather than trying to find another way to do it?
导出(或数据泵,如果你有10克/ 11克)是这样做的方法。为什么不问问如何解决你的问题,而不是试图找到另一种方法呢?
#5
There are some easy steps to make Dump file of your Tables,Users and Procedures:
有一些简单的步骤来制作表,用户和程序的转储文件:
Goto sqlplus or any sql*plus
connect by your username or password通过您的用户名或密码转到sqlplus或任何sql * plus connect
- Now type host it looks like SQL>host.
- Now type "exp" means export.
- It ask u for username and password give the username and password of that user of which you want to make a dump file.
- Now press Enter.
- Now option blinks for Export file: EXPDAT.DMP>_ (Give a path and file name to where you want to make a dump file e.g e:\FILENAME.dmp) and the press enter
- Select the option "Entire Database" or "Tables" or "Users" then press Enter
- Again press Enter 2 more times table data and compress extent
- Enter the name of table like i want to make dmp file of table student existing so type student and press Enter
- Enter to quit now your file at your given path is dump file now import that dmp file to get all the table data.
现在键入host看起来像SQL> host。
现在输入“exp”表示导出。
它要求您输入用户名和密码,并提供要为其创建转储文件的用户的用户名和密码。
现在按Enter键。
现在导出文件的选项闪烁:EXPDAT.DMP> _(将路径和文件名提供给您想要转储文件的位置,例如e:\ FILENAME.dmp)并按下输入
选择“整个数据库”或“表”或“用户”选项,然后按Enter键
再次按Enter键2次表数据并压缩范围
输入表格的名称就像我要制作表学生的dmp文件所以输入学生并按Enter键
输入现在退出您在给定路径的文件是转储文件现在导入该dmp文件以获取所有表数据。
#1
EXP (export) and IMP (import) are the two tools you need. It's is better to try to run these on the command line and on the same machine.
EXP(导出)和IMP(导入)是您需要的两个工具。尝试在命令行和同一台机器上运行它们会更好。
It can be run from remote, you just need to setup you TNSNAMES.ORA correctly and install all the developer tools with the same version as the database. Without knowing the error message you are experiencing then I can't help you to get exp/imp to work.
它可以从远程运行,您只需要正确设置TNSNAMES.ORA并安装所有与数据库版本相同的开发人员工具。在不知道您遇到的错误消息的情况下,我无法帮助您使exp / imp工作。
The command to export a single user:
导出单个用户的命令:
exp userid=dba/dbapassword OWNER=username DIRECT=Y FILE=filename.dmp
This will create the export dump file.
这将创建导出转储文件。
To import the dump file into a different user schema, first create the newuser in SQLPLUS:
要将转储文件导入其他用户模式,请首先在SQLPLUS中创建新用户:
SQL> create user newuser identified by 'password' quota unlimited users;
Then import the data:
然后导入数据:
imp userid=dba/dbapassword FILE=filename.dmp FROMUSER=username TOUSER=newusername
If there is a lot of data then investigate increasing the BUFFERS or look into expdp/impdp
如果有大量数据,则调查增加BUFFERS或查看expdp / impdp
Most common errors for exp and imp are setup. Check your PATH includes $ORACLE_HOME/bin, check $ORACLE_HOME is set correctly and check $ORACLE_SID is set
exp和imp的最常见错误是设置。检查你的PATH包含$ ORACLE_HOME / bin,检查$ ORACLE_HOME是否设置正确并检查$ ORACLE_SID是否已设置
#2
Just to keep this up to date:
只是为了保持最新:
The current version of SQLDeveloper has an export tool (Tools > Database Export) that will allow you to dump a schema to a file, with filters for object types, object names, table data etc.
当前版本的SQLDeveloper有一个导出工具(工具>数据库导出),允许您将模式转储到文件,包含对象类型,对象名称,表数据等的过滤器。
It's a fair amount easier to set-up and use than exp and imp if you're used to working in a GUI environment, but not as versatile if you need to use it for scripting anything.
如果您习惯于在GUI环境中工作,那么设置和使用比使用exp和imp更容易,但如果您需要使用它来编写任何脚本,那么它就不是那么通用了。
#3
Just as an update this can be done by using Toad 9 also.Goto Database>Export>Data Pump Export wizard.At the desitination directory window if you dont find any directory in the dropdown,then you probably have to create a directory object.
就像更新一样,这也可以通过使用Toad 9来完成。转到数据库>导出>数据泵导出向导。如果在下拉列表中找不到任何目录,则在desitination目录窗口中,您可能必须创建一个目录对象。
CREATE OR REPLACE DIRECTORY data_pmp_dir_test AS '/u01/app/oracle/oradata/pmp_dir_test';
See this for an example.
请参阅此示例。
#4
Export (or datapump if you have 10g/11g) is the way to do it. Why not ask how to fix your problems with that rather than trying to find another way to do it?
导出(或数据泵,如果你有10克/ 11克)是这样做的方法。为什么不问问如何解决你的问题,而不是试图找到另一种方法呢?
#5
There are some easy steps to make Dump file of your Tables,Users and Procedures:
有一些简单的步骤来制作表,用户和程序的转储文件:
Goto sqlplus or any sql*plus
connect by your username or password通过您的用户名或密码转到sqlplus或任何sql * plus connect
- Now type host it looks like SQL>host.
- Now type "exp" means export.
- It ask u for username and password give the username and password of that user of which you want to make a dump file.
- Now press Enter.
- Now option blinks for Export file: EXPDAT.DMP>_ (Give a path and file name to where you want to make a dump file e.g e:\FILENAME.dmp) and the press enter
- Select the option "Entire Database" or "Tables" or "Users" then press Enter
- Again press Enter 2 more times table data and compress extent
- Enter the name of table like i want to make dmp file of table student existing so type student and press Enter
- Enter to quit now your file at your given path is dump file now import that dmp file to get all the table data.
现在键入host看起来像SQL> host。
现在输入“exp”表示导出。
它要求您输入用户名和密码,并提供要为其创建转储文件的用户的用户名和密码。
现在按Enter键。
现在导出文件的选项闪烁:EXPDAT.DMP> _(将路径和文件名提供给您想要转储文件的位置,例如e:\ FILENAME.dmp)并按下输入
选择“整个数据库”或“表”或“用户”选项,然后按Enter键
再次按Enter键2次表数据并压缩范围
输入表格的名称就像我要制作表学生的dmp文件所以输入学生并按Enter键
输入现在退出您在给定路径的文件是转储文件现在导入该dmp文件以获取所有表数据。