ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > oracle 11G数据库之数据导入导出与事物

oracle 11G数据库之数据导入导出与事物

Oracle 作者：chenyi18 时间：2014-02-20 23:03:15 0 删除 编辑

     正在使用oracle数据库中对备份恢复是最关键的，当数据库泛起故障时可以或许实时恢复减少没必要要的益失。下里我将简单介绍一下导进导出数据库,方案,表,和正在使用的过程当中事物的感导。

1.导出数据表

[oracle@centos ~]$ exp scott/system tables=t file="/home/oracle/t.dump" log="/home/oracle/t.log";    --导出scott用户中的t表数据(tables=可以或许写多个表,full=y  齐部导出,ignore=y  示意疏忽重复的数据)
Export: Release 11.2.0.1.0 - Production on Mon Feb 17 16:28:04 2014
Copyright (c) 1982, 2009, 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.导进数据表

[oracle@centos ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 17 16:29:13 2014
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Enter user-name: scott
Enter password:
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
SQL> drop table t;    --删除我方才导出的表
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: Release 11.2.0.1.0 - Production on Mon Feb 17 16:29:58 2014
Copyright (c) 1982, 2009, 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, 2009, Oracle.  All rights reserved.
Enter user-name: scott
Enter password:
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
SQL> select * from t;    --成功导进
A
----------
1
SQL>

3.导出用户下的一切表(scott)

[oracle@centos ~]$ exp scott/system   file="/home/oracle/scott.dump";   --导进scott本身的一切表(exp scott/system owner=用户名 file="路径"  --导出别的用户下的表)
Export: Release 11.2.0.1.0 - Production on Mon Feb 17 16:34:07 2014
Copyright (c) 1982, 2009, 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.导进用户下的一切表(scott)

[oracle@centos ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 17 16:39:53 2014
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Enter user-name: scott
Enter password:
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
SQL> drop table t;    --删除表
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";   --导进scott用户下的表
Import: Release 11.2.0.1.0 - Production on Mon Feb 17 16:40:39 2014
Copyright (c) 1982, 2009, 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, 2009, Oracle.  All rights reserved.
Enter user-name: scott
Enter password:
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
SQL> select * from tab;     --审查表被导进成功
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.导出整个数据库

[oracle@centos ~]$ exp system/system file="/home/oracle/database.dump" log="/home/oracle/database.log";     --导出整个数据库

6.事物

一、甚么是事务
      事务用于保证数据的划一性，它由一组相干的dml语句组成，该组的dml语句要么齐部成功，要们齐部失败。如：网上转帐就是典型的要用事务去向理，用以保证数据的划一性。

2、事务和锁
      当执止事务操纵时，Oracle会正在被感导的表上加锁，防止其他用户改表。

3、提交事务
      正在执止使用commit语句可以或许提交事务，当执止了commit语句后，会确认事务的刷新，竣事事务，删除保存面，开释锁。当使用commit语句竣事事务之后，其他会话将可以或许审查到事务刷新后的新数据。

4、回退事务
     正在介绍回退事务前，我们先介绍一下保存面（savepoint）的见解和感导，保存面是事务中的一面，用于挨消局部事务，当竣事事务时，会主动的删除该事务所界说的一切保存面。当执止rollback时，经过过程拟订保存面可以或许回退到指定的面。

五、只读事务
     只读事务是指只容许执止查询的操纵，而没有容许执止任何其他dml操纵的事务，使用只读事务可以或许确保用户只能得到某韶光面的数据。
设置只读事务：

SQL> savepoint qw;     --创建保存面
Savepoint created.
SQL> delete from t;     --删除表中的数据，没有能commit提交
2 rows deleted.
SQL> select * from t;
no rows selected
SQL> rollback to qw;    --回退到保存面
Rollback complete.
SQL> select * from t;    --数据回去了
A
----------
1
2
sql>set transaction read only;    --数据库的表只能查询没有能点窜和删除

 

本文出自 “一起走过的日子” 博客，开尽转载！


<!-- 正文结束 -->

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24125337/viewspace-1114906/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 没有了~
下一篇： 没有了~
请登录后发表评论 登录
全部评论
chenyi18

注册时间：2010-06-20

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593