Data Import and Export and transaction in Oracle 11g Database

In the use of Oracle databases, Backup recovery is the most critical. When the database fails, it can be restored in a timely manner to reduce unnecessary losses. Next I will briefly introduce the import and export of databases, solutions, tables, and the role of things in the Process of use.
1. Export data tables
[Oracle @ CentOS ~] $ Exp scott/system tables = t file = '/home/oracle/t. dump 'Log = '/home/oracle/t. log'; -- export t table data in scott (tables = can write multiple tables, full = y all export, ignore = y indicates ignore duplicate data)
Export: Release 11.2.0.1.0-Production on Mon Feb 17 16:28:04 2014
Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
Server uses WE8MSWIN1252 character set (possible charset conversion)
About to export specified tables via Conventional Path...
.. Exporting table T 1 rows exported
Export terminated successfully without warnings.
[Oracle @ centos ~] $

2. Import Data Tables
[Oracle @ centos ~] $ Sqlplus
SQL * Plus: Release 11.2.0.1.0 Production on Mon Feb 17 16:29:13 2014
Copyright (c) 1982,200 9, Oracle. All rights reserved.
Enter user-name: scott
Enter password:
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop table t; -- delete the table I just exported
Table dropped.
SQL> commit;
Commit complete.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[Oracle @ centos ~] $ Imp scott/system tables = t file = '/home/oracle/t. dump'; -- import a data table
Import: Release 11.2.0.1.0-Production on Mon Feb 17 16:29:58 2014
Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT: V11.02.00 via conventional path
Import done in US7ASCII character set and AL16UTF16 NCHAR character set
Import server uses WE8MSWIN1252 character set (possible charset conversion)
. Importing SCOTT's objects into SCOTT
. Importing SCOTT's objects into SCOTT
.. Importing table "T" 1 rows imported
Import terminated successfully without warnings.
[Oracle @ centos ~] $ Sqlplus
SQL * Plus: Release 11.2.0.1.0 Production on Mon Feb 17 16:30:03 2014
Copyright (c) 1982,200 9, Oracle. All rights reserved.
Enter user-name: scott
Enter password:
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from t; -- import successful
A
----------
1
SQL>

3. Export all tables under the user (scott)
[Oracle @ centos ~] $ Exp scott/system file = '/home/oracle/scott. dump '; -- import all the tables of scott (exp scott/system owner = user name file = 'path' -- export tables of other users)
Export: Release 11.2.0.1.0-Production on Mon Feb 17 16:34:07 2014
Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
Server uses WE8MSWIN1252 character set (possible charset conversion)
. Exporting pre-schema procedural objects and actions
. Exporting foreign function library names for user SCOTT
. Exporting PUBLIC type synonyms
. Exporting private type synonyms
. Exporting object type definitions for user SCOTT
About to export SCOTT's objects...
. Exporting database links
. Exporting sequence numbers
. Exporting cluster definitions
. About to export SCOTT's tables via Conventional Path...
.. Exporting table DEPT 4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
.. Exporting table EMP 12 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
.. Exporting table SALGRADE 5 rows exported
EXP-00091: Exporting questionable statistics.
.. Exporting table SYS_TEMP_FBT
.. Exporting table T 1 rows exported
.. Exporting table TONG1 14 rows exported
EXP-00091: Exporting questionable statistics.
. Exporting synonyms
. Exporting views
. Exporting stored procedures
. Exporting operators
. Exporting referential integrity constraints
. Exporting triggers
. Exporting indextypes
. Exporting bitmap, functional and extensible indexes
. Exporting posttables actions
. Exporting materialized views
. Exporting snapshot logs
. Exporting job queues
. Exporting refresh groups and children
. Exporting dimensions
. Exporting post-schema procedural objects and actions
. Exporting statistics
Export terminated successfully with warnings.
[Oracle @ centos ~] $

4. Import all tables under the user (scott)
[Oracle @ centos ~] $ Sqlplus
SQL * Plus: Release 11.2.0.1.0 Production on Mon Feb 17 16:39:53 2014
Copyright (c) 1982,200 9, Oracle. All rights reserved.
Enter user-name: scott
Enter password:
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop table t; -- delete a table
Table dropped.
SQL> drop table tong1;
Table dropped.
SQL> commit;
Commit complete.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[Oracle @ centos ~] $ Imp scott/system file = '/home/oracle/scott. dump'; -- import a table under the scott user
Import: Release 11.2.0.1.0-Production on Mon Feb 17 16:40:39 2014
Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT: V11.02.00 via conventional path
Import done in US7ASCII character set and AL16UTF16 NCHAR character set
Import server uses WE8MSWIN1252 character set (possible charset conversion)
. Importing SCOTT's objects into SCOTT
IMP-00015: following statement failed because the object already exists:
"Create table" DEPT "(" DEPTNO "NUMBER (2, 0)," DNAME "VARCHAR2 (14)," LOC "VAR"
"CHAR2 (13) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 6"
"5536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 freelist groups 1 BUFFER_POOL DE"
"FAULT) logging nocompress"
IMP-00015: following statement failed because the object already exists:
"Create table" EMP "(" EMPNO "NUMBER (4, 0)," ENAME "VARCHAR2 (10)," JOB "VARCH"
"AR2 (9)," MGR "NUMBER (4, 0)," HIREDATE "DATE," SAL "NUMBER (7, 2)," COMM "NUM"
"BER (7, 2)," DEPTNO "NUMBER (2, 0) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"
"S 255 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST"
"GROUPS 1 BUFFER_POOL DEFAULT) logging nocompress"
IMP-00015: following statement failed because the object already exists:
"Create table" SALGRADE "(" GRADE "NUMBER," LOSAL "NUMBER," HISAL "NUMBER) P"
"CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 65536 NEXT 104"
"8576 MINEXTENTS 1 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT )"
"Logging nocompress"
IMP-00015: following statement failed because the object already exists:
"Create global temporary table" SYS_TEMP_FBT "(" SCHEMA "VARCHAR2 (32)," OBJEC"
"T_NAME" VARCHAR2 (32), "OBJECT #" NUMBER, "RID" UROWID (4000), "ACTION" CHAR (1"
") On commit preserve rows"
.. Importing table "T" 1 rows imported
.. Importing table "TONG1" 14 rows imported
Import terminated successfully with warnings.
[Oracle @ centos ~] $ Sqlplus
SQL * Plus: Release 11.2.0.1.0 Production on Mon Feb 17 16:40:47 2014
Copyright (c) 1982,200 9, Oracle. All rights reserved.
Enter user-name: scott
Enter password:
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from tab; -- check whether the table has been imported successfully.
TNAME TABTYPE CLUSTERID
-----------------------------------------------
BONUS TABLE
DEPT TABLE
TNAME TABTYPE CLUSTERID
-----------------------------------------------
EMP TABLE
SALGRADE TABLE
SYS_TEMP_FBT TABLE
T TABLE
TONG1 TABLE
16 rows selected.
SQL>

5. Export the entire database
[Oracle @ centos ~] $ Exp system/system file = '/home/oracle/database. dump' log ='/home/oracle/database. log'; -- export the entire database

6. Transactions
1. What is a transaction?
A transaction is used to ensure data consistency. It consists of a group of related dml statements. The dml statements in this group must be successful or fail. For example, online transfer is typically handled by transactions to ensure data consistency.
Ii. Transactions and locks
When a transaction is executed, Oracle locks the target table to prevent other users from modifying the table.
Iii. Commit transactions
You can use the commit statement to submit a transaction. After the commit statement is executed, the transaction changes are confirmed, the transaction ends, the storage point is deleted, and the lock is released. After the transaction ends with the commit statement, other sessions can view the new data after the transaction changes.
Iv. rollback transactions
Before introducing the rollback transaction, let's first introduce the concept and function of the savepoint. The save point is a point in the transaction and is used to cancel some transactions. When the transaction ends, all save points defined by the firm are automatically deleted. When rollback is executed, you can roll back to the specified vertex by specifying a save point.
5. Read-only transactions
A read-only transaction is a transaction that allows only query operations, but not any other dml operations. Using a read-only transaction, you can only obtain data at a certain time point.
Set read-only transactions:
SQL> savepoint qw; -- create a save point
Savepoint created.
SQL> delete from t; -- delete table data, cannot submit
2 rows deleted.
SQL> select * from t;
No rows selected
SQL> rollback to qw; -- roll back to the storage point
Rollback complete.
SQL> select * from t; -- data is returned
A
----------
1
2
SQL> set transaction read only; -- database tables can only be queried and cannot be modified or deleted