Oracle11g学习笔记

一、sys账号的默认密码登录

SYS用户是Oracle中权限最高的用户，而SYSTEM是一个用于数据库管理的用户。

win+R，输入cmd，回车。打开命令台，输入sqlplus，进入oracle登录界面。用户名为sys，其密码可以为

1. / as sysdba

1. as sysdba

1. change_on_install as sysdba

1. ...

参考网址：

https://www.shuzhiduo.com/A/A7zg3Zq1z4/

二、解除密码180天过期限制

Oracle数据库 11g默认密码过期时间为180天过期

更改语句，执行即可：

alter profile default limit password_life_time unlimited;

如果密码已经过期，则进行如下修改

alter user 用户 identified by 密码 account unlock;

三、字符集的转换

现象：使用imp导入数据库时 报错 ORA-12899: 列XXX的值太大，字符集问题 通常是数据库为utf-8 dump文件为gbk 或gb23112情况。

方法如下：

1. 可以使用如下命令查看数据库字符集

select userenv('language') from dual;

结果：显示SIMPLIFIED CHINESE_CHINA.AL32UTF8，需要修改为 SIMPLIFIED CHINESE_CHINA.ZHS16GBK

1. ssh连接数据库（cmd中操作）

sqlplus /as sysdba

1. 按顺序执行如下语句：

shutdown immediate;  --停止数据库
startup mount; --把database重启到可更改状态 装载数据库，打开控制文件
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
shutdown immediate; --停止数据库
startup; --启动数据库

注：字符集AL32UTF8=》ZHS16GBK。如需要更改回去，则将上面语句中的ZHS16GBK替换为AL32UTF8即可。

四、表空间的创建与删除

1. 创建表空间

create tablespace 表空间名称 
datafile 表空间存放路径(非文件夹，注意''攘括) 
size 50m 
autoextend on 
next 50m maxsize 1024m;

注：1024m为表空间最大存储容量（由原表空间存储大小决定）。示例：

create tablespace name 
datafile 'D:\oracle\space\name.dbf' 
size 50m 
autoextend on 
next 50m maxsize 1024m;

1. 删除表空间

drop tablespace 表空间名称 including contents and datafiles cascade constraint;

注：会将表空间及表空间中的数据全部删除，慎重删除。

修改默认表空间

alter user 用户名 default tablespace 表空间;

五、用户的创建与删除

1. 创建用户并赋予权限

create user 用户名 identified by 密码 default tablespace 表空间;
grant connect,resource,dba to 用户名;
alter user 用户名 quota unlimited on 表空间;

注：如无需指定表空间，则删除参数tablespace。

示例:

CREATE USER test IDENTIFIED BY 123456 default tablespace AAA;
grant connect,resource,dba to test;
alter user test quota unlimited on AAA;

1. 删除用户

drop user 用户名 cascade;

注：需要有删除用户的权限，且当前用户不能是连接状态。

示例:

drop user test cascade;

六、数据的导入导出

查询当前数据库实例名：

select instance_name from v$instance;

参考网址：

https://www.cnblogs.com/songdavid/articles/2435439.html

1. 导出==>exp

本地环境：

exp 账号/密码@实例名 file=文件导出路径 log=日志导出路径 选择模式
-- 如果需要远程服务器数据库地址 则可以这样写
-- 账号/密码@//IP地址:端口/实例名

示例（cmd中操作）：

1. 导出整个库（必须具有DBA权限）（导出了整个库，而非这个用户，不建议使用）

exp app/123456@orcl file=D:\ceshi\exp.dmp log=D:\ceshi\exp.log full=y

1. 导出指定表空间（常用）

exp app/123456@orcl file=D:\ceshi\exp.dmp log=D:\ceshi\exp.log tablespaces=app_space 
-- exp app/123456@//192.168.0.x:1521/orcl file=D:\ceshi\exp.dmp log=D:\ceshi\exp.log tablespaces=app_space 

1. 导出指定用户（非DBA用户不可导出其他表）

exp app/123456@orcl file=D:\ceshi\exp.dmp log=D:\ceshi\exp.log owner=(app,app2)

参考网址：

Oracle 只导出某个用户下的表及数据

1. 导入==>imp

本地环境：

imp 用户/密码@实例名 file=备份文件绝对路径 ignore=y tablespaces=表空间名称 选择模式

注：ignore=y相当于，如果没有的表，创建并导入数据，如果已经有的表，忽略创建的，但不忽略导入。tablespaces参数可除去。

示例（cmd中操作）：

1. 导入所有

imp test/123456@orcl ignore=y file=D:\ceshi\exp.dmp log=D:\ceshi\imp.log tablespaces=AAA full=y

1. 导入指定用户（建议使用）

imp test/123456@orcl ignore=y file=D:\ceshi\exp.dmp log=D:\ceshi\imp.log tablespaces=AAA fromuser=mes_app touser=test

注：fromuser=原用户名 touser=现用户名。

当出现ORA-00959: 表空间 'xxxx(原表空间)' 不存在时，一般是发生有clob字段在表上。此时推荐将当前表空间名称改成与原表空间一致。

七、数据的泵导入导出

关于directory：

查询directory目录

select * from dba_directories;

创建或者修改 directory目录：

create[or replace] directory 虚拟目录 as 物理目录路径;

赋予用户 directory目录的读写权限

grant read,write on 虚拟目录 to 用户;

删除directory目录

drop directory 虚拟目录;

1. 泵导出==>expdp

本地环境：

操作步骤：

首先在C盘（最好放在C盘，放在其他盘容易出现权限不足等无法读取写入的问题）创建物理目录，接下来ssh连接数据库执行sql语句。

create directory 虚拟目录 as 物理目录路径; --创建临时文件
grant read,write on 虚拟目录 to 用户;

然后cmd中执行导出语句（两种模式，schemas导出用户，tablespaces导出表空间）

expdp '用户/密码@实例名 as sysdba' schemas=导出用户[or tablespaces=表空间名称] directory=虚拟目录 dumpfile=导出文件.DMP logfile=导出日志.log

导出结束后，删除对应虚拟文件夹，物理文件夹。

drop directory 虚拟目录;

示例：

• C盘创建物理目录 C:\expdp

• ssh连接数据库执行sql语句

create directory data_expdp as 'C:\expdp';
grant read,write on directory data_expdp to expdp_user;

• cmd中执行导出（模式二选一）

导出用户下的表：

expdp 'expdp_user/123456@orcl as sysdba' schemas=expdp_user directory=data_expdp dumpfile=expdp_user.dmp logfile=expdp.log

导出表空间下的表：

expdp 'expdp_user/123456@orcl as sysdba' tablespaces=expdp_space directory=data_expdp dumpfile=expdp_user.dmp logfile=expdp.log

• 导出完毕删除虚拟、物理目录

drop directory data_expdp;

1. 泵导入==>impdp

本地环境：

操作步骤：

首先在C盘（最好放在C盘，放在其他盘容易出现权限不足等无法读取写入的问题）创建物理目录，接下来ssh连接数据库执行sql语句。

create directory 虚拟目录 as 物理目录路径; --创建临时文件
grant read,write on 虚拟目录 to 用户;

然后cmd中执行导入语句（两种模式，full=y导入所有，remap_tablespace导入指定表空间）

impdp '用户/密码@实例名 as sysdba' remap_tablespace=(原表空间1名称:现表空间名称,原表空间2名称:现表空间名称,...)[or full=y] remap_schema =原用户:现用户 directory=虚拟目录 dumpfile=导入文件.DMP logfile=导入日志.log

导入结束后，删除对应虚拟文件夹，物理文件夹。

drop directory 虚拟目录;

示例：

• C盘创建物理目录 C:\impdp

• ssh连接数据库执行sql语句

create directory data_impdp as 'C:\impdp';
grant read,write on directory data_impdp to impdp_user;

• cmd中执行导入（模式二选一）

导入所有：

impdp 'impdp_user/123456@orcl as sysdba' full=y remap_schema =expdp_user:impdp_user directory=data_impdp dumpfile=EXPDP.DMP logfile=impdp.log

导入指定表空间：

impdp 'impdp_user/123456@orcl as sysdba' remap_tablespace=expdp_space:impdp_space remap_schema=expdp_user:impdp_user directory=data_impdp dumpfile=EXPDP.DMP logfile=impdp.log

注：当存在多个导出表空间导入至指定表空间时

impdp 'impdp_user/123456@orcl as sysdba' remap_tablespace=(USERS:impdp_space,expdp_space:impdp_space) remap_schema=expdp_user:impdp_user directory=data_impdp dumpfile=EXPDP.DMP logfile=impdp.log 

• 导出完毕删除虚拟、物理目录

drop directory data_impdp;

八、导出空表时存在少表情况

因为ORACLE 11G中有个新特性，当表中无数据时，不分配segment，以节省空间。这会导致Oracle 11g在导出空表时存在少表的情况，所以在数据迁移之前，需要针对这个特性修改一下配置。（必要）

1. 修改配置：

alter system set deferred_segment_creation=false;

1. 检查该用户有无空表

select 'alter table '||table_name||' allocate extent;'
from user_tables where num_rows=0 or num_rows is null;

1. 如果结果窗口生成了以下类似的sql语句

alter table E2USER_STATE allocate extent;
alter table ENTERPRISE_INFO_TYPE allocate extent;
........

1. 将其全部复制后，执行这些sql语句，之后开始执行数据的迁移，备份，导出等操作。

1. 恢复配置

alter system set deferred_segment_creation=true;

九、insert插入语句

1. 单条插入

insert into 表名(列名1,列名2,列名3.....) values (值1,值2,值3.....);

示例：

id	name	age	birthday
VARCHAR2	VARCHAR2	NUMBER	DATE
1	null	12	2022-01-13

insert into Student(id,name,age,birthday) values ('1',null,12,to_date('2022-01-13','yyyy-mm-dd'));

注：to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')。

常见问题：

在笔者使用过程中，遇见插入带单引号的字符串时会遇见SQL 错误: ORA-00917: 缺失逗号。此时需要更改一下语法。

--错误方式
insert into text(id,name,sex) values('2','zl','a'd');
--正确方式
insert into text(id,name,sex) values('2','zl',q'(asda'123)');
--此时sex列插入的值为asda'123

参考网址：

Oracle之TO_DATE用法详解_oracle_脚本之家

1. 一张表的数据插入到另外一张表

如果结构完全一致：

insert into 表1 select * from 表2;

如果结构不一致

insert into 表1(列1,列2,列3) select 列a,列b,列c from 表2;

1. 对一张表进行批量插入

方法a：

insert all into 表1(列1,列2,列3) 
into 表1(列1,列2,列3) values (值1,值2,值3) 
into 表1(列1,列2,列3) values (值1,值2,值3) 
select 值1 as 列1,值2 as 列2,值3 as 列3 from dual;

示例：

insert all into Student(id,name,age) 
into Student(id,name,age) values ('1','李三',18) 
into Student(id,name,age) values ('2','王小二',5) 
select '3' as id,'张六' as name,15 as age from dual;

---

方法b：

insert into 表1(列1,列2,列3) 
select 值1,值2,值3 from dual 
union all 
select 值1,值2,值3 from dual 
union all 
select 值1,值2,值3 from dual;

示例：

insert into student(id, name, age) 
select '1','李三',18 from dual 
union all 
select '2','王小二',5 from dual 
union all 
select '3','张六',15 from dual;

---

方法c：

insert into 表1(列1,列2,列3)
select 列1,列2,列3 from (
    select 值1 列1,值2 列2,值3 列3 from dual
    union all
    select 值1 列1,值2 列2,值3 列3 from dual
    union all
    select 值1 列1,值2 列2,值3 列3 from dual
);

示例：

insert into Student(id,name,age)
select id,name,age from (
    select '1' id,'李三' name,18 age from dual
    union all
    select '2' id,'王小二' name,5 age from dual
    union all
    select '5' id,'张六' name,15 age from dual
);

十、merge合并语句

使用方法：

merge into 并入表 别名1 using 来源表 别名2  
on (条件A) --例如 别名1.列1 = 别名2.列1,...
when matched then --当符合条件A时 可以更新 删除(删除条件为更新条件的子集)
update set 别名1.列1 = 别名2.列1, 别名1.列2 = '常数',... where 条件B  
delete where (条件C) 
when not matched then --当不符合条件A时 可以新增
insert ( 别名1.列1,别名1.列3,别名1.列2,... ) values ( 别名1.列1,null,别名2.列1,... ) where 条件D;

注：四个条件的关系如图所示，条件B，条件D并列为条件A的子集，可以互相交叉，而条件D一定为条件B的自己。当条件超出范围时，所属语句不生效。

参考网址：

Oracle中merge into的使用方法_oracle的merge into_lanxingbudui的博客-CSDN博客

---

原表结构：

student				friends
id	3	4	5	id	3	4	6
name	b	a	a	name	GAN	ASD	BAX
age	3	3	3	age	12	11	11
birthday	null	null	null

错误示例：

merge into Student S using Friends F 
on (S.id = F.id) 
when matched then 
update set S.name = F.name , S.age = F.age Where S.name = 'a' 
delete where (S.id = '3') 
when not matched then 
insert ( S.id,S.name,S.birthday ) values ( F.id,F.name,null );
-- 2 行已合并。

结果：

student
id	3	4	5	6
name	b	ASD	a	BAX
age	3	11	3	null
birthday	null	null	null	null

原因：delete where (S.id = '3') 语句中，条件S.id = '3'，并不在update的条件之内，故此语句未执行。

---

正确示例1：

merge into Student S using Friends F 
on (S.id = F.id) 
when matched then 
update set S.name = F.name , S.age = F.age Where S.name = 'a' 
delete where (S.id = '4') 
when not matched then 
insert ( S.id,S.name,S.birthday ) values ( F.id,F.name,null );
-- 2 行已合并。

结果：

student
id	3	5	6
name	b	a	BAX
age	3	3	null
birthday	null	null	null

---

正确示例2：

merge into Student S 
using (
select '4' id,'AAA' name,14 age from dual
union all
select '6' id,'c' name,14 age from dual
) F 
on (S.id = F.id) 
when matched then 
update set S.name = F.name , S.age = F.age
when not matched then 
insert values ( F.id,F.name,F.age,null );
-- 2 行已合并。

结果：

student
id	3	4	5	6
name	b	AAA	a	c
age	3	14	3	14
birthday	null	null	null	null

注：insert语句中，当省略定义列时，后面的值则一一对应并入表的列，缺少列则报错（SQL 错误: ORA-00947: 没有足够的值）。

十一、数据表的复制

使用方法：将表1复制至表2：

create table 表2 as select * from 表1 where 1<>1;

因为oracle中，复制表时，会指定默认表空间。同时，主键与默认值会丢失。则参考如下命令：

1.更改表空间：

alter table 表2 move tablespace 表空间;

2.添加主键

alter table 表2 add constraint 表2_PK primary key("主键");
//示例
alter table TEST2 add constraint TEST2_PK primary key("ID");

注：如果主键为ID，则此处为"ID"。必须是双引号。

3.更改主键索引表空间

alter index 表2_PK rebuild tablespace 表空间;

4.添加主键默认值 SYS_GUID() （时间默认值 sysdate）

alter table 表2 modify 主键 default SYS_GUID();
//示例
alter table TEST2 modify ID default SYS_GUID();

注意：Oracle在创建表或复制表时，对于表名的字数有限制。同时，过长的表名会导致主键索引被挤压。

比如。

表名：XXXX_XXXXXX_X_XXX_XXXX_XXXX。

其主键索引应为：XXXX_XXXXXX_X_XXX_XXXX_XXXX_PK。

但实际上其为：XXXX_XXXXXX_X_XXX_XXXX_XX_PK。

结论：Oracle表明最长只能容纳31个字符。而由于主键索引的限制，表面最长到27个字符，则后续字符会被忽略。故而，建表时，表名推荐在20个字符以内。

十二、切换数据库实例SID

打开注册表：HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_ORADb10g_home1下面将Oracle_SID改为你需要切换的SID。