Oracle常用操作

--删除用户

DROP USER XNCSMIS CASCADE;

DROP USERMIDDLEWARE CASCADE;

 

--删除表空间

DROP TABLESPACE CSMIS INCLUDING CONTENTSAND DATAFILES;

DROP TABLESPACECSMISTEMP 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 segmentspace management auto;

 

--10g创建临时表空间

create temporary tablespace csmistemptempfile '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 unlimitedminimum 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 unlimitedminimum extent 512k

default storage(initial 1M next 1M

minextents 1

maxextents unlimited

pctincrease 0)

temporary;

 

--以system登录,创建用户csmis,同时分配权限

create user xa0224 identified by csmisdefault tablespace csmis temporary tablespace csmistemp;

grant dba,connect,resource to xa0224;

 

--创建数据库连接

create shared public database linkDL_HRBSEG_UP_MIN

 connect to csmis  identified bycsmis

 authenticated by system identified by manager

 using 'csmisdb';

 

--导出全局数据库

exp system/csmis rows=y indexes=ncompress=n full=y file=E:\演示工程\csmis-%date:~2,8%.dmplog=E:\演示工程\csmis-%date:~2,8%-log

 

--导出用户下的数据

exp xacsmis_dev/csmis@csmisdb file=D:\xacsmis-%date:~2,8%.dmplog=D:\xacsmis-%date:~2,8%.log owner=xacsmis_dev

 

--导入用户下的数据

imp userid=xa0224/csmisfile=D:\XA-12-02-24\XA-12-02-24.dmp log=F:\newlog.log fromuser=xa0224touser=xa0224

 

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

 

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

 

 

--修改共享内存

sqlplus sys/change_on_install as sysdba

 

 

SQL> showparameter shared_pool_size;

 

NAME               TYPE         VALUE

----------------------------------------------- ------------------------------

shared_pool_size   big integer  83886080

 

SQL> select 83886080/(1024*1024) fromdual;83886080/(1024*1024)

--------------------

80

 

SQL> alter system setshared_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 GlobalArea 269554828 bytes

Fixed Size 451724 bytes

Variable Size 234881024 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

 

 

--修改数据文件大小

ALTER DATABASE DATAFILE'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 whereSTATUS='INVALID' and object_type in ( 'VIEW' ))

 

 

--解决不能删除的方法：

insert intopub_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 WHERETO_CHAR(T.VC_DATE,'yyyy-MM')='2009-10';

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

 

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

delete from email_t_info t WHERETO_CHAR(T.VC_DATE,'yyyy-MM')='2009-12';