ORACLE 资料整理(一)创建

--创建用户并指定表空间 
create tablespace testTablespace
datafile 'D:\oracle\esunnet.dbf'
size 10m autoextend on;
create user  testUser identified by testUser#1234 default tablespace esunnet;
grant sysdba to  testUser ;
grant connect,resource  to testUser ;
grant create session to testUser ;
ALTER USER testUser ACCOUNT UNLOCK;
commit;

alter user esun default tablespace testTablespace;

--创建数据表空间 
create tablespace testTablespace
logging 
--上一层目录必须有
datafile 'G:\database\testTablespace.dbf' 
size 5m 
autoextend on 
next 5m maxsize unlimited;

 - ***

## 导出

***

windows 上进入plsql上使用exp命令,完全导出

C:\Users\Administrator>exp testUser/testUser#1234@10.33.114.20:1521/voice inctype=complete file=D:\testbak.dmp owner=(testUser) log=D:\explog.log
imp testUser/testUser#1234@orcl   fromuser=testUser touser=otherUser file=D:\Techsung.dmp     buffer=102400 log=D:\explog.log
imp testUser/testUser#1234@orcl    file=D:\Techsung.dmp  FULL=Y   buffer=102400 log=D:\explog.log
imp testUser/testUser#1234@orcl   fromuser=testUser touser=otherUser  file=D:\Techsung.dmp tables=
--指定只导入那几张表(TableName1,TableName2,TableName3,TableName4)  ignore=y  buffer=102400 log=D:\explog.log
---------------------------------导入的时候使表不会导入到其他表空间
--收回无限使用表空间权限
REVOKE UNLIMITED TABLESPACE FROM testTablespace;

--设置默认表空间：
alter user EsunReport default tablespace  testTablespace;

--设置默认的表空间无限配额：
alter user EsunReport quota unlimited on testTablespace;
 
 设置Esunnet用户对其他表空间的quota为0
 alter user testUser quota 0 on users; 
----------------------------------
 
--给用户授予权限 
grant sysdba to  testUser ;
grant connect,resource  to testUser ;
grant create session to testUser ;
grant connect,dba,resource to testUser ;

plsql
–用户名
–密码
–127.0.0.1:1521/实例名
sqlplus Techsung/Techsung@127.0.0.1:1521/orcl
---------用户名/密码@配置名字
connect Techsung/Techsung@Techsung
---------用户名/密码@IP/port/实例名
sqlplus callout/test@127.0.0.1:1521/whtest
sqlplus Techsung/Techsung@127.0.0.1:1521/Techsung
sqlplus WeiXin/WeiXin@127.0.0.1:1521/WeiXin
sqlplus sys/pwd@10.31.114.2:1521/test as sysdba
imp Techsung/Techsung@127.0.0.1:1521/voice file=D:\rel\Techsung.dmp FULL=Y buffer=102400
imp WeiXin/WeiXin@127.0.0.1:1521/voice file=D:\rel\WeiXin.dmp FULL=Y buffer=102400
–用后对用户导入

imp EsunCallOut/EsunCallOut@127.0.0.1:1521/voice fromuser=Techsung touser=EsunCallOut file=D:\rel\Techsung.dmp buffer=102400

imp EsunReport/EsunReport@127.0.0.1:1521/voice touser=EsunReport file=D:\rel\Techsung.dmp FULL=Y buffer=102400

exp Techsung/Techsung inctype=complete file=D:\Techsung.dmp
exp WeiXin/WeiXin inctype=complete file=D:\rel\WeiXin.dmp

oracle修改字符集

select userenv(‘language’) from dual;
conn / as sysdba;
shutdown immediate;
startup mount;
alter system enable restricted session;
alter system set aq_tm_processes=0;
alter database open;
–alter database character set internal_use JA16SJIS;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8;
shutdown immediate;
startup;