20190507 Oracle相关学习记录

1、数据库加一个字段，并且加备注

alter table pay_business_notice add (BACK_URL varchar2(200));
comment on column pay_business_notice.BACK_URL is '支付成功过后通过这个地址告诉业务系统回调成功';

2、导入导出数据库

//cmd执行导出
exp platform2/1@orcl file=C:\\dmp\yngzf.dmp(目标路径)
exp platform2/1@192.168.1.110/orcl file=E:\work\project\yngzf\platform2.dmp(目标路径)

//cmd执行导入
imp platform2/1@orcl file=C:\Users\Administrator\Desktop\platform.DMP log=C:\Users\Administrator\Desktop\log.log FULL=y;

3、创建表空间和用户

导入前要先创建表空间和用户

//创建表空间
create tablespace BOSSSOFT(表空间名)
logging
datafile 'E:\oracle\oradata\orcl\BOSSSOFT.dbf'
size 512M
autoextend on
next 128M maxsize 20480m
extent management local;

//在表空间下创建用户
create user platform2
identified by 1
default tablespace BOSSSOFT
temporary tablespace TEMP
profile DEFAULT;
  
//给用户权限
grant connect to platform2;
grant dba to platform2;
grant resource to platform2;
grant unlimited tablespace to platform2;  

4、使用expdp/impdp导出/导入数据

1、在plsql里创建目录，将dmp文件放在目录下
create directory test1 as 'd:\test\dump';

2、//查看管理理员目录
select * from dba_directories;

3.导入数据库
--expdp导出数据
expdp CQXS/CQXS@orcl directory = "test1" dumpfile ="oracleExpdp.dmp" logfile = oracleExpdp20161103.log
--impdp导入数据
impdp CQXS/CQXS@orck directory="test1" dumpfile="oracleExpdp.dmp" logfile = oracleImpdp20161103.log FULL=y;

5、 无法删除当前已连接用户

查询进程号，最后删除对应的进程、在删除对应的用户

select username,sid,serial# from v$session;
//根据sid,serial删除
alter system kill session'15,10431' 
//删除用户，删除表空间
drop user cqxs cascade;
drop tablespace jfcqxs;

6、 查看oracle的所有用户

select * from dba_users