Import, export between Oracle databases, copy database table structure (no data) __python

See if Jdbc:oracle:thin:@192.168.88.205:1521:test can access, Telnet 192.168.88.205 1521 try

1 Creating user statements, assigning permissions

Create user username identified by password;
Grant CONNECT,RESOURCE,DBA to user name;

2 Import Export Database

   Exp system_v1/12345@196.168.1.10:1521/iwms  file=d:/wenjian.dmp owner= (SYSTEM_V1) 
     imp system_v2/88075998@ 196.168.1.10:1521/ORCL full=y  file=d:/wenjian.dmp ignore=y

3 How to empty a database

Pilot into a database, and then use this statement to get all the deleted table records of statements, batch execution, with the Plsql export function, export these batch SQL

Select ' Delete Table ' | | table_name| | '; ' from User_tables

4 "| |" Role in Oracle or SQL

Connection strings or fields in query results, such as Select Column1| |       Column2 from table; Select ' 123 ' | | | 456 ' | | ' 789 ' from dual;

Data export:
 1 completely export database test, user Name System Password Manager exported to D:\daochu.dmp
   exp system/ manager@196.168.1.10:1521 file=d:\daochu.dmp full=y
 2 export tables for system users and SYS users in the database
   exp system/ manager@196.168.1.10:1521 file=d:\daochu.dmp owner= (system,sys)
 3 export tables in the database table1, table2 exports
   Exp system/manager@196.168.1.10:1521 file=d:\daochu.dmp tables= (table1,table2)
 4 The fields in the table table1 in the database filed1 to Data from "00" is exported
   exp system/manager@196.168.1.10:1521  file=d:\daochu.dmp tables= (table1) query=\ " where filed1 like  ' 00% '
 
     above is a common export, for compression I do not care, with WinZip DMP file can be very good compression.
                      but add compress=y  after the above command.

Import of data
1 Import the data from D:\DAOCHU.DMP into the test database.
Imp system/manager@test file=d:\daochu.dmp
There may be something wrong with it, because some tables already exist, and then it complains, and the table is not imported.
Just add ignore=y to the back.
2 Import the table table1 in D:\daochu.dmp
Imp system/manager@test file=d:\daochu.dmp tables= (table1)

Basically, the import export above is sufficient. There are a lot of situations where I delete the table completely and import it.

Attention:
You need to have enough permissions, you don't have enough permissions, it will prompt you.
Database can be connected to. You can use tnsping test to get the database test to connect.

Data export:
Exp hkb/hkb@@196.168.1.10:1521 full=y file=c:\orabackup\hkbfull.dmp log=c:\orabackup\hkbfull.log;

Export considerations: Exports the current user's data, and the current user, if he has DBA authority, exports all data.

Data import between users with the same name:
Imp hkb/hkb@@196.168.1.10:1521 file=c:\orabackup\hkbfull.dmp log=c:\orabackup\hkbimp.log full=y

Data import between different names:
Imp system/test@@196.168.1.10:1521 fromuser=hkb touser=hkb_new file=c:\orabackup\hkbfull.dmp

Log=c:\orabackup\hkbimp.log; --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------

1. Copy table structure and its data: CREATE TABLE Table_name_new as SELECT * from Table_name_old

2. copy-only table structure: CREATE TABLE table_name_new as SELECT * from Table_name_old where 1=2; Or: Create table table_name_new like Table_name_old

3. Copy only table data: If two table structure is the same: INSERT INTO table_name_new SELECT * Table_name_old

If two table structures are different: INSERT INTO table_name_new (Column1,column2 ...) select Column1,column2 ... from Table_name_old