oracle数据备份还原 ,及oracle 11g备份出现空表解决方案



【数据库部分】

1、连接登陆数据库
sqlplus system/elpdb_1Pwd@wizbank

2、如果lms用户及表空间已经存在则使用以下命令删除lms用户和表空间：

drop user lms cascade;
drop tablespace LMS including contents;
drop tablespace LMS_IND including contents;

3、重新使用以下命令来创建lms用户和表空间：

CREATE TABLESPACE  LMS
 DATAFILE 'D:\app\jimmy\oradata\yitai\LMS.DBF' SIZE 5120M reuse autoextend on next 10m
 DEFAULT STORAGE (
  INITIAL 2000K
  NEXT 2000K
  MINEXTENTS 1
  MAXEXTENTS unlimited
  PCTINCREASE 0)
 ONLINE;

CREATE TABLESPACE  LMS_IND
 DATAFILE 'D:\app\jimmy\oradata\yitai\LMS_IND.DBF' SIZE 500M reuse autoextend  on next 10m
 DEFAULT STORAGE (
  INITIAL 200K
  NEXT 200K
  MINEXTENTS 1
  MAXEXTENTS unlimited
  PCTINCREASE 0)
 ONLINE;

4、创建lms账号及oracle密码

CREATE USER LMS IDENTIFIED BY oracle
DEFAULT TABLESPACE LMS
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON LMS;

5、赋予lms账号权限

GRANT connect,resource TO LMS;

grant dba to lms;

6、oracle 11g  设置其值为false  对其以后导入的表，无论其表为空表还是非空表  都分配segment
alter system set deferred_segment_creation=false scope=both;

7、密码无限期
ALTER  PROFILE  DEFAULT  LIMIT  PASSWORD_LIFE_TIME  UNLIMITED;

8、执行quit命令退出。

还原数据库语句

imp system/oracle@yitaigroup fromuser=lms touser=lms  file=E:\datebase_bak\yitai1020\yitai1020.dmp log=E:\datebase_bak\yitai1020\yitai1020.log 

imp system/oracle@yitaigroup fromuser=lms touser=lms full=y ignore=y  file=D:\yitai1020\yitai1020.dmp log=D:\yitai1020\yitai1020.log 

 

备份数据库语句

exp lms/oracle@yitaigroup file=D:\yitai1020\yitai1025.dmp owner=lms log=D:\yitai1020\yitai1025.log statistics=none  

exp lms/oracle@192.168.2.40:1521:gedibbyh file=E:\gedibak\gedi1121.dmp owner=lms log=E:\gedibak\gedi1121.log statistics=none  

查询表空间大小

  SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 1; 

=================================================

oracle11g的新特性，数据条数是0时不分配segment，所以就不能被导出。
 
解决方法：
 
1插入一条数据（或者再删除），浪费时间，有时几百张表会累死的。
2创建数据库之前
使用代码：

Sql代码
alter system set  deferred_segment_creation=false;
 
调整再建表
这两种方都不好
下面是终极方法：
 
先查询一下哪些表是空的：

Sql代码
select table_name from user_tables where NUM_ROWS=0;
 
 
下面我们通过select 来生成修改语句：
Sql代码
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
 
然后就在结果窗口里面生成了下面那些东西：
 
Sql代码
alter table E2USER_STATE allocate extent;
alter table ENTERPRISE_E2USER allocate extent;
alter table ENTERPRISE_INFO_TYPE allocate extent;
alter table ENTERPRISE_MAPMARK allocate extent;
alter table ENTERPRISE_NEEDTASK allocate extent;
alter table ENTERPRISE_PICTURE allocate extent;
alter table ENTERPRISE_REPORT allocate extent;
alter table ENTERPRISE_REPORT_TYPE allocate extent;
alter table ENTERPRISE_TEAM allocate extent;
alter table FROMUSER_ADJUNCT_TARGET allocate extent;
alter table FROMUSER_OFFER allocate extent;
alter table CALENDAR_TYPE allocate extent;
 
 
 
ok 执行上面那些sql，之后再exp吧，那就是见证奇迹的深刻。

建议：
以后新建数据库，最好设置deferred_segment_creation 参数
设置deferred_segment_creation 参数
该参数值默认是TRUE，当改为FALSE时，无论是空表还是非空表，都分配segment。修改SQL语句：
alter system set deferred_segment_creation=false scope=both;

　　需注意的是：该值设置后对以前导入的空表不产生作用，仍不能导出，只能对后面新增的表产生作用。如需导出之前的空表，只能用第一种方法。