首页 > 数据库技术 > 详细

ORACLE备份还原

时间:2020-04-01 00:04:32      阅读:106      评论:0      收藏:0      [点我收藏+]
----------------------------------*备份数据库*----------------------------------
1、WINDOWS(图标)+R --> CMD
2、SQLPLUS HUAWEI/RSGL@127.0.0.1/ORCL
3、SHOW PARAMETER DEFERRED_SEGMENT_CREATION;(如果为TRUE,则将该参数改为FALSE)
4、ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;
5、SELECT ‘ALTER TABLE ‘||TABLE_NAME||‘ ALLOCATE EXTENT;‘ FROM USER_TABLES WHERE NUM_ROWS=0 OR NUM_ROWS IS NULL;
6、EXP HUAWEI/RSGL@127.0.0.1/ORCL FILE=D:\HUAWEI\HUAWEI.DMP LOG=D:\HUAWEI\HUAWEI.LOG OWNER=HUAWEI
---------------------------------*删除原表空间*---------------------------------
1、WINDOWS(图标)+R --> CMD
1、SQLPLUS /NOLOG
2、CONN /AS SYSDBA
3、DROP USER HUAWEI CASCADE;
4、DROP TABLESPACE HUAWEI INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
   DROP TABLESPACE HUAWEI_DATA INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
5、重启ORACLE服务
6、手动删除表空间文件(*.DBF)
7、删除表空间时提示错误
   ORA-02429:无法用于删除强制唯一/主键的索引
 (1)SELECT ‘ALTER TABLE ‘ || OWNER || ‘.‘ || TABLE_NAME || ‘ DROP CONSTRAINT ‘ || CONSTRAINT_NAME || ‘;‘
    FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE IN (‘U‘, ‘P‘) AND (INDEX_OWNER, INDEX_NAME) IN
    (SELECT OWNER, SEGMENT_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = ‘HUAWEI‘);
    表空间:HUAWEI
 (2)执行查询的语句:ALTER TABLE HUAWEI_CPZX.A01 DROP CONSTRAINT SYS_C0051828;
 (3)重新删除表空间:DROP TABLESPACE HUAWEI INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
 (4)重启ORACLE服务
 (5)手动删除表空间文件(*.DBF)
----------------------------------*还原数据库*----------------------------------
1、WINDOWS(图标)+R --> CMD
2、SQLPLUS /NOLOG
3、CONN /AS SYSDBA
4、CREATE TEMPORARY TABLESPACE HUAWEI_TEMP TEMPFILE ‘D:\DataBase\Oracle\TableSpace\HUAWEI_TEMP.DBF‘ SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 20480M EXTENT MANAGEMENT LOCAL;
5、CREATE TABLESPACE HUAWEI_DATA LOGGING DATAFILE ‘D:\DataBase\Oracle\TableSpace\HUAWEI_DATA.DBF‘ SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 20480M EXTENT MANAGEMENT LOCAL;
6、CREATE USER HUAWEI IDENTIFIED BY RSGL DEFAULT TABLESPACE HUAWEI_DATA TEMPORARY TABLESPACE HUAWEI_TEMP;
7、GRANT CONNECT,RESOURCE,DBA TO HUAWEI;
8、IMP FILE=H:\HUAWEI\HUAWEI_2020_03_09_09_37.dmp LOG=H:\HUAWEI\HUAWEI_2020_03_09_09_37.log STATISTICS=NONE USERID=HUAWEI/RSGL@127.0.0.1/ORCL BUFFER=100000000 COMMIT=NO FULL=YES GRANTS=YES IGNORE=YES INDEXES=YES ROWS=YES SHOW=NO CONSTRAINTS=YES

  

ORACLE备份还原

原文:https://www.cnblogs.com/hongwei2085/p/12609516.html

(1)
(1)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!