Use of Oracle Database IMP/EXP commands

1. Export the database as follows:
2. Enter cmd in the command line to enter the DOS window.
3. Enter exp ccsbi/ccsbi_jyfx@ Ccsbi_55File = d: daochu. dmp ignore = y
4. To export the entire database.
6. Data export:
7.  1Export the database TEST completely, and the username system Password manager is exported to D: daochu. dmp.
8. Exp ccsbi/ccsbi_jyfx@ Ccsbi_55File = d: daochu. dmp full = y
9.  2Export the tables of system users and sys users in the database
10. Exp ccsbi/ccsbi_jyfx@ Ccsbi_55File = d: daochu. dmp owner = (system, sys)
11.  3Export the inner_policy and policy_staff_relat tables in the database.
12. Exp aichannel/aichannel@ TESTDB2File = d: datanewsmgnt. dmp tables =
14. (Inner_policy, policy_staff_relat)
16.  4Set the field filed1 in table 1 in the database"00"Export data with headers
17. Exp system/manager@ TESTFile = d: daochu. dmp tables = (table1) query ="Where filed1 like '20140901 '"
19. The above is a commonly used export. For compression, you can use winzip to compress the dmp file.
20. You can also add compress = y to the command above.
22. Data Import
23.  1Import the data in D: daochu. dmp to the TEST database.
24. Imp system/manager@ TESTFile = d: daochu. dmp
25. Imp aichannel/aichannel@ HUSTFull = y file = d: datanewsmgnt. dmp ignore = y
26. The above may be a problem, because some tables already exist, and then it will report an error, the table will not be imported.
27. Add ignore = y to the end.
28.  2Import table 1 in d: daochu. dmp
29. Imp system/manager@ TESTFile = d: daochu. dmp tables = (table1)
31. The preceding import and export operations are sufficient. In many cases, you must first completely delete the table and then import it.
33. Note:
34. If the operator has sufficient permissions, a prompt is displayed.
35. Databases can be connected. You can use tnsping TEST to obtain whether the database TEST can be connected.
37. Appendix 1:
38. Add data import permissions to users
39. First, start SQL * puls
40. Second, log in with system/manager
41. Third, create user username identified by password (this step can be omitted if you have already created a user)
42. Fourth, grant create user, drop user, alter user, create any view,
43. Drop any view, EXP_FULL_DATABASE, IMP_FULL_DATABASE,
44. DBA, CONNECT, RESOURCE, create session to Username
45. Fifth, run-cmd-to enter the directory where the dmp file is located,
46. Imp userid = system/manager full = y file = *. dmp
47. Or imp userid = system/manager full = y file = filename. dmp
49. Example:
50. F: WorkOracle_Databackup> imp userid = test/test full = y file = inner_policy.dmp
52. Screen Display
53. Import: Release8.1.7.0.0-Production on Thursday2Month16 16:50:05 2006
54. (C) Copyright2000Oracle Corporation. All rights reserved.
56. Connect to: Oracle8i Enterprise Edition Release8.1.7.0.0-Production
57. With the Partitioning option
58. JServer Release8.1.7.0.0-Production
60. EXPORT from EXPORT: V08.01.07Created File
61. The ZHS16GBK Character Set and ZHS16GBK NCHAR character set have been imported.
62. The export server uses the UTF8 NCHAR character set (possible ncharset conversion)
63. . Importing AICHANNEL object to AICHANNEL
64. ... Importing table..."Inner_policy"4Row imported
65. Prepare to enable constraints...
66. Import is terminated successfully, but a warning is displayed.
69. Appendix 2:
70. Oracle cannot directly change the table owner. Export/Import can be used to achieve this purpose.
71. First create import9.par,
72. The command is as follows: imp parfile =/filepath/import9.par
73. The content of import9.par is as follows:
74. FROMUSER = TGPMS
75. TOUSER = TGPMS2 (Note: Change the table owner from FROMUSER to TOUSER. Users of FROMUSER and TOUSER can be different.
77. )
78. ROWS = Y
79. INDEXES = Y
80. GRANTS = Y
81. CONSTRAINTS = Y
82. BUFFER =409600
83. File =/backup/ctgpc_20030623.dmp
84. Log =/backup/import_20030623.log
86. Note:
87. 1. Add feedback = to the import/export command.1000Allows the process to display an increasing number of "..." to change the previous blinking cursor