oracle数据库备份还原脚本详解

alter system kill session 'sid,serial#';

------查询某个表被什么用户锁死了

SELECT /*+ rule */ ername,

decode(l.type,'TM','TABLE LOCK',

'TX','ROW LOCK',

NULL) LOCK_LEVEL,

o.owner,o.object_name,o.object_type,

s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser

FROM v$session s,v$lock l,dba_objects o

WHERE l.sid = s.sid

AND l.id1 = o.object_id(+)

AND ername is NOT NULL

---查询表空间的大小

select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0) ts_size

from dba_tablespaces t, dba_data_files d

where t.tablespace_name = d.tablespace_name

group by t.tablespace_name;

--备份

exp his_user/asd2828@hisgn file=e:\20120629hisgn.dmp CONSISTENT=y full=y exp his_user/asd2828@hisgn file=e:\20120720hisgnuser.dmp CONSISTENT=y owner=his_user buffer=10240000 --建议用此语句

drop user his_testgn cascade; --删除用户

--drop tablespace his_user_data including contents and datafiles cascade constraints; --删除表空间

-----------------创建用户 RESOURCE角色允许用户使用数据库中的存储空间

--CONNECT角色允许用户连接至数据库，并创建数据库对象

grant all privileges to user1 ;--赋予user1所有权限

CREATE USER his_testgn IDENTIFIED BY asd2828;

GRANT CONNECT,RESOURCE,dba TO his_userbj;

GRANT DEBUG ANY PROCEDURE TO his_userbj;

GRANT CREATE SEQUENCE TO MARTIN; --此系统权限允许用户在当前模式中创建序列，此权限包含在CONNECT角色中

--

CREATE USER his_testgn profile default IDENTIFIED BY asd2828 account unlock; GRANT CONNECT,RESOURCE,dba TO his_userbj;

---修改用户密码

alter user his_testgn identified by test11 --将用户his_testgn密码改为test11

alter user scott account unlock identified by tiger;--将用户scott密码改为tiger 并解锁

--select * from v$session

-----锁定用户

alter user his_testgn account lock;

CREATE VIEW VW_SESSION AS SELECT * FROM v$session;

GRANT ALL ON VW_SESSION TO his_userbj; ---一般是把sys下面的视图

VW_SESSION的所有权限给用户his_userbj

--创建表空间

CREATE TABLESPACE HIS_USER_DATA DATAFILE

'E:\ORACLE\HIS_USER_DATA01.DBF' SIZE 1824M

AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE HIS_USER_INDEX DATAFILE

'E:\ORACLE\HIS_USER_INDEX01.DBF' SIZE 1824M

AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

---修改表空间

ALTER USER his_testgn DEFAULT TABLESPACE HIS_TEST_DATA; --将创建的表空间给用户

--添加物理文件当表空间不足时追加表空间

ALTER TABLESPACE HIS_USER_DATA

ADD DATAFILE 'E:\ORACLE\HIS_USER_DATA02.DBF' SIZE 1824M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

ALTER TABLESPACE HIS_USER_INDEX

ADD DATAFILE 'E:\ORACLE\HIS_USER_INDEX02.DBF' SIZE 1824M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

------还原

cmd;

imp his_testgn/asd2828@hisgn file=e:\20120805hisgnuser.dmp fromuser=his_user touser=his_userbj buffer=5400000 ignore=y statistics=none

IMP sys/asd2828@XNJ FROMUSER=HIS_USER TOUSER=HIS_USER FILE =

E:\GuangNIngDB0105.dmp BUFFER = 10240000

exp his_user/asd2828@xnj file = E:\HIS_USER.dmp full=y log=user.log

--select * from dba_tablespaces --查询表空间

---彻底删除表空间以及数据文件