About orcal Database Import and Export

Orcal Data Import and Export

1. Database Backup: Start Menu-run cmd SQL statement for database backup: EXP gdstd/gdstd@gdstd_19.16.192.42 file = D:/gdstd2006111_dmp 2. Import SQL statement: import the pub_menu.dmp under the D disk as an example: IMP gdstd/gdstd@gdstd_19.16.192.42 full = y file = D:/pub_menu.dmp Note: gdstd/gdstd@gdstd_19.16.192.42 is used to connect to the database, the writing format is according to the user name/password @ Database Service name format, the application server to connect to the Database Service name is gdstd_19.16.192.42, therefore, if you perform backup and other operations on the application server, the Database Service name is gdstd_19.16.192.42. If you perform backup and other operations on your own machine, it depends on the Database Service name of the Oracle client installed on the machine. For example, if the database service name on the machine is gdstd, it should be written as gdstd/gdstd @ gdstd.PLSQL/Oracle data backup and import Two methods: 1. Code method (SCRIPT)-in software development 2. Back up data from the server in binary mode (Oracle built-in function) Method 1: Code (SCRIPT)-in software development // Only export the table structure Open PLSQL-> tool-> Export user object-> select the table to be exported, and remove the include storage, including the owner's check-> select the export path and name it-> Click Export // Export the table structure and data Open PLSQL-> tool-> export table-> select the table to be exported, select SQL insert, check the deleted table-> export path and name it-> Export // Import table structure and data Open PLSQL-> Tools-> Import tables-> select SQL insert-> Select File Path-> point import-> below to refresh the page. Method 2: Back up data from the server in binary mode (Oracle built-in function) Export: EXP Scott/tiger file = E:/liukai. dmp on the console Export all data, relatively slow Import: IMP Scott/tiger file = E:/liukai. dmp on the console All data is imported, which is relatively slow.

 

1. log on to a computer with an existing database, log on to PLSQL developer with a user with permissions, and select

Tools --> export tables..., you can export files in two formats (*. DPM and *. PVDF)

2. log on to the computer on which you want to import the database, log on to PLSQL developer with the same permissions as the first computer, and select the menu

In the "tools --> Import tables..." column, select the copied (*. DPM or *. PVDF) file for import. In the "import" dialog box

The command line is imp.exe, which can be executed in the bin directory of oracle.

 

 

1. import data from D:/daochu. dmp to the test database.
IMP system/manager @ test file = D:/daochu. dmp
IMP aichannel/aichannel @ HUST full = y file = D:/data/newsmgnt. dmp ignore = y
The above may be a problem, because some tables already exist, and then it will report an error, the table will not be imported.
Add ignore = Y to the end.
2. Import table 1 in D:/daochu. dmp
IMP system/manager @ test file = D:/daochu. dmp tables = (Table1)
 
The preceding import and export operations are sufficient. In many cases, you must first completely delete the table and then import

 

 

There are three ways to export table data using PL/SQL developer: Oracle export, SQL insert, PL/SQL developer

 

The table structure and index structure remain unchanged after the "Oracle export" method is used for export and re-import. The other two methods both change the index type. In addition
The SQL insert statement can be imported into SQL statements and can be opened in a text editor.
PL/SQL developer can also export data, but cannot open the text editor.

Differences:
Oracle export, SQL insert, PL/SQL developer
The first is the file format exported as. dmp. The. dmp file is binary and can be cross-platform and contain permissions, which is very efficient and widely used.

The second type is exported as a. SQL file, which can be viewed in a text editor and has good versatility, but is not as efficient as the first type. It is suitable for importing and exporting small data volumes. Note that there cannot be large fields (blob, clob, long) in the table. If so, the system will prompt that the data cannot be exported (the prompt is as follows:
Table contains one or more Long Columns cannot export in SQL format, user PL/SQL developer format instead), which can be exported in the first and third ways.

The third type is exported to. PVDF format, which is the file format of PL/SQL developer. Only PL/SQL developer can import and export the file by itself. You cannot view the file in the editor,

 

Export data to an Excel file:

Select a data table -- query data -- select the data to be exported -- Right-click Copy to excel

Import Excel data to the database:

1. Prepare the data to be imported in Excel. Specific Operation: In Excel, set the first column as an empty column. The columns after the second column should correspond to the fields of the data table to be imported.
2. log on to PLSQL developer, find the database table for which you want to import data, right-click -- edit data, enter the data edit table dialog box, and click an empty row on the left.
3. copy the data records in the Excel table and paste the data into the table column displayed by PLSQL developer in the previous step.
4. Click "Post" in PLSQL developer.

Note: PLSQL developer does not know "-"