oracle操作实例

技术杂铺 1.5k 字 次

记录oracle操作实例，不过查询的较多。由于同时涉及多种数据库，会混入其它库的记录。

搭建oracle服务

使用docker搭建。见另文章。

创建表、用户等

用system用户使用navicat连接，成功后。
“其它”->“表空间”->“新建表空间”。
默认“常规”：名称：opt/oracle/oradata/HIGHWAY/system01.dbf，大小1，单位G，路径：’/‘。自动扩展：ON。疑惑：此处的大小表征的是什么？如果后续数据超过1G会如何？
“存储”：文件类型BIGFILE，大小：1，扩展区管理LOCAL。

“保存”，输入表空间名: HIGHWAY。

用户->用户，“新建用户”，输入用户名称，密码，选择刚才创建的表空间。“成员属于”，勾选DBA所有项。“保存”。

（似乎要创建用户才能看到数据库）

连接工具

（注：本节待移动）
navicat，该工具可连接多种数据库。

可在不同数据库中迁移，“工具”->“数据传输”。实际发现，此方式迁移的表的字段类型，可能会与原表不一致，如把char改为text，会出错。
导出：可导出不同类型文件，如sql文件，txt文件，xml、json，等等。可选要导出的列。

1、原数据库导出为txt，再在新数据库导入。在导出过程，默认字段为text，不一定符合要求。
2、原数据库导出为sql，再在新数据库导入。sql文件格式不一定通用，要修改，再导入。

实践总结

用户A访问其它表空间OTHER_TABLESPACE：

1
2
3
4
5
6
7
8
9
10
11
12
alter user A quota unlimited on OTHER_TABLESPACE;

grant all on SSOUSER.INSTITUTION to FEE;

grant all on SSOUSER.INSTITUTION_VERSION to FEE;


grant all on SSOUSER.TOLL_SECTION to FEE;

alter user FEE quota unlimited on TOLL_DATA;


命令行导入：

1
2
3
sqlplus fee/123456@//localhost:1521/ORCLCDB

@/opt/oracle/oradata/share/foo.sql

问题

创建的用户无权限

创建的用户没有权限，提示user test lacks create session privilege logon denied。解决：

1
2
3
sqlplus /nolog
onn /as sysdba
grant create session to test

create table xxx ()缺失右括号。 

1
alter user FEE default tablespace FEE quota 500 M on FEE;

密码过期

oracle密码过期，程序抛出异常，提示：ORA-28001: the password has expired。使用 navicat 连接也有类似提示。解决： 在 navicat 提示框输入旧、新密码，连接到数据库。
操作如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
查看密码有限期：
select * 
 FROM dba_profiles s 
Where s.profile='DEFAULT' 
AND resource_name='PASSWORD_LIFE_TIME';

查询结果：
DEFAULT	PASSWORD_LIFE_TIME	PASSWORD	180	NO	NO	NO

修改为无期限：
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

由于前面已经改了密码，这里再改回原来的密码（将用户foobar的密码改为123456）：
alter user foobar identified by 123456

类型对应关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
SQL Server 数据类型	Oracle 数据类型
bigint	NUMBER(19,0)
binary(1-2000)	RAW(1-2000)
binary(2001-8000)	BLOB
bit	NUMBER(1)
char(1-2000)	CHAR(1-2000)
char(2001-4000)	VARCHAR2(2001-4000)
char(4001-8000)	CLOB
date	DATE
datetime	DATE
datetime2(0-7)	TIMESTAMP(7)（对于 Oracle 9 和 Oracle 10）；VARCHAR(27)（对于 Oracle 8）
datetimeoffset(0-7)	TIMESTAMP(7) WITH TIME ZONE（对于 Oracle 9 和 Oracle 10）；VARCHAR(34)（对于 Oracle 8）
decimal(1-38, 0-38)	NUMBER(1-38, 0-38)
float(53)	FLOAT
float	FLOAT
地理	BLOB
geometry	BLOB
hierarchyid	BLOB
图像	BLOB
int	NUMBER(10,0)
money	NUMBER(19,4)
nchar(1-1000)	CHAR(1-1000)
nchar(1001-4000)	NCLOB
ntext	NCLOB
numeric(1-38, 0-38)	NUMBER(1-38, 0-38)
nvarchar(1-1000)	VARCHAR2(1-2000)
nvarchar(1001-4000)	NCLOB
nvarchar(max)	NCLOB
real	real
smalldatetime	DATE
int	NUMBER(5,0)
smallmoney	NUMBER(10,4)
sql_variant	N/A
sysname	VARCHAR2(128)
text	CLOB
time(0-7)	VARCHAR(16)
timestamp	RAW(8)
tinyint	NUMBER(3,0)
uniqueidentifier	CHAR(38)
varbinary(1-2000)	RAW(1-2000)
varbinary(2001-8000)	BLOB
varchar(1-4000)	VARCHAR2(1-4000)
varchar(4001-8000)	CLOB
varbinary(max)	BLOB
varchar(max)	CLOB
xml	NCLOB

实例

查询

杂项

1
2
3
判断字段空或非空（即null）
select * from xxx where foo is null
select * from xxx where foo is not null

常用查询

模糊查找：

1
select * from MYDB.STATION where name like '%出口%'

查找交易表中指定的ID，日期和类型，限制5条：

1
2
3
4
5
select * from MYDB.TRADE_INFO 
where ID='001250' 
and to_char(trans_time, 'yyyy-MM-dd HH24:MI:SS') like '2020-08-08 %' 
and TYPE = 1 
and rownum < 5

注:日期转换要用to_char(trans_time, 'yyyy-MM-dd HH24:MI:SS')这种形式。

交易时间最早：

1
2
升序
select * from MYDB.TRADE_INFO g where  rownum < 10 order by g.TRANS_TIME

交易时间最新：

1
2
降序
select * from MYDB.TRADE_INFO g where  rownum < 10 order by g.TRANS_TIME desc

注：这是错误的，先取10条，再排序，不符合逻辑。但未找到合适的。特别在几千万条的记录中找，耗时。

多表查询：

1
2
3
4
5
select a.id, a.id_hex, a.name from TableA A, TableB B where a.name =  b.name and A.id_hex='510156'

select a.id, a.id_hex, a.name from TableA A, TableB B where a.name like "%" || b.name || "%" and A.id_hex='510156'
注：A、B表，各有各的字段，但包含共同的name字段，以此为条件查两表，另一条件为指定id_hex。
第二语句为a的name有多个，但b的name只有一个，属于包含关系。第一语句为相等

查询去重
如果结果有多条，在语句后续添加group by A.id_hex。有时出错，则在select后加DISTINCT关键字。错误示例： 

1
选择列表中的列 'TableA.id_hex' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。 (8120)

查询指定字段有重复（即超过1个）的记录：

1
select * from AAA where version='001'  group by sta having count(sta)>1

索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
创建索引：
CREATE INDEX 索引名 ON 表名(列名);

创建多列的复合索引：
CREATE INDEX 索引名 ON 表名(列名1, 列名2, 列名3, ...);

删除索引：
DROP INDEX 索引名;

查看表中的所有索引：
SELECT * FROM ALL_INDEXES WHERE TABLE_NAME = '表名'

查看表中的所有索引的列：
SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = '表名'

导出

导出查询，将结果导出sql。
按条件查询，得到结果，再点击“导出结果”（在“运行”附近），可保存得到的结果。此法适用同一表中有不同版本的记录（即同一条记录存在多个版本，重复）。如要保存所有记录，直接导出即可。

直接右键某个表，“导出向导…”，可得不同格式文件。“转储SQL文件”，可得含结构和数据，或仅含结构。