进入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;
oracle数据库备份还原
最新推荐文章于 2024-04-05 10:16:17 发布