[size=medium]**: {}表示变量或注解 []表示可选项 ()表示原始意义 ,-表示省略重复或者循环
[b]1,oracle导出某用户的数据库-非exp工具的exp命令(oracle自带程序):[/b]
exp {user name}/{user password}@<oracle IP>:<port>/{oracle SID} [color=red]owner=({username1 of who the tables you will export belongs},{username2 of who the tables you will export belongs to},-)[/color] file={dmp file storage directory} [log={log file storage directory}] [grants=y] [compress=y] [color=red][full=y][/color]
example1: exp rofine/rofine@192.168.1.60:1521/orcl owner={system,rofine} file=C:\daochu.dmp log=C:\daochu.log compress=y
example2: exp rofine/rofine@orcl owner=rofine file=C:\daochu.dmp log=C:\daochu.log compress=y grants=y
**: 1,grants=y,对于没有拥有到处权限或者非管理员的账户,需要赋予此权限才能进行导出操作;
2,compress=y,是否压缩dmp数据;
3,full=y,将导出所有用户的数据,此时不能指定owner参数,否则导致 冲突模式错误;
4, 其中oracle IP和port,如果是本机上操作并且port是默认的1521,则ip和port都可以不指定,否则,需要指定ip和port,他们之间用冒号':'隔开,port和SID之间用反斜杠'/'隔开;
[b]2,oracle导出某用户的数据库中的某些表-非exp工具的exp命令(oracle自带程序):[/b]
exp {user name}/{user password}@{oracle SID} file={dmp file storage directory} log={log file storage directory} [color=red]tables=({full name of table1, name of table2,-})[/color] [color=red][query=\" where {column name} like '%{value}%'\"][/color] [grants=y]
example1: exp rofine/rofine@orcl file=C:\daochu.dmp log=C:\daochu.log tables={role_privileges,privileges} grants=y
example2: exp rofine/rofine@orcl file=C:\daochu.dmp log=C:\daochu.log tables={role_privileges} query=\" where fk_role_id like '%10%'\" grants=y compress=y
**: 1,grants=y,对于没有拥有到处权限或者非管理员的账户,需要赋予此权限才能进行导出操作;
2,导出具体的某些表数据时,切忌不能使用owner参数来指定要导出的表所属的用户,但是必须在tables参数里面,对table name要写全名,即加上所属用户名,否则将导致 冲突模式错误;
3,如果要对某一个表进行query查询query符合条件的数据记录,则tables里面只能有一个table name,query格式是必须采用‘\’转义;
[b]3, oracle导入dmp数据-非imp工具的imp命令(oracle自带程序):[/b]
imp {user name}/{user password}@{oracle SID} fromuser={username of the user who the import file belongs to} touser={username of the user import into} file={the import file directory} [log={the log file storage directory}] [grants=y]
example: imp rofine/rofine@orcl fromuser=rofine touser=rofine file=C:\daochu.dmp log=C:\daochu.log grants=y[/size]
[b]1,oracle导出某用户的数据库-非exp工具的exp命令(oracle自带程序):[/b]
exp {user name}/{user password}@<oracle IP>:<port>/{oracle SID} [color=red]owner=({username1 of who the tables you will export belongs},{username2 of who the tables you will export belongs to},-)[/color] file={dmp file storage directory} [log={log file storage directory}] [grants=y] [compress=y] [color=red][full=y][/color]
example1: exp rofine/rofine@192.168.1.60:1521/orcl owner={system,rofine} file=C:\daochu.dmp log=C:\daochu.log compress=y
example2: exp rofine/rofine@orcl owner=rofine file=C:\daochu.dmp log=C:\daochu.log compress=y grants=y
**: 1,grants=y,对于没有拥有到处权限或者非管理员的账户,需要赋予此权限才能进行导出操作;
2,compress=y,是否压缩dmp数据;
3,full=y,将导出所有用户的数据,此时不能指定owner参数,否则导致 冲突模式错误;
4, 其中oracle IP和port,如果是本机上操作并且port是默认的1521,则ip和port都可以不指定,否则,需要指定ip和port,他们之间用冒号':'隔开,port和SID之间用反斜杠'/'隔开;
[b]2,oracle导出某用户的数据库中的某些表-非exp工具的exp命令(oracle自带程序):[/b]
exp {user name}/{user password}@{oracle SID} file={dmp file storage directory} log={log file storage directory} [color=red]tables=({full name of table1, name of table2,-})[/color] [color=red][query=\" where {column name} like '%{value}%'\"][/color] [grants=y]
example1: exp rofine/rofine@orcl file=C:\daochu.dmp log=C:\daochu.log tables={role_privileges,privileges} grants=y
example2: exp rofine/rofine@orcl file=C:\daochu.dmp log=C:\daochu.log tables={role_privileges} query=\" where fk_role_id like '%10%'\" grants=y compress=y
**: 1,grants=y,对于没有拥有到处权限或者非管理员的账户,需要赋予此权限才能进行导出操作;
2,导出具体的某些表数据时,切忌不能使用owner参数来指定要导出的表所属的用户,但是必须在tables参数里面,对table name要写全名,即加上所属用户名,否则将导致 冲突模式错误;
3,如果要对某一个表进行query查询query符合条件的数据记录,则tables里面只能有一个table name,query格式是必须采用‘\’转义;
[b]3, oracle导入dmp数据-非imp工具的imp命令(oracle自带程序):[/b]
imp {user name}/{user password}@{oracle SID} fromuser={username of the user who the import file belongs to} touser={username of the user import into} file={the import file directory} [log={the log file storage directory}] [grants=y]
example: imp rofine/rofine@orcl fromuser=rofine touser=rofine file=C:\daochu.dmp log=C:\daochu.log grants=y[/size]