oracle数据库备份还原

    进入sqlplus界面登录数据库 : conn 用户名/密码
    
(1)删除用户和表空间
/*步骤一:删除用户USER*/
DROP USER ETL_EXT_KY CASCADE;
COMMIT;
    /*删除不掉时*/
    SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME = 'ETL_EXT_KY';
    ALTER SYSTEM KILL SESSION '9,16585';
    ALTER SYSTEM KILL SESSION '411,1807';
    ALTER SYSTEM KILL SESSION '46,21725';
    ALTER SYSTEM KILL SESSION '221,7440';
    ALTER SYSTEM KILL SESSION '231,15475';
    ALTER SYSTEM KILL SESSION '234,1490';
    ALTER SYSTEM KILL SESSION '391,10664';
    ALTER SYSTEM KILL SESSION '398,1678';
    ALTER SYSTEM KILL SESSION '596,17417';
    ALTER SYSTEM KILL SESSION '606,4638';
/*步骤二:删除表空间TABLESPACE*/
DROP TABLESPACE ETL_EXT_KY INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
COMMIT;
    /*删除不掉时，查找所有的表空间*/
    select * from v$tablespace;
    alter database datafile '/opt/oracle/oradata/devdata/ETL_EXT_KY.DBF' offline drop;
    drop tablespace ETL_EXT_KY;
(2)创建表空间
CREATE TABLESPACE ETL_EXT_KY
DATAFILE '/opt/oracle/oradata/ETL_EXT_KY.DBF' SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
COMMIT;
(3)创建用户及授权表空间
CREATE USER ETL_EXT_KY PROFILE DEFAULT IDENTIFIED BY eplugger DEFAULT TABLESPACE ETL_EXT_KY TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
COMMIT;
GRANT CONNECT,RESOURCE,DBA TO ETL_EXT_KY;
COMMIT;
(4)数据库备份,先断开用户连接
select 'alter table ', S.TABLE_NAME, ' allocate extent;'  FROM USER_TABLES S WHERE S.SEGMENT_CREATED = 'NO';--如果有查询出来的结果执行并提交查询出来的结果，没有则不必执行。
exp ETL_EXT_KY/eplugger@192.168.1.9/orcl file=/keyan/oracle/app/oradata/orcl/ ETL_EXT_KY_EXP-HZL20190429.dmp log=/keyan/oracle/app/oradata/orcl/ ETL_EXT_KY_EXP-HZL20190429.logs
(5)数据库还原,先断开用户连接
imp ETL_EXT_KY/eplugger@192.168.1.9/orcl file=/opt/oracle/oradata/ETL_EXT_KY.dmp full=y;