ORACLE数据库备份还原常用语句

--删除用户
DROP USER XNCSMIS CASCADE;
DROP USER MIDDLEWARE CASCADE;

--删除表空间
DROP TABLESPACE CSMIS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE CSMISTEMP INCLUDING CONTENTS AND DATAFILES;

--10g创建表空间
create tablespace csmis datafile 'D:\oracle\product\10.2.0\oradata\csmisdb\csmis.dbf' size 500M
autoextend ON next 512k maxsize unlimited
logging extent management local segment space management auto;

--10g创建临时表空间
create temporary tablespace csmistemp tempfile 'D:\oracle\product\10.2.0\oradata\csmisdb\csmistemp.dbf' size 300m;

--9i创建永久表空间
create tablespace csmis datafile 'E:\oracle\data\csmis.dbf' size 300M
autoextend ON next 512k maxsize unlimited minimum extent 512k
default storage(initial 1M next 1M
minextents 1
maxextents unlimited
pctincrease 0)
permanent;

--9i创建临时表空间
create tablespace csmistemp datafile 'E:\oracle\data\csmistemp.dbf' size 200M
autoextend ON next 512k maxsize unlimited minimum extent 512k
default storage(initial 1M next 1M
minextents 1
maxextents unlimited
pctincrease 0)
temporary;

--以system登录,创建用户csmis,同时分配权限
create user xa0224 identified by csmis default tablespace csmis temporary tablespace csmistemp;
grant dba,connect,resource to xa0224;

--创建数据库连接
create shared public database link DL_HRBSEG_UP_MIN
connect to csmis identified by csmis
authenticated by system identified by manager
using 'csmisdb';

--导出全局数据库
exp system/csmis rows=y indexes=n compress=n full=y file=E:\演示工程\csmis-%date:~2,8%.dmp log=E:\演示工程\csmis-%date:~2,8%-log

--导出用户下的数据
exp xacsmis_dev/csmis@csmisdb file=D:\xacsmis-%date:~2,8%.dmp log=D:\xacsmis-%date:~2,8%.log owner=xacsmis_dev

--导入用户下的数据
imp userid=xa0224/csmis file=D:\XA-12-02-24\XA-12-02-24.dmp log=F:\newlog.log fromuser=xa0224 touser=xa0224

exp system/orcl@orcl file=D:\orcl-%date:~2,8%.dmp log=D:\orcl-%date:~2,8%.log owner=system

imp userid=system/orcl@orcl file=D:\orcl-12-07-12.dmp log=F:\orcl-%date:~2,8%.log fromuser=system touser=system123


--修改共享内存
sqlplus sys/change_on_install as sysdba


SQL> show parameter shared_pool_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 83886080


SQL> select 83886080/(1024*1024) from dual;83886080/(1024*1024)
--------------------
80

SQL> alter system set shared_pool_size=104857600 scope=both;
System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.Total System Global Area 269554828 bytes
Fixed Size 451724 bytes
Variable Size 234881024 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.


--修改数据文件大小
ALTER DATABASE DA

TAFILE 'E:\oracle\data\CSMIS.DBF' resize 2000m;
--增加数据文件
ALTER TABLESPACE csmis ADD DATAFILE 'E:\oracle\data\CSMIS2.DBF' SIZE 10m;

--修改用户密码
sqlplus / as sysdba
alter user system identified by csmis;

--批量添加数据
insert into PUB_T_STATION
(VC_STATION$NUMBER,VC_LINEID)
SELECT t.VC_STATION$NUMBER,t.VC_LINEID
FROM csmisgzd.PUB_T_STATION t;

insert into PUB_T_PERSON(VC_ID,VC_NUMBER)
SELECT t.VC_ID,t.VC_NUMBER
FROM PUB_T_PERSON@DL_181 t

--重新执行未通过的视图
select 'ALTER VIEW '||TNAME||' COMPILE;' from tab where tabtype='VIEW' AND TNAME IN
(select OBJECT_NAME from user_objects where STATUS='INVALID' and object_type in ( 'VIEW' ))


--解决不能删除的方法：
insert into pub_t_util$action(vc_table$name) select tname from tab where tname not in (select vc_table$name from pub_t_util$action);



select count(*) from email_t_info t WHERE TO_CHAR(T.VC_DATE,'yyyy-MM')='2009-10';
select count(*) from email_t_info_detail p where p.vc_send_id in(select t.vc_id from email_t_info t WHERE TO_CHAR(T.VC_DATE,'yyyy-MM')='2009-10');

delete from email_t_info_detail p where p.vc_send_id in(select t.vc_id from email_t_info t WHERE TO_CHAR(T.VC_DATE,'yyyy-MM')='2009-12');
delete from email_t_info t WHERE TO_CHAR(T.VC_DATE,'yyyy-MM')='2009-12';